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

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

目標

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

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

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

getSheetsData 関数の出力のスクリーンショット

仕組み

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 コードを確認するには、以下の [ソースコードを表示] をクリックします。

ソースコードを表示

コード.gs

solutions/custom-functions/summarize-sheets-data/Code.js
// 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 が管理しています。

次のステップ