https://swfz.hatenablog.com/entry/2024/07/14/182931

スプレッドシートにデータを集めて分析なり集計して、定期的にデータを更新、追加をする場合

BigQueryを使うほどでもない、もしくはデータの内容的にプライベートでなんとかしたいのでスプレッドシートで…というパターンは一定存在すると思う

ある程度生に近いデータを期間ごとにシートを分けて追加
サマリー的なシートを用意し各生データのシートのデータを読み込む
データの更新は最新の期間のシートに行う

とすれば、一定更新のしやすさも保ちつつ分析や集計が行える

しかし、データの更新(シートの追加)のたびにサマリーシートのクエリを編集しなくてはならず手間となる

これを自動で行えないかなということで調べてみたが、スプレッドシートの関数にはシートのリストを取得するための関数がなかったためやむなくGASを使って処理することにしたのでそのときのメモ

各rawデータのシート

summary

各rawシートのデータをまとめて一覧に

summaryシートのA1の数式

=QUERY({raw_1!A2:C;raw_2!A2:C;raw_3!A2:C}, "SELECT * WHERE Col1 IS NOT NULL ORDER BY Col1")

複数のデータ範囲(ヘッダー行を除く)を垂直に結合して、クエリを記述している

この複数のデータ範囲の指定を動的にするためGASを使用した

GASのコード

createDynamicDataRangeを実行すると、上述した複数のデータ範囲に対してQUERYした内容を表示できる

// 読み込み対象シートのデータ範囲
const RAW_SHEET_TARGET_RANGE = 'A2:C';
// raw_ で始まるシートのみを取得、必要に応じて変更する
const RAW_SHEET_NAME_PREFIX = "raw_";
const SUMMARY_SHEET_NAME = "summary";
const SUMMARY_FORMULA_CELL = "A1";

function createDynamicDataRange() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();

  const sheets = ss.getSheets();
  const rawSheetNames = sheets.map(s => s.getName()).filter(name => name.startsWith(RAW_SHEET_NAME_PREFIX));

  const dataRanges = rawSheetNames.map(name => `'${name}'!${RAW_SHEET_TARGET_RANGE}`);
  // 必要に応じてクエリは変更する
  const query = `=QUERY({${dataRanges.join(';')}}, "SELECT * WHERE Col1 IS NOT NULL ORDER BY Col1")`;

  const summarySheet = ss.getSheetByName(SUMMARY_SHEET_NAME);
  summarySheet.getRange(SUMMARY_FORMULA_CELL).setFormula(query);
}

おわり

GASのトリガー設定で「編集したら実行」にすれば、rawシートが追加された段階でsummaryシートの内容も更新してくれるようになる

実際に分析なり集計なりする場合は、summary以外に別のシートを用意してsummaryのデータに対して集計や分析用のクエリを書く感じになるはず(GASのコード上にクエリを記述してしまっているため変更に手間が掛かるため)

普段はほとんどのデータをBigQueryに入れてしまえ派ではあるが

あまりオープンにして共有できない内容だったりする場合はこういう手法でまずスプレッドシートにデータ集めて見てみるというのをよくやるのでメモ