yuji0602’s blog

日々思っていることなど。

Googleスプレッドシートでqueryを利用して、日付を検索条件として値を表示したい

これはなに?

Googleスプレッドシートでqueryを使うとSQLっぽいものが書けるようなので使っていたのですが、日付を検索条件とした時にハマったので解消するまでのメモを残します。

Googleスプレッドシートのqueryとは

Googleのドキュメント エディタ ヘルプには、Google Visualization API のクエリ言語を使用して、データ全体に対するクエリを実行します。という記載があります。

support.google.com

使い方について

マニュアルに記載されている構文や使用例は以下のようになります。

構文 QUERY(データ, クエリ, [見出し])

使用例 QUERY(A2:E6,"select avg(A) pivot B") QUERY(A2:E6,F2,FALSE)

同一シートや別シートの利用方法については以下の内容が分かりやすかったです。

qiita.com

検索条件や結果の表示については以下の内容がとても参考になりました。

qiita.com

ハマったこと

例えば以下のような表がスプレッドシートの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&"'")

f:id:yuji0602:20180412164643p:plain

A2:Cの範囲にある内容から、 A列の日付と、E列の日付が一致したら表示しようとしましたが、クエリがからの出力で完了しました。というエラーが出たので検索がうまく行ってないようでした。

検索条件の部分を where A = '2018/04/01'"と記述してもダメでした。

解決

色々と検索していると以下のところで解決しました。

chaika.hatenablog.com

webapps.stackexchange.com

まず、日付の部分を以下のようにすると表示できるようになりました。画像はF2の部分のみ変更しています。

=query($A$2:C,"select B, C where A = date '2018-04-01'")

f:id:yuji0602:20180412164707p:plain

where A = date '2018-04-XX'"と1行ずつ書くのはしんどいので、where A = date '"&E:E&"'"のようにしたいのですが、これではダメです。

f:id:yuji0602:20180412164723p:plain

関数 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")&"'")

f:id:yuji0602:20180412164744p:plain

F2に記載した式をF3以降に記載すると、意図通りの内容が表示されるようになりました。

f:id:yuji0602:20180412164757p:plain

F6については2018/04/05のデータが無いので #N/Aで良さそうですが、IFERRORで何も表示しないようにしようと思います。

=iferror(query($A$2:C,"select B, C where A = date '"&text(E:E,"YYYY-MM-DD")&"'"),"")

f:id:yuji0602:20180412164811p:plain

これで、一致しない部分は何も表示されないようになりました。

感想とまとめ

queryを利用して色々なことが出来るのですが、検索条件が日付の場合は要注意ですね。理解していくと便利に使えそうだったのでもう少しうまく使えるようになりたいです。