google-apps-script - 如何根据来自另一个单元格的标准在所有工作表中获取单元格的总数？

Question

我有一个预算电子表格，每个支付期都有标签。这些选项卡是根据需要创建的，没有我可以提前知道的名称。例如，一个将是“10/15 - 10/28”，因为那是支付期。下个月我创建一个新的“10/29 - 11/11”。我希望能够对所有工作表求和一个值。例如，每个工作表都有一个名为“保存”的行，一些工作表有一个名为“租金”的行，但并非每个工作表都包含具有这些名称的行，而且当它们出现时，它们并不总是位于相同的单元格编号中。

样品表

我见过一些例子，其中有一堆 SUMIF，每张表都是手动命名的，但我宁愿不必这样做，因为这张表经常被复制，而且表名永远不会相同。

=SUMIFS('Tab 1' !A1:A10, 'Tab 1'!B1:B10, "Rent")
+SUMIFS('Tab 2' !A1:A10, 'Tab 2'!B1:B10, "Rent")
+SUMIFS('Tab 3' !A1:A10, 'Tab 3'!B1:B10, "Rent")

这是否可以使用标准公式或脚本？

样本数据

所需的最终选项卡

第 1 列的值是预先知道的，因此可以硬编码。例如，永远不会出现随机的“更多内容”，我不会通过在最终选项卡中添加新行来总结。

标签： google-apps-scriptgoogle-sheets

Answer 1

虽然还有另一个适用于此的答案，但我认为使用文本查找器和getRange,getValue和setFormulain 循环并不是最好的方法，因为它大大增加了对电子表格服务的调用量，减慢了脚本（请参阅最小化调用其他服务）。

方法一、onEdit触发：

一个选项是onEdit在用户编辑电子表格时使用触发器执行以下操作：

• 遍历所有工作表（不包括Totals）。
• 对于每张纸，循环遍历所有数据。
• 对于每一行，检查之前是否已找到该类别。
• 如果尚未找到，请将其（以及相应的金额）添加到存储总数的数组中（items在下面的函数中调用）。
• 如果已找到，请将当前金额添加到先前的总数中。
• 将结果数据写入Totals.

它可能是这样的（查看内联评论以获取更多详细信息）：

const TOTAL_SHEET_NAME = "Totals";
const FIRST_ROW = 4;

function onEdit(e) {
  const ss = e.source;
  const targetSheet = ss.getSheetByName(TOTAL_SHEET_NAME);
  const sourceSheets = ss.getSheets().filter(sheet => sheet.getName() !== TOTAL_SHEET_NAME);
  let items = [["Category", "Amount"]];
  sourceSheets.forEach(sheet => { // Loop through all source sheets
    const values = sheet.getRange(FIRST_ROW, 1, sheet.getLastRow()-FIRST_ROW+1, 2).getValues();
    values.forEach(row => { // Loop through data in a sheet
      const [category, amount] = row;
      const item = items.find(item => item[0] === category); // Find category
      if (!item) { // If category doesn't exist, create it
        items.push([category, amount]);
      } else { // If category exists, update the amount
        item[1] += amount;
      }
    });
  });
  targetSheet.getRange(FIRST_ROW-1, 1, items.length, items[0].length).setValues(items);
}

方法二、自定义函数：

另一种选择是使用 Apps Script Custom Function。

setValues在这种情况下，不需要通过写入数据，返回结果就足够了：

const TOTAL_SHEET_NAME = "Totals";
const FIRST_ROW = 4;

function CALCULATE_TOTALS() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sourceSheets = ss.getSheets().filter(sheet => sheet.getName() !== TOTAL_SHEET_NAME);
  let items = [["Category", "Amount"]];
  sourceSheets.forEach(sheet => { // Loop through all source sheets
    const values = sheet.getRange(FIRST_ROW, 1, sheet.getLastRow()-FIRST_ROW+1, 2).getValues();
    values.forEach(row => { // Loop through data in a sheet
      const [category, amount] = row;
      const item = items.find(item => item[0] === category); // Find category
      if (!item) { // If category doesn't exist, create it
        items.push([category, amount]);
      } else { // If category exists, update the amount
        item[1] += amount;
      }
    });
  });
  return items;
}

保存脚本后，您可以像使用任何工作表内置函数一样使用此函数：

这种方法的问题在于，当更改任何源数据时，公式不会自动重新计算。为此，请参阅上述方法。

方法3. onSelectionChange 触发器：

从您的评论中：

我希望能够在打开总计表时触发它，但这似乎不可能

您可以通过结合使用onSelectionChange触发器和PropertiesService来做到这一点。

这个想法是，每次用户更改单元格选择时，该函数都应该检查当前工作表是否是Totals以及先前活动的工作表是否不是Totals。如果是这种情况，这意味着用户刚刚打开了Totals工作表，结果应该会更新。

它可能是这样的：

function onSelectionChange(e) {
  const range = e.range;
  const sheet = range.getSheet();
  const sheetName = sheet.getName();
  const previousSheetName = PropertiesService.getUserProperties().getProperty("PREVIOUS_SHEET");
  if (sheetName === TOTAL_SHEET_NAME && previousSheetName !== TOTAL_SHEET_NAME) {
    updateTotals(e);
  }
  PropertiesService.getUserProperties().setProperty("PREVIOUS_SHEET", sheetName);
}

function updateTotals(e) {
  const ss = e.source;
  const targetSheet = ss.getSheetByName(TOTAL_SHEET_NAME);
  const sourceSheets = ss.getSheets().filter(sheet => sheet.getName() !== TOTAL_SHEET_NAME);
  let items = [["Category", "Amount"]];
  sourceSheets.forEach(sheet => { // Loop through all source sheets
    const values = sheet.getRange(FIRST_ROW, 1, sheet.getLastRow()-FIRST_ROW+1, 2).getValues();
    values.forEach(row => { // Loop through data in a sheet
      const [category, amount] = row;
      const item = items.find(item => item[0] === category); // Find category
      if (!item) { // If category doesn't exist, create it
        items.push([category, amount]);
      } else { // If category exists, update the amount
        item[1] += amount;
      }
    });
  });
  targetSheet.getRange(FIRST_ROW-1, 1, items.length, items[0].length).setValues(items);
}

注意：请注意，为了使此触发器起作用，您需要refresh the spreadsheet once the trigger is added and every time the spreadsheet is opened( ref )。

参考：

• onEdit(e)
• Google 表格中的自定义函数
• onSelectionChange(e)