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

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

目標

• ソリューションの機能を理解します。
• Apps Script サービスがソリューション内でどのように機能するかを理解します。
• スクリプトを設定します。
• スクリプトを実行します。

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

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

仕組み

getSheetsData() というカスタム関数は、シートの [Status] 列に基づいて、スプレッドシートの各シートのデータを要約します。このスクリプトは、ReadMe シートや Summary シートなど、集計に含めてはならないシートを無視します。

Apps Script サービス

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

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

前提条件

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

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

スクリプトを設定する

下のボタンをクリックすると、「スプレッドシートのデータを集計する」カスタム関数スプレッドシートのコピーが作成されます。このソリューションの Apps Script プロジェクトは、スプレッドシートに接続されています。
コピーを作成

スクリプトを実行する

1. コピーしたスプレッドシートの [概要] シートに移動します。
2. セル A4 をクリックします。getSheetsData() 関数はこのセルにあります。
3. いずれかのオーナーシートに移動し、データを更新または追加します。次のような操作を試すことができます。
   • サンプル チケット情報を含む新しい行を追加します。
   • [Status] 列で、既存のチケットのステータスを変更します。
   • [ステータス] 列の位置を変更します。たとえば、[Owner1] シートの [Status] 列を列 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 Developer Experts の協力により Google が保守しています。

次のステップ

• Google スプレッドシートのカスタム関数
• Google スプレッドシートの拡張