Solution to Write to a Google Sheet That has Array Formulas

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).

1 Like

Hi Matt,

Thank you for the detailed explanation of the issue. I have filed a bug report about this in Github.