# 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:
SERVICE ACCOUNT AUTHENTICATION
Connecting using a service account allows you to authenticate your Google Sheets connection without tying the connection to a specific person in your organization. For production use cases, we highly recommend 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:
Sign in to your Workato account and navigate to the project where you plan to add your Google Sheets connection.
Click Create > Connection.
Search for and select Google Sheets as your connection.
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. |
Click Sign in with Google.
Sign in with your Google account.
Click Allow to enable Workato to access your Google account.
Click Allow to enable Workato to access your Google account
# Service account
Service accounts in the Google Sheets connector are 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 and can be used to run API requests on your behalf.
Service accounts present numerous benefits:
- Authentication can be used in Google Sheets to ensure that the solution continues running even if individual users' permissions change.
- Service accounts have their own permissions, so they can only access the Google Sheets that are shared with them.
- Service accounts consume their own API quotas, which can be managed through GCP. You can reduce the risk of 429 rate limit errors by requesting quota increases directly from Google.
Complete the following steps to set up a service account connection:
Sign in to your Google Cloud Platform (GCP) console and enable the Google Sheets API in the API library (opens new window).
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.
Sign in to your Workato account and navigate to the project where you plan to add your Google Sheets connection.
Click Create > Connection.
Search for and select Google Sheets as your connection.
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 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. |
| 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
Click Sign in with Google.
Sign in with your Google account.
Click Allow to enable Workato to access your Google account.
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
Refer to the Google Workspace documentation to learn more about service accounts (opens new window).
Last updated: 8/29/2024, 3:00:41 PM