How to update Google Sheet with only the edited cells in a table?


Question: Is it possible to only update certain columns of data back to Google Sheets, rather than every column of an updated row?

Context: I've got a Retool table populated with data from a Google Sheet. The underlying Google Sheet has some columns of data that are calculated (using arrayformulas).

I'd like to be able to edit certain cols of data in my Retool table, and only write those editable columns back to my Google Sheet.

If I use Retool's "bulk update a spreadsheet by primary key" action, every cell in each row gets updated to the Google Sheet (which breaks the sheet).

I've now set up a JS query (screenshot) that calls a function to update the sheet row by row - and this works, but it also updates every column in each updated row using .recordUpdates.

Any guidance on how I can limit the update to only certain columns? Possible to modify .recordUpdates to a subset of columns? Or is there a better approach?

Thank you!!

Screen Shot 2021-12-02 at 5.05.34 PM|690x277

Hey @jashton!

Great question, this should definitely be doable. The bulk update via primary key query type accepts an Array of rows to update. {{table.recordUpdates}} contains an array of objects. Each object represents a row, with keys mirroring the name of the columns and each value its corresponding value. If you only want to update a specific column, we will only need to include that column and some identifying column, the one used for the *Primary key column *(typically an id). So for example, if I only wanted to update the name in this table:

Instead of passing {{table1.recordUpdates}}, I could map through and replace each row object with one that only includes name and id like this:

{{> Object.assign({,}) ) }}

Do you think this could work for your use case here?\

Excellent! That works great, much appreciated @Chris-Thompson !