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.

👋

This tutorial assumes you have already set up a Google Sheets resource in Retool. Refer to our Google Sheets documentation for a guide to connecting your Sheets account in a few quick steps.

Building your first application with Google Sheets

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.

Spreadsheet requirements

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 be no empty columns between your column names. You should also avoid empty rows, as they can cause offset issues when appending data.

To easily reference your column names in Retool, we 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, A:Z, 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 A:AB.)

Reading your data into a Table component

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 .data of the first query you create (e.g. query1). In Retool, {{query1.data}} is an array of JSON objects, one for each row structured like his:

{
  "name":"Ines",
  "phone_number":"303-555-0132",
  "interested_in_beta_testing":"FALSE",
  "beta_invite_sent":"FALSE",
}

Options on your read query

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:

[
  {
    "name":"Ines",
    "phone_number":"303-555-0132",
    "interested_in_beta_testing":"FALSE",
    "beta_invite_sent":"FALSE",
  },
  {
    "name":"Oliver",
    "phone_number":"907-555-0146",
    "interested_in_beta_testing":"TRUE",
    "beta_invite_sent":"FALSE",
  }
]

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:

[
  {
    "name": "Oliver",
    "phone_number": "907-555-0146",
    "interested_in_beta_testing": "TRUE",
    "beta_invite_sent": "FALSE"
  },
  {
    "name": "Priya",
    "phone_number": "312-555-0188",
    "interested_in_beta_testing": "FALSE",
    "beta_invite_sent": "FALSE"
  },
  {
    "name": "Asia",
    "phone_number": "818-555-0173",
    "interested_in_beta_testing": "TRUE",
    "beta_invite_sent": "FALSE"
  },
  {
    "name": "Isaac",
    "phone_number": "415-555-1045",
    "interested_in_beta_testing": "TRUE",
    "beta_invite_sent": "FALSE"
  }
]

A1 Notation

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 B1:E6, or to get any rows added in the future, B:E. To use A1 notation, click use A1 notation:

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

Appending rows to your data

In this example, you'll enable the users of your app to add a new beta tester to the table. This new data will then be appended to your spreadsheet.

Prepare your table to accept new rows

First, click on table1 in the canvas, and in the Inspector, make each column editable:

Next, still in the Inspector, toggle on the Show add row button.

To test it out and see how Retool stores this newly added row, click the + button and fill out the information for a new beta tester:

After filling out the row in table1, the data for the new beta tester is stored in table1.newRow.

Create a query to append rows to your spreadsheet

Next, create a query named appendRow using your Google Sheets resource and configure it with the following settings:

1. In the Action type dropdown, select Append data to a spreadsheet.
2. In the Spreadsheet dropdown, select your Google Sheets spreadsheet.
3. In the Values to append field, enter [ {{ table1.newRow }}]. This populates your spreadsheet with the new rows in table1. Note that this field can contain any array of JSON objects, including literal objects, as long as the object keys match the column names of your spreadsheet.
4. In the Event handlers > Success section, enter query1.trigger(). This reloads your table so the new rows are immediately visible.

Connect the appendRow query to your table

The last step to append a row to your spreadsheet is connecting the appendRow query to an event handler in table1. Select table1 and in the Interaction section of the Inspector:

1. Click + Add next to Event handlers.
2. In the Event dropdown, select Save new.
3. In the Query dropdown, choose your appendRow query.

Updating a row

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 table1 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, invite_text_sent, to your spreadsheet:

Create another new query, update_invite_sent, 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 phone_number 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 phone_number and the value to be {{ table1.selectedRow.data.phone_number }}. Next, you'll want to turn your new column's value to TRUE, so in the update value field, add `{ "invite_text_sent": "TRUE" }.

You'll next need to hook up the new update_invitation_sent 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 update_invitation_sent 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 FALSE to TRUE.

Value formatting

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

Let’s take a look at our Beta Invites example spreadsheet (we’ve added one more column, num_users 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:

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 "TRUE" or "FALSE" , resulting in an always checked Checkbox as both values evaluate to true:

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.

Unformatted typed values

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:

Updating a table with unformatted values

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. {{table1.selectedRow.data.id}}, 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:

Bulk updating rows

New: Bulk update a spreadsheet by primary key

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 phone_number 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!

Bulk update via JS Loop

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 table1.recordUpdates array. Also, you can click into the left panel to see the value of recordUpdates at any point in time. Try changing Priya's interested_in_beta_testing status from “FALSE” to “TRUE”:

Now you can write a row update query that will map to a single entry in recordUpdates. As before, you need to tell Retool which row to update, so we’ll use a filter on phone_number again, since it is a unique column value per row. Set the update value to be {{ table1.recordUpdates[i] }}.

The i variable will evaluate as 0 by default, making this a single update to the row filtered by table1.recordUpdates[0].phone_number. The values that are table1.recordUpdates[0] 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 update_row query for each item in the recordUpdates array. Rename it to bulk_update.

Here you'll define i for each instance in the 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 you'll trigger your read query, query1 , to reload and show all of the changes in the table.

Here’s the code above:

const updates =  table1.recordUpdates.map((d,i) => {
  update_row.trigger({
    additionalScope: {"i":i},
    onSuccess: function(data) {
      if (i == table1.recordUpdates.length-1) {
        query1.trigger();
      }
    }
  });
});

return Promise.all(updates);

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 bulk_updates query when the "Save Changes" button is clicked by the user.