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

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

• javascript
• google-apps-script
• google-sheets
• google-sheets-formula

• Possibly related: stackoverflow./questions/46883862 – TheMaster Commented Jul 28, 2020 at 10:45

3 Answers 3

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

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

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