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:
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.
|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
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.
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.
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:
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.
These settings configure the name and location of the resource within Retool.
The name to use for the resource (e.g., Google Sheets).
The folder in which to save the resource.
A brief description of the resource (e.g., Customer data).
These settings configure how Retool connects to Google Sheets.
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.
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.
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.
"title": "Your Google Sheet",
"title": "User info",