Skip to main content

​
Intro

Google Spreadsheets is a Web-based application that enables you to create, update, modify spreadsheets and share the data live online. To learn more about the Google Sheets API, see (https://developers.google.com/sheets/api/guides/concepts ). You can connect to your Google Sheets account in the Data Center. This topic discusses the fields and menus that are specific to the Google Sheets connector user interface. To add DataSets, set update schedules, and edit DataSet information, see Adding a DataSet Using a Connector.

​
Prerequisites

To connect to your Google Sheets account and create a DataSet, you must have the Service Token JSON key. To generate a Google Sheets service account JSON key, do the following:
  1. Log into Google Cloud Console using https://console.cloud.google.com/.
  2. Select the project.
  3. On the left pane select IAM & Admin > Service Accounts.
  4. Click + CREATE SERVICE ACCOUNT.
  5. Under Service account details, enter the service account name and click CREATE AND CONTINUE.
  6. From the Select a role filter, under Quick Access select Basic > Owner and click CONTINUE.
  7. Click Done.
  8. On the left pane select IAM & Admin > Service Accounts and click the service account created.
  9. Click Keys > Add Key > Create new key. A pop-up window opens.
  10. Select the Key type as JSON and click Create.
  11. The JSON file containing the JSON key downloads automatically.
  12. Save the downloaded file.

​
Enabling Google Sheets API

To enable the Google Sheets API, perform the following steps:
  1. Navigate to the GCP Console.
  2. On the left pane, under APIs and services, select Library.
  3. Search and click the Google Sheets API.
  4. On the top of the page, click Enable.
Note: The Google service account’s email address must be added as a Google Sheet Viewer.

​
Connecting to Your Google Sheets Account

This section enumerates the options in the Credentials and Details panes in the Google Sheets Connector page. The components of the other panes in this page, Scheduling and Name & Describe Your DataSet, are universal across most connector types and are discussed in great detail in Adding a DataSet Using a Connector.

​
Credentials Pane

This pane contains fields for entering credentials to connect to your Google Sheets account. The following table describes what is needed for each field:

Field

Description

Service Token JSON

Copy and paste the complete contents of the.json file, generated from your Google API Console.

Once you have entered valid Google Sheets credentials, you can use the same account any time to create a new Google Sheets DataSet. You can manage connector accounts in the Accounts tab in the Data Center. For more information about this tab, see Manage Connector/Adapter Accounts.

​
Details Pane

Menu

Description

File SelectionThe Spreadsheet ID is selected from the drop-down list.
Spreadsheet IDEnter the Spreadsheet ID. The Spreadsheet ID is the last string of characters in the URL of your spreadsheet. For example, in the URL,https://docs.google.com/spreadsheets/d/12wVPoRgSLYcD1CBHpN4dpQiPHRNqV7azwGF4JbKoXaw/edit#gid=0,{” ”}the spreadsheet ID is, 12wVPoRgSLYcD1CBHpN4dpQiPHRNqV7azwGF4JbKoXaw .
Worksheet NameSelect the worksheet name from the drop-down list.
Spreadsheet LayoutSelect the spreadsheet layout from the drop-down list.
Manually Enter Cell RangesSelect this checkbox, if you want to manually enter the cell range.
Column Header Cell RangeSpecify the cell range for the header column(For example, A1:N1).
Column Data Cell RangeSpecify the cell range for the data columns (Optional, for example, A2:N27).
Row Header Cell RangeSpecify the cell range for the header row (For example, A1:A27).
Row Data Cell RangeSpecify the cell range for the data rows (Optional, for example, B1:N27).
Date Format

Select the date format from the drop-down list.

There are two date formats:

  • Month Day Year (MMDDYYYY)
  • Day Month Year (DDMMYYYY)

​
Other Panes

For information about the remaining sections of the connector interface, including how to configure scheduling, retry, and update options, see Adding a DataSet Using a Connector.