複数のシートのデータを要約する

コーディング レベル: 初級
所要時間: 5 分
プロジェクト タイプ: カスタム関数

目標

• ソリューションの機能について理解する。
• ソリューション内での Apps Script サービスの役割を理解する。
• スクリプトを設定します。
• スクリプトを実行します。

このソリューションについて

スプレッドシート内の複数のシートに、チームメンバーのカスタマー サポート指標など、同様の構造のデータがある場合は、このカスタム関数を使用して各シートの概要を作成できます。このソリューションはカスタマー サポート チケットに重点を置いていますが、ニーズに合わせてカスタマイズできます。

仕組み

getSheetsData() というカスタム関数は、シートの [ステータス] 列に基づいて、スプレッドシート内の各シートのデータの概要を表示します。このスクリプトは、集計に含めるべきでないシート（ReadMe シートやSummary シートなど）を無視します。

Apps Script サービス

このソリューションでは、次のサービスを使用します。

• スプレッドシート サービス - 要約するシートを取得し、指定された文字列に一致するアイテムの数をカウントします。次に、スクリプトは、スプレッドシート内でカスタム関数が呼び出された場所を基準として、計算された情報を範囲に追加します。

前提条件

このサンプルを使用するには、次の前提条件を満たしている必要があります。

• Google アカウント（Google Workspace アカウントの場合、管理者の承認が必要となる可能性があります）。
• インターネットに接続できるウェブブラウザ。

スクリプトを設定する

下のボタンをクリックして、スプレッドシート データを要約するカスタム関数のスプレッドシートのコピーを作成します。このソリューションの Apps Script プロジェクトがスプレッドシートに添付されています。
コピーを作成

スクリプトを実行する

1. コピーしたスプレッドシートで、[概要] シートに移動します。
2. セル A4 をクリックします。このセルに getSheetsData() 関数があります。
3. 所有者のシートのいずれかに移動し、シートのデータを更新または追加します。試すことができるアクションには次のようなものがあります。
   • サンプルのチケット情報を含む新しい行を追加します。
   • [ステータス] 列で、既存のチケットのステータスを変更します。
   • [ステータス] 列の位置を変更します。たとえば、[Owner1] シートで、[ステータス] 列を列 C から列 D に移動します。
4. [Summary] シートに移動し、getSheetsData() がセル A4 から作成した更新された概要表を確認します。カスタム関数のキャッシュに保存された結果を更新するには、行 10 のチェックボックスをオンにする必要があります。Google は、パフォーマンスを最適化するためにカスタム関数をキャッシュに保存します。
   • 行を追加または更新すると、スクリプトはチケットとステータスの数を更新します。
   • [ステータス] 列の位置を移動した場合でも、スクリプトは新しい列番号で意図したとおりに機能します。

コードを確認する

このソリューションの Apps Script コードを確認するには、下の [ソースコードを表示] をクリックします。

ソースコードを表示

コード.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 スプレッドシートの拡張