Documentation

Welcome to Retool! We're a fast way to build custom internal software.

You'll find the 5 minute demo, quickstart guide, and documentation for each of our connectors and components here. If you've got any questions -- chat with us on the bottom right!

Get Started    Guides

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

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

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

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.

Note

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.

Note

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.

Note

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'.

Note

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.

Note

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.
  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 {{ readSheets.data }}.

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 readSheets.data and returnsTable.selectedRow.data.columnName.

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 = {{ returnsTable.selectedRow.data.user_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.

Note

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 {{ getEmail.data.email[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 the Google Sheets data via SQL

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

Note

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 {{googleSheetsBudget.data}}
order by budget::int ASC;
  1. Add optional filters

Filtering with like

Use ILIKE to filter your search values. For example:

select * from {{googleSheetsBudget.data}} 
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:

select
  users.*, payments.*
from
  {{ userSheets.data }} as users,
  {{ paymentsApi.data }} as payments
where
  users.id = payments.user_id

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.