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

Denodo Google Sheets Custom Wrapper - User Manual

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

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

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 one used in VDP.

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

  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 “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: If marked, the names of the columns of the base view will be taken from the first row of the Google sheet.
  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.