GASで複数のスプレッドシートを1つに自動集約する方法

店舗別、部署別に分かれたスプレッドシートの数字を、月末になるたびに1つのシートへ手作業でまとめている場合、転記漏れや数字の写し間違いが起きやすくなります。

この記事では、Google Apps Scriptで複数のスプレッドシートを1つの集約シートへ自動でまとめる仕組みを、スプレッドシートIDの管理方法から列構成のズレへの対処まで整理しています。

スプレッドシート集約の自動化を無料で相談する

どんなときに使うか

店舗ごとに売上を記録するスプレッドシートが分かれている、部署ごとに経費申請のシートが独立している、といった構成は珍しくありません。

現場ごとに入力しやすい形式を維持できる一方で、本部側で全体の数字を見るには、誰かが毎回すべてのファイルを開いて1つのシートに転記する作業が発生します。

店舗数や部署数が増えるほど、その時間とコピー漏れや二重貼り付けのリスクが比例して膨らむ点が悩みどころです。

Google Apps Scriptを使うと、各スプレッドシートのIDをあらかじめ登録しておくだけで、集約先のシートへ自動でデータを積み上げられます。

毎月・毎週といった決まったタイミングで実行すれば、本部の担当者が各店舗のファイルを個別に開く手間そのものがなくなります。

手順

準備からトリガー設定まで、4つのステップで完成する流れです。

Step1. 集約元のスプレッドシートIDを一覧化する

まず、集約先となる新しいスプレッドシートを1つ作成し、1行目に集約後のヘッダー行を用意します。

続けて、集約したい各店舗・各部署のスプレッドシートを開き、URLに含まれるIDをメモしておきます。

スプレッドシートのURLは https://docs.google.com/spreadsheets/d/【ここがID】/edit という形式です。

d/ の直後から /edit の直前までの文字列がIDです。

集約先のスプレッドシート上部のメニューから拡張機能を開き、Apps Scriptを選ぶとスクリプトエディタが立ち上がります。

Step2. コードを貼り付ける（コード全文）

スクリプトエディタに表示されているコードを全て消し、次のコードを貼り付けます。

function mergeSpreadsheetsToMaster() {
  const sourceSpreadsheetIds = [
    '1AbCdEfGhIjKlMnOpQrStUvWxYz0123456789abcde',
    '2AbCdEfGhIjKlMnOpQrStUvWxYz0123456789abcde',
  ]; // ★ここを変える: 集約元スプレッドシートのIDを列挙
  const sourceSheetName = '売上'; // ★ここを変える: 集約元シートの名前（各シートで共通の場合）
  const masterSheetName = '集約シート';

  const masterSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(masterSheetName);
  if (!masterSheet) {
    throw new Error(`シート「${masterSheetName}」が見つかりません`);
  }

  sourceSpreadsheetIds.forEach((spreadsheetId) => {
    const sourceSpreadsheet = SpreadsheetApp.openById(spreadsheetId);
    const sourceSheet = sourceSpreadsheet.getSheetByName(sourceSheetName);
    if (!sourceSheet) {
      throw new Error(`スプレッドシート「${sourceSpreadsheet.getName()}」にシート「${sourceSheetName}」が見つかりません`);
    }

    const lastRow = sourceSheet.getLastRow();
    if (lastRow < 2) {
      return;
    }

    const lastColumn = sourceSheet.getLastColumn();
    const values = sourceSheet.getRange(2, 1, lastRow - 1, lastColumn).getValues();
    const rowsWithSourceName = values.map((row) => [sourceSpreadsheet.getName(), ...row]);

    masterSheet
      .getRange(masterSheet.getLastRow() + 1, 1, rowsWithSourceName.length, rowsWithSourceName[0].length)
      .setValues(rowsWithSourceName);
  });
}

sourceSpreadsheetIds に登録した順番で各スプレッドシートを開き、2行目以降のデータを取得して集約シートへ追記していく仕組みです。

rowsWithSourceName の部分で、どの店舗・部署から来たデータかが分かるように、先頭列へ元のスプレッドシート名を付け加えています。

実行するたびに集約シートの末尾へ追記される作りなので、同じ月に2回実行すると同じデータが重複して積み上がる点は覚えておいてください。

Step3. 1つのIDだけでテスト実行する

いきなり全店舗分を対象にすると、ミスがあった場合の確認が大変になります。

sourceSpreadsheetIds を1件だけにした状態でスクリプトエディタの実行ボタンを押し、集約シートに正しく1店舗分のデータが追記されるかを確認してください。

Googleアカウントへのアクセス許可を求める画面が出た場合は、内容を確認したうえで許可を進めます。

1件での動作を確認できたら、残りのIDを配列に追加していきます。

Step4. 時間トリガーを設定する

スクリプトエディタの左側にある時計マークの「トリガー」を開き、右下の「トリガーを追加」から新しいトリガーを設定してください。

実行する関数には mergeSpreadsheetsToMaster を選び、イベントのソースは時間主導型、種類は週ベースまたは月ベースのタイマーを選びます。

続けて実行するタイミングを指定したうえで保存してください。

保存が終われば、指定したタイミングで全店舗・全部署分のデータ集約が自動で繰り返されます。

Google Apps Scriptでどの業務から自動化すべきか迷う場合は、判断基準を別記事で整理しています。

Google Apps Scriptの自動化は何から始める？選び方と3つの場面Google Apps Scriptで業務を自動化するとき、どの作業から手をつけるかの判断基準と、スプレッドシート・メール・帳票の代表的な自動化場面を整理しています。

列構成がずれているときの対処

集約元のスプレッドシートが全て同じ人の手で作られているとは限らず、列の並び順や列数が微妙にずれているケースがよく起こります。

Step2のコードは列の位置をそのままコピーする作りのため、ズレたまま実行すると「売上」の列に「担当者名」が入り込むといった事故につながります。

状況ごとに、次のような対処を組み合わせてください。

列順がバラバラで統一する手間をかけられない場合は、位置ではなくヘッダー名で列を突き合わせる書き方に変更する方法もあります。

function mergeSpreadsheetsByHeaderName() {
  const sourceSpreadsheetIds = [
    '1AbCdEfGhIjKlMnOpQrStUvWxYz0123456789abcde',
    '2AbCdEfGhIjKlMnOpQrStUvWxYz0123456789abcde',
  ]; // ★ここを変える: 集約元スプレッドシートのIDを列挙
  const sourceSheetName = '売上';
  const masterSheetName = '集約シート';

  const masterSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(masterSheetName);
  if (!masterSheet) {
    throw new Error(`シート「${masterSheetName}」が見つかりません`);
  }
  const masterHeaders = masterSheet.getRange(1, 1, 1, masterSheet.getLastColumn()).getValues()[0];

  sourceSpreadsheetIds.forEach((spreadsheetId) => {
    const sourceSheet = SpreadsheetApp.openById(spreadsheetId).getSheetByName(sourceSheetName);
    if (!sourceSheet) {
      throw new Error(`シート「${sourceSheetName}」が見つかりません`);
    }

    const lastRow = sourceSheet.getLastRow();
    if (lastRow < 2) {
      return;
    }

    const sourceHeaders = sourceSheet.getRange(1, 1, 1, sourceSheet.getLastColumn()).getValues()[0];
    const values = sourceSheet.getRange(2, 1, lastRow - 1, sourceHeaders.length).getValues();
    const reorderedRows = values.map((row) => {
      return masterHeaders.map((header) => {
        const columnIndex = sourceHeaders.indexOf(header);
        return columnIndex === -1 ? '' : row[columnIndex];
      });
    });

    masterSheet
      .getRange(masterSheet.getLastRow() + 1, 1, reorderedRows.length, reorderedRows[0].length)
      .setValues(reorderedRows);
  });
}

masterHeaders に登録した見出しの並びを基準にして、各シートの該当列を indexOf で探し出し、見つからない列は空欄で埋めます。

見出しの並び順が店舗ごとに違っていても、見出し名さえ揃っていれば正しい列にデータが入る仕組みです。

逆にヘッダー名の表記が1文字でも違うと空欄扱いになるため、表記揺れの統一は別途必要です。

集約したデータをきっかけに担当者へ通知したい場合は、シート更新の通知を組み合わせる方法も参考になります。

Google Apps Scriptでシート更新をSlackに自動通知する方法Google Apps ScriptとSlackのIncoming Webhookを使い、スプレッドシートの特定列が特定の値になったときだけ自動通知する設定手順とコード全文を解説する。

Google Apps Scriptでのスプレッドシート集約について相談してみませんか？

「店舗ごとにシートの作り方がバラバラで統一できていない」「集約後の数字が合わない原因を特定できない」「社内にGoogle Apps Scriptを設計できる人材がいない」

少しでもお心当たりがあれば、お気軽にご相談ください。

現在、Google Apps Scriptを使った業務自動化のお悩みをお伺いする 無料の個別相談 を実施しています。

よくある質問

Q. 集約元のスプレッドシート数に上限はありますか？

コード自体に上限はありません。

ただし SpreadsheetApp.openById() を1回のスクリプト実行内で何十件も呼び出すと、Google Apps Scriptの実行時間の上限に達する可能性があります。

店舗数が多い場合は、集約対象を複数のトリガーに分けて時間差で実行する構成を検討してください。

Q. 集約元シートの数式や書式も一緒にコピーされますか？

コピーされません。

getValues() は数式の計算結果だけを値として取得するため、集約シート側には数値や文字列だけが書き込まれ、数式や色付けなどの書式は引き継がれません。

Q. 同じ月に2回実行すると、データが重複しますか？

重複します。

Step2のコードは実行するたびに末尾へ追記する作りのため、同じ期間のデータを対象に2回実行すると同じ行が二重に積み上がります。

再実行する必要がある場合は、集約シートの該当期間の行を一度削除してから実行するか、実行前に重複チェックの処理を追加してください。

Q. 集約先のシートが別のスプレッドシートでも動きますか？

動きます。

SpreadsheetApp.getActiveSpreadsheet() の部分を SpreadsheetApp.openById('集約先のID') に置き換えれば、スクリプトを実行しているスプレッドシートとは別のファイルを集約先にできます。