Denodo Google Sheets Tool - User Manual

Last modified on: 14 May 2020

Overview

Google Sheets is a web-based application for creating and editing spreadsheets. 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.

Requirements

Get OAuth2 credentials

Google API 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:

Access the Google Developers Console at https://console.developers.google.com.

Go to the “Credentials” tab and create a new Project clicking:

Create Credentials > OAuth Client ID.

Choose Application type : “Other” and give it a name.

Once the Developer account is configured two values will be used for the Oauth authentication, they will be known both to Google and the application:

Client ID.

Client Secret.

Make sure the Google Sheets API is enabled for the account. To do so, go to the Dashboard section in the Developer Console and click on Enable if needed.

Generate OAuth2 tokens in VDP

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:

Authentication grant: Authorization code grant

Client identifier: Obtained from the Credentials section of the Google sheets API

Client secret: Obtained from the Credentials section of the Google sheets API

Authentication method: Include the client credentials in the body of the request

Token endpoint URL: https://accounts.google.com/o/oauth2/token

Authorization server URL: https://accounts.google.com/o/oauth2/auth

Scopes: https://www.googleapis.com/auth/spreadsheets

Once the form is completed, you have to click on Generate the authorization URL:

The wizard generates a 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.

Installation

Importing the Stored Procedure

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 and later).

Adding the Stored Procedure: VQL Shell

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> ]

Adding the Stored Procedure: Virtual DataPort Administration Tool menu

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>.

Usage

The stored procedure requires the following input parameters:

SPREADSHEET_ID (mandatory): the identifier of the Google spreadsheet. You can extract it from the url, which will be something like https://docs.google.com/spreadsheets/d/<SPREADSHEET_ID>/edit

SHEET_NAME (mandatory): You’ll find it in the tabs of the spreadsheet. In this example, it would be sheet2.

VIEW NAME (mandatory): The name you want for the derived view which is going to represent your Google sheet in VDP.

FIRST_ROW_COLUMN_NAMES (mandatory): If set to true, the column names in the view will be extracted from the first row of the Google sheet. If false, they will be named with the same alphabetic sequence used in any spreadsheet.

NUM_COLUMNS: If set to null, the number of columns will be determined by the number of columns with data in the sheet. If a number is specified, the view will have exactly that number of columns.

ACCESS TOKEN (mandatory): Obtained in “Generate OAuth2 tokens in VDP” section.

REFRESH TOKEN (mandatory): Obtained in “Generate OAuth2 tokens in VDP” section.

CLIENT ID (mandatory): Obtained in “Generate OAuth2 tokens in VDP” section.

CLIENT SECRET (mandatory): Obtained in “Generate OAuth2 tokens in VDP” section.

Executing the Stored Procedure

There are four possibilities:

Click the Execute button in the dialog that displays the schema of the stored procedure. The VDP Administration Tool will show a dialog to enter the input values.

Execute the CALL statement from the VQL Shell:

CALL GOOGLE_SHEETS_TOOL(

'<spreadsheet_id>',

'<sheet_name>',

'<view_name>',

'<first_row_column_names',

'<num_columns',

'<access_token',

'<refresh_token>',

'<client_id>',

'<client_secret>'

);

Execute as a SELECT statement in the VQL Shell:

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>'

);

Execute as a SELECT statement in the VQL Shell:

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.

Refresh the VDP interface

Once the execution finishes OK, you’ll see a message like this:

This means that the Google Sheets elements were 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:

Limitations

The views will only have String types

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.

Using first row of Google Sheet to extract column names

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.