• User Manuals /
  • Denodo Google Sheets Custom Wrapper - User Manual

Denodo Google Sheets Custom Wrapper - User Manual

Last modified on: 29 Nov 2022

Download original document


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 Custom Wrapper you will be able to query the data in your spreadsheets directly in Denodo. With a datasource based on this CW, you can create base views that point to the desired sheets and query their data.

Requirements

Get OAuth2 credentials

Google API configuration

Before using the Google Sheets API with this Custom Wrapper, it is necessary to have a Google Developers Account configured to be able to authenticate with OAuth 2.0:

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

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

  1. Create Credentials > OAuth Client ID.

  1. Choose Application type : “Desktop app” and give it a name.
  1. Note that if you are accessing VDP from a different domain than localhost, in this step you will need to select “Web application” and fill the Authorized redirect URLs field with the appropriate URL(s) which should be something like:
    http://<host_name>:<port>/oauth/2.0/redirectURL.jsp
    Otherwise, if you use “Desktop app”, you’ll be prompted with a "Error 400: redirect_uri_mismatch” error.

    In this scenario, using the     Google OAuth 2.0 Playground tool is the preferred way. If you use this tool, you will also need to add to the Authorized redirect URLs this URL https://developers.google.com/oauthplayground, so the refresh token can be used to obtain new access tokens. See “Alternative method: Google OAuth 2.0 Playground” section to check out how to obtain the OAuth2 tokens using this tool.

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

  1. Client ID.
  2. Client Secret.

  1. 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. Note that this method should be used only when the application created in the previous chapter is a Desktop app type. If you created a web application, you should go to the Alternative method: Google OAuth 2.0 Playground” section in order to obtain the OAuth2 tokens.

You have to fill in the form with the following parameters:

  • Authentication grant: Authorization code grant
  • Client identifier: Obtained from the “Google API configuration” section
  • Client secret: Obtained from the “Google API configuration” section
  • 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 step number 3. Now click on Obtain the OAuth 2.0 credentials and then click on Copy the credentials to the clipboard. Paste them into a text file, they will be part of the input parameters of the Denodo Google Sheets Tool Custom Wrapper data source.

Alternative method: Google OAuth 2.0 Playground

As a first step to get the OAuth2 tokens using the Google OAuth 2.0 Playground, you’ll need to make sure the Google Sheets API is enabled for the account. This is explained in the “Google API configuration” section in step 4.

After that, access https://developers.google.com/oauthplayground/ and do the following:

  1. Select & authorize APIs: Check the https://www.googleapis.com/auth/spreadsheets option and click on Authorize APIs.

    Note that you should check the     Use your own OAuth credentials checkbox under OAuth 2.0 configuration options, as well as fill the OAuth Client ID and OAuth Client secret parameters with the values generated in the “Google API configuration” section of this manual.


  2. Google will ask you to select the account you’re going to use to authenticate to the service. Select the desired account and accept the permissions request.
  3. You’ll be redirected back to the Google OAuth 2.0 Playground with an Authorization Code. Now click on the Exchange authorization code for tokens button. The application will generate, finally, an access token and a refresh token.

Installation

The Denodo Google Sheets Custom Wrapper distribution consists of:

  • /dist:

  • denodo-google-sheets-custom-wrapper-{denodo-version}-{version}.jar. The custom wrapper.
  • denodo-google-sheets-custom-wrapper-{denodo-version}-{version}-jar-with-dependencies.jar. The custom wrapper plus its dependencies. This is the wrapper we recommend to use, as it is easier to install in VDP.
  • denodo-google-sheets-custom-wrapper-{denodo-version}-{version}-sources. The custom wrapper source code.

Import the Custom Wrapper

To import the custom wrapper, follow these steps:

  1. In the VDP Administration Tool, go to:
  • Denodo 6.0: File → Jar management
  • From Denodo 7.0: File → Extension management

  1. Click on “Create” button and select the “denodo-google-sheets-custom-wrapper-{denodo-version}-{version}-jar-with-dependencies.jar” file, located in the dist folder of the Denodo Google Sheets Custom Wrapper distribution, downloaded from the Denodo Support Site.

Create the Google Sheets data source

To create a new Google Sheets custom data source:

  1. In the VDP Administration Tool, go to: File → New… → Data source → Custom

  1. In the form displayed, do the following:

  • Set a name for the new Google Sheets data source in the “Name” field.

  • Click on “Select Jars” and select the file imported in the previous section.
  • The “Class name” field must be filled with:

com.denodo.connect.google.sheets.wrapper.GoogleSheetsWrapper

  • Click on the Refresh button. The Route parameter will be displayed.


  • To configure the Route parameter:
  • Configuration:
  1. HTTP method (mandatory): GET.
  2. URL (mandatory): Here you have to insert the URL used to edit the Google sheet. You can get it directly by copying and pasting it from the browser navigation bar. Note that you should copy only the part of the URL until the identifier. The rest of the url should be discarded.

    One example of spreadsheet URL could be:
    https://docs.google.com/spreadsheets/d/<spreadsheet_id>/edit#gid=0.

    What you should set for the     URL input parameter would be:
    https://docs.google.com/spreadsheets/d/<spreadsheet_id>/

  • Authentication:
  1. If the spreadsheet is set to public and no permissions are required to access it, you can set Authentication to “Off” (the default option). But if the spreadsheet is protected you’ll have to configure the authentication as follows, using the data generated in the “Generate OAuth2 tokens in VDP section”.
  1. Authentication: OAuth 2.0
  2. Authentication grant: Authentication code grant.
  3. Client identifier: Generated in the “Google API configuration” section.
  4. Client secret: Generated in the “Google API configuration” section.
  5. Authentication method: Include the client credentials in the body of the request.
  6. Access token: Generated in the “Generate OAuth2 tokens in VDP” section.
  7. Request signing method: “Authorization” request header.
  8. Refresh token: Generated in the “Generate OAuth2 tokens in VDP” section.
  9. Token endpoint URL:
    https://accounts.google.com/o/oauth2/token.

    Note that, if you are using the     Google OAuth2 Playground tool to obtain the tokens, the token endpoint URL must be: https://oauth2.googleapis.com/token  

  1. You can test the connection to check if all the parameters were set properly and then click “OK” to close the Route configuration.

  1. Click on the “Save” button.

Usage

To create a new base view using the Google Sheets data source created in the previous section:

  1. Double-click on the Google Sheets data source and then click on “Create base view”.

  1. Set the parameters as follows:
  • Sheet Name (mandatory): The name of the sheet of the desired Google spreadsheet.
  • Range (optional): By default, the Custom Wrapper will read all the columns with data in the sheet. In this parameter you can set the specific range of cells that will be retrieved in the base view. You can find more information about this parameter in the “Range parameter” section.
  • Has Headers (optional): If checked, the names of the columns of the base view will be taken from the first row of the Google sheet.
  • Headers Rows (optional): If the Has Headers parameter is checked, you can specify manually the number of rows the columns headers have.
  1. Now you can see the new base view. You can configure the last properties, such as the view name, and then click on “Save”.


  2. Once saved, you can start querying the new base view. For example, select * from bv_google_sheets_dates:

Range parameter

As said before, with the Range parameter you can specify the exact portion of the sheet that you want for your base view. These are some examples of ranges that you can define:

  • A1:B10 - A range from cell A1 through B10
  • 5:7          - Rows 5-7
  • D:F          - Columns D-F
  • A:A70          - The first 70 cells in column A
  • A70:A          - Column A from row 70 to the end
  • B5:5          - B5 to the end of row 5
  • D3:D          - D3 to the end of column D
  • C:C10          - From the beginning of column C to C10

Note that with this parameter you can get only the cells you want for one view. But you are also able to, for example, get different tables from the same sheet.

Example: Multiple tables in one sheet

Let’s consider this sheet, called Multiple, with two tables inside it:

We can have two different base views by setting up the right range in the base view creation:

  • BV 1:



  • BV 2:


Limitations

Time and Duration Types

Google Sheets supports by default these data types:

They are all supported in the Denodo Google Sheets Custom Wrapper, but we have some limitations with the Time and Duration types:

  • Denodo 6 and later:
  • Duration: In the base views, fields will be defined as Text and it’s not allowed to filter queries using parameters of this type.
  • Denodo 6:
  • Time: In the base views, fields will be defined as Date and it’s not allowed to filter queries using parameters of this type.

Wrong format / type in a column

In the previous explained limitation we could see the available data types for Google Sheets. Anyway, you can insert values that don’t match the data type set for a column directly into the sheet. For example:

The “NUMBER” column is defined as Number type, but you can insert a String as shown. For this kind of situations, the Google Sheets Custom Wrapper will set that values to null, as they don’t match the type defined for that column in the base view:

Set a sheet name that does not exist

It’s mandatory to set a sheet name when you create a base view with this Custom Wrapper. But Google Sheets will not validate if a sheet exists with that name in the spreadsheet, so if you provide a sheet name that doesn’t exist, the API will return the first sheet of the spreadsheet.

Delegated operators

Due to restrictions in the underlying API, only the following operators will be delegated to Google Sheets: <=, <, >, >=, =, !=, <>, as well as combinations of these operators using and, or and not. All other filtering combinations will be computed in memory by VDP.

Filter views

Due to restrictions in the underlying API, no support is provided for querying filter views defined in Google Sheets.