Skip to main content

Connect to Google Sheets

Learn how to connect to Google Sheets and read, write, update spreadsheet data in Retool.

Google Sheets is a cloud-based spreadsheet application that allows you to create, edit, and share spreadsheets online. With Retool's Google Sheets integration, you can build apps and automations that read from and write to your spreadsheets, enabling you to create admin panels, data entry forms, and reporting dashboards on top of your spreadsheet data.

What you can do with Google Sheets​

  • Read spreadsheet data - Display spreadsheet data in tables, charts, and other components.
  • Write new rows - Append data from forms and other user inputs.
  • Update existing data - Modify spreadsheet data with inline editing or bulk operations.
  • Query with SQL - Use Retool's SQL transformer for complex data operations.
  • Combine with other data sources - Enrich spreadsheet data by joining with databases or APIs.

Before you begin​

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

  • A Google Account - Access to the spreadsheets you want to query.
  • Spreadsheet sharing permissions - Edit access to spreadsheets you want to write to, View access for read-only operations.
  • 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 connection settings​

Configure the following connection settings for your Google Sheets resource.

Resource name

Give your resource a descriptive name that indicates the spreadsheets or team it's associated with.

Example
// Descriptive resource names
sales_team_sheets
marketing_data_sheets
customer_feedback_sheets

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 data. Creating multiple resources is usually unnecessary unless you need to support multiple OAuth applications.

Outbound region

If your organization uses outbound regions, select the region that should be used for requests to Google Sheets. This controls which geographic region your requests originate from.

3. Configure authentication​

Choose an authentication method based on your deployment type and use case.

When to use each authentication method​

OAuth 2.0:

  • You need to access spreadsheets owned by a specific user.
  • You want users to authenticate with their own Google accounts.
  • You need granular per-user access control.
  • You're building an app where different users access different spreadsheets.

Service Account:

  • You need programmatic access without user interaction.
  • You want a single credential for all spreadsheets.
  • You're accessing sheets in automated workflows.
  • Your spreadsheets are shared with the service account email.

Authentication setup​

Cloud organizations can authenticate using OAuth 2.0 or Service Account.

Option A: OAuth 2.0 (Recommended)

OAuth 2.0 allows your resource to access spreadsheets on behalf of individual users. Retool provides a hosted OAuth app for quick setup.

  1. Select your preferred scope using the Type radio buttons.
  2. Choose Read only if queries should only read data from spreadsheets.
  3. Choose Read and write if queries need to update rows, append data, or create spreadsheets.
  4. Click Sign in with Google.
  5. Choose your Google account in the popup window.
  6. Grant Retool permission to access Google Sheets on your behalf.

After successful authentication, you see Authenticated as [your-email@example.com].

Option B: Service Account

A service account is a special Google account that represents an application rather than an individual user. You must share spreadsheets explicitly with the service account email.

  1. Create a service account in Google Cloud Console.
  2. Download the service account JSON key file.
  3. In Retool, paste the contents of the JSON key file into the Service Account Key field.
  4. The Service Account Email field automatically populates from the key.
  5. Share your Google Sheets with the service account email address to grant access.
Example service account key structure
{
  "type": "service_account",
  "project_id": "your-project-id",
  "private_key_id": "key-id",
  "private_key": "-----BEGIN PRIVATE KEY-----\n...\n-----END PRIVATE KEY-----\n",
  "client_email": "your-service-account@your-project.iam.gserviceaccount.com",
  "client_id": "123456789",
  "auth_uri": "https://accounts.google.com/o/oauth2/auth",
  "token_uri": "https://oauth2.googleapis.com/token"
}

4. Test the connection​

Click Test connection to verify Retool can authenticate with Google Sheets. If the test succeeds, you see a success message. If it fails, check the following:

  • OAuth 2.0 - Ensure you completed the Google authentication flow and granted all requested permissions.
  • Service Account - Verify the JSON key is valid and properly formatted.
  • Spreadsheet access - Confirm the authenticated user or service account has access to at least one spreadsheet.

After testing the connection, click View in console to open the Debug Tools console. The console displays detailed information about the test, including request details (URL, method, headers) and response (status code, body), execution time, and error details if the test fails. This information is helpful for troubleshooting connection issues.

5. Save the resource​

Click Create resource to save your Google Sheets resource. You can now use it in queries across your Retool apps and automations.

Query Google Sheets data​

Once you've created a Google Sheets resource, you can query spreadsheet data in your Retool apps and automations.

Create a query​

You can create a Google Sheets query in a Retool app using Assist to generate queries with natural language, or manually using code.

Use Assist to generate queries from natural language prompts. Assist can create queries to read, write, and update spreadsheet data from your Google Sheets resource.

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 (if not already visible).
  2. Write a prompt describing the data you want to retrieve, 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.
Example prompt
read all data from Sheet1 using @Google Sheets

Action types​

Google Sheets queries support multiple action types for different operations:

Action TypeDescriptionUse Case
Read dataRead rows from a spreadsheet or range.Display data in tables, charts, or forms.
Append dataAdd new rows to the end of a sheet.Insert form submissions or new records.
Update dataModify existing rows in a sheet.Update records from inline table edits.
Bulk updateUpdate multiple rows based on a primary key.Sync data changes from a table component.
Delete rowsRemove rows from a sheet.Delete records based on user selection.
Copy sheetDuplicate a sheet within or across spreadsheets.Create backups or templates.
Create spreadsheetCreate a new Google Sheets spreadsheet.Generate new workbooks programmatically.
List spreadsheetsRetrieve a list of accessible spreadsheets.Populate dropdowns or show available sheets.
Get spreadsheet infoFetch metadata about a spreadsheet.Display sheet names or properties.

Query configuration fields​

Each query type has specific configuration fields:

Spreadsheet​

Select the spreadsheet you want to query. You can choose from a dropdown of accessible spreadsheets or provide a spreadsheet ID directly.

Spreadsheet example
// Use dropdown selection
{{ spreadsheetDropdown.value }}

// Use spreadsheet ID directly
1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms

// Dynamic spreadsheet ID from table selection
{{ table1.selectedRow.data.spreadsheet_id }}

Sheet name​

Specify the name of the sheet (tab) within the spreadsheet.

Sheet name example
// Static sheet name
Sheet1

// Dynamic sheet name from dropdown
{{ sheetDropdown.value }}

// Computed sheet name
{{ "Data_" + formatDate(new Date(), "YYYY-MM") }}

Range​

Define the range of cells to read or write using A1 notation. Leave empty to read all data.

Range example
// Specific range
A1:D100

// Entire columns
A:Z

// Dynamic range
{{ "A1:Z" + table1.data.length }}

// Named range
SalesData

Filter by​

Filter rows based on column values. Provide an array of filter objects with column names and values.

Filter by example
// Single filter
[
  { column: "Status", value: "Active" }
]

// Multiple filters
[
  { column: "Status", value: "Active" },
  { column: "Region", value: "West" }
]

// Dynamic filter from input
[
  { column: "Category", value: {{ categorySelect.value }} }
]

Data to write​

For append and update operations, provide the data to write as an array of objects or a reference to component data.

Data to write example
// From form component
{{ form1.data }}

// From table selection
{{ table1.selectedRow.data }}

// Manual data structure
{
  "Name": {{ nameInput.value }},
  "Email": {{ emailInput.value }},
  "Status": "Active"
}

// Array of rows
[
  { "Name": "Alice Chen", "Email": "alice@example.com" },
  { "Name": "Bob Kumar", "Email": "bob@example.com" }
]

Value formatting​

Choose how to format values when reading data.

Value formatting example
// Options:
// - "formatted" - Returns formatted strings (e.g., "$1,000.00")
// - "unformatted" - Returns raw values (e.g., 1000)
// - "formula" - Returns formulas instead of computed values

Data types and formatting​

Google Sheets handles different data types when reading and writing data.

Reading data​

When reading data from Google Sheets, you can control how values are formatted using the Value formatting field.

Format TypeDescription
Formatted valuesReturns data as it appears in the spreadsheet, including number formatting, dates, and formulas evaluated to their results. Use formatted values when you want to display data exactly as users see it in Google Sheets.
Unformatted valuesReturns the raw underlying values without formatting. Numbers return as numbers, dates return as serial values, and formulas return their computed results. Use unformatted values when you need to perform calculations or transformations on the data.
Formula valuesReturns the actual formula text instead of computed results. Use formula values when you need to inspect or modify formulas programmatically.

Writing data​

When writing data to Google Sheets, Retool automatically converts JavaScript types to appropriate spreadsheet values.

Value TypeDescription
String valuesWritten as text. Numbers within strings are treated as text unless Google Sheets auto-converts them based on the cell format.
Number valuesWritten as numeric values and can be formatted using Google Sheets number formatting.
Date valuesWritten as date serial numbers. Google Sheets recognizes standard date formats and applies appropriate formatting.
Boolean valuesWritten as TRUE or FALSE.
Null or undefined valuesWritten as empty cells.

Common use cases​

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

Read and display spreadsheet data

Read rows from a Google Sheets spreadsheet and display them in a Table component.

Query configuration:

Action type: Read data
// Spreadsheet: Select your spreadsheet from dropdown
// Sheet name: Sheet1
// Range: A:Z (reads all columns)
// Filter by: Leave empty to read all rows

Table component:

Set the Table component's Data property to {{ query1.data }} to display the spreadsheet data.

Tips:

  • Use column headers in the first row of your spreadsheet for automatic column naming.
  • Enable pagination in the Table component for large datasets.
  • Add search and filtering capabilities using the Table's built-in features.
Append new rows from a form

Collect user input in a Form component and append it as a new row in Google Sheets.

1. Create a Form component:

Add a Form component to your app with fields matching your spreadsheet columns (e.g., Name, Email, Phone, Status).

2. Create append query:

Action type: Append data
// Spreadsheet: Select your spreadsheet
// Sheet name: Sheet1
// Data to write: {{ form1.data }}

3. Add event handler:

Add an event handler to the Form component's Submit event that runs the append query, then shows a success notification and resets the form.

Result: When users submit the form, a new row is added to your spreadsheet with the form data.

Update rows with inline table editing

Allow users to edit rows directly in a Table component and sync changes back to Google Sheets.

1. Enable table editing:

Set the Table component's Editable property to {{ true }} and configure which columns are editable.

2. Create bulk update query:

Action type: Bulk update
// Spreadsheet: Select your spreadsheet
// Sheet name: Sheet1
// Primary key: id
// Data to write: {{ table1.changesetArray }}

3. Add save button:

Add a Button component with an event handler that runs the bulk update query when clicked.

Result: Users can edit cells directly in the table, and clicking the save button syncs all changes to the spreadsheet.

Filter data with user input

Let users filter spreadsheet data using a Select component.

1. Add Select component:

Create a Select component with filter options (e.g., status values, categories).

2. Create filtered read query:

Action type: Read data
// Spreadsheet: Select your spreadsheet
// Sheet name: Sheet1
// Range: A:Z
// Filter by:
[
  { column: "Status", value: {{ statusSelect.value }} }
]

3. Configure query trigger:

Set the query to run automatically when statusSelect.value changes.

Result: The table data updates automatically as users change the filter selection.

Delete rows with confirmation

Delete rows from a spreadsheet with user confirmation.

1. Add delete button to table:

Add an action column to your Table component with a delete button in each row.

2. Create delete query:

Action type: Delete rows
// Spreadsheet: Select your spreadsheet
// Sheet name: Sheet1
// Filter by:
[
  { column: "id", value: {{ table1.selectedRow.data.id }} }
]

3. Add confirmation dialog:

Configure the delete button's event handler to show a confirmation dialog before running the delete query.

Result: Users can delete individual rows, but must confirm the action before the deletion occurs.

Best practices​

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

Performance​

  • Cache responses: For data that doesn't change frequently, enable query caching to reduce API calls and improve response times.
  • Use targeted ranges: Specify exact cell ranges instead of reading entire sheets to reduce data transfer and processing time.
  • Batch updates: Use bulk update operations instead of multiple individual updates to minimize API calls.
  • Limit row counts: Read only the data you need using filters or specific ranges rather than entire sheets.
  • Optimize read operations: Use unformatted values when you need to perform calculations to avoid parsing formatted strings.

Security​

  • Use configuration variables: Store service account keys and OAuth credentials in configuration variables or rather than hardcoding them in the resource configuration.
  • Use service accounts for automation: For workflows and background jobs, prefer service accounts over OAuth to avoid dependency on individual user authentication.
  • Limit OAuth scope: Use read-only OAuth scope when queries only need to read data, reducing the impact of compromised credentials.
  • Rotate credentials regularly: Periodically rotate service account keys and refresh OAuth tokens according to your security policies.
  • Use resource environments: Organizations on an Enterprise plan can configure multiple resource environments to maintain separate configurations for production, staging, and development.
  • Apply least privilege: Share spreadsheets with only the minimum required permissions (view access for read-only operations, edit access only when necessary).

Data integrity​

  • Validate user input: Sanitize and validate all user input before writing to spreadsheets to prevent data corruption.
  • Handle errors gracefully: Configure error notifications and fallback behavior for failed API calls to improve user experience.
  • Use primary keys: When using bulk updates, ensure your spreadsheet has a reliable primary key column to prevent accidental overwrites.
  • Verify responses: Check response status and validate data structure before displaying results to users.
  • Implement retry logic: For transient failures, use Retool's automatic retry settings or implement custom retry logic with exponential backoff.
  • Backup critical data: Regularly back up important spreadsheets before allowing write operations in production apps.