Google Sheets Integration

You can build Retool apps using your Google Sheets data. Your apps can read and write data in Google Sheets like any other data source, allowing you to query data using SQL and combine it with other data sources.

Create a new Google Sheets resource

First, create a new resource to connect to Google Sheets. A resource represents a connection between Retool and your data source. It stores authentication and connection details so you can use the data in your apps.

To get started, log in to Retool and select the Resources tab. Click Create new and then select Google Sheets. Enter a name for the new resource (e.g., David's Google Sheets).

๐Ÿ“˜

You can configure multiple resources of the same type. Use descriptive names to help distinguish between similar resources.

Authenticate with Google

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

MethodDescription
User OAuthOAuth-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+. You can activate this authentication method from the Beta tab in your organization settings.
Shared OAuthOAuth-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 using apps built on top of Google Sheets, all users in a Retool organization access and edit sheets that have been shared with the user who completed the authentication process.
Service account authenticationAuthenticate 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. You also have some control over the scope of permissions that Retool has with 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. We recommend 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. If you want all users in your organization to share access using your Google credentials, rather than be prompted to authenticate themselves, check the Share Google Sheets credentials between users option.

Google Sheets OAuth optionsGoogle Sheets OAuth options

Select the scope and whether you want to share credentials between users, then click Connect to Google Sheets to begin Google's authorization flow. After completing the authorization flow, you can click Create a new app to start building an app with Google Sheets data.

OAuth and Self-hosted Retool

๐Ÿšง

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.

Once you have created 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. We use this API to retrieve the list of spreadsheets available for use in Retool.
  • Google Sheets API. We use this API to interact with spreadsheets.

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

๐Ÿ“˜

What if I'm already using Okta OpenID SSO?

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

Service account authentication method

We recommend using service account authentication if you'd like 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.

First, create a new service account to use for Retool. Navigate to the Google Sheets API credentials in the Google Cloud Platform console, confirm that project selected in the navigation bar matches the project you'd like to use, and verify that the Google Sheets API and Google Drive API are enabled for the project.

GCP consoleGCP console

Click Create credentials and select Service account. Give your service account a name (e.g., Retool Sheets resource) so it's easy to identify and click Done.

Next, navigate to the service account settings to create a new key. Select Keys, then click Add Key > Create a new key. Select JSON as the key type and click Create. Download the JSON key for the service account and make sure to save it somewhere safe.

Finally, navigate back to the Google Sheets resource configuration screen in Retool and enter the newly created JSON private key. Retool displays a confirmation message that includes the email address associated with the service account. Make a note of this email address as you must share with it any sheets that you want Retool to access. We recommend including this email address as part of the resource name so it's easy for users to identify when they write queries.

Google Sheets resource configurationGoogle Sheets resource configuration

Query Google Sheets

You can now select your new Google Sheets resource from the Resource dropdown when creating queries in your Retool apps. Refer to our Interacting with Google Sheets tutorial for a guide to querying, and writing back to, Google Sheets with Retool.

Depending on how data is formatted in the spreadsheet, the Google Sheets API may return all values as strings (e.g.,"0.05"). You can use a transformer to parse number values and use them across your app without any additional casting or parsing. You can include the following JavaScript snippet as a transformer for any Google Sheets query to parse number values.

const dataAsObject = formatDataAsObject(data);
const parsedData = {};
_.each(dataAsObject, function (v, k) {
  parsedData[k] = v.map((d) => (isNaN(d) ? d : Number(d)));
});
return parsedData;

Using a query transformer to parse numerical values in a Read data Google Sheets query responseUsing a query transformer to parse numerical values in a Read data Google Sheets query response


Did this page help you?