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.
The user was experiencing issues with updating a Google Sheet row and inadvertently updating a calculated column that should not be edited directly. The user found a workaround by including a blank hidden field with the same name as the calculated column in their form to push a blank value to the Google Sheet column, causing the array formula to properly expand over it. However, this felt like a hack and may not work for those using a similar setup with JSONForm.
An alternate solution was provided using lodash's _.omit function 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.
Overall, the user found a solution that worked for their specific use case but may not be the ideal or recommended approach. The alternate solution using _.omit offers a more flexible and robust solution for omitting specific keys/values from the data before submission.