如何使用Google脚本将联接和筛选公式应用于Google工作表中的整个列？

Question

我对谷歌脚本非常陌生，因此非常感谢任何帮助。

我被困在如何将我的公式应用到Google表单中的整个列上，该公式使用联接和筛选。

我的公式是：=JOIN（“，”，FILTER（N:N，B:B=R2））

我需要将这个公式添加到列S中的每个单元格（除了标题单元格），但是每行都有“R2”的变化，所以在第3行是“R3”，第4行是“R4”等。

这个公式在谷歌工作表本身，但因为我有一个新的更新版本每天自动更换的工作表，我需要设置一个谷歌脚本运行在一定的时间，我可以通过触发器设置添加这个公式到我指定的列。

我尝试了一些我在网上找到的脚本，但没有一个成功。

Answer 1

可以使用setFormulas（公式）将一组公式设置为范围内的所有单元格<在本例中，代码>公式是指二维数组，外部数组表示不同的行，每个内部数组表示每个特定行中的不同列。您应该使用不同的公式构建这个2D数组，同时考虑到R中的行索引对于每个公式都应该不同。

你可以这样做：

function settingFormulas() {
  var sheet = SpreadsheetApp.getActive().getSheetByName("Sheet1");
  var firstRow = 2;
  var column = 19; // Column S index
  var range = sheet.getRange(firstRow, column, sheet.getLastRow() - firstRow + 1);
  var formulas = range.getValues().map((row, index) => {
    let rowIndex = index + firstRow;
    return ["=JOIN(\", \",FILTER(N:N,B:B=R" + rowIndex + "))"];
  });
  range.setFormulas(formulas);
}

• 在这个函数中，方法映射中可选的index参数用于跟踪行索引，并将其添加到公式中。
• 在此函数中，工作表名称用于标识函数必须将公式设置为哪个工作表（在本例中，名称为Sheet1）。在这里，我假设一旦工作表被新的工作表替换，工作表名称保持不变。

拥有此函数后，您只需安装时间驱动触发器即可每天执行此函数，可以按照以下步骤手动执行，也可以通过编程方式运行此函数一次：

function creatingTrigger() {
  ScriptApp.newTrigger("settingFormulas")
    .timeBased()
    .everyDays(1)
    .create();
}

• 设置公式（公式）
• getRange（行、列、numRows）
• 可安装触发器

Answer 2

由于您的公式始终采用以下格式：

=JOIN（"，"， FILTER（N： N， B： B=R

如果要将其应用于大量行，可以使用间接和行来实现动态公式。这个答案有一个很好的例子说明如何使用它。

使用公式，您不会因为应用程序脚本而面临时间限制的风险

您的最终公式应如下所示：

=JOIN(", ",FILTER($N:$N,B:B=INDIRECT(CONCAT("R",ROW($R2)))))

此外，您还可以像任何其他公式一样将其向下拖动到其他单元格！