Googleスプレッドシートでqueryを利用して、日付を検索条件として値を表示したい
これはなに？
Googleスプレッドシートでqueryを使うとSQLっぽいものが書けるようなので使っていたのですが、日付を検索条件とした時にハマったので解消するまでのメモを残します。
Googleスプレッドシートのqueryとは
Googleのドキュメント エディタ ヘルプには、Google Visualization API のクエリ言語を使用して、データ全体に対するクエリを実行します。という記載があります。
使い方について
マニュアルに記載されている構文や使用例は以下のようになります。
構文 QUERY(データ, クエリ, [見出し])
使用例 QUERY(A2:E6,"select avg(A) pivot B") QUERY(A2:E6,F2,FALSE)
同一シートや別シートの利用方法については以下の内容が分かりやすかったです。
検索条件や結果の表示については以下の内容がとても参考になりました。
ハマったこと
例えば以下のような表がスプレッドシートのA列からC列にあるとします。
| 日付 | 食べたもの | 金額 |
|---|---|---|
| 2018/04/01 | ラーメン | 700 |
| 2018/04/02 | チャーハン | 800 |
| 2018/04/03 | ぶっかけうどん | 350 |
| 2018/04/04 | インドカレー | 800 |
ここの、日付と一致する内容を基に表示する以下のような式を書いたのですがうまくいきませんでした。
=query($A$2:C,"select B, C where A = '"&E:E&"'")
A2:Cの範囲にある内容から、 A列の日付と、E列の日付が一致したら表示しようとしましたが、クエリがからの出力で完了しました。というエラーが出たので検索がうまく行ってないようでした。
検索条件の部分を where A = '2018/04/01'"と記述してもダメでした。
解決
色々と検索していると以下のところで解決しました。
まず、日付の部分を以下のようにすると表示できるようになりました。画像はF2の部分のみ変更しています。
=query($A$2:C,"select B, C where A = date '2018-04-01'")
where A = date '2018-04-XX'"と1行ずつ書くのはしんどいので、where A = date '"&E:E&"'"のようにしたいのですが、これではダメです。
関数 QUERY のパラメータ 2 のクエリ文字列を解析できません: Invalid date literal [43191]. Date literals should be of form yyyy-MM-dd.というメッセージが出ているので、日付のフォーマットを変更すると良いようでした。
=query($A$2:C,"select B, C where A = date '"&text(E:E,"YYYY-MM-DD")&"'")
F2に記載した式をF3以降に記載すると、意図通りの内容が表示されるようになりました。
F6については2018/04/05のデータが無いので #N/Aで良さそうですが、IFERRORで何も表示しないようにしようと思います。
=iferror(query($A$2:C,"select B, C where A = date '"&text(E:E,"YYYY-MM-DD")&"'"),"")
これで、一致しない部分は何も表示されないようになりました。
感想とまとめ
queryを利用して色々なことが出来るのですが、検索条件が日付の場合は要注意ですね。理解していくと便利に使えそうだったのでもう少しうまく使えるようになりたいです。
追記（2018.9.3）
データが書いてあるシートが別だった場合は以下のような式にすると良さそうでした。
=iferror(query('別のシート名'!$A$2:C,"select B, C where A = date '"&text(E:E,"YYYY-MM-DD")&"'"),"")
何かとシートを分けることが多いのでたまに調べたりしてたのですが、とりあえず追記してみました。
