1

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

thinkpad23
  • 11
  • 3

2 Answers2

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)

Alfredo
  • 762
  • 4
  • 16
  • Maybe I misunderstood what you mean, but actually, I don't understand the purpose then, of the setFormulaR1C1 function if we can't use R1C1 notations? I'm just following the example given by Google... Sorry if I get your answer wrong, I don't understand everything because of the language barrier. Thinkpad23 – thinkpad23 Apr 12 '21 at 09:38
  • Do you know how to run a function using Google Apps Script? – Alfredo Apr 12 '21 at 09:45
1

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

enter image description here

Mike Steelson
  • 14,650
  • 2
  • 5
  • 20