Skip to main content

Connect to Google Sheets

Connect Google Sheets to Retool to read, write, and manage spreadsheet data.

Google Sheets is a cloud-based spreadsheet application. Retool connects to Google Sheets using either OAuth 2.0 or a Google service account, and provides a query editor with Retool-native action types for reading, writing, and managing spreadsheet data without writing API calls.

After you create a Google Sheets resource in Retool, you can:

  • Display spreadsheet data in tables, charts, and other components.
  • Append rows from forms, file uploads, and other user inputs.
  • Update individual cells, ranges, or sets of rows by primary key.
  • Create, copy, and delete spreadsheets and sheets.
  • List available spreadsheets and inspect sheet metadata.

Before you begin​

To connect Google Sheets to Retool, you need the following:

  • Google account: A Google account or Google Workspace user with access to the spreadsheets you want to query.
  • Retool permissions: Edit all permissions for resources in your organization.

Create a Google Sheets resource​

Follow these steps to create a Google Sheets resource in your Retool organization.

1. Create a new resource​

In your Retool organization, navigate to Resources in the main navigation and click Create new → Resource. Search for Google Sheets and click the Google Sheets tile to begin configuration.

Use folders to organize your resources by team, environment, or data source type. This helps keep your resource list manageable as your organization grows.

2. Configure general settings​

Specify a name and description for the resource that indicates which Google account or workspace it connects to. The description provides more context to users and Assist about how to use the resource.

Example nameExample description
Google SheetsDefault Google Sheets resource for the analytics team. Read and write access for reporting dashboards.
Google Sheets (read only)Read-only Google Sheets resource for displaying spreadsheet data in internal apps.

When using Retool's hosted OAuth app (Cloud) or a custom OAuth app (self-hosted), you typically only need one resource. Each user authenticates individually and can only access their own spreadsheets. Creating multiple resources is usually unnecessary unless you need to support multiple OAuth applications or mix OAuth with service account authentication.

3. Configure authentication​

Configure the connection settings for your Google Sheets resource under the Credentials section of the resource configuration form.

Google Sheets resource configuration form.

Authentication​

Google Sheets supports two authentication methods. Choose based on whether queries should run as the signed-in user or as a non-interactive service identity.

Authentication methodUse cases
OAuth 2.0Interactive apps where each user authenticates with their own Google account. Each user only sees the spreadsheets they have access to.
Service AccountAutomated workflows, scheduled queries, and shared internal apps that need to act as a non-interactive service identity. Spreadsheets must be explicitly shared with the service account's email address.
option A: OAuth 2.0 with Retool's hosted app (recommended)

Retool provides a hosted OAuth 2.0 client for Cloud organizations, so no external configuration is required.

  1. Select OAuth 2.0 as the Authentication method.
  2. Choose the Type:
    • Read only—your Retool queries can only read data from spreadsheets.
    • Read and write—your Retool queries can read values, update rows, append rows, copy sheets, create new spreadsheets, and more.
  3. Optionally enable Share credentials between users to allow all users of the resource to share a single set of credentials. By default, each user authenticates individually.
  4. Click Connect with OAuth and authorize Retool to access your Google Sheets.
  5. Click Create resource to save.
option B: Service Account

Use a service account when queries should run as a non-interactive service identity rather than as the signed-in user.

  1. Create a service account in the Google Cloud Console and generate a JSON key.
  2. In Google Drive, share the spreadsheets the service account needs to access with the service account's email address. For read-only access, grant Viewer permission. For read and write access, grant Editor permission.
  3. In Retool, select Service Account as the Authentication method.
  4. Paste the JSON contents of your service account key into the Service account key field. Retool extracts the service account's email address from the key and displays it for reference.
  5. Click Create resource to save.

Refer to Google's service account documentation for setup details and key rotation guidance.

Type​

The Type setting controls the OAuth scopes used for Sheets API calls. Google Sheets requests scopes for both the Sheets API and the Drive API, since Sheets operations require locating spreadsheets through Drive.

TypeAPI access
Read onlyRead spreadsheet data and metadata. Includes spreadsheets.readonly and drive.readonly scopes.
Read and writeFull read and write access, including create, update, append, copy, and delete. Includes spreadsheets, drive, and drive.file scopes.

To change the Type after a resource has been created, update the authentication settings and re-authorize the resource. Users are prompted to re-consent on the next query.

4. Save the resource​

Click Create resource to save your Google Sheets resource.

Google Sheets resources cannot be tested from the resource configuration screen. Save the resource and verify the connection by running a List available spreadsheets query.

Query Google Sheets data​

Once you've created a Google Sheets resource, you can query it in apps, workflows, and agent tools. Unlike most API integrations, Retool surfaces Google Sheets operations as Retool-native action types—you configure the spreadsheet, sheet, range, and values through a guided UI instead of constructing API requests by hand.

Create a query​

You can create a Google Sheets query using Assist to generate queries with natural language, or manually by selecting an action type.

Use Assist to generate queries from natural language prompts.

To create a query with Assist:

  1. In the Retool app IDE, click the Assist button at the bottom of the left toolbar to open the Assist panel.
  2. Write a prompt describing the data you want to retrieve or the operation you want to perform, referencing your resource using @.
  3. Press Enter to submit the prompt.
  4. Select your Google Sheets resource when prompted.
  5. Review the generated query and click Run query to add it to your app.
read all rows from the Orders sheet using @Google Sheets
append a row with name={{ form1.data.name }} and email={{ form1.data.email }} to the Customers sheet using @Google Sheets
bulk update the Inventory sheet from {{ table1.changesetArray }} using primary key sku using @Google Sheets

Action types​

Google Sheets queries support the following action types:

Action typeDescriptionRead or write
Read data from a spreadsheetReturns rows from a sheet or A1 range. Supports filters, limits, and offsets.Read
Append data to a spreadsheetAdds new rows to the end of a sheet.Write
Update a spreadsheetWrites values to a specific A1 range.Write
Bulk update a spreadsheet by primary keyUpdates multiple rows by matching against a primary key column.Write
Clear values in a rangeRemoves cell values from a range without deleting the rows or formatting.Write
Delete a single row from a spreadsheetRemoves one row from a sheet, shifting rows below it up.Write
Create a spreadsheetCreates a new spreadsheet in the user's Drive.Write
Copy a single sheet to another spreadsheetCopies one sheet from a source spreadsheet to a target spreadsheet.Write
List available spreadsheetsReturns the list of spreadsheets the authenticated user can access.Read
Get spreadsheet infoReturns metadata about a single spreadsheet, including its sheets and properties.Read

Query configuration fields​

Field availability varies by action type. The fields below describe the configuration shown in the query editor UI.

Action type​

Selects the operation the query performs. See the action types table above for the full list.

Spreadsheet​

Identifies the spreadsheet to query. The query editor provides a picker that lists spreadsheets the authenticated account can access. To use a dynamic spreadsheet ID—for example, one selected from another query or stored in a database—toggle Use raw spreadsheet ID and provide the ID directly.

Examples
# From a table's selected row
{{ table1.selectedRow.data.spreadsheetId }}

# Static spreadsheet ID
1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms

Sheet name​

The name of the sheet (tab) within the spreadsheet. Available for read, append, update, bulk update, clear, and delete actions. Use embedded expressions to reference values from app state.

Examples
# Static sheet name
Sheet1

# Bound to a Select component
{{ sheetSelect.value }}

A1 notation​

A range specified using A1 notation. Available for read, update, and clear actions. Omit to operate on the entire sheet (for read) or specify a range to limit the operation.

Examples
# Specific range
A1:D10

# All rows in columns A through Z
A:Z

# Range computed from a table's row count
{{ '`B2:B' + table1.data.length + '`' }}

Filters to match row​

For Read data from a spreadsheet, filter rows by column values. Add one or more conditions consisting of a column name (key), a comparison operator, and a value.

When Compare formatted spreadsheet cell values is enabled, Retool compares against the display strings (for example, "$1.23" or "FALSE"). Disable it to compare against the underlying typed values (for example, 1.23 or false).

Limit​

Maximum number of rows to return. Enter a static number or bind to a paginator's state. Available for Read data from a spreadsheet.

Examples
# Static page size
100

# Bound to a Table's page size
{{ table1.pageSize }}

Offset​

Number of rows to skip before returning results. Useful for pagination—bind to a paginator's offset to step through results.

Examples
# Start from the first row
0

# Bound to the current page of a paginator
{{ table1.page * table1.pageSize }}

Values to append​

The data to append. Available for Append data to a spreadsheet. Retool accepts several shapes:

  • An array of arrays—each inner array is a row, values matched to columns by position.
  • An array of objects—keys map to column headers in the sheet.
  • The column-data format returned by SQL queries—{ columnName: [val1, val2, ...] }.
Example
[
  { name: {{ form1.data.name }}, email: {{ form1.data.email }}, signupDate: {{ moment().toISOString() }} }
]

Update value​

For Update a spreadsheet with a single value, the value to write at the specified A1 range. For multi-cell ranges, use Values to update with an array.

Primary key column​

For Bulk update a spreadsheet by primary key, the column header that uniquely identifies each row.

Examples
# Common column header names
id
email
sku

Array of rows to update​

For Bulk update a spreadsheet by primary key, an array of objects representing the rows to update. Each object must include the primary key column and the columns to update.

Example
[
  { id: 1, status: "shipped", trackingNumber: "ABC123" },
  { id: 2, status: "shipped", trackingNumber: "XYZ789" }
]

Title of spreadsheet​

For Create a spreadsheet, the name of the new spreadsheet.

Examples
# Date-stamped title generated at runtime
Weekly Report — {{ moment().format('YYYY-MM-DD') }}

Sheet ID to copy​

For Copy a single sheet to another spreadsheet, the numeric ID of the source sheet to copy. Sheet IDs are returned in the response from a Get spreadsheet info call.

Sheet to copy to​

For Copy a single sheet to another spreadsheet, the target spreadsheet to copy the sheet into. Select from the picker or toggle to a raw spreadsheet ID.

Data types and formatting​

Google Sheets stores all cell values as either formatted strings or typed values (numbers, booleans, dates). When reading data, Retool lets you choose which representation to return.

Read data as typed values​

Returns cells as their underlying types—numbers as numbers, booleans as booleans, dates as ISO strings. This is the recommended option for most use cases because it lets Retool's Table component automatically detect column formats, sort numerically, and support typed filters.

Example response
[
  { revenue: 1234.56, active: true, signedUpAt: "2026-01-15T09:30:00Z" }
]

Read data as formatted strings​

Returns cells as the display strings shown in the spreadsheet UI—for example, "$1,234.56", "TRUE", "Jan 15, 2026". Use this when your app needs to preserve the user-facing formatting from the sheet, such as currency symbols, percentage signs, or localized date formats. Note that Retool's Table component cannot apply column formats or sort numerically when values are strings.

Example response
[
  { revenue: "$1,234.56", active: "TRUE", signedUpAt: "Jan 15, 2026" }
]

Compare formatted vs typed values in filters​

When using filters in Read data from a spreadsheet, the Compare formatted spreadsheet cell values toggle controls how Retool matches filter values against cell contents:

  • Enabled—Retool compares filter values against the display string. Match "$1.23" against the formatted cell text.
  • Disabled—Retool compares filter values against the typed value. Match 1.23 against the underlying number.

Match your filter expression to the value type you provide. Mixing formatted filters against typed values (or vice versa) returns no results.

Google Sheets query examples​

These examples demonstrate the most common Google Sheets operations in Retool apps.

display spreadsheet data in a table

Create a query named readSheetQuery to read rows from a sheet and display them in a Table component.

Configure the query:

FieldValue
Action typeRead data from a spreadsheet
Spreadsheet(Pick from the dropdown.)
Sheet nameOrders
Limit100

Select Read data as typed values so Retool's Table component can detect column formats and sort numerically.

Add a Table component and set its Data property to {{ readSheetQuery.data }}.

append rows from a form

Add a Form component with inputs for the columns in your sheet.

Create a query named appendRowQuery to add a new row to the sheet.

Configure the query:

FieldValue
Action typeAppend data to a spreadsheet
Spreadsheet(Pick from the dropdown.)
Sheet nameCustomers

Set Values to append to:

[
  {
    name: {{ form1.data.name }},
    email: {{ form1.data.email }},
    plan: {{ planSelect.value }},
    signupDate: {{ moment().format('YYYY-MM-DD') }}
  }
]

Add an event handler to the form's Submit event that runs appendRowQuery and reruns readSheetQuery to refresh the table.

bulk update rows by primary key

For tables where users edit multiple rows inline, use Bulk update by primary key to write all changes in one query.

Create a query named bulkUpdateQuery:

FieldValue
Action typeBulk update a spreadsheet by primary key
Spreadsheet(Pick from the dropdown.)
Sheet nameInventory
Primary key columnsku

Set Array of rows to update to:

{{ table1.changesetArray }}

table1.changesetArray returns only the rows that the user has edited, each containing the primary key and the modified columns. Add an event handler to the Table's Save changes event that runs bulkUpdateQuery and reruns the read query on success.

delete a row with confirmation

Create a query named deleteRowQuery to remove the selected row from the sheet.

Configure the query:

FieldValue
Action typeDelete a single row from a spreadsheet
Spreadsheet(Pick from the dropdown.)
Sheet nameOrders
Filters to match rowid = {{ table1.selectedRow.data.id }}

Add a Button with a Click event handler that opens a confirmation modal and, on confirm, runs deleteRowQuery and reruns the read query.

list all spreadsheets the user can access

Create a query named listSpreadsheetsQuery:

FieldValue
Action typeList available spreadsheets

Bind a Select component's options to {{ listSpreadsheetsQuery.data }}, with the Label key set to name and the Value key set to id. Use the selected spreadsheet ID in downstream queries by toggling Use raw spreadsheet ID and binding to {{ spreadsheetSelect.value }}.

Best practices​

Follow these best practices to optimize performance, maintain security, and ensure data integrity when working with REST APIs.

Performance​

  • Cache responses: For data that doesn't change frequently, enable query caching to reduce API calls and improve response times.
  • Use pagination: Implement pagination for endpoints that return large datasets to reduce payload size and improve performance.
  • Batch requests: When available, use batch API endpoints to combine multiple operations into a single request.
  • Minimize payload size: Request only the fields you need using query parameters or API-specific field selection features.
  • Set appropriate timeouts: Configure query timeouts based on expected API response times to prevent hung requests.

Security​

  • Use configuration variables: Store API keys and tokens in configuration variables or secrets rather than hardcoding them.
  • Use HTTPS only: Always connect to APIs over HTTPS to encrypt data in transit and protect authentication credentials.
  • Rotate credentials regularly: Follow your API provider's recommendations for credential rotation and key management.
  • Validate SSL certificates: Keep SSL certificate verification enabled unless absolutely necessary for development environments.
  • Use resource environments: Configure multiple resource environments to maintain separate API configurations for production, staging, and development.
  • Apply least privilege: Use API keys with minimal required permissions. Create separate keys for different environments.

Data integrity​

  • Validate user input: Sanitize and validate all user input before including it in API requests to prevent injection attacks.
  • Handle errors gracefully: Configure error notifications and fallback behavior for failed API calls to improve user experience.
  • Use idempotency keys: For APIs that support idempotency keys, include them in POST/PATCH requests to prevent duplicate operations.
  • Verify responses: Check response status codes and validate response data structure before using it in your app.
  • Implement retry logic: For transient failures, use Retool's automatic retry settings or implement custom retry logic with exponential backoff.

Google Sheets-specific considerations​

  • Use typed values for tables: Read with Read data as typed values when binding to Retool's Table component. The Table can then sort numerically, apply column formats, and support typed filters. Reach for formatted strings only when preserving sheet-side formatting is essential.
  • Sheets is not a database: Treat Google Sheets as a lightweight backing store, not a transactional system. For high-write workloads, frequent concurrent edits, or strict consistency requirements, use a database instead.
  • Cap reads with Limit: Sheets read calls return the entire range by default. Set Limit for any read used in a high-traffic app to keep responses fast and stay within Google's per-minute quotas.
  • Use changesetArray for bulk edits: When Table editing is enabled, {{ table1.changesetArray }} returns only the modified rows. Pair it with Bulk update by primary key so each save writes one query instead of N queries.
  • Choose the narrowest scope: Use the Read only type unless your app needs to write. Switching from a broader scope to a narrower one later forces every user to re-consent.
  • Share with the service account email: For service account auth, Retool can only access spreadsheets that have been explicitly shared with the service account's email address. The address is shown in the resource form after pasting the JSON key.