Google Sheets

Google Sheets (opens new window) is a powerful cloud application that enables real-time team collaboration on spreadsheets, available on any device.

Workato enables you to automate data entry into Google Sheets as part of a workflow. For example, configure this connector to add completed lead intake forms to a spreadsheet automatically. You can also read data from Google Sheets and transfer it to another app, such as moving respondents who completed a Google Form into a marketing campaign app or a CRM (customer relationship management) system.

API version

The Google Sheets connector uses Google Sheets API v4 (opens new window).

How to connect to Google Sheets on Workato

The Google Sheets connector supports the following authentication methods:

• OAuth 2.0
• Service account

SERVICE ACCOUNT AUTHENTICATION

You can use a service account to authenticate without a personal user account. For consistent use, Workato recommends service account authentication.

Connection field	Description
Connection name	Give this Google Sheets connection a unique name that identifies to which Google Sheets instance it is connected.
Location	Select the project or folder where you plan to store this connection.
Authentication type	Choose OAuth 2.0 when using a user account and Service account when using a service account to connect to Google Sheets.
GCP Project service account email	Provide the email of your service account. Applicable only to service account authentication.
Private key	Provide the private key of your service account. Applicable only to service account authentication.
Advanced settings	Optional. Adjust the scopes of your connection. Applicable only to service account authentication.
Disable formula	Optional. Specify whether to disable adding or updating rows with formulas in Google Sheets.
Custom OAuth profile	Optional. Select a custom OAuth profile to use for this connection.

OAuth 2.0

Complete the following steps to set up an OAuth 2.0 connection:

1

Sign in to your Workato account and navigate to the project where you plan to add your Google Sheets connection.

2

Click Create > Connection.

3

Search for and select Google Sheets as your connection.

4

Configure the following fields:

Connection field	Description
Connection name	Give this Google Sheets connection a unique name that identifies to which Google Sheets instance it is connected.
Location	Select the project or folder where you plan to store this connection.
Authentication type	Select OAuth 2.0.
Disable formula	Optional. Specify whether to disable adding or updating rows with formulas in Google Sheets.
Custom OAuth profile	Optional. Select a custom OAuth profile to use for this connection.

5

Click Sign in with Google.

6

Sign in with your Google account.

7

Click Allow to enable Workato to access your Google account.

Click Allow to enable Workato to access your Google account

Service account

A Google service account is a specialized Google account associated with a Google Cloud Project (GCP) that can run API requests on your behalf.

Service accounts provide the following benefits:

• Continuous operation: Service accounts ensure that operations continue even if individual user permissions change.
• Dedicated permissions: Service accounts can only access projects that you share with them.
• Dedicated API quotas: You can manage a service account's API quotas through GCP and request quota increases directly from Google.

Refer to the Google service account documentation (opens new window) to learn more about service accounts.

Complete the following steps to set up a service account connection:

1

Sign in to your Google Cloud Platform (GCP) console and enable the Google Sheets API in the API library (opens new window).

2

Refer to the Google Cloud documentation to learn how to complete the following:

• Create a new service account (opens new window) in your GCP project.
• Create a new private key and download the key in JSON format (opens new window).

After you download the key file, you can't download it again.

3

Sign in to your Workato account and navigate to the project where you plan to add your Google Sheets connection.

4

Click Create > Connection.

5

Search for and select Google Sheets as your connection.

6

Configure the following fields:

Connection field	Description
Connection name	Give this Google Sheets connection a unique name that identifies to which Google Sheets instance it is connected.
Location	Select the project or folder where you plan to store this connection.
Authentication type	Choose Service account when using a service account to connect to Google Sheets.
GCP Project service account email	Provide the email of your service account.
Private key	Provide the private key of your service account.
Advanced settings > Requested permissions (Service auth scopes)	Optional. Adjust the scopes of your connection. By default, Workato requests the See and download all your Google Drive files and See, edit, create, and delete all your Google Sheets spreadsheets permissions. Selecting permissions from picklist overwrites the default permissions.
Disable formula	Optional. Specify whether to disable adding or updating rows with formulas in Google Sheets.
Custom OAuth profile	Optional. Select a custom OAuth profile to use for this connection.

PRIVATE KEY

Copy the private key from -----BEGIN PRIVATE KEY----- to -----END PRIVATE KEY-----\n, both inclusive, and paste it in the Private key field.

Configure the Google Sheets service account connection

7

Click Sign in with Google.

8

Sign in with your Google account.

9

Click Allow to enable Workato to access your Google account.

Set up a Google service account

Complete the following steps to set up a Google service account:

1

Create a service account (opens new window) in your GCP project.

2

Go to IAM & Admin > Service accounts. Ensure your dashboard is scoped to the project that contains your service account. Check the scope of your dashboard.

3

Click the Email of the service account you intend to use. Click the Email of the service account you intend to use.

4

Copy the service account's Email and save it to configure your connection later.
Copy the account's Email.

5

Go to the KEYS tab.

6

Generate a private key (opens new window) and download it in JSON format. You can only download the key once.

7

Open the JSON file, then copy the entire private key from -----BEGIN PRIVATE KEY----- to -----END PRIVATE KEY-----\n (inclusive) and save it to configure your connection later.

Because your service account doesn't have access to any sheets, you must explicitly share the Google Sheets you plan to run automated workflows on with the email associated with your service account. You can share the specific sheets from within Google Sheets.

Share Google Sheets spreadsheets with your service account email

Enable the Google Sheets API, then return to Workato to finish setting up your connection.

Refer to the Google Workspace documentation to learn more about service accounts (opens new window).