Query関数とは
Googleスプレッドシートのワークシート関数にQueryというのがあって、これがSQL風の記述で他のセルや配列から条件に合った者を抽出できて超便利。HLOOKUP,VLOOKUPとかより遙かに柔軟性がある。もちろんIMPORTRANGEとも組み合わせて使える。
結果が空になる問題
しかし、時々絶対存在するはずのクエリ結果が空になる謎現象に悩まされてた。
なかなか原因が判明しなかったが、色々調べた結果、この記事にたどり着いた。
QUERY will convert columns with mixed data types into one data type. If the data is mostly numerical values, then text strings (eg 1-2 in your data) will be converted to blank cells.
なんと！
文字列と数値の混在する列で、文字列が少数派だと勝手に空白セルに変換してしまうらしい。
なにそれ！？バグなの？
対策
ではどうするか？
元のセルの表示形式を全部「書式なしテキスト」にしてしまえば、この問題は起きない。
とはいえ、それでは困るというケースの方が多いだろう。
件のStackOveflowの記事でも書かれてるが、一度全部文字列に変換するという手がある。
ただ、記事のなかで例示されてる関数式は複雑すぎて読むのも苦痛で読んでない。
代わりに考えた解決策がこれ。
=QUERY(ARRAYFORMULA(TO_TEXT('シート1'!A:X)),"select Col1... where Col1...")
まあ、見ての通り文字列変換してるだけです。
あと、Query式で同じシートのRangeを参照する場合は'A'とか'B'とか列名がそのまま使えますが関数変換した場合'Col1','Col2','Col3'...という列名になります。
これで文字列と数値の混成データは特に問題ないですが、日付データが混じっているとwhereで日付による条件指定が困難になるという弱点があります。
その解決策は…必要に迫られたら考えてみます。スミマセン。
おまけ(Queryの使い方Tips)
ImportRangeでのカラム名
同じスプレッドシートのRangeの場合、Query式では'A'とか'B'とか列名がそのまま使えるがImportRangeで読み込んだ範囲の場合'Col1','Col2','Col3'...という列名になる。TO_TEXTやTRANSPOSEなどで変換した場合も同様。
先頭の見出し行を除去
クエリの結果で、先頭に見出し行が入るのが邪魔な場合がある。label ＜列名＞ '' で消せる。列名の右はクォーテーション( ' )を二つ並べたもの。
"select Col1, Col2, Col3 where ... label Col1 '', Col2 '', Col3 ''..."
※selectしてない列のlabelを設定しようとするとエラーになるので注意
max(A) とか A+B みたいな式も、そのまま繰り返してやればOKです。
"select max(Col1), Col2+Col3 where ... label max(Col1) '', Col2+Col3 ''..."
※実際は同じQuery内で max(Col1) と Col2+Col3 を同時には select 出来ないけどあくまで label の例として見て下さい。
行でselect & where したい
"select 'Row1' where 'Row1'..." みたいなことは出来ないっぽい。
TRANSPOSE関数を使おう！