汇总多个工作表中的数据

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

目标

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

关于此解决方案

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

工作原理

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

Apps Script 服务

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

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

前提条件

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

• 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

在 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 表格