汇总多个工作表中的数据
使用集合让一切井井有条
根据您的偏好保存内容并对其进行分类。
编码水平:初级
时长:5 分钟
项目类型:自定义函数
目标
- 了解解决方案的功能。
- 了解 Apps 脚本服务在解决方案中的作用。
- 设置脚本。
- 运行脚本。
关于此解决方案
如果您在一个电子表格的多个工作表中拥有类似的结构化数据,例如团队成员的客户服务指标,则可以使用此自定义函数来创建每个工作表的摘要。此解决方案侧重于客户支持服务工单,但您可以根据自己的需求进行自定义。

运作方式
名为 getSheetsData()
的自定义函数会根据电子表格中每个工作表的状态列来汇总数据。该脚本会忽略不应纳入汇总范围的工作表,例如 ReadMe 和 Summary 工作表。
Apps 脚本服务
此解决方案使用以下服务:
- 电子表格服务 - 获取需要汇总的工作表,并统计与指定字符串匹配的项数。然后,脚本会将计算出的信息添加到电子表格中调用自定义函数的位置的相关范围。
前提条件
如需使用此示例,您需要满足以下前提条件:
- Google 账号(Google Workspace 账号可能需要管理员批准)。
- 可访问互联网的网络浏览器。
设置脚本
点击下方按钮,复制总结电子表格数据自定义函数电子表格。此解决方案的 Apps 脚本项目已附加到电子表格。
制作副本
运行脚本
- 在复制的电子表格中,前往摘要工作表。
- 点击单元格
A4
。getSheetsData()
函数位于此单元格中。
- 前往其中一个所有者工作表,然后更新或向该工作表添加数据。您可以尝试采取以下措施:
- 添加包含示例工单信息的新行。
- 在状态列中,更改现有支持请求的状态。
- 更改状态列的位置。例如,在 Owner1 工作表中,将状态列从 C 列移到 D 列。
- 前往摘要工作表,查看
getSheetsData()
根据单元格 A4
创建的更新后的摘要表。您可能需要勾选第 10 行中的复选框,以刷新自定义函数的缓存结果。Google 会缓存自定义函数,以优化性能。
- 如果您添加或更新了行,脚本会更新工单和状态计数。
- 如果您移动了状态列的位置,脚本仍会按预期使用新的列索引运行。
查看代码
如需查看此解决方案的 Apps 脚本代码,请点击下方的查看源代码:
修改
您可以根据需要随意修改自定义函数。以下是手动刷新自定义函数结果的可选补充。
刷新缓存结果
与内置函数不同,Google 会缓存自定义函数以优化性能。这意味着,如果您更改自定义函数中的某些内容(例如正在计算的值),系统可能不会立即强制更新。如需手动刷新函数结果,请按以下步骤操作:
- 如需向空白单元格添加复选框,请依次点击插入
> 复选框。
- 将包含复选框的单元格添加为自定义函数的参数,例如
getSheetsData(B11)
。
- 选中或取消选中相应复选框,以刷新自定义函数结果。
贡献者
此示例由 Google 在 Google 开发者专家的帮助下维护。
后续步骤
如未另行说明,那么本页面中的内容已根据知识共享署名 4.0 许可获得了许可,并且代码示例已根据 Apache 2.0 许可获得了许可。有关详情,请参阅 Google 开发者网站政策。Java 是 Oracle 和/或其关联公司的注册商标。
最后更新时间 (UTC):2025-08-31。
[null,null,["最后更新时间 (UTC):2025-08-31。"],[[["\u003cp\u003eThis custom function, \u003ccode\u003egetSheetsData()\u003c/code\u003e, summarizes data from multiple sheets in a spreadsheet, excluding "ReadMe" and "Summary" sheets, based on the "Status" column.\u003c/p\u003e\n"],["\u003cp\u003eThe function utilizes the Spreadsheet service to retrieve sheet data, count items matching specific statuses, and display the summarized information in a table.\u003c/p\u003e\n"],["\u003cp\u003eUsers need a Google Account and a web browser to use this solution; to set it up, they should make a copy of the provided spreadsheet containing the script.\u003c/p\u003e\n"],["\u003cp\u003eTo run the script, users navigate to the "Summary" sheet, observe the function output, modify data in other sheets, and check for updates on the "Summary" sheet, optionally refreshing the cache if necessary.\u003c/p\u003e\n"]]],["The custom function `getSheetsData()` summarizes data from multiple sheets in a spreadsheet, excluding \"ReadMe\" and \"Summary\" sheets. It analyzes each sheet's \"Status\" column, counts tasks based on status (e.g., Complete, In-Progress), and totals all tasks per sheet. The script uses the Spreadsheet service to get sheet data and then adds the calculated information in a summary format to the location in the spreadsheet where the custom function is entered. It is designed for sheets with similarly structured data.\n"],null,["# Summarize data from multiple sheets\n\n**Coding level** : Beginner \n\n**Duration** : 5 minutes \n\n**Project type** : [Custom function](/apps-script/guides/sheets/functions)\n\nObjectives\n----------\n\n- Understand what the solution does.\n- Understand what the Apps Script services do within the solution.\n- Set up the script.\n- Run the script.\n\nAbout this solution\n-------------------\n\nIf you have similarly structured data on multiple sheets in a spreadsheet, such\nas customer support metrics for team members, you can use this custom function\nto create a summary of each sheet. This solution focuses on customer support\ntickets, but you can customize it to fit your needs.\n\n### How it works\n\nThe custom function, called `getSheetsData()`, summarizes data from each sheet\nin the spreadsheet based on a sheet's **Status** column. The script ignores\nsheets that shouldn't be included in the aggregation, such as the **ReadMe** and\n**Summary** sheets.\n\n### Apps Script services\n\nThis solution uses the following service:\n\n- [Spreadsheet service](/apps-script/reference/spreadsheet)--Gets the sheets that need to be summarized and counts the number of items that match a specified string. Then, the script adds the calculated information to a range relative to where the custom function was called in the spreadsheet.\n\nPrerequisites\n-------------\n\nTo use this sample, you need the following prerequisites:\n\n- A Google Account (Google Workspace accounts might require administrator approval).\n- A web browser with access to the internet.\n\nSet up the script\n-----------------\n\nClick the button below to make a copy of the **Summarize spreadsheet data\ncustom function** spreadsheet. The Apps Script project for\nthis solution is attached to the spreadsheet.\n\n[Make a copy](https://docs.google.com/spreadsheets/d/1ZyhrTm9q98oOWzKgD6OVb31Yc38ODEHINOOjgVwQpCg/copy)\n\nRun the script\n--------------\n\n1. In your copied spreadsheet, go to the **Summary** sheet.\n2. Click cell `A4`. The `getSheetsData()` function is in this cell.\n3. Go to one of the owner sheets and update or add data to the sheet. Some actions you can try include the following:\n - Add a new row with sample ticket information.\n - In the **Status** column, change the status of an existing ticket.\n - Change the position of the **Status** column. For example, in the **Owner1** sheet, move the **Status** column from column C to column D.\n4. Go to the **Summary** sheet and review the updated summary table that `getSheetsData()` created from cell `A4`. You might need to check the checkbox in row 10 to [refresh the cached results](#cache) of the custom function. Google caches custom functions to optimize performance.\n - If you added or updated rows, the script updates the ticket and status counts.\n - If you moved the position of the **Status** column, the script still works as intended with the new column index.\n\nReview the code\n---------------\n\nTo review the Apps Script code for this solution, click **View source code**\nbelow: \n\n#### View source code\n\n### Code.gs\n\n\u003cbr /\u003e\n\nsolutions/custom-functions/summarize-sheets-data/Code.js \n[View on GitHub](https://github.com/googleworkspace/apps-script-samples/blob/main/solutions/custom-functions/summarize-sheets-data/Code.js) \n\n```javascript\n// To learn how to use this script, refer to the documentation:\n// https://developers.google.com/apps-script/samples/custom-functions/summarize-sheets-data\n\n/*\nCopyright 2022 Google LLC\n\nLicensed under the Apache License, Version 2.0 (the \"License\");\nyou may not use this file except in compliance with the License.\nYou may obtain a copy of the License at\n\n https://www.apache.org/licenses/LICENSE-2.0\n\nUnless required by applicable law or agreed to in writing, software\ndistributed under the License is distributed on an \"AS IS\" BASIS,\nWITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.\nSee the License for the specific language governing permissions and\nlimitations under the License.\n*/\n\n/**\n * 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.\n * \n * @return {string} Summary data from other sheets.\n * @customfunction\n */\n\n// The following sheets are ignored. Add additional constants for other sheets that should be ignored.\nconst READ_ME_SHEET_NAME = \"ReadMe\";\nconst PM_SHEET_NAME = \"Summary\";\n\n/**\n * Reads data ranges for each sheet. Filters and counts based on 'Status' columns. To improve performance, the script uses arrays \n * until all summary data is gathered. Then the script writes the summary array starting at the cell of the custom function.\n */\nfunction getSheetsData() {\n let ss = SpreadsheetApp.getActiveSpreadsheet();\n let sheets = ss.getSheets();\n let outputArr = [];\n\n // For each sheet, summarizes the data and pushes to a temporary array.\n for (let s in sheets) {\n // Gets sheet name.\n let sheetNm = sheets[s].getName();\n // Skips ReadMe and Summary sheets.\n if (sheetNm === READ_ME_SHEET_NAME || sheetNm === PM_SHEET_NAME) { continue; }\n // Gets sheets data.\n let values = sheets[s].getDataRange().getValues();\n // Gets the first row of the sheet which is the header row.\n let headerRowValues = values[0];\n // Finds the columns with the heading names 'Owner Name' and 'Status' and gets the index value of each.\n // Using 'indexOf()' to get the position of each column prevents the script from breaking if the columns change positions in a sheet.\n let columnOwner = headerRowValues.indexOf(\"Owner Name\");\n let columnStatus = headerRowValues.indexOf(\"Status\");\n // Removes header row.\n values.splice(0,1);\n // Gets the 'Owner Name' column value by retrieving the first data row in the array.\n let owner = values[0][columnOwner];\n // Counts the total number of tasks.\n let taskCnt = values.length;\n // Counts the number of tasks that have the 'Complete' status.\n // If the options you want to count in your spreadsheet differ, update the strings below to match the text of each option.\n // To add more options, copy the line below and update the string to the new text.\n let completeCnt = filterByPosition(values,'Complete', columnStatus).length;\n // Counts the number of tasks that have the 'In-Progress' status.\n let inProgressCnt = filterByPosition(values,'In-Progress', columnStatus).length;\n // Counts the number of tasks that have the 'Scheduled' status.\n let scheduledCnt = filterByPosition(values,'Scheduled', columnStatus).length;\n // Counts the number of tasks that have the 'Overdue' status.\n let overdueCnt = filterByPosition(values,'Overdue', columnStatus).length;\n // Builds the output array.\n outputArr.push([owner,taskCnt,completeCnt,inProgressCnt,scheduledCnt,overdueCnt,sheetNm]);\n }\n // Writes the output array.\n return outputArr;\n}\n\n/**\n * Below is a helper function that filters a 2-dimenstional array.\n */\nfunction filterByPosition(array, find, position) {\n return array.filter(innerArray =\u003e innerArray[position] === find);\n}\n```\n\n\u003cbr /\u003e\n\n\u003cbr /\u003e\n\nModifications\n-------------\n\nYou can edit the custom function as much as you'd like to fit your needs. Below\nis an optional addition to manually refresh custom function results. \n\n#### Refresh cached results\n\n\nUnlike built-in functions, Google caches custom functions to optimize\nperformance. This means that if you change something within your custom\nfunction, such as a value that's being calculated, it might not immediately\nforce an update. To refresh the function result manually, take the following\nsteps:\n\n1. Add a checkbox to an empty cell by clicking **Insert** \\\u003e **Checkbox**.\n2. Add the cell that has the checkbox as a parameter of the custom function, for example, `getSheetsData(B11)`.\n3. Check or uncheck the checkbox to refresh the custom function results.\n\nContributors\n------------\n\nThis sample is maintained by Google with the help of Google Developer Experts.\n\nNext steps\n----------\n\n- [Custom functions in Google Sheets](/apps-script/guides/sheets/functions)\n- [Extending Google Sheets](/apps-script/guides/sheets)"]]