かもメモ

自分の落ちた落とし穴に何度も落ちる人のメモ帳

GAS SpreadSheet 複数のシートのデータをまとめて表示したい

1日目, 2日目, 3日目のイベント情報のデータがあってそれぞれ別のシートにあるから全体を見れるように 1つのシートで表示したいみたいなケース

day1

A B C D
1 id name day action
2 1 星宮いちご day1 xxx
3 2 霧矢あおい day1 xxx
4 3 紫吹蘭 day1 xxx

day2

A B C D
1 id name day action
2 4 大空あかり day1 xxx
3 5 氷上すみれ day1 xxx
4 6 新条ひなき day1 xxx

day3

A B C D
1 id name day action
2 7 神崎美月 day1 xxx
3 8 夏樹みくる day1 xxx
4 9 紅林珠璃 day1 xxx

これを縦に連結した 1 つのシートを作成したい

QUERY 関数で複数シートを連結表示できる

データを縦に連結する場合は各範囲を {} 内に ; 区切りで指定すればOK

=QUERY({範囲A; 範囲B; 範囲C}, "select *")

上記の day1, day2, day3 シートのデータをまとめるなら

=QUERY({day1!A:D; day2!A:D; day3!A:D}, "select *")

と書けば OK そうなのですが、これでは day1 のデータしか表示されません。

各シートのデータが入っている範囲を指定しなければならない

=QUERY({day1!A:D; day2!A:D; day3!A:D}, "select *")

この書き方の場合 day1 シートの A:D の範囲から入力の有無に関わらず全行取ってこようとするので、行数の最大値まで day_1 のデータで埋まってしまっていた為に day1 のデータしか表示されないように見えていました。

範囲が固定なら day1!A:D100 のように範囲を指定してあげれば OK です

=QUERY({day1!A:D4; day2!A:D4; day3!A:D4}, "select *")

範囲が動的な場合は QUERY を使って入力がある行を判定させる

固定範囲にした場合、元データに変更があると集計データのコードも変更しなければならないので面倒です。
なので各シートの入力がある行だけを取ってくるように変更します。

=QUERY({
  QUERY(day1!A:D4, "SELECT * WHERE A is not null");
  QUERY(day2!A:D4, "SELECT * WHERE A is not null");
  QUERY(day3!A:D4, "SELECT * WHERE A is not null")
}, "select *")

QUERY 関数の {} 内は QUERY を入れ子に出来るので、各シートからデータを取ってくる段階で入力のない行を除外してやればOK

これで動的に各シートのデータを集計した表示ができるようになりました!
最初のシートのデータしか表示されなかった時に空白の行も取ってきている事になかなか気づけませんでした。
これは罠!


[参考]