Queries

Global Variables

Javascript

Recent Posts

Archive

Google Sheets

Query data from Google Sheets in Retool.

Retool supports reading and writing data from Google Sheets. And with Retool's query JSON via SQL, you can easilyGoogl combine with other datasources. For example - you could pull in user data from Sheets, join it via SQL to payment data from your database, and then pull in more data for each payment via an API (eg. Stripe).

Setup

If you currently use Retool's cloud hosted solution, navigate to the Resources screen, press "Add" and then choose Google Sheets as a datasource, and then authorize Retool to manage your Google sheets.

You will have the option to enable Retool to write back to Google Sheets at this step.

Using it

1. Getting the data from Google Sheets

Select a Google Sheets resource, and choose a spreadsheet to query in the dropdown.

You'll have to specify the sheet range. If you want to query the whole sheet, just enter the name of the sheet. By default, the first sheet is called Sheet1:

Finding the sheet name inside Google Sheets.

You can also specify sheet ranges via A1 notation:

A1 NotationThis is a string like Sheet1!A1:B2, that refers to a group of cells in the spreadsheet, and is typically used in formulas. For example, valid ranges are:

Sheet1!A1:B2 refers to the first two cells in the top two rows of Sheet1.

Sheet1!A:A refers to all the cells in the first column of Sheet1.

Sheet1!1:2 refers to the all the cells in the first two rows of Sheet1.

Sheet1!A5:A refers to all the cells of the first column of Sheet 1, from row 5 onward.

A1:B2 refers to the first two cells in the top two rows of the first visible sheet.

Sheet1 refers to all the cells in Sheet1.

If the sheet name has spaces or starts with a bracket, surround the sheet name with single quotes ('), e.g 'Sheet One'!A1:B2. For simplicity, it is safe to always surround the sheet name with single quotes.

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

Just make a new query, with the resource Query JSON with SQL, and start typing SQL. To refer to the data from Google Sheets, use .data:

Updating data in Google Sheets

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

In order to use the features below you need to change your Googlesheets connection to allow for editing values, and then you'll have to reauthorize Retool.

Retool currently supports creating new Google spreadsheets and appending data to a Google spreadsheet

Creating a Google spreadsheet

To create a new Google spreadsheet from Retool, you can use the interface below to customize the title of the new spreadsheet.

After running the query, the data property of the query will contain various metadata about the spreadsheet including the spreadsheet id and the spreadsheet url.

One common feature you can use is to display the created spreadsheet in an IFrame component. For example, below you can access the spreadsheet url returned by the createSpreadsheet query

Appending data to your Google spreadsheet

Suppose you have data from an SQL query and you would like to insert this into your Google spreadsheet. You can use the append action type to accomplish this goal.

In above, we have appended to the Google spreadsheet some data from the usersDb SQL Query. If you had used an IFrame to display the Googlesheet, you should see the spreadsheet automatically update once the query finishes thanks to Google sheet's auto-updating functionality!

Retool supports three different formats for data to append to a Google spreadsheet. Here are three equivalent expressions