匯總多份工作表中的資料

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

目標

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

認識這項解決方案

如果試算表中有多個工作表含有類似的結構化資料，例如 做為團隊成員的客戶服務指標 建立每份工作表的摘要這項解決方案著重於客戶服務 但您可以視需求自訂選項。

運作方式

這個自訂函式稱為 getSheetsData()，可摘要列出每份工作表的資料 試算表的「狀態」欄。指令碼會忽略 不應納入匯總範圍的工作表，例如 ReadMe 和 摘要工作表。

Apps Script 服務

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

• 試算表服務：取得試算表 需要彙整的摘要資料，並計算與 指定的字串。接著，指令碼會將計算結果加到某個範圍 也就是在試算表中呼叫該自訂函式的位置

必要條件

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

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

設定指令碼

請點選下方按鈕，建立「匯總試算表資料」 自訂函式試算表。適用於下列應用程式的 Apps Script 專案： 這個解決方案已附加至試算表
建立副本

執行指令碼

1. 在複製的試算表中，前往「摘要」工作表。
2. 按一下儲存格 A4。這個儲存格中有 getSheetsData() 函式。
3. 請前往任一擁有者工作表，在工作表中更新或新增資料。只有部分通知 可以嘗試下列動作：
   • 新增含有範例票券資訊的資料列。
   • 在「狀態」欄中變更現有支援單的狀態。
   • 變更「狀態」欄的位置。例如，在 「擁有者 1」Owner1工作表，將「狀態」Owner1欄從 C 欄移至 D 欄。
4. 前往「摘要」工作表，查看更新後的摘要表格 已從儲存格 A4 建立「getSheetsData()」。您可能需要勾選核取方塊 ，重新整理自訂函式的快取結果。 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 Developers 專家的協助下維護。

後續步驟

• Google 試算表的自訂函式
• 擴充 Google 試算表