匯總多份工作表中的資料

程式碼程度：初學者
時間長度：5 分鐘
專案類型：自訂函式

目標

• 瞭解解決方案的功能。
• 瞭解解決方案中的 Apps Script 服務功能。
• 設定指令碼。
• 執行指令碼。

認識這項解決方案

在試算表中，如果有多份工作表內含結構相似的資料，例如團隊成員的客服指標等，就可以用這個自訂函式為每份工作表製作摘要。這項解決方案的適用對象主要為客服支援單，但也能視需求自行調整。

運作方式

自訂函式 (名為 getSheetsData()) 會根據試算表中每個工作表的「狀態」欄，統整工作表的資料。這項指令碼會忽略不應納入匯總的試算表，例如「ReadMe」和「Summary」試算表。

Apps Script 服務

這項解決方案會使用下列服務：

• 試算表服務：取得需要摘要的試算表，並計算符合指定字串的項目數量。接著，指令碼會將計算出的資訊新增至試算表中呼叫自訂函式的位置。

必要條件

如要使用這個範例，您必須符合下列先決條件：

• Google 帳戶 (Google Workspace 帳戶可能需要管理員核准)。
• 可連上網際網路的網路瀏覽器。

設定指令碼

按一下下方按鈕，複製「Summarize spreadsheet data custom function」(摘要試算表資料自訂函式) 試算表。這個解決方案的 Apps Script 專案會附加至試算表。
建立副本

執行指令碼

1. 在複製的試算表中，前往「摘要」工作表。
2. 按一下儲存格 A4。getSheetsData() 函式位於這個儲存格中。
3. 前往任一擁有者工作表，更新或新增工作表中的資料。你可以嘗試採取下列幾項行動：
   • 新增一列，並填入票證資訊範例。
   • 在「狀態」欄中，變更現有支援單的狀態。
   • 變更「狀態」欄的位置。舉例來說，在「擁有者 1」工作表中，將「狀態」欄從 C 欄移至 D 欄。
4. 前往「摘要」工作表，查看 getSheetsData() 從儲存格 A4 建立的更新摘要表格。您可能需要在第 10 列中勾選核取方塊，重新整理自訂函式的快取結果。Google 會快取自訂函式，以提升效能。
   • 如果您新增或更新資料列，指令碼會更新支援單和狀態計數。
   • 如果移動「狀態」欄的位置，指令碼仍會使用新的欄索引，正常運作。

檢查程式碼

如要查看這項解決方案的 Apps Script 程式碼，請按一下下方的「查看原始碼」：

查看原始碼

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 試算表功能