スプレッドシートのQuery関数の挙動確認

背景

スプレッドシートの一部をQuery関数で抽出したデータをGASでBigQueryに格納しようとしたときにカラム名に改行が入ってしまいエラーが発生していたので、具体的な挙動を確認して、解決方法を考える。

 発生していた問題元テーブルに対して下記記述のQuery関数を適用したときに下記問題が発生
セルに改行が含まれてしまっている（もともとのエラー原因はこれ）
具体的なuser_idやユーザー名がカラム名に含まれている

 Query関数の内容=QUERY(
'元データ'!A1:D12,
"select A, B, D where B='C'"
)

 データ※こちらのユーザー名はテストデータ・ジェネレータを利用して作成


元テーブル
クエリ結果

umeru9

 結論改行を解決するには改行コードを空文字に変換する。下記記事を参考
https://qiita.com/Shinoda_Naoki/items/d0da0faf2b0000e20db3

具体的なデータが含まれてしまうケースについてはQuery関数の見出しを指定する

 解決したデータカラムに不要なデータが含まれておらず、セル内改行も解消している。

 クエリARRAYFORMULAを利用すると擬似的な範囲が返ってくるので A, Bなどの列名指定ができず、 Col1 ,Col2のような形に変換する必要がある。
空白セルは空文字にはならないので is nullのような条件判定が利用可能
=QUERY(
ARRAYFORMULA(SUBSTITUTE('元データ'!A1:D12,CHAR(10),"")),
"select Col1, Col3, Col4 where Col2='C' and Col3 is null" ,
1
)

 結果

umeru9

注意点

ARRAYFOMULA関数を利用するときは各セルが文字列として返ってくるためwhereの条件で日付の条件判定の方法が変わる。
具体的にはQuery関数では 日付カラム比較演算子 date 'yyyy-mm-dd' の形式
参考：【QUERY関数】where句で日付データを条件にして行を抽出する | 隣IT

  =QUERY(
'元データ'!A1:D12,
"select A, B, D where C= date '2025-09-09'"
)

一方ARRAYFOMULAを利用した場合は、2025/09/09 のように直接文字列指定にする必要がある。
これは実際にセルに表示されている文字を指定するで大丈夫だと思う。

=QUERY(
ARRAYFORMULA(SUBSTITUTE('元データ'!A1:D12,CHAR(10),"")),
"select Col1, Col2, Col4 where Col3='C' and Col2='2025/09/09'" ,
1
)