How To Loop Google Sheet Formula In App Script

Question

I'm trying to set a formula in Google Sheet from Google App Script.

Here's the illustration of the Spreadsheet:

I also tried to make the code but I don't know how to loop it so for every values added to cell range 'A3:F' will automatically SUM it to the Total column (column G). Could you show me how to loop it? Your response will be appreciated :)

function onEdit(e){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = ss.getSheetByName('Sheet1');

  var cell = s.getRange("G3");
  cell.setFormula("=SUM((A3/100)*D3)+((B3/100)*E3)+((C3/100)*F3)");
}

EDIT

Here's the updated code that works for me:

function onEdit(e){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = ss.getSheetByName('Sheet1');

  const cell = s.getRange(3, 7, s.getLastRow());
  cell.setFormula("=SUM((A3/100)*D3)+((B3/100)*E3)+((C3/100)*F3)");
  
}

Possibly related: https://stackoverflow.com/questions/46883862 — TheMaster, Jul 28 '20 at 10:45

score 4 · Accepted Answer · answered Jul 28 '20 at 09:38

4

Loop is not needed. SetFormula will automatically adjust to a new range, as if autofilled:

  const cell = s.getRange(`G3:G${s.getLastRow()}`);
  cell.setFormula("=SUM((A3/100)*D3)+((B3/100)*E3)+((C3/100)*F3)");

answered Jul 28 '20 at 09:38

TheMaster

45,448
6
62
85

this code looks simple. But I cannot apply it, there's error " Exception: Range not found (line 6, file "sheet") ". Line 6 is where the getLastRow code is. Do you know what's the issue? – Ray Jul 28 '20 at 10:12
@Ray Is v8 enabled? – TheMaster Jul 28 '20 at 10:15
Yupp Chrome V8 enabled – Ray Jul 28 '20 at 10:23
The one in my question is the full code for my onEdit function. I tried 'const cell = s.getRange(3, 7, s.getLastRow());' but it shows 0 on the rest of the blank row in the Total column – Ray Jul 28 '20 at 10:37
@ray *I tried 'const cell = s.getRange(3, 7, s.getLastRow());' but it shows 0 on the rest of the blank row in the Total column* Is there a formula in all of column G? – TheMaster Jul 28 '20 at 10:41

score 2 · Answer 2 · answered Jul 28 '20 at 09:35

You can have a "template" for your formula and replace the necessary params with the row number in a loop. Here's an example that sets the formula from row 3 to row 12

var ss              = SpreadsheetApp.getActiveSpreadsheet();
var s               = ss.getSheetByName( 'Sheet1' );
var firstRowNum     = 3;
var lastRowNum      = 12;
var formulas        = [];
var formulaTemplate = '=SUM((A{rowNum}/100)*D{rowNum})+((B{rowNum}/100)*E{rowNum})+((C{rowNum}/100)*F{rowNum})';

for ( var i = firstRowNum; i <= lastRowNum; i++ ) {
    formulas.push( [ formulaTemplate.replace( /{rowNum}/g, i ) ] );
}

s.getRange( 'G' + firstRowNum + ':G' + lastRowNum ).setFormulas( formulas );

score 1 · Answer 3 · answered Apr 28 '21 at 12:05

You can do this with a 'simple' function instead of scripts.

SUMPRODUCT is good but it doesn't loop down. You can use the ARRAYFORMULA, but you'll need to list out each multiplication like A/100*D.

To loop down with ARRAYFORMULA, each column range would be in the format A3:A etc.

Your formula in cell G3 would therefore be:

=ARRAYFORMULA((A3:A/100*D3:D)+(B3:B/100*E3:E)+(C3:C/100*F3:F))

NOTE: row numbers need to be the same as where you've inserted the main ARRAYFORMULA (ie. 3 as per the example). If you get it wrong, your sheet can generate a huge number of rows before you know it!!

How To Loop Google Sheet Formula In App Script

3 Answers3