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.

---

1. Log into your Retool dashboard and in the top menu bar click Resources.

1. Click on Create new.

1. Select Google Sheets from the list of resource types.

1. Enter a name for your resource that you’ll use when querying it in the editor (e.g. “David’s Google Sheets”). Next, you’ll need to decide whether you want to grant Read and write or Read only access to Retool. We recommend Read and write: in addition to reading values out of your sheets, your apps will also be able to update rows, append rows, copy sheets, create new spreadsheets, and more.

1. Click Connect to Google Sheets to begin Google’s authorization flow.

1. After completing the authorization flow, you should see a message indicating a successful connection. Click the Create a new app now link in that message to create a blank Retool app and start building with Google sheets!

---

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

---

Let’s say you’d like to build a Retool app to send invites to users who’ve signed up to be beta testers of your new product. Their contact information is stored in a Google Sheets spreadsheet in the Google Drive that you setup as your new resource. To kick off building your app, let’s first make sure your spreadsheet is properly formatted.

In Retool, you can specify which sheet in a Google spreadsheet from which you’d like to query data. The first row in that sheet must have column names that map to the data listed in the rows below. Here’s what a well-formatted sheet looks like:

Just like the example above, the sheet you query in Retool should have no additional data besides:

1. Your first row of column names
2. Your rows of data

There should also be no empty columns between your column names. To easily reference your column names in Retool, we also recommend the following naming conventions:

• Use alphanumeric characters
• Use lowercase letters
• Use underscores to separate words, instead of spaces

If you have a sheet that does have some additional data in it (e.g. some summary statistics off to the right) you can instead use our A1 notation option. It requires a bit more work to setup, so for this tutorial we instead recommend creating a new sheet that references your desired data, and formatting that new sheet as above.

Retool also queries a maximum of 26 columns, , by default. If you have more than 26 columns in your data, you’ll need to use A1 notation. (For example, if you have 28 columns, your A1 notation would be .)

In your app, create a new query that you’ll use to read in data from your sheet.

Select your Google Sheets resource from the Resource dropdown, for this example that’s “David’s Google Sheets”. Then select your desired spreadsheet, here that’s “Beta signups”. (If this is left blank, Retool will use the first sheet in the spreadsheet.)

📘

Choosing a spreadsheet by ID

In addition to selecting a spreadsheet name from the dropdown menu, you can also enter the spreadsheet ID as specified in its URL. For example, if the Google sheet URL is

https://docs.google.com/spreadsheets/d/1L2h7HMrHjcpU_-vpT8pGVKNlfBaxh8FUEN-UHXt--mU/edit#gid=0

, the ID would be

1L2h7HMrHjcpU_-vpT8pGVKNlfBaxh8FUEN-UHXt--mU

.

Click Preview to see your data in Retool!

To save, click Save & Run. Next, drag a Table component onto your canvas:

By default, Retool’s Table component automatically populates with the

of the first query you create (e.g. ). In Retool, is an array of JSON objects, one for each row structured like his:

Retool provides a few different options for a read query with Google Sheets.

Limit: sets an integer limit on the rows returned from your sheet, excluding your column names row.

In the example sheet:

A read query with a limit of 2 would return the rows for Ines and Oliver, excluding the rows for Priya, Asia, and Isaac. The following JSON would be returned:

Offset: sets an integer offset on the rows returned from your sheet, excluding your column names row.

Again, in the example sheet:

A read query with an offset of 1 would return the rows for Oliver, Priya, Asia, and Isaac and exclude the row for Ines. The following JSON would be returned:

If you need to select a specific section of a sheet, you can use A1 Notation in Retool. In this example spreadsheet, the row data starts at column B and there are some summary statistics off to the right:

To select the data from this sheet, you can write A1 notation to get the exact above cells with

, or to get any rows added in the future, . To use A1 notation, click :

Add your A1 notation, here , and your data will returned as an array of JSON objects:

Let’s say you want to enable the users of your app to add a new beta tester to the table — and have that propagate to the data source, your spreadsheet.

You can start by creating a new query, call it

, that will append data to your spreadsheet. You’ll want to select Append data to a spreadsheet from the Action Type dropdown. Then click on and change the On row add dropdown in the right-hand inspector to be your new query:

Notice right now, there is nothing in the Values to append field. First you'll setup your

component to allow for adding rows, and then you’ll come back to hooking up the query.

First, click on

in the canvas, and in the table's property inspector, make each column editable one-by-one:

Users of your app will add a new row by clicking on the + button on

:

Let’s test it out, and see how Retool stores this newly added row. You can add a new beta tester to your app, in this case, Hui-lan, by filling out her information:

When you do that, your

's field is populated with a JSON object for your new row, which you can see in the left-hand panel of the editor:

Now, as a final step, you'll need to hook up your

query and the data that you enter in your component. The append action supports appending an array of JSON objects for each row, so you'll use :

Any array of row JSON objects will work, for example, this array of literal objects:

The only requirement is that the keys of your JSON objects match the first row of your spreadsheet, your column names.

The new row will be added at the end of your row data, which is detected by Google automatically. Lastly, whenever you write an append query, you likely will want to trigger your read query as the on success trigger, so that your table data is reloaded to reflect the new row you just appended.

Now you can press the Save changes button on your table. This should trigger the

query to run and the new row will append to your spreadsheet.

Next, you can wire up a “Send invite text” button. You'll need to add an Action button column to the table. Click on the

component to select it, then click on New action in the property inspector.

A new column called "Actions" will appear in your table, with each row featuring a button. You can rename the button by clicking on Action 1 in the list and then changing Action button text to "Send invite text".

You'd probably want to use something like the Twilio API to send the text message when this button is pushed. This example, though, will just focus on updating the spreadsheet as if you’ve sent the text message successfully.

Next, you'll want to add a new column,

, to your spreadsheet:

Create another new query,

, and select the Update a spreadsheet action. Retool needs a way to figure out which row you intend to update. To do that, you must specify a unique column and the corresponding value, such that Retool can detect the single row to update:

A good candidate for your unique column would be a primary key like id, but in this case you can use the

column, as that is unique per beta tester. You'll want to match the phone number for whichever row the “Send invite text” button is clicked. Set your Filter by column to be and the value to be . Next, you'll want to turn your new column's value to , so in the update value field, add `{ "invite_text_sent": "TRUE" }.

You'll next need to hook up the new

query to the action buttons in the table. With the table selected, click on the Action Button in the inspector, and set the Action query to the new query:

As a final step, once again set your read query as the on success trigger for this update query, so that the table data always matches what is in the Google sheet:

That's it! Now, when you click the “Send Invite Text” button, you’ll see the “invite_text_column” update from

to .

By default, Retool fetches data from Google Sheets as formatted strings. For example, if your spreadsheet cell has a value of formatted in USD, a query in Retool from Google Sheets will return

.

Let’s take a look at our Beta Invites example spreadsheet (we’ve added one more column,

so we can work with numeric values):

When you inspect the returned results of a query by hovering over it, you can see the formatted strings:

Notice "10" vs. 10

Formatted strings work well when you are:

• Displaying data in a table exactly as it appears in Google Sheets, and you don’t need to use any sorting or formatting features of Retool’s table
• Using column formats supported by Google Sheets that are not yet supported by Retool. Drop us a note at [email protected] with any requests for more formatting types!

Formatted strings do not work well when you try to apply Retool’s column formats to them. For example, a formatted boolean from Google Sheets is

or , resulting in an always checked Checkbox as both values evaluate to :

Additionally, any column sorting will be string based, which is likely not what you want when sorting columns that are numbers in your spreadsheet, but formatted strings when pulled into Retool:

As you can see, a lot of Retool’s table features don’t work as well out of the box with formatted strings so we recently built the ability to read in unformatted typed values from Google Sheets.

Now, if your Google spreadsheet has multiple data types, like booleans and numbers, you can instead choose to read in those typed values by selecting “Read data as typed values” in the Data Types section of your read query in the Query Editor. We default you to the original behavior, formatted strings:

But you can now switch to read in unformatted typed values:

Here’s an example of changing a query over to unformatted typed values. You’ll notice that Retool automatically detects the column format and allows other formats to be applied correctly. Also, sorting now works as you’d expect:

Earlier in our tutorial we walked through how to update data in your spreadsheet from changes in your Retool table. If the value in your “Filter by” is populated from a table, e.g.

, and that table reads from a query with unformatted typed values, then we need to make the same distinction in our update query so that we can properly match rows from our Google spreadsheet.

Again, by default we will check “Compare formatted spreadsheet cell values,” but uncheck it like in the screenshot below to compare via unformatted typed values:

Previously, bulk updating a spreadsheet from a table required a lot of custom JS in Retool, so we built an action for it!

First, make sure that your data set in your Google Sheet has a primary key column — any column that has a unique value per row is a good candidate (e.g. a unique ID).

In our example spreadsheet, we could use

as the primary key:

If your data set does not have a primary key, you can add a simple row number to identify your row:

Now, let’s say you've set up a query to read your data from your Google Sheet into a table:

Make sure that you make any columns you want to change editable (you can learn more about Retool's Table component in Working With Tables:

Now, you need to create your bulk update query and set it up so that it is triggered when you edit your table.

1. Select Bulk update a spreadsheet by primary key from the actions list
2. Add your primary key (e.g.
   phone_number
   )
3. Add
   {{ your_table_name.recordUpdates }}
   as the Array of rows to update so that you're capturing the edits you make to the table
4. Select that query as the Bulk update action for the table in the Bulk update action section of the right-hand panel

Lastly, remember to re-run your read query to populate your table with the updated values:

Now, you can edit multiple rows at once, click Save Changes and see those updates in your Google Spreadsheet!

If the action to bulk update a spreadsheet by primary key described above does not work for you, drop us a note at [email protected]. You can also still use the following set of instructions to loop through each change to a row in the table, and trigger a single row update query for each change.

Here’s the table as it exists right now, after previously making all of the columns editable:

When you edit any cell, Retool adds the updated row to the

array. Also, you can click into the left panel to see the value of at any point in time. Try changing Priya's status from “FALSE” to “TRUE”:

Now you can write a row update query that will map to a single entry in

. As before, you need to tell Retool which row to update, so we’ll use a filter on again, since it is a unique column value per row. Set the update value to be .

The variable will evaluate as 0 by default, making this a single update to the row filtered by

. The values that are will be written to your Google sheet, effectively replacing the entire row with the updated values from Retool.

Next, create a new Run JS Code query to trigger the

query for each item in the array. Rename it to .

Here you'll define for each instance in the additionalScope object option on the

JS Method, and that value will overwrite the default value. If the item is the last update, then you'll trigger your read query, , to reload and show all of the changes in the table.

Here’s the code above:

Last step! Set the table to use this JS Query as its "Bulk update action" setting in the inspector under the "Table Edit Queries" section of the table settings. That will cause the table to trigger the

query when the "Save Changes" button is clicked by the user.