Google Sheets

Query data from Google Sheets in Retool.

Retool makes it easy for you to build apps with Google Sheets data, including connecting Google Sheets with other APIs. Retool supports reading and writing data from Google Sheets, and with our query JSON via SQL, you can easily combine Google Sheets with other data sources.

For example, you can easily pull user data from Google Sheets, join it with payment data from your database using SQL, and pull more data for each payment via an API (e.g. Stripe).

Setup Google Sheets

You can setup Google Sheets either in the cloud or on-premise.


On-premise setup

To set up your Google Sheets using the On-premise setup process, see Google Sheets setup instructions.

Cloud setup

  1. Log into your Retool dashboard and navigate to Resources from the sidebar.
  2. Click the Add button located on the right-hand side of the screen.

The Resources page

  1. Select Google Sheets.
  1. Enter a Label name. This is the name that appears for your Google Sheets on the Resources landing page and is also used to reference the resource from the App Editor's UI.
  1. If you want to enable Retool to write to your Google Sheet, toggle Enable writing back to Google Sheets to Yes .
  1. Click Connect to Sheets to initiate the Google connection to your sheet.

Retool format requirements for Google Sheets

Retool expects the first row of a sheet to contain the field names when retrieving data from an existing sheet. To more easily reference columns in Retool, use the following column naming conventions:

  • Use alphanumeric values
  • Use lowercase letters
  • Use underscores to separate words instead of spaces.

Expected sheet format

Query Google Sheets from your app

Querying a spreadsheet will result in a table of object values similar the example below.

How Retool formats a sheet's values

To query a spreadsheet from your Google Sheets:

  1. From the query editor, select your Google Sheet from the Resource dropdown menu. For this example, we will use the Sample Sheets (googlesheets) resource.
  1. Choose the spreadsheet you want to query from your Google Sheets doc.
  1. Enter the range you want to use for your selected sheet.
  1. Click the Save button to save your changes.
  1. To change the name of your query, click the tab to activate the query name editor. Edit the text and press the Enter on your keyboard.

Creating Google Sheets and updating spreadsheet data

Retool supports the ability to:

  • Create new Google Sheets documents.
  • Append data to a Google Sheets document.
  • Update data for an existing Google Sheets document.


You need to grant Retool edit access to your Google sheets!

In order to use the create, append, and update features, you need to ensure your Google Sheets connection allows for editing values. If you make a change to your Google Sheets connection, you will need to reauthorize Retool with Google Sheets.

Create a Google Sheets document

To create a new Google Sheets document from Retool:

  1. Open the Resource dropdown, scroll to the top of the list, and select + Add a new resource.
  1. You will be taken to the Resources page. Click Google Sheets.
  1. Name your Google Sheets document and set Enable writing back to Google Sheets to Yes to enable Retool to write to the document.



The Name field is the name for the resource within Retool, not the actual name of the Google Sheets document. You will need to create an actual Google Sheets document in your Google account, that will be reference within the query editor.

  1. Click Connect to Sheets to authorize Retool to create and save your Google Sheets document.



Connecting to Google Sheets will take you to a Google authorization flow. Once you've authorized Google Sheets, you will be redirected back to Retool.

  1. You will see a message confirming you've connected to Google Sheets.

Append data to your Google Sheets document

You can insert SQL query data to your Google Sheets document from the query editor.

  1. From the Resource menu, select your Google Sheets document, and from the Action Type menu choose Append data to your spreadsheet.
  1. Choose the spreadsheet you want to edit and the Sheet name if needed.



If no Sheet name is entered, the first sheet will automatically be selected.

  1. Enter the values you want to append to your Google Sheets document. The values box uses JSON where each object representing a new row. You can also use an existing data variable for this field.

The first row of your Google Sheets document are automatically used as column names. The column names are used as keys in the JSON, so your values must match the text in the first row of your Google Sheets document.

  1. Each time this query runs, the values will append to your Google Sheets document!

Update data for your Google Sheets document

For this example, we will change John Smith's first name to 'Jonathon'.



The cell corresponding to John Smith's first name is A3. This is important when referencing the cell we want to update.

To update the Google Sheets data from the query editor:

  1. Select Update your spreadsheet from the Action Type menu.
  1. Choose the spreadsheet you want to edit and the Sheet name if needed.



If no Sheet name is entered, the first sheet will automatically be selected.

  1. Click Use Sheet range and specify the sheet range as A3:A3, to update only the A3 cell.


How to use Sheet Range

Sheet range works the same in Retool as it does in Google Sheets. For example, if you want to specify the range A1:C4 on a sheet called "Sheet No. 2" then you would use 'Sheet No. 2'!A1:C4.

If only specify a range (e.g. A:AZ) Retool assumes the first sheet.

  1. Specify the first_name value as Jonathon to replace the information located at A3.

Displaying the data in a table

Retool has ready-made components which can display data. Combine and arrange components to create custom interfaces for your apps. For this example, let’s show our Sheets data in a table.

  1. Drag a Table component from the Components list to the canvas.
  2. In properties menu on the right, rename the table to returnsTable, so it can be easily referenced.
  3. The data in the table will be pre-filled with the results of your last query {{ }}.


Variable references

Everything in Retool is a variable that can be referenced anywhere else in the app. For this example readSheets is a query, and returnsTable is a Table. Variables also have accessible fields like and

Setting up Bulk Updates to Google Sheets in a Table

Now that we have our data displayed in a table, we're able to use this to either populate other UI values based on the property, or we can set up inline editing for the table. The Google Sheets integration doesn't directly support bulk updates but we can still set up bulk updates on tables, with any type of resources by using a Run JS Code type query to loop through each change and trigger a single update for each change.

Here is our example data:

Example data for our Google sheet

Now that we are loading this sheet with {{ }} in our table's data setting, we enable editing for specific columns by clicking on the column names and toggling "Make editable". Modifying a value inside of the table will highlight the cell in yellow and add the modified values in the row to the table1.recordUpdates array.

Let's create a new query to our Google Sheets resource with the "Update your spreadsheet" action selected, and the same sheet as we chose in the readSheets query selected in the Spreadsheet setting.

We'll want to use the "Filter by" option to specify the range that will be updated. My primary key on this sheet is id so we'll use that to locate the correct row for each update.

Filter by: id = {{table1.recordUpdates[i].id}}
Update value: {{table1.recordUpdates[i]}}

The i variable will evaluate as 0 by default, making this a single update to match the 0th index's id value of table1.recordUpdates[0].id and update the values in that row of the table to match all of the values in table1.recordUpdates[0].

Next we'll create a new Run JS Code type query to trigger the updateRow query for each item in the recordUpdates array. Here we'll define i for each instance in our additionalScope object option on the .trigger() JS Method, and that i value will overwrite the default value. If the item is the last update, then we'll trigger the readSheets query to reload and show all of our changes in the table.

JS Query to trigger a single update for each change

Here's that code:

const updates =,i)=>{
    additionalScope: {"i":i},
    onSuccess: function(data){
      if( i == table1.recordUpdates.length-1){

return Promise.all(updates)

Lastly, we'll need to set the table to use this JS Query as it's "Bulk update action" setting in the right panel of the editor under the "Table Edit Queries" section of the table settings. That will cause the table to trigger our bulkUpdateRows query when the "Save Changes" button is clicked by the user.

Combining with other data sources

Retool enables you to combine Google Sheets data with other sources, like databases or APIs.

For this example, let’s fetch a selected user’s email from our sample PostgreSQL database, where our Google Sheets that the return warehouse produces does not have the customer emails - only their user IDs.

Retrieve the user_id of the selected row:

  1. Press the New Query button.
  2. Select onboarding_db (readonly) from the Resource dropdown menu.
  3. Write the SQL query Select email from users where id = {{ }} to find the email corresponding to the user_id in the selected row of the table. Note: You can press Preview to verify if the SQL works.
  4. Click Save to save the query.
  5. Click the query tab name and rename the query to getEmail.



If you select a different row of the table, the query will automatically re-run and fetch the email corresponding to the newly selected row.

Let’s set the To field to auto-fill with the result of this query.

  1. Click on the To field component to display the properties menu.
  2. Navigate to the Default value property and enter {{[0] }} as the value.

When we change the selected row, the query to retrieve the row’s email will automatically run and fill in the data in the To TextInput component.

Manipulating Google Sheets data via SQL

If you want to filter, sort, or otherwise manipulate your Sheets data, you can query it via SQL.



The query will be a new Query JSON with SQL resource and not a Google Sheets resource.

To query your Google Sheets:

  1. Create a new query with the resource Query JSON with SQL.
  1. Enter the SQL statement(s) into the Query field. Use .data to refer to the data from Google Sheets.
select * from {{}}
order by budget::int ASC;
  1. Add optional filters

Filtering with like

Use ILIKE to filter your search values. For example:

select * from {{}} 
where item ILIKE {{'%' + textinput1.value + '%'}}
order by budget::int ASC;

Joining Google Sheets to another resource (Sheets or otherwise)

You can join Google Sheets data with other objects in Retool. The data can be joined from an API, database, or Sheets. For example:

  users.*, payments.*
  {{ }} as users,
  {{ }} as payments
where = payments.user_id

Updated 3 months ago

Google Sheets

Query data from Google Sheets in Retool.

Suggested Edits are limited on API Reference Pages

You can only suggest edits to Markdown body content, but not to the API spec.