跨多个选项卡的工作表过滤脚本在主工作表上显示空白行，并导致其他脚本停止运行

我有一个多方面的问题。

我正在尝试编写一个脚本来提取选项卡名称，并使用该信息从每个单元格中提取特定单元格（文档的选项卡经常更改），以便创建主表。主表旨在显示所有打开的操作项目，并过滤掉任何关闭的项目/空白行。到目前为止，我的脚本可以工作，但它会从每个选项卡中提取所有空行 - 这是我不想要的。

这是我为主表准备的 2 个当前脚本中的 1 个：

function onEdit(e) {
  
  //set variable
  const masterSheet = "Open Action Items";
  const mastersheetFormulaCell = "E2";
  const ignoreSheets = ["Template", "Blank"];
  const dataRange = "I2:K";
  const checkRange = "J2:J"
  //end set variables

  const ss = SpreadsheetApp.getActiveSpreadsheet();

  ignoreSheets.push(masterSheet);

  const allSheets = ss.getSheets();

  const filteredListOfSheets = allSheets.filter(s => ignoreSheets.indexOf(s.getSheetName()) == -1);

  let formulaArray = filteredListOfSheets.map(s => `iferror(FILTER('${s.getSheetName()}'!${dataRange}, NOT(ISBLANK('${s.getSheetName()}'!${checkRange}))),{"","",""})`);

  let formulaText = "={" + formulaArray.join(";") + "}";
 
  ss.getSheetByName(masterSheet).getRange(mastersheetFormulaCell).setFormula(formulaText);

}

另一部分是另一个脚本，当它是唯一运行的脚本时一直运行正常，但在引入其他脚本时已停止工作。该脚本根据 B 列中的条件向 C 列添加了一个复选框。

这是该脚本：

function onEdit(e) {
  if (e.range.getColumn() == 2) {
    var sheet = e.source.getActiveSheet();
    if (e.value === "Tech Note" || 
        e.value === "Intake Process")
      sheet.getRange(e.range.getRow(),3).insertCheckboxes();
    else
      sheet.getRange(e.range.getRow(),3).removeCheckboxes();
  }
}

这是一个样本表 https://docs.google.com/spreadsheets/d/1-Jd6Zm4VsxHAsRW29Dbm_0Nsmb0RjtP_8kmsl_1k0Kw/edit?usp=sharing

“打开操作项”选项卡是脚本要更新的主表。它应该列出其他选项卡中所有打开的项目（如下所述） “E3-O 案例注释副本”是一个选项卡，它是每个选项卡最终外观的基础。此选项卡的 F-K 列从 A-E 中拉出打开的项目。可能有一种更有效的方法来对整个工作表进行排序......

任何帮助表示感谢，谢谢！

我先解决第二个问题，因为这是一个更根本的问题。

另一部分是另一个脚本，当它是唯一运行的脚本时一直运行正常，但在引入其他脚本时已停止工作。

在附加到示例的脚本项目中，您有 3 个文件，每个文件定义一个onEdit()功能。这是有问题的，因为每次你定义onEdit()您正在重新定义相同的标识符。该项目只有一个全局作用域，因此只能有 1 个onEdit()定义的函数，无论您的项目包含多少个文件.

本质上，这相当于您在项目中定义的内容：

function onEdit(e) {
  console.log("onEdit #1");
}

function onEdit(e) {
  console.log("onEdit #2");
}

function onEdit(e) {
  console.log("onEdit #3");
}

onEdit();

运行上面的代码片段只会执行最后的定义onEdit().

为了完成您想要做的事情，您可以为您想要执行的所有操作定义唯一的函数，然后在一个onEdit()定义后，您可以调用这些函数。就像是：

function editAction1(e) {
  console.log("edit action #1");
}

function editAction2(e) {
  console.log("edit action #2");
}

function editAction3(e) {
  console.log("edit action #3");
}

function onEdit(e) {
  editAction1(e);
  editAction2(e);
  editAction3(e);
}

onEdit();

当定义一个onEdit()触发器，你确实想要优化它，以便它能够尽快完成其执行。从 Apps 脚本最佳实践来看，您要特别注意“尽量减少对其他服务的调用” https://developers.google.com/apps-script/guides/support/best-practices#minimize_calls_to_other_services and “使用批量操作” https://developers.google.com/apps-script/guides/support/best-practices#use_batch_operations.

给您一些具体提示：

• 避免重复调用相同的 Apps 脚本 API（例如Sheet.getName()）。相反，运行一次并将值存储在局部变量中。
• 尽可能避免在循环内和传递给方法的回调函数中进行 Apps 脚本 API 调用，例如Array.prototype.filter() and Array.prototype.map().
• 当您确实需要循环访问数据时，尤其是涉及 Apps 脚本 API 调用时，请尽量减少循环访问数据的次数。
• With onEdit()触发器，尝试构建逻辑，以便识别可以提前退出的情况（类似于在继续操作复选框之前执行列检查的方式）。我怀疑您实际上需要遍历所有工作表并为每次编辑更新“打开操作项”公式。如果我正确解释了该公式，那么只有在添加或删除工作表时才应该执行此操作。

最后，要解决公式输出中的空白行，而不是使用SORT()对可以使用的空白行进行分组QUERY()真正过滤掉它们。

就像是：

=QUERY({ <...array contents...> }, "select * where Col1 is not null")

使用时请注意QUERY()您需要注意输入数据的类型是否一致。从文档 https://support.google.com/docs/answer/3093343?hl=en（强调我的）：

如果单列中存在混合数据类型，则使用大多数数据类型 确定用于查询目的的列的数据类型。少数民族 数据类型被视为空值.

在您的示例表中，许多示例数据各不相同，并且与您实际期望看到的不匹配（例如，“dghdgh”作为日期列中的值）。考虑到上面的警告，这一点很重要......当给定列（即数字和字符串）混合数据类型时，无论哪种类型最不流行，都将默默地被视为空。

在仔细、端到端地查看您的示例后，我注意到您正在执行一系列非常复杂的转换（例如，在数据表中存在隐藏的“D”列，QUERY()实际数据右侧的列等）。这一切最终导致大量并行QUERY()您通过您的onEdit()执行。

这一切都可以变得更加简单。这是简化 Apps 脚本代码的过程，这还取决于清理其附加的电子表格。

function onEdit(e) {
  /*
  Both onEdit actions are specific to a subset of the sheets. This
  regular expression is passed to both functions to facilitate only
  dealing with the desired sheets.
  */
  const validSheetPattern = /^E[0-9]+/;
  
  updateCheckboxes(e, validSheetPattern);
  updateActionItems(e, validSheetPattern);
}

function updateCheckboxes(e, validSheetPattern) {
  const sheet = e.range.getSheet();

  // Return immediately if the checkbox manipulation is unnecessary.
  if (!validSheetPattern.exec(sheet.getName())) return;
  if (e.range.getColumn() != 2) return;

  const needsCheckbox = ["Tech Note", "Intake Process"];
  const checkboxCell = sheet.getRange(e.range.getRow(), 3);
  if (needsCheckbox.includes(e.value)) {
      checkboxCell.insertCheckboxes();
  } else {
      checkboxCell.removeCheckboxes();
  }
}

function updateActionItems(e, validSheetPattern) { 
  const masterSheetName = "Open Action Items";
  const dataLocation = "A3:E";

  /*
  Track the data you need for generating formauls in an array
  of objects. Adding new formulas should be as simple as adding
  another object here, as opposed to duplicating the logic
  below with a growing set of manually indexed variable names
  (e.g. cell1/cell2/cell3, range1/range2/range3, etc.).
  */
  const formulas = [
    {
      location: "A3",
      code: "Tech Note",
    },
    {
      location: "E3",
      code: "Intake Process",
    },
  ];
  
  const masterSheet = e.source.getSheetByName(masterSheetName);
  const sheets = e.source.getSheets();
  
  /*
  Instead of building an array of QUERY() calls, build an array of data ranges that
  can be used in a single QUERY() call.
  */
  let dataRangeParts = [];
  for (const sheet of sheets) {
    // Only call getSheetName() once, instead of multiple times throughout the loop.
    const name = sheet.getSheetName();

    // Skip this iteration of the loop if we're not dealing with a data sheet.
    if (!validSheetPattern.exec(name)) continue;

    dataRangeParts.push(`'${name}'!${dataLocation}`);
  }
  const dataRange = dataRangeParts.join(";");
    
  for (const formula of formulas) {
    /*
    And instead of doing a bunch of intermediate transformations within the sheet,
    just query the data directly in this generated query.
    */
    const query = `SELECT Col5,Col1,Col4 WHERE Col2='${formula.code}' AND Col3=FALSE`;
    const formulaText = `IFERROR(QUERY({${dataRange}},"${query}"),{"","",""})`;
    
    formula.cell = masterSheet.getRange(formula.location);
    formula.cell.setFormula(formulaText);
  }
}

Here's 您可以参考的修改后的示例电子表格 https://docs.google.com/spreadsheets/d/11nNf0w8W8fQU5W6yMzN-KJ1wMj_NQbH4Wyq3Kg8txoc/edit?usp=sharing.

我做出的一个让步是数据表仍然有一个“站点代码”列，该列是通过公式自动填充的。拥有您输入的范围内的所有数据QUERY()使“打开操作项目”表的整体公式变得更加简单。