汇总多个工作表中的数据

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

目标

• 了解解决方案的用途。
• 了解 Apps 脚本服务在此解决方案中执行的操作。
• 设置脚本。
• 运行脚本。

关于此解决方案

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

运作方式

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

Apps 脚本服务

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

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

前提条件

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

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

设置脚本

点击下面的按钮可创建汇总电子表格数据自定义函数电子表格的副本。此解决方案的 Apps 脚本项目已附加到该电子表格中。
复制

运行脚本

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

查看代码

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

查看源代码

Code.gs

solutions/custom-functions/summarize-sheets-data/Code.js

在 GitHub 上查看

// 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() {
  let ss = SpreadsheetApp.getActiveSpreadsheet();
  let sheets = ss.getSheets();
  let outputArr = [];

  // For each sheet, summarizes the data and pushes to a temporary array.
  for (let s in sheets) {
    // Gets sheet name.
    let sheetNm = sheets[s].getName();
    // Skips ReadMe and Summary sheets.
    if (sheetNm === READ_ME_SHEET_NAME || sheetNm === PM_SHEET_NAME) { continue; }
    // Gets sheets data.
    let values = sheets[s].getDataRange().getValues();
    // Gets the first row of the sheet which is the header row.
    let 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.
    let columnOwner = headerRowValues.indexOf("Owner Name");
    let 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.
    let owner = values[0][columnOwner];
    // Counts the total number of tasks.
    let 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.
    let completeCnt = filterByPosition(values,'Complete', columnStatus).length;
    // Counts the number of tasks that have the 'In-Progress' status.
    let inProgressCnt = filterByPosition(values,'In-Progress', columnStatus).length;
    // Counts the number of tasks that have the 'Scheduled' status.
    let scheduledCnt = filterByPosition(values,'Scheduled', columnStatus).length;
    // Counts the number of tasks that have the 'Overdue' status.
    let 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 开发者专家的帮助下进行维护。

后续步骤

• Google 表格中的自定义函数
• 扩展 Google 表格功能