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: stackoverflow.com/questions/46883862

TheMaster
– TheMaster

2020-07-28 10:45:25 +00:00
Commented Jul 28, 2020 at 10:45 — TheMaster
– TheMaster, Commented Jul 28, 2020 at 10:45

TheMaster · Accepted Answer · 2020-07-28 09:38:30Z

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, 2020 at 9:38

TheMaster

51.6k7 gold badges76 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Ray Over a year ago

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?

TheMaster Over a year ago

@Ray Is v8 enabled?

Ray Over a year ago

Yupp Chrome V8 enabled

Ray Over a year ago

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

TheMaster Over a year ago

@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?

Thum Choon Tat · Accepted Answer · 2020-07-28 09:35:44Z

2

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

answered Jul 28, 2020 at 9:35

Thum Choon Tat

3,0901 gold badge24 silver badges26 bronze badges

Comments

Aresvik · Accepted Answer · 2021-04-28 12:05:23Z

1

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!!

answered Apr 28, 2021 at 12:05

Aresvik

4,6181 gold badge8 silver badges18 bronze badges

Collectives™ on Stack Overflow

How To Loop Google Sheet Formula In App Script

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related