Photo by National Cancer Institute on Unsplash

Google Sheets（スプレッドシート）のQUERY関数で元範囲がわかる状態で複数範囲を結合する方法

ユーザー調査で使っているTypeformの結果をGoogleスプレッドシートで集計しているんだけど、QUERY関数で複数の範囲を縦に結合する際、元の範囲が何だったのかを結合後に引き継ごうとすると不要な見出し行が出力されて困ったけど、まとまった解決方法が見当たらなかったのでメモ。

結論としてはこんな感じ。

=QUERY({
 IFERROR(QUERY('Sheet1'!A2:B,”SELECT A,B,'Sheet1' WHERE B is not null LABEL 'シート1' ''”, 0),{””,””,””});
 IFERROR(QUERY('シート2'!A2:B,”SELECT A,B,'シート2' WHERE B is not null LABEL 'シート2' ''”, 0),{””,””,””})
},”SELECT * LABEL Col1 '種類' Col2 '内容' Col3'シート'” , 1)

全体の構造は「Query関数 結合 縦」とかでググるとわかる。

QUERY('シート1'!A2:B,”SELECT A,B,'シート1' WHERE B is not null LABEL 'シート1' ''”, 0);

改行して見やすくします。
ちなみに改行した状態でも動きます。

QUERY(
'シート1'!A2:B,
”SELECT A,B,'シート1' WHERE B is not null LABEL 'シート1' ''”,
0);

1.「シート1」を2列持ってくる

'シート1'!A2:B

2. さっきの2列には含まれていない3列目として、全行「シート1」という文字列が入った列を仮想的に追加する。

SELECT A,B,'シート1'

3. AがIDなどでIDだけが並んでいる場合は空の行でいっぱいになっちゃうので、実データの列で空のセルが無いものだけにする。

WHERE B is not null 

4. さっき作った’シート1'が全行に入った列に空の名前を指定して消す。これをしないと「シート1()」という仮の見出しがついてしまい、見出し行が強制的に出力される。

LABEL 'シート1' ''
※注 「LABEL '列の指定' '列の名前' 」という構成

5. クエリとクエリの間をセミコロンでつなぐと縦に結合する。
最後のクエリの後ろには不要。
```
;
```

見出しをつける時は一番外側のQUERY関数にLABELオプションで指定する。

”SELECT * LABEL Col1 '種類' Col2 '内容' Col3'シート'” , 1

これで完成！だけど、結果が0件だと

ARRAY_LITERAL の配列リテラルで、1 つ以上の行の値が見つかりませんでした。

というエラーになってしまうので、中のQUERY関数をIFERRORで囲みます。
エラーの時は、配列を返す必要があるので、`{}`の中にカンマ区切りで空の要素`””`をSELECTで指定したカラムと同じ数置きます。

IFERROR(QUERY('シート1'!A2:B,”SELECT A,B,'シート1' WHERE B is not null LABEL 'シート1' ''”, 0),{””,””,””})

これできれいな結果が返ってくるはず。

※試してないのでエラーが出たら教えてください 笑