Googleスプレッドシートで串刺し集計：GASで複数シートのデータ合算を自動化

2025年2月3日

前回の記事では、クエリ関数を使ってGoogleスプレッドシートの複数シートのデータを集計する方法をご紹介しました。

ですが、クエリ関数ではシートが増減するたびに数式を修正する必要があり、シート数が多くなると面倒になる場合もあります。

そこで今回は、Google Apps Script（GAS）を活用して、シートの増減に柔軟に対応できる集計方法をご紹介します。

GASを使えば手動で数式を修正することなく、自動でデータを取得して集計できるようになります。

完成イメージ

各シートA列の項目（商品名）を基準に、B列の金額の合計を算出

今回のスクリプトでは、一つのスプレッドシートファイル内における全シートを対象に集計を行います。

手順

STEP

Googleスプレッドシートを開く

集計したいデータのあるスプレッドシートを開きます。

各シートのフォーマットは合わせておく必要があります。

（A列に商品名、B列に売上など）

STEP

GASエディタを開く

Googleスプレッドシートを開き、メニューの「拡張機能」から「Apps Script」をクリックしてGASエディタを開きます。

STEP

スクリプトの作成

function myFunction(){

}

が最初から入っているため、消去して以下のスクリプトを貼り付けます。

function aggregateSheets() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();
  var data = [];
  var header = ["商品名", "売上合計"];
  var targetSheetName = "集計"; // シート名を"集計"に設定

  for (var i = 0; i < sheets.length; i++) {
    var sheet = sheets[i];
    if (sheet.getName() !== targetSheetName) { // "集計"シートを除外
      var values = sheet.getDataRange().getValues();
      data = data.concat(values.slice(1)); // ヘッダー行を除外して追加
    }
  }

  // 商品名ごとに売上を集計
  var summary = {};
  data.forEach(function(row) {
    var item = row[0];
    var amount = row[1];
    if (item && amount) {
      if (!summary[item]) {
        summary[item] = 0;
      }
      summary[item] += amount;
    }
  });

  // 集計データを配列に変換
  var outputData = Object.entries(summary).map(([item, total]) => [item, total]);

  // "集計"シートの取得または作成
  var targetSheet = ss.getSheetByName(targetSheetName);
  if (!targetSheet) {
    targetSheet = ss.insertSheet(targetSheetName);
  }

  // "集計"シートのデータをクリアして新しいデータをセット
  targetSheet.clear();
  targetSheet.getRange(1, 1, 1, header.length).setValues([header]); // ヘッダー
  targetSheet.getRange(2, 1, outputData.length, outputData[0].length).setValues(outputData); // データ書き込み
}

スクリプトの説明

スプレッドシートの全シートを取得

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheets = ss.getSheets();

getActiveSpreadsheet() で現在のスプレッドシートを取得します。
getSheets() を使い、すべてのシートを配列として取得します。

「集計」シートのヘッダーを設定

var header = ["商品名", "売上合計"];
targetSheet.clear();
targetSheet.getRange(1, 1, 1, header.length).setValues([header]); // ヘッダー

header 配列 ["商品名", "売上合計"] を1行目に設定します。（必要に応じてヘッダー名を変更）

シートを1つずつ処理し、「集計」シートを除外

for (var i = 0; i < sheets.length; i++) {
  var sheet = sheets[i];
  if (sheet.getName() !== targetSheetName) { // "集計"シートを除外
    var values = sheet.getDataRange().getValues();
    data = data.concat(values.slice(1)); // ヘッダー行を除外して追加
  }
}

すべてのシートをループ処理し、targetSheetName（=「集計」）と一致しないシートのデータを取得します。
getDataRange().getValues() でシートの全データを取得します。
slice(1) で1行目（ヘッダー行）を除外し、データのみを data 配列に追加します。

商品名ごとに売上を集計

var summary = {};
data.forEach(function(row) {
  var item = row[0];
  var amount = row[1];
  if (item && amount) {
    if (!summary[item]) {
      summary[item] = 0;
    }
    summary[item] += amount;
  }
});

summary というオブジェクトを作成し、商品名をキーに売上を累積します。
forEach() で data 配列をループし、summary[item] に売上を加算します。

集計データを配列形式に変換

var outputData = Object.entries(summary).map(([item, total]) => [item, total]);

Object.entries(summary) を使って summary のオブジェクトを [キー, 値] のペアの配列 に変換します。
map() で [商品名, 売上合計] の形式の配列を作成します。

「集計」シートの取得 or 作成

var targetSheet = ss.getSheetByName(targetSheetName);
if (!targetSheet) {
  targetSheet = ss.insertSheet(targetSheetName);
}

getSheetByName(targetSheetName) で「集計」シートが存在するか確認します。
シートが存在しない場合は insertSheet(targetSheetName) で新規作成します。

「集計」シートにデータを書き込む

targetSheet.getRange(2, 1, outputData.length, outputData[0].length).setValues(outputData);

setValues(outputData) を使って、集計結果を2行目以降に書き込みます。

STEP

スクリプトを保存

スクリプトを書いたら、名前を付けて保存します。

（例：「データ集計」）

STEP

スクリプトを実行

スクリプトを実行して、ファイル内の全シートのデータを集計します。

初めてそのスクリプトを実行する場合は権限の確認が必要です。

そのため、『権限を確認』を押します。

許可の詳細手順

「詳細」をクリックします。

「無題のプロジェクト（安全ではないページ）に移動」をクリックします。

「許可」をクリックします。

STEP

集計シートが作成される

ファイル内に「集計」シートが作成され、集計結果が表示されます。

（すでに「集計」シートがある場合は、そのシートに上書きされます）

GASを使った自動集計のメリットとデメリット

メリット	デメリット
シートの数が変わっても自動で対応トリガー設定で定期的な自動集計が可能	実行時間の制限がある（6分）スクリプトのカスタマイズには知識が必要

メリット

シートの数が変わっても自動で対応: クエリ関数では新しいシートを追加するたびに手動で数式を修正する必要がありますが、GASならすべてのシートを自動取得し処理するため、スクリプトを変更することなく対応できます。
トリガー設定で定期的な自動集計が可能: GASでは、スクリプトを一定の間隔で実行する「トリガー」を設定することができます。
たとえば、「1日1回」や「毎時」などの頻度でスクリプトを実行し、常に最新のデータを集計しておくことが可能です。
これにより、手作業なしでリアルタイムなデータ管理を実現できます。

デメリット

実行時間の制限がある（6分）: GASはクラウド上で実行されるため、無料プランでは1回の実行につき最大6分の制限があります（Google Workspaceアカウントの場合は30分）。
大量のデータを処理しようとするとスクリプトがタイムアウトし、途中で停止してしまう可能性があります。
対策として、データを分割して処理するなどがあります。
スクリプトのカスタマイズには知識が必要: GASを活用すると柔軟なデータ集計が可能ですが、条件を変更・追加する場合はスクリプトを理解し修正する必要があります。