ググっても意外とこれだというものを見つけられなかったのでメモっておきます。

要件

1つのスプレッドシート(Google Spreadsheet)に手動でつくられたいくつかのシートと自動的に生成される複数のシートがぶら下がっている
自動的につくられたシートはシート名で識別可能
自動的につくられたシートのすべての行から条件に一致する行だけを抜き出して、適当なシートで一覧したい

結論

スクリプトエディタで以下の関数を定義する。

function getAllTargetRows() {
  var allSheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  var allTargetSheets = allSheets.filter(function(sheet) {
    // シート名が年月日っぽいものだけに絞る
    return /\d{4}\/\d{1,2}\/\d{1,2}/.test(sheet.getName());
  });
  
  var allRows = [];
  allTargetSheets.forEach(function(sheet) {
    var rows =  sheet.getDataRange().getValues();
    allRows = allRows.concat(rows);
  });
  
  return allRows;
}

適当なシートの適当なセルで以下の内容を入力する。

=QUERY(getAllTargetRows(), "select * where Col2 = 'hoge'")

GAS初心者なのでこれがベストかどうかは知らないです。

過程

GASを使わなくても関数の組み合わせでなんとかなるのではと思ったけど、すべてのシートを取得するような関数が見つけられなかったので無理そうと判断した
- シートが増えるペースなどによってはシートが増えたら人間が手動更新するでも良いかも知れない。今回は手動更新したくないなと思った
最初は{'Sheet1'!A:Z;'Sheet2'!A:Z}みたいな書き方でシートを結合してやれないかと思ってGASで対象となるシート名の一覧を返すだけの関数を定義していたけど、よくよく調べてみるとQUERY関数は検索範囲を文字列で受けることはできなさそう(要調査)、INDIRECTは単一の範囲しか受け取れ無さそうで先述のフォーマットの文字列を渡してもエラーになった(要調査)ので、こりゃもうGAS側でやった方が楽だなと判断した
GAS側でどこまでやるべきか検討したが、すべてのシートの行を結合して返すところまでやってくれればあとはVLOOKUPなりなんなり使えばよくて、その方が後々融通もききそうなのでそのようにすることにした
結果、上述のGASが出来上がった

下林明正のブログ

個人的かつ雑多なブログです。

GAS(Google App Script)を使ってGoogle Spreadsheetの条件に一致するすべてのシートの内容を結合して、さらにSpreadsheet側でQUERY関数とかで絞り込む

要件

結論

過程