Is it possible to append a row in a Google Sheet using Google Apps Script with R1C1 formulas inside the array?

Question

Hi I am trying to append a row in my Google sheet using appendRow() with array of data to be appended. The challenge is I have R1C1 formulas in my array. When the data(array) is appended in the worksheet, the cell with the formula will return a value with formula parsing error. So any possible way appendRow() can include an R1C1 formula in the array to be added as a formula to the google sheet?

This is the sample of my code:

       var gs = SpreadsheetApp.getActiveSheet();

       var data= [[]];
       data[0][0] = someValue;           
       data[0][1] = someValue;
       data[0][2] = someValue;
       data[0][3] = '=R[0]C[-1]-(R[0]C[-3]+R[0]C[-2])';

       gs.appendRow(data[0]);

I successfully appended the data in the google sheet but the cell will formula returned an parsing error. Any help with this is very well appreciated. Thank you guys.

Benoît Wéry Benoît Wéry · Accepted Answer · 2020-01-15T11:16:36

You need to use "indirect" in formulas ; see R1C1 cell reference in Google Sheets doesn't work for me

data[0][3] = '=indirect("R[0]C[-1]", FALSE)-indirect("R[0]C[-3]", FALSE)+indirect("R[0]C[-2]", FALSE)';

Is it possible to append a row in a Google Sheet using Google Apps Script with R1C1 formulas inside the array?

1 Answers