How do I see the full API call? / How do I prevent a column from updating on Google Sheets

[EricSGS]

I have a query that does an update row to a Google Sheet. However it is updating a column that isn’t in any of the fields I am feeding it. Is there a way to see exactly what is POST/PUT?

What is occurring is I am using an arrayformula to provide business logic to one of the columns, giving numbers for each row based on some logic. A particular row has “0” in that field. I run an update for that row and update other columns, then when I open that spreadsheet I find that the cell in the arrayformula column is now 0 instead of being blank and being filled in by the array.

So I want to inspect the query to make sure it is not providing a value for that column.

And then I need to figure out a way to make sure that column (and columns like it) are not updated.

(More detail in case anyone cares. My spreadsheet has two sheets. one sheet has a list of orders and a column that includes qty remaining. The other sheet is a list of shipments which includes which order and the qty shipped. The qty remaining column on the order sheet is an arrayformula which returns the header in row 1, blank in any blank rows, and quantity minus sum of related shipments from the shipment sheet. This is just one instance of business logic on the spreadsheet but there will likely be more in the future. I’m using a spreadsheet as my “database” because we don’t generate a lot of items and I am very familiar with scripting for spreadsheets.)

EDIT: it looks like the latter issue is the bigger one, finding a way to update a Google Sheets row without injecting values into an arrayformula column. Even though it reads “50” in that cell, Google Sheets doesn’t really have “50” there, it has an array at the top of the column which when expanded would display “50” in that cell.

[EricSGS]

I found a solution to the latter issue. This feels like a hack/workaround and not the “right” way of doing it, but it works. It definitely means people who are using a similar setup to me cannot use JSONForm at all.

What I am doing is including a text field in my form named after my column and then leaving it blank (and hiding it). You could do the same thing any way you can force the value of the appropriate keys to be blank.

In doing so it pushes an update to the spreadsheet and for the column that should be an array it pushes a blank in that cell, then the array normally expands over it.

I have to remember to do this for any calculated column for a sheet I am pushing to, but as long as I remember to do this, it works perfectly.

Would still like to see the way it “should” be done, or perhaps the Update Row method for Google Sheet tweaked to properly accommodate calculated columns.

[GreenFlux]

Hi @EricSGS , thank you for sharing the details on your first solution. Here’s some more info that may help.

@EricSGS

This feels like a hack/workaround and not the “right” way of doing it, but it works. It definitely means people who are using a similar setup to me cannot use JSONForm at all.

Another approach is to use lodash _.omit:

You can also use _.omit to supply the source data to the JSON form, which removes the value before the form sees it, instead of having to hide the field later.

Your method may work better if you wanted to conditionally omit values, though. Then you could use the widget’s show/hide JS to add logic to when it is omitted.