You are viewing content for 4.9 | 4.8 | 4.7 | 4.6 | 4.5 | 4.4 | 4.3 | Previous Releases


Connectors → Google Sheets

About Google Sheets

Google Sheets is a free, web-based spreadsheet program. It is included as part of Google’s office software. You store a Google Sheet in your Google Drive.

Google Sheets Connector

The Google Sheets Connector is an enhancement of the preexisting Incorta Google Sheets connector that points the data source to a folder in addition to a file. In the previous version of the Google Sheets connector, when you created a Google Sheets data source and shared it with other users, they were able to see all the Google Sheets files in My Google Drive. This version of the Google Sheets connector allows you to control what folders and files are accessible by the data source upon creation.

The Google Sheets connector supports the following Incorta specific functionality:

Feature Supported
CMC Enabled
Encryption at ingest
Incremental loading
Multi File Connection
Performance Optimization
Webhook Callbacks

Deployment Steps

Here are the steps to deploy the Google Sheets connector:

  • Access the Google Sheets API from the Google API Library.
  • If you do not already have a project defined:

    • Select Create Project.
    • Enter a name for the project in Project Name.
    • Click Create. Otherwise, select an existing project and select the OPEN option.
  • Select Enable for the Google Sheets API.
  • Select Create Credentials.

    • Select the OAuth client ID option.
    • In Application type, select Web application.
    • In Name, enter the name of the OAuth 2.0 client.
    • Under Authorized redirect URIs, select the + ADD URI button.
    • In URIs, enter the following:
    • https://<Incorta Host>:<Incorta HTTPS Port>/incorta/service/datasource/oauthRedirect,

      NOTE

      Replace Incorta Host and Incorta HTTPS port with actual values.

    • Select Create.
  • An Oath Client Created window displays containing values for Your Client ID and Your Client Secret. Here are the steps to enter these values into the corresponding CMC properties for the Default Tenant Configuration:

    • Log into the Incorta CMC.
    • In the Navigation bar, select Clusters.
    • In the cluster list, select a Cluster name.
    • In the canvas tabs, select Cluster Configurations.
    • In the panel tabs, select Default Tenant Configurations.
    • In the left pane, select Integration.
    • Enter Your Client ID in Google Drive Client ID.
    • Enter Your Client Secret in Google Drive Client Secret.
    • Select Save.
      Here are the steps to enter these values into the corresponding CMC properties for a specific tenant:
    • Log into the Incorta CMC.
    • In the Navigation bar, select Clusters.
    • In the cluster list, select a Cluster name.
    • In the canvas tabs, select Tenant.
    • For the given tenant, select Configure.
    • In the left pane, select Integration.
    • Enter Your Client ID in Google Drive Client ID.
    • Enter Your Client Secret in Google Drive Client Secret.
    • Select Save.

IMPORTANT

After you specify a Google Drive Client ID and Client Secret, you must restart the entire Incorta cluster, including the CMC, analytics and loader services.

Steps to connect Google Sheets and Incorta

To connect Google Sheets and Incorta, here are the high level steps, tools, and procedures:

RECOMMENDATION

Uniquely name each Google Sheet before you connect it to Incorta to avoid potential errors.

Create an external data source

Here are the steps to create a external data source with the Google Sheets connector:

  • Sign in to the Incorta Unified Data Analytics Platform (UDAP).
  • In the Navigation bar, select Data.
  • In the Action bar, select + NewAdd Data.
  • In the Choose a Data Source dialog, in Other, select Google Sheets.
  • In the New Data Source dialog, specify the applicable connector properties.
  • To test, select Test Connection.
  • Select Ok to save your changes.

NOTE

If you select the lowest folder in the tree, you will see No Data in the Select Directory from dialog. You will have access to the Google Sheets in this folder upon schema creation.

Google Sheets connector properties

Here are the properties for the Google Sheets connector:

Property Control Description
Data Source Name text box Enter the name of the data source
Authorize button Select this button to authenticate your Google account and grant Incorta read access to your Google Sheets. Choose an account to use to access Google Sheets and select the Allow button. The New Data Source dialog will reappear, and the Authorize button will change to Authorized with the name of the Google account to the right.
Browse button Select a folder from the directories shown that contains the Google Sheets you would like to connect to. If you do not choose a folder, you will have access to all folders and files found in My Drive and Shared with me upon schema creation.

Create a schema with the Schema Wizard

Here are the steps to create a Google Sheets schema with the Schema Wizard:

  • Sign in to the Incorta UDAP.
  • In the Navigation bar, select Schema.
  • In the Action bar, select + New → Schema Wizard
  • In (1) Choose a Source, specify the following:

    • For Enter a name, enter the schema name.
    • For Select a Datasource, select the Google Sheets external data source.
    • Optionally create a description.
  • In the Schema Wizard footer, select Next.
  • In (2) Manage Tables, in the Data Panel, navigate the directory tree as necessary to select the Google Sheets. For each Google Sheets file, select the individual sheets to import as tables. You can either check the Select All checkbox or select individual sheets.
  • In the Schema Wizard footer, select Next.
  • In (3) Finalize, in the Schema Wizard footer, select Create Schema.

Create a schema with the Schema Designer

Here are the steps to create a Google Sheets schema using the Schema Designer:

  • Sign in to the Incorta UDAP.
  • In the Navigation bar, select Schema.
  • In the Action bar, select + New → Create Schema.
  • In Name, specify the schema name, and select Save.
  • In Start adding tables to your schema, select Google Sheets.
  • In the Data Source dialog, specify the Google Sheets table data source properties.
  • Select Add.
  • In the Table Editor, in the Table Summary section, enter the table name.
  • To save your changes, select Done in the Action bar.

Google Sheets table data source properties

For a schema table in Incorta, you can define the following Google Sheets specific data source properties as follows:

Property Control Description
Type drop down list Default is Google Sheets
Data Source drop down list Select the Google Sheets external data source
File Name drop down list Select the Google Sheets file
Sheet drop down list Select the individual Google Sheet to add to the schema as a table
Number of rows to skip from header text box Enter the number of rows to skip from the top of the sheet. Since the Google Sheets connector assumes the first row contains the column headers, this property can be used if the column headers are in a subsequent row.
Number of rows to skip from footer text box Enter the number of rows to skip from the bottom of the sheet
Incremental Load toggle Enable incremental loading for the schema table, in which the whole file is loaded and its data upserted into Incorta using the defined key column
Callback toggle Enables the Callback URL field
Callback URL text box This property appears when the Callback toggle is enabled. Specify the URL.

WARNING

An error will occur if you remove a column from a Google Sheet after you create a schema table in Incorta. However, you can add an extra column without error.

View the schema diagram with the Schema Diagram Viewer

Here are the steps to view the schema diagram using the Schema Diagram Viewer:

  • Sign in to the Incorta UDAP.
  • In the Navigation bar, select Schema.
  • In the list of schemas, select the Google Sheets schema.
  • In the Schema Designer, in the Action bar, select Diagram.

Load the schema

Here are the steps to perform a Full Load of the Google Sheets schema using the Schema Designer:

  • Sign in to the Incorta UDAP.
  • In the Navigation bar, select Schema.
  • In the list of schemas, select the Google Sheets schema.
  • In the Schema Designer, in the Action bar, select Load → Load Now → Full.
  • To review the load status, in Last Load Status, select the date.

Explore the schema

With the full load of the Google Sheets schema complete, you can use the Analyzer to explore the schema, create your first insight, and save the insight to a new dashboard.

To open the Analyzer from the schema, follow these steps:

  • In the Navigation bar, select Schema.
  • In the Schema Manager, in the List view, select the Google Sheets schema.
  • In the Schema Designer, in the Action bar, select Explore Data.