Google Sheets is a web-based application for creating and editing spreadsheets. Google Sheets, along with Google Docs and Google Slides, is a part of what Google calls Google Drive. It's similar to how Microsoft Excel, Microsoft Word, and Microsoft PowerPoint are each individual parts within Microsoft Office.
With Google Sheets, you can create and edit spreadsheets directly in your web browser or mobile devices. Multiple people can work simultaneously, you can see people’s changes as they make them, and every change is saved automatically.
With the Denodo Google Sheets Tool Stored Procedure you’ll be able to query the data in your spreadsheets directly in Denodo. With a simple call of the procedure, several data sources and views will be created, but the most important one will be a derived view that will represent the data the same way you can see it directly in Google Sheets, along with some custom configuration.
Google Sheets offers a REST API with capabilities to perform CRUD operations. Before executing the stored procedure, which will create several data sources and views using this API, it is necessary to have a Google Developers Account configured to be able to authenticate with OAuth 2.0:
Now, we can create the tokens needed to authenticate the Denodo data sources that the stored procedure will create. To make this, we will use the OAuth credentials wizards from VDP.
You have to fill in the form with the following parameters:
Once the form is completed, you have to click on Generate the authorization URL:
The wizard generates an URL. Click in Open URL and a webpage will be displayed:
As shown, you now have to copy the URL generated and paste it into the step number 3. Now click in Obtain the OAuth 2.0 credentials and then the click in Copy the credentials to the clipboard. Paste them in a text file, they will be part of the input parameters of the Denodo Google Sheets Tool Stored Procedure.
Note: It’s recommended to generate the tokens in VDP like explained in this section. Using other external tools, including Google's OAuth Playground, may result in not working refresh tokens as they might be generated with a different token endpoint version than the used in VDP.
For running the Denodo Google Sheets Tool Stored Procedure you have to load the denodo-google-sheets-tool-{vdpversion}-{version}-jar-with-dependencies.jar file, using the File > Jar Management menu of the VDP Administration Tool (or File > Extension management in Denodo 7.0).
You can add the stored procedure with the statement CREATE PROCEDURE:
CREATE [OR REPLACE] PROCEDURE <name:identifier> CLASSNAME='com.denodo.connect.google.sheet.storedprocedure.GoogleSheetsToolStoreProcedure' JARS 'denodo-google-sheets-tool-<vdpversion>'; [ FOLDER = <literal> ] [ DESCRIPTION = <literal> ] |
Alternatively to using VQL, you can add the stored procedure clicking Stored procedure on the menu File > New:
You must set a name in the Name field and select the Select Jars checkbox in order to use the Jar file, denodo-google-sheets-tool-stored-procedure-<vdpversion>.
The stored procedure requires the following input parameters:
There are four possibilities:
CALL GOOGLE_SHEETS_TOOL( '<spreadsheet_id>', '<sheet_name>', '<view_name>', '<first_row_column_names', '<num_columns', '<access_token', '<refresh_token>', '<client_id>', '<client_secret>' ); |
SELECT * FROM GOOGLE_SHEETS_TOOL( '<spreadsheet_id>', '<sheet_name>', '<view_name>', '<first_row_column_names>', '<num_columns>', '<access_token>', '<refresh_token>', '<client_id>', '<client_secret>' ); |
SELECT * FROM GOOGLE_SHEETS_TOOL() WHERE SPREADSHEET_ID = 'spreadsheet_id' and SHEET_NAME = 'sheet_name' and VIEW_NAME = 'view_name' and FIRST_ROW_COLUMN_NAMES = 'first_row_column_names' and NUM_COLUMNS = 'num_columns' and ACCESS_TOKEN = 'access_token' and REFRESH_TOKEN = 'refresh_token' and CLIENT_ID = 'client_id' and CLIENT_SECRET = 'client_secret'; |
The Denodo Google Sheets Tool Stored Procedure has an output parameter, RESULT, that shows if the process ended successfully or not.
Once the execution finishes OK, you’ll see a message like this:
This means that the Google Sheets elements where successfully created, but you have to refresh the VDP interface in order to see them. To get this done, just right click in the database where you have executed the Stored Procedure and select Refresh:
Now you can finally see three new elements: the final derived view, which is the view that will represent the selected Google Sheet, and a data source and a base view, structures needed for the derived view to work. In the current example, dv_example is the mentioned final view:
If we execute it, we can see the data stored in the Google Sheet:
The Google Sheets API returns JSON responses where all parameters are defined as Strings, even if they are numbers or other data types. Because of this, the parameters of the views in Denodo will also be text types.
This feature requires reading the whole sheet as we execute a base view that uses the Google Sheets API method that retrieves the whole data in rows, so the filter is applied in memory.