Issue
I am trying to update a row in a Google Sheet using a query, but it is updating a column that I didn't intend to update. This column has an arrayformula which provides business logic to the sheet. I need to find a way to inspect the query and make sure it is not providing a value for that column. Additionally, I want to figure out a way to update the row without injecting values into the arrayformula column. I found a workaround by including a blank text field in my form and hiding it, but it doesn't feel like the right solution.
Resolution
There are some workarounds for this issue:
- Include a blank hidden field with the same name as the calculated column in the form to push a blank value to the Google Sheet column, causing the array formula to properly expand over it.
- Use lodash's
_.omitfunction to remove specific keys/values from the data object before submitting it to the Google Sheet. This approach can be used to conditionally omit values based on certain criteria and can be done before the form sees the data, avoiding the need for hidden fields.