Google Apps Script how to increment with setFormula?

Question

I have this code that works well. The loop copy the formula over the cells i need.

  for(i=0;i<52;i++){
  activeSheet.getRange(124,2+i).setValue(text2);
  activeSheet.getRange(125,2+i).setFormula("=SUMIF($A$1:$A$40;\""+text+"\";B1:B40)*B124"); }

However I need the formula to increment during the loop. As exemple I need the loop to write this :

cell 1: =SUMIF($A$1:$A$40;\""+text+"\";B1:B40)*B124
cell 2: =SUMIF($A$1:$A$40;\""+text+"\";C1:C40)*B124
cell 3: =SUMIF($A$1:$A$40;\""+text+"\";D1:D40)*B124

Can someone help with this ?

Make more vars. You can loop over the alphabet with https://stackoverflow.com/questions/12504042/what-is-a-method-that-can-be-used-to-increment-letters — mplungjan, Dec 01 '17 at 17:21
Do you need to set the formulas in the range `B1:BA40`, and the last bottom right formula would be `=SUMIF($A$1:$A$40;\""+text+"\";BA1:BA40)*B124` ? — Slai, Dec 01 '17 at 23:45

score 1 · Answer 1 · answered Dec 01 '17 at 23:51

All formulas can be set at once. The relative rows or columns without $ will be auto adjusted:

// getRange(row, column, numRows, numColumns)
activeSheet.getRange(124, 2, 1, 51).setValue(text2)

activeSheet.getRange("B1:BA40").setFormula("=SUMIF($A$1:$A$40;\""+text+"\";B$1:B$40)*B$124")

Google Apps Script how to increment with setFormula?

1 Answers1