Skip to main content

Use inline table editing to update Google Sheets

This guide explains how to update a Google Sheets spreadsheet using inline table editing.

Before Getting Started​

Fetch data from Google Sheets​

  1. Create a new Backend API with a Google Sheets step
    • Update the API name to getSheetsData
  2. Set the action to Read the whole spreadsheet
  3. Select the Spreadsheet and Sheet name to read from
  4. [Optional] Check Use Row 1 of the sheet as the Table Header, if applicable
  5. Run the API to fetch the data

Display data in a Table component​

  1. Drag a Table component on to the canvas
  2. Set the Table Data to {{getSheetsData.response}}

Write data to Google Sheets​

In this section, we'll create an API with 2 steps, the first will clear the existing spreadsheet and the second will write the Table's data into the spreadsheet.

warning

Please note, this API will completely overwrite your spreadsheet's existing data. Be sure to follow the instructions closely, as well as test and validate on a backed up spreadsheet to prevent data loss before using on production spreadsheets.

  1. In the Table component's column section, update the necessary rows to be Editable
  2. Toggle Enable Row Insertion and Enable Row Deletion, if applicable
  3. Create a new Backend API with a Google Sheets step
    • Update the API name to updateSheetsData
  4. Set the action to Clear the spreadsheet
  5. Select the Spreadsheet and Sheet name to write to
  6. Check Preserve Table header, if applicable
  7. Set the Header row number
  8. Create a new Google Sheets step
  9. Set the action to Create spreadsheet row(s)
  10. Select the Spreadsheet and Sheet name to write to
  11. Check Include a header row in this sheet and set Header row number, if applicable
  12. Set Write location to Append data starting at the first empty row
  13. Set Rows to append to {{Table1.allEdits}}
  14. In the Table component's onSaveChanges Event Handler, add actions to Run the updateSheetsData API and a second action in series to run the getSheetsData API
The onSaveChanges event handler is used to run two APIs sequentially
success

Now, you can edit the data directly in the Table component. When you click Save, the data will be written back into Google Sheets and refreshed to show the latest.

Limitations​

It is known behavior that writing data back into a Google Sheet will always be parsed as a string. This will be denoted by a leading ' in the cell's value.