My Issue
#REF errors appear in cells using array formulas in Google Sheets when Appsmith writes updates to the google sheet.
Expected Behavior
What makes this so confusing, is that I was expecting appsmith to only write data into fields/columns that I explicitly requested to be updated by my query.
Sample Table
| rowIndex | Name | Phone | PhoneAreaCode (array formula calc) | PhoneCountryCode (array formula calc) |
|---|---|---|---|---|
| 0010 | John Anderson | +01 111 123-4567 | 111 | 01 |
| 0012 | Bob Bartleby | +44 222 158-5955 | 222 | 44 |
| 0033 | Carl Cohort | +66 555 328-8544 | 555 | 66 |
sample update query
{{
{"rowIndex": 0033,
"Phone": +01 888 123-9999
}}
Sample Updated Table
| rowIndex | Name | Phone | PhoneAreaCode (array formula calc) | PhoneCountryCode (array formula calc) |
|---|---|---|---|---|
| 0010 | John Anderson | +01 111 123-4567 | #REF! | #REF! |
| 0012 | Bob Bartleby | +44 222 158-5955 | ||
| 0033 | Carl Cohort | +01 888 123-9999 | 888 | 01 |
The Proposed Cause
So, I want to thank Nikhil Nandagopal for his phenomenal support. Without him, I am not sure this journey learning appsmith would have gone so well. He helped work through and trouble shoot issues like these with me.
What we believe is happening is that Appsmith is writing data to every field/column of a row that is being updated in google sheet, despite the fact that the update query intentionally has certain columns/fields omitted from the update.
-
For instance, if you want to write a change to the “phone” column, and the phone column only, it will write that change, but for all other columns, it will also copy and paste the existing values in place… so it will appear as thought it hasn’t written anything to the other columns, but in reality, it has…
-
Using our explicit example from above, for rowIndex 0033, the query above asked for only the phone column to be updated, and the phone column was indeed updated, but also the NAME, PhoneAreaCode, and PhoneCountryCode columns were written over (albeit with the pre-existing values, but a write-over nonetheless).
Normally this is benign behavior, except when it comes to array formulas. For the array formula to work, all cells that the array values are overflowing into must be blank.
- So, back to our example, even thought the 888 for the PhoneAreaCode is correct, and even though the 01 for the PhoneCountryCode is a correct value, those cells should remain empty in order to receive the array calculated value; thus, when Appsmith explicitly writes those values (888 and 01 respectively) into the cells that were receiving a calculated array value, the whole column gets broken.
A Work-Around
If you have array formulas the you need to leave in your spreadsheet, and you need Appsmith to update rows on that same google sheet, then one current solution is to write your query so that everysingle field/column on your spreadsheet is named in the query… AND, for those columns containing array formulas, write an empty string for the field value.
For example, continuing with the Sample situation given above, here would be the corrected query:
{{
{"rowIndex": 0033,
"Phone": +01 888 123-9999,
"PhoneAreaCode_array_formula_calc" : ""
"PhoneCountryCode_array_formula_calc" : ""
}}
Notice, i left out Name still… if I were to update that to a “”, obviously, it would overwrite my name with a blank string… here I just let AppSmith do its thing - write over the existing value with itself => (Carl Cohort becomes Carl Cohort).