如何在 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              = 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 );

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）。如果你弄错了，你的工作表会在你不知不觉中生成大量的行！！