汇总多个工作表中的数据

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

目标

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

关于此解决方案

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

工作原理

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

Apps 脚本服务

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

• 电子表格服务 - 获取工作表 计算与查询中匹配的项的数量 指定字符串。然后，脚本将计算出的信息添加到某个范围， 自定义函数在电子表格中的调用位置。

前提条件

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

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

设置脚本

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

运行脚本

1. 在复制的电子表格中，转到摘要工作表。
2. 点击单元格 A4。getSheetsData() 函数位于此单元格中。
3. 转到其中一个所有者工作表，然后在该工作表中更新或添加数据。部分 您可以尝试以下操作：
   • 添加包含示例票券信息的新行。
   • 在 Status（状态）列中，更改现有工单的状态。
   • 更改状态列的位置。例如，在 Owner1 工作表，将 Status 列从 C 列移到 D 列。
4. 转到摘要表格并查看更新后的摘要表格 已使用单元格 A4 创建getSheetsData()。您可能需要选中 以刷新自定义函数的缓存结果。 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 表格