匯總多份工作表中的資料

程式設計層級：新手
時間長度：5 分鐘
專案類型：自訂函式

目標

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

認識這項解決方案

如果試算表的多張工作表中有類似的結構化資料 (例如團隊成員的客戶支援指標)，您可以利用這個自訂函式，為每張工作表建立摘要。本解決方案著重於客戶服務票證，但您可以視需求自訂。

運作方式

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

Apps Script 服務

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

• 試算表服務 - 取得需要匯總並計算符合指定字串的項目數量的工作表。然後，指令碼會將計算結果新增至相對於試算表呼叫自訂函式的位置範圍。

必要條件

如要使用這個範例，您必須具備以下先決條件：

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

設定指令碼

點選下方按鈕即可建立「匯總試算表資料自訂函式」試算表的副本。這項解決方案的 Apps Script 專案已附加至試算表。
建立副本

執行指令碼

1. 在複製的試算表中，前往「摘要」工作表。
2. 按一下儲存格 A4。getSheetsData() 函式位於這個儲存格中。
3. 前往任一擁有者的工作表，在工作表中更新或新增資料。您可以嘗試加入以下動作：
   • 請新增一列，當中包含支援單資訊範例。
   • 在「狀態」欄中變更現有支援單的狀態。
   • 變更「狀態」欄的位置。例如，在「Owner1」Owner1工作表中，將「Status」Owner1欄從 C 欄移至 D 欄。
4. 前往「Summary」工作表，查看 getSheetsData() 透過儲存格 A4 建立的更新後的摘要表格。您可能需要勾選第 10 列中的核取方塊，才能重新整理自訂函式的快取結果。Google 會快取自訂函式，將效能最佳化。
   • 如果您新增或更新資料列，指令碼會更新票券和狀態計數。
   • 如果您移動「Status」欄的位置，指令碼仍如預期般運作新資料欄索引。

檢查程式碼

如要查看這個解決方案的 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 試算表