汇总多个工作表中的数据

编码级别：初级
时长：5 分钟
项目类型：自定义函数

目标

  • 了解此解决方案的功能。
  • 了解 Apps 脚本服务在此解决方案中的功能。
  • 设置脚本。
  • 运行脚本。

关于此解决方案

如果您在一个电子表格的多个工作表中拥有类似的结构化数据，例如团队成员的客户服务指标，则可以使用此自定义函数来创建每个工作表的摘要。此解决方案侧重于客户支持服务工单，但您可以根据自己的需求进行自定义。

由 getSheetsData 函数生成的显示工单状态计数的汇总表。

运作方式

自定义函数 getSheetsData() 会根据工作表的状态 列汇总电子表格中每个工作表的数据。该脚本会忽略不应包含在汇总中的工作表，例如 ReadMe 和摘要 工作表。

Apps 脚本服务

此解决方案使用以下服务：

  • 电子表格服务 - 获取需要汇总的工作表 ，并统计与指定字符串匹配的项数 。然后，该脚本会将计算出的信息添加到电子表格中调用自定义函数的位置的相关范围。

前提条件

如需使用此示例，您需要满足以下前提条件：

  • Google 账号（Google Workspace 账号可能需要管理员批准）。
  • 可访问互联网的网络浏览器。

设置脚本

如需复制汇总电子表格数据自定义函数 电子表格，请点击以下按钮：

复制聊天机器人

此解决方案的 Apps 脚本项目已附加到电子表格。

运行脚本

  1. 在复制的电子表格中，前往摘要 工作表。
  2. 点击单元格 A4。getSheetsData() 函数位于此单元格中。
  3. 前往其中一个所有者工作表，然后更新或向该工作表添加数据。您可以尝试执行以下操作：
    • 添加包含示例工单信息的新行。
    • 在状态 列中，更改现有工单的状态。
    • 更改状态 列的位置。例如，在 Owner1 工作表中，将状态 列从 C 列移到 D 列。
  4. 前往摘要 工作表，然后查看 getSheetsData() 从单元格 A4 创建的更新后的摘要表。您可能需要选中第 10 行中的复选框 ，以刷新自定义函数的缓存结果。 Google 会缓存自定义函数，以优化性能。
    • 如果您添加或更新了行，该脚本会更新工单和状态计数。
    • 如果您移动了状态 列的位置，该脚本仍会按预期使用新的列索引。

查看代码

如需查看此解决方案的 Apps 脚本代码，请点击 查看源代码：

查看源代码

Code.gs

solutions/custom-functions/summarize-sheets-data/Code.js
// To learn how to use this script, refer to the documentation:
// https://developers.google.com/apps-script/samples/custom-functions/summarize-sheets-data

/*
Copyright 2022 Google LLC

Licensed under the Apache License, Version 2.0 (the "License");
you may not use this file except in compliance with the License.
You may obtain a copy of the License at

    https://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.
*/

/**
 * Gets summary data from other sheets. The sheets you want to summarize must have columns with headers that match the names of the columns this function summarizes data from.
 *
 * @return {string} Summary data from other sheets.
 * @customfunction
 */

// The following sheets are ignored. Add additional constants for other sheets that should be ignored.
const READ_ME_SHEET_NAME = "ReadMe";
const PM_SHEET_NAME = "Summary";

/**
 * Reads data ranges for each sheet. Filters and counts based on 'Status' columns. To improve performance, the script uses arrays
 * until all summary data is gathered. Then the script writes the summary array starting at the cell of the custom function.
 */
function getSheetsData() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheets = ss.getSheets();
  const outputArr = [];

  // For each sheet, summarizes the data and pushes to a temporary array.
  for (const s in sheets) {
    // Gets sheet name.
    const sheetNm = sheets[s].getName();
    // Skips ReadMe and Summary sheets.
    if (sheetNm === READ_ME_SHEET_NAME || sheetNm === PM_SHEET_NAME) {
      continue;
    }
    // Gets sheets data.
    const values = sheets[s].getDataRange().getValues();
    // Gets the first row of the sheet which is the header row.
    const headerRowValues = values[0];
    // Finds the columns with the heading names 'Owner Name' and 'Status' and gets the index value of each.
    // Using 'indexOf()' to get the position of each column prevents the script from breaking if the columns change positions in a sheet.
    const columnOwner = headerRowValues.indexOf("Owner Name");
    const columnStatus = headerRowValues.indexOf("Status");
    // Removes header row.
    values.splice(0, 1);
    // Gets the 'Owner Name' column value by retrieving the first data row in the array.
    const owner = values[0][columnOwner];
    // Counts the total number of tasks.
    const taskCnt = values.length;
    // Counts the number of tasks that have the 'Complete' status.
    // If the options you want to count in your spreadsheet differ, update the strings below to match the text of each option.
    // To add more options, copy the line below and update the string to the new text.
    const completeCnt = filterByPosition(
      values,
      "Complete",
      columnStatus,
    ).length;
    // Counts the number of tasks that have the 'In-Progress' status.
    const inProgressCnt = filterByPosition(
      values,
      "In-Progress",
      columnStatus,
    ).length;
    // Counts the number of tasks that have the 'Scheduled' status.
    const scheduledCnt = filterByPosition(
      values,
      "Scheduled",
      columnStatus,
    ).length;
    // Counts the number of tasks that have the 'Overdue' status.
    const overdueCnt = filterByPosition(values, "Overdue", columnStatus).length;
    // Builds the output array.
    outputArr.push([
      owner,
      taskCnt,
      completeCnt,
      inProgressCnt,
      scheduledCnt,
      overdueCnt,
      sheetNm,
    ]);
  }
  // Writes the output array.
  return outputArr;
}

/**
 * Below is a helper function that filters a 2-dimenstional array.
 */
function filterByPosition(array, find, position) {
  return array.filter((innerArray) => innerArray[position] === find);
}

修改

您可以根据需要随意修改自定义函数。如需查看 手动刷新自定义函数结果的可选添加项， 请点击 刷新缓存的结果：

刷新缓存的结果

与内置函数不同，Google 会缓存自定义函数，以优化 性能。这意味着，如果您更改自定义 函数中的某些内容（例如正在计算的值），系统可能不会立即 强制更新。如需手动刷新函数结果，请按以下步骤操作：

  1. 点击插入 > 复选框，向空单元格添加复选框。
  2. 将包含复选框的单元格添加为自定义函数的形参， 例如 getSheetsData(B11)。
  3. 选中或取消选中复选框，以刷新自定义函数结果。

贡献者

此示例由 Google 在 Google 开发者专家的帮助下维护。

后续步骤