Google Sheets

🐕‍🦺 Setup guide

This guide will help you set up Google Sheets as a data source for Zenskar.

🚧

Prerequisites

• A Google Cloud Platform (GCP) project.
• Google Sheets API must be enabled in your GCP project
• You must possess a Service Account Key with access to the Google Sheets spreadsheet you want to replicate
• URL of the Google spreadsheet you want to sync.

Set up GCP

Create a service account

1. Open the Service Accounts page in your Google Cloud console.
2. Select an existing project, or create a new project.

3. At the top of the page, click + CREATE SERVICE ACCOUNT.

4. Enter a name and description for the service account, then click CREATE AND CONTINUE.

5. Under Service account permissions, select the roles to grant to the service account, then click Continue. We recommend the Viewer role.

Generate a key

1. Go to the APIs and services page and click on the email address of the service account you just created.

2. In the Keys tab, click the ADD KEY drop-down menu, and click on the Create new key option.

3. Select JSON as the key type, and click CREATE. This will generate and download the JSON key file that you'll use for authentication.

Enable the Google Sheets API

1. Go to the API Library page.
2. Ensure that the correct project is selected at the top of the page.
3. Find and select the Google Sheets API from the API library.
4. Click ENABLE.

📚

Note

If your spreadsheet is viewable by anyone with its link, no further action is needed. If not, give your Service Account access to your spreadsheet.

⚙️ Set up data source and type

1. Log into your Zenskar account.
2. In the left navigation bar, click Metering > Data Sources. In the top-right corner, click + ADD DATA SOURCE.
3. In the Set Up Source section of the Add New Data Source page, enter a name for the Google Sheets data source connection.
4. Select Google Sheets from the Source Type drop-down menu.

⚙️ Configure data source

Configure the following options in the Source Config section of the Add New Data Source page:

1. In Row Batch Size, define the number of records you want the Google API to fetch at a time. The default value is 200.

🚧

Note

The Google API rate limit is 100 requests per 100 seconds per user and 500 requests per 100 seconds per project. Zenskar respects these rate limits and batches requests to the API in order to efficiently pull data.

2. Select the Service Account Key Authentication option from the Authentication Type drop-down menu.
3. In the Service Account Information textbox, enter your service account key in JSON format.

📚

Information

Zenskar's Google Sheets data-source connector pulls data from a single Google Sheets spreadsheet. To replicate multiple spreadsheets, set up multiple Google Sheets data sources.

🚧

Permissions

Please make sure the Service Account has the Project Viewer permission.

👍

Security

Zenskar only accesses the spreadsheet you want to replicate. We do not access any other file in your Google Drive.

4. In the Spreadsheet Link textbox, enter the link to the Google Sheets spreadsheet. To get the link, go to the Google spreadsheet you want to sync, click Share in the top right corner, and click Copy link.
5. Click on the SAVE SOURCE button.
6. You will be taken to the Sync Required Tables & Columns page where you can select the tables and columns you would like to sync with Zenskar.

Each individual sheet in a Google Sheets spreadsheet is treated as a separate table in Zenskar's database.

You can choose which sheet to sync with Zenksar.

All columns within a sheet are treated as columns in corresponding tables in the destination database.

You can choose which columns to sync with Zenksar.

7. Click on the FINISH SETUP button.