How do I update certain columns in a GSheets table from a Javascript Query

Queries and Resources
#1

I have created a form that takes some input.
It kicks off a Javascript query that does stuff with the input.

At the end of that JS, I want to write resulting data into a currently selected row in a Google Sheets table - specifically update column4, column5, and column9.

What is the correct JS syntax to do this?
Or, should some data be passed to a second GSheets query for this update?

#2

Hey @steve.b.c3 and welcome to the community! I would be remiss if I didn’t link to a video we just made on this topic :slight_smile: https://www.youtube.com/watch?v=9GcAEAuWUT8

To answer your question more directly - a table is just a frontend representation of your data, so you need to update the underlying resource that’s powering your table. If it’s Google Sheets, you’ll want to create a query, choose “update your spreadsheet” from the dropdown, and fill in the requisite details. We have a tutorial for Google Sheets specifically here.

In terms of ordering, you can configure your new GSheets query to run after the JS query you’ve triggered - click on your JS query, scroll down to the “after this query runs” section, and choose your GSheets query in the “on success trigger” field. If you have a GSheets query that’s populating your table, you’ll probably also want to rerun that once the update is made.

Hope this helps!!

#3

Hi @justin!

Great. Thanks!
Sounds like I need to use this answer here to do the thing: Send data from one JavaScript Query to another

Here is my understanding of the steps:

  1. Form1 auto-populates with selected table row data
  2. User adds/modifies form data and hits submit
  3. JavaScript Query runs, manipulates some data, triggers other queries, completes (successfully)
  4. On successful completion JavaScript Query triggers a Google Sheets Update query, passing in the data needed to identify the row, columns, and updated values for those columns
  5. Google Sheets Update query runs, updating the Google Sheet with passed values
  6. Google Sheets read query reruns to get newly updated data
  7. Table refreshes, showing updated data

Is that about right?

#4

Exactly - but between 5. and 6., there’s 5a. Google Sheets read query reruns to get newly updated data.

1 Like