匯總多份工作表中的資料

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

目標

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

認識這項解決方案

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

運作方式

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

Apps Script 服務

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

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

必要條件

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

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

設定指令碼

點選下方按鈕，複製「匯總試算表資料的自訂函式」試算表。這個解決方案的 Apps Script 專案已附加到試算表中。
「建立副本」

執行指令碼

1. 在複製的試算表中，前往「摘要」工作表。
2. 按一下儲存格 A4。getSheetsData() 函式位於這個儲存格中。
3. 前往其中一個擁有者工作表，並在工作表中更新或新增資料。您可以嘗試下列幾種做法：
   • 新增一列，並輸入票證資訊。
   • 在「狀態」欄中，變更現有支援單的狀態。
   • 變更「狀態」欄的位置。例如，在「Owner1」工作表中，將「Status」欄從 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 試算表