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 write data to a Google spreadsheet automatically as part of a workflow, for example, leads filling out a form will be moved to a spreadsheet. You can also read rows of data from Google spreadsheets and move it to another app, for example, moving respondents who had filled out a Google Form from Google Sheets into a marketing campaign app, or into a CRM.

API version

Workato's Google Sheets connector currently uses both Google Sheets API v4 (opens new window) and API v3 (opens new window)

How to connect to Google Sheets on Workato

The Google Sheets connector allows two types of authentication: OAuth 2.0 and Service Account.

TIP

Connecting via Service Account allows you to authenticate your Google Sheet connection without tying the connection to a specific person in your organization. For production use cases, we highly recommend service account authentication.

Field	Description
Connection name	Give this Google Sheets connection a unique name that identifies which Google Sheets instance it is connected to.
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 (Applicable only to Service Account Auth)	The email of your service account.
Private key (Applicable only to Service Account Auth)	The private key of your service account.
Advanced settings (Applicable only to Service Account Auth)	Adjust the scopes of your connection.

OAuth 2.0

Connecting to Google Sheets connector is as simple as signing in to the Google account.

Select Link your account:

Sign in with your Google account. Your Google account should have Edit permission to all spreadsheets that you will use in Workato.

And you are good to go:

Service Account

Service Account in Google Sheets connector is used to authenticate to Google Sheets using a Google Cloud service account. A service account is a special type of Google account that is associated with your Google Cloud Project that can be used to run API requests on your behalf.

Service accounts present numerous benefits:

1. Authentication can be used in Google Sheets to ensure that the solution will continue running even if individual users' permissions change
2. Service accounts have their own permissions so they may only access the Google sheets which are shared with them.
3. Service accounts consume their own API quotas which can be managed through GCP. You may reduce the risks of 429 rate limit errors by requesting for quota increases directly from Google.

Read more about service accounts here (opens new window).

Enabling the Google Sheets API

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

Creating a Service account

To create a service account, follow this guide to create a new service account (opens new window) in your GCP project. Next, this obtain private key (opens new window) and download the key in JSON format. Note that after you download the key file, you cannot download it again.

Copy the private key from -----BEGIN PRIVATE KEY----- to -----END PRIVATE KEY-----\n both inclusive and paste it as input on Workato.

Sharing required Google sheets with Service account

Lastly, since your service account does not have access to any sheets, you will need to explicitly share your Google Sheets which you need to run automations on.

You may do so from the Google Sheets UI by sharing the sheet with your Service Account's email.