Connect to Google Sheets

Learn how to connect your Google Sheets data to Retool.

You can connect to Google Sheets and make it available as a resource. Once complete, you can write queries to interact with the Google Sheets API.

Requirements

All users for Retool organizations on Free or Team plans have global Edit permissions and can add, edit, and remove resources. If your organization manages user permissions for resources, you must be a member of a group with Edit all permissions.

To create your Google Sheets resource, you need:

• REST API connection details.
• OAuth 2.0 or service account authentication credentials.

If Google Sheets is behind a firewall, you must also allow access from Retool's IP addresses. Add these IP addresses to your firewall's allowlist before you create the resource.

Google Sheets authentication options

You can use the following authentication methods when connecting to Google, depending on the type of access you need.

Method	Description
User OAuth	OAuth-based authentication with individual user credentials. Each user is prompted to authenticate with Google Sheets using an OAuth flow, and API calls from Retool are made on behalf of the logged-in user. When using apps built on top of Google Sheets, users can only access and edit sheets that have been shared with them in Google. User OAuth is available on Retool Cloud and in beta for Self-hosted Retool v2.95+. Navigate to the Beta settings to enable.
Shared OAuth	OAuth-based authentication with shared user credentials. The user creating the resource is prompted to authenticate with Google using an OAuth authentication flow, and subsequent API calls from Retool are made on behalf of the user that completed authentication. When building apps on top of Google Sheets, all users in a Retool organization can access and edit sheets that have been shared with the user who completed the authentication process.
Service account authentication	Authenticate with a service account tied to a Google Cloud project. Allows user to give Retool access on a spreadsheet by spreadsheet basis by sharing a spreadsheet with the service account’s email.

OAuth authentication methods

OAuth enables you to quickly authenticate with Google. It also provides some control over the access Retool has to your Google Sheets data.

You can grant Retool either Read and write or Read only access to your Google Sheets data. This option determines the scopes passed with the OAuth request. Retool recommends Read and write so that Retool can read and write spreadsheet data (e.g., create new sheets or update cell rows).

Retool's default OAuth configuration is at the user level. This prompts every user to authenticate. You can also Share Google Sheets credentials between users so that users don't have to authenticate individually.

OAuth and Self-hosted Retool

caution

User OAuth is a beta feature for Self-hosted Retool v2.95+. You can activate this authentication method from the Beta tab in your organization settings.

Self-hosted Retool users must create a Google OAuth client app before completing the OAuth authentication flow.

After you create a Google OAuth client app, use the Google Cloud Platform console to verify that the project in the navigation bar is associated with the client app (under Enabled APIs & Services). You must also enable the following services in the Google API Library dashboard:

• Google Drive API. Retool uses this API to retrieve available spreadsheets.
• Google Sheets API. Retool uses this API to interact with spreadsheets.

Once complete, you can perform the OAuth authentication flow to connect with Google Sheets.

note

If you are already using Okta OpenID SSO, you can safely authorize Google SSO without presenting an additional sign in method. Retool only displays one SSO button on the login page and Okta has priority over Google when both are enabled.

Service account authentication method

Retool recommends using service account authentication when you want to share credentials across users, but limit Retool's access to a subset of spreadsheets. This authentication flow restricts Retool's access to sheets shared with the service account email address only.

To use this authentication method, you need:

• A service account.
• A service account key.

1. Create a Google Sheets resource

Retool can connect to almost any API or database, and has built-in integrations for popular data sources. Sign in to your Retool organization and create a new Google Sheets resource from the Resources tab.

2. Configure the resource

Provide the necessary details to configure the resource so Retool can connect to Google Sheets. Not all settings are required but refer to the Google Sheets documentation to learn what is needed.

General

These settings configure the name and location of the resource within Retool.

Name

The name to use for the resource (e.g., Google Sheets).

Folder

The folder in which to save the resource.

Description

A brief description of the resource (e.g., Customer data).

Credentials

These settings configure how Retool connects to Google Sheets.

Authentication

The authentication type to use. Options include OAuth 2.0 and Service Account. If you select OAuth 2.0, you need to select the access type and click the Connect with OAuth → button.

If you select Service Account, you need to provide a Service account key.

Type

The access type to grant Retool. This option is only available when using the OAuth 2.0 authentication type.

Share credentials between users

Whether to share authentication credentials between users. This option is only available when using the OAuth 2.0 authentication type.

3. Save the resource

Click Create resource to complete the setup. You can then click either Create an app to immediately start building a Retool app or Back to resources to return to the list of resources.

Wrap up

Your Google Sheets resource is now ready to use. You write queries to make API requests by specifying the request method and endpoint, additional parameters to include, and the request body.

To verify you can successfully interact with Google Sheets, write a test query to retrieve some data. The following example retrieves data about the spreadsheet passed in the request.

Request

GET https://sheets.googleapis.com/v4/fef23rdfu2980ur0923u90

Response

{
  "spreadsheetId": "fef23rdfu2980ur0923u90",
  "properties": {
    "title": "Your Google Sheet",
    "locale": "en_US",
    "autoRecalc": "ON_CHANGE",
    "timeZone": "America/Los_Angeles",
    "defaultFormat": {
      "backgroundColor": {
        "red": 1,
        "green": 1,
        "blue": 1
      },
      "padding": {
        "top": 2,
        "right": 3,
        "bottom": 2,
        "left": 3
      },
      "verticalAlignment": "BOTTOM",
      "wrapStrategy": "OVERFLOW_CELL",
      "textFormat": {
        "foregroundColor": {},
        "fontFamily": "arial,sans,sans-serif",
        "fontSize": 10,
        "bold": false,
        "italic": false,
        "strikethrough": false,
        "underline": false,
        "foregroundColorStyle": {
          "rgbColor": {}
        }
      },
      "backgroundColorStyle": {
        "rgbColor": {
          "red": 1,
          "green": 1,
          "blue": 1
        }
      }
    },
    "spreadsheetTheme": {
      "primaryFontFamily": "Arial",
      "themeColors": [
        {
          "colorType": "TEXT",
          "color": {
            "rgbColor": {}
          }
        },
        {
          "colorType": "BACKGROUND",
          "color": {
            "rgbColor": {
              "red": 1,
              "green": 1,
              "blue": 1
            }
          }
        }
      ]
    }
  },
  "sheets": [
    {
      "properties": {
        "sheetId": 0,
        "title": "User info",
        "index": 0,
        "sheetType": "GRID",
        "gridProperties": {
          "rowCount": 1000,
          "columnCount": 27
        }
      }
    }
  ],
  "spreadsheetUrl": "https://docs.google.com/spreadsheets/d/fef23rdfu2980ur0923u90/edit?ouid=1234"
}