Error using R1C1 notations in Formula on Google Script

Question

I've encountered some issues on my new Macro.

In fact, I'm translating a VBA Macro in Google Apps Script language but I don't understand something.

Instructions :

  feuille_31.insertColumnAfter(8); // Inserting a new column
  feuille_31.getRange(1,9).setValue(libel); // Rename my column.
  feuille_31.getRange("I2"+":I"+lig).setFormulaR1C1("=SUM(R[0]C[-2]:R[0]C[-1])");
  // SUM between the value in column -2 and column -1.

I literraly copied google syntax for this :

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];

var cell = sheet.getRange("B5");
// This sets the formula to be the sum of the 3 rows above B5
cell.setFormulaR1C1("=SUM(R[-3]C[0]:R[-1]C[0])");

But I'm still getting this type of error : Screen of my sheet

I've read some things about INDIRECT but I don't really understand how to use that and if that could be useful on what I'm dealing with.

If someone could explain to me why I'm getting an #ERROR, what is wrong with my instruction, it could be very nice.

Thanks for reading. Thinkpad23

Answer 1

You can not copy/paste the =SUM(R[-3]C[0]:R[-1]C[0]) parameter as a formula, it only works when executed with Google Apps Script by calling the setFormulaR1C1 function, which parses the rows and columns values and returns a valid formula.

In your case, for cell "I3" Apps Script would end up converting cell.setFormulaR1C1("=SUM(R[0]C[-2]:R[0]C[-1])"); to =SUM(G3:H3)

Answer 2

First of all, if you want to use R1C1 notation, you must use INDIRECT with the second parameter FALSE, i.e.

=INDIRECT("R[0]C[-2]",FALSE)+INDIRECT("R[0]C[-1]",FALSE)

nevertheless, with google sheets you should better use arrayformula at the top of the column

={"QTE_APRES";ARRAYFORMULA(G2:G+H2:H)}