Connectors → Google Sheets

About Google Sheets

Google Sheets is a free, web-based spreadsheet program. It is included as part of Google’s software office. Google Drive stores Google Sheet files.

About the Google Sheets Connector

In this release, the Google Sheets Connector allows you to control what folders and files are accessible by the data source upon creation. 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 browse all Google Sheets files in My Google Drive.

The Google Sheets connector supports the following Incorta specific functionality:

Feature	Supported
Chunking
Data Agent
Encryption at Ingest
Incremental Load	✔
Multi-Source	✔
OAuth	✔
Performance Optimized	✔
Remote
Single-Source	✔
Spark Extraction
Webhook Callbacks	✔

The Google Sheets connector requires the following:

Security configurations for the Google Sheets Connector
Default and Tenant Configurations for the Google Sheets Connector

Some configurations may differ if you are deploying the Google Sheets connector in an Incorta Cloud instance.

Note

The Security and Default and Tenant Configurations for the Google Sheets connector are the same as those required for the Google Drive Connector. These configurations need to be completed one time only by the administrator in order to support use of one or both connectors.

Security configurations for the Google Sheets connector

Security and system administrators typically address the security requirements for the Google Sheets Connector. The Google Sheets Connector uses the Google Sheets API, and as such, requires the following:

HTTPS for the Incorta Cluster
G Suite account
Google API Project with a readonly scope

HTTPS for the Incorta Cluster

In order to use the Google Drive connector or Google Sheets connector, you must configure your Incorta Cluster to use HTTPS (Hypertext Transfer Protocol Secure). Typically, a System Administrator for the operating system with root access configures an Incorta Cluster for HTTPS.

Important

The Google APIs do not accept self-signed security certificates. You must use a valid certificate for a known public domain.

To learn more about how to configure HTTPS with TLS/SSL for your Incorta Cluster using Let’s Encrypt, Certbot and OpenSSL, please review Security → HTTPS for Apache Tomcat with OpenSSL.

Client Credentials

A Security or System Administrator who manages your organization’s G Suite accounts as well as the Incorta Cluster creates the required Google API project. The G Suite Account must sign in to the Google Developers Console, create a project, create an OAuth consent screen, and then create the client credentials.

To learn more about how to create client credentials for a Google API project, please review Security → Client credentials for a Google API project

Default and Tenant Configurations for the Google Sheets Connector

A Cluster Management Console (CMC) administrator for your Incorta Cluster must configure each tenant to use the client credentials.

Important

After configuration, the Incorta Cluster must be restarted including the CMC, the Analytics Service, Loader service, and any Add-ons.

Specify the client credentials for the Default Tenant Configuration

Here are the steps to specify the required properties for the Default Tenant Configuration:

Sign in to the 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.
In the right pane, specify
- Your Client ID in Google Drive Client ID.
- Your Client Secret in Google Drive Client Secret.
Select Save.

Specify the client credentials for a Tenant Configuration

Here are the steps to specify the required properties for a specific tenant:

Sign in to the 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.
In the right pane, specify:
- Your Client ID in Google Drive Client ID.
- Your Client Secret in Google Drive Client Secret.
Select Save.

Restart the Incorta Cluster

Here are the steps to restart an Incorta Cluster from the Cluster Management Console (CMC).

As the CMC Administrator, sign in to the CMC.
In the Navigation bar, select Clusters.
In the cluster list, select a Cluster name.
In the Details canvas tabs, in the footer bar, select Restart.

Steps to connect Google Sheets and Incorta

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

Create an external data source
Create a schema with the Schema Wizard
or, Create a schema with the Schema Designer
Load the schema
Explore the schema

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 Direct Data Platform.
In the Navigation bar, select Data.
In the Action bar, select + New → Add Data Source.
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.

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.

Note

When you create a data source and select Browse, within the Select Directory from dialog you only see folders, and will not see individual Google Sheets within the folder. You can select All to include Google Sheets within all Shared Directories, or select an individual folder to access the Google Sheets within the folder and its child folders. If you select the lowest folder in the tree, you will see a No Data message in the Select Directory from dialog. You will have access to the Google Sheets in this folder 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 Direct Data Platform.
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 Direct Data Platform.
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
Page Size	text box	Specify the number of records to discover and retrieve simultaneously. This option is especially beneficial when dealing with large sheets.
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 Direct Data Platform.
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 Direct Data Platform.
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: