如何在 App 脚本中循环 Google 表格公式

Question

我正在尝试通过 Google App Script 在 Google 表格中设置公式。

这是电子表格的插图：

我也尝试编写代码，但我不知道如何循环它，因此对于添加到单元格范围“A3:F”的每个值都会自动将其求和到总计列（G 列）。你能告诉我如何循环吗？您的回复将不胜感激 :)

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

}

编辑

这是对我有用的更新代码：

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

  

}

Answer 1

不需要循环。SetFormula 将自动调整到新的范围，就像自动填充一样：  const cell = s.getRange(`G3:G${s.getLastRow()}`);  cell.setFormula("=SUM((A3/100)*D3)+((B3/100)*E3)+((C3/100)*F3)");

Answer 2

您可以为公式创建一个“模板”，并在循环中用行号替换必要的参数。这是一个将公式从第 3 行设置到第 12 行的示例var ss&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; = SpreadsheetApp.getActiveSpreadsheet();var s&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;= ss.getSheetByName( 'Sheet1' );var firstRowNum&nbsp; &nbsp; &nbsp;= 3;var lastRowNum&nbsp; &nbsp; &nbsp; = 12;var formulas&nbsp; &nbsp; &nbsp; &nbsp; = [];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++ ) {&nbsp; &nbsp; formulas.push( [ formulaTemplate.replace( /{rowNum}/g, i ) ] );}s.getRange( 'G' + firstRowNum + ':G' + lastRowNum ).setFormulas( formulas );

Answer 3

您可以使用“简单”函数而不是脚本来执行此操作。SUMPRODUCT 很好，但它不会向下循环。您可以使用 ARRAYFORMULA，但您需要列出每个乘法，例如 A/100*D。要使用 ARRAYFORMULA 向下循环，每个列范围将采用 A3:A 等格式。因此，您在单元格 G3 中的公式为：=ARRAYFORMULA((A3:A/100*D3:D)+(B3:B/100*E3:E)+(C3:C/100*F3:F))注意：行号需要与您插入主 ARRAYFORMULA 的位置相同（即示例中的 3）。如果你弄错了，你的工作表会在你不知不觉中生成大量的行！！