読者です 読者をやめる 読者になる 読者になる

$shibayu36->blog;

株式会社はてなでエンジニアをしています。プログラミングや読書のことなどについて書いています。

Google SpreadsheetとQuery Language

Google Spreadsheetを使ってたら、Query Languageというのがあるということに気づいて非常に便利だったのでメモ。

Query Language Reference (Version 0.7) - Google Charts — Google Developers

Query LanguageというのはGoogle Spreadsheetの内容をSQLっぽい文法で絞り込んだり計算したりできるもの。

例として非常に雑な例を出すと、
f:id:shiba_yu36:20140913121221p:plain

  • 簡単なTODOリストをおいている
  • Sattus, Task名, 締切が書いてある
  • 「TODO全体」というシートに書かれている

というようなシートがあるとする。

このシートから

  • 別シートに、DOING状態になっているタスクを日付順に抜き出したい
  • 別シートに、Due dateを超えたタスクを抜き出したい

という2つのことをやりたいとすると、Query Languageで行うことができる。

別シートに、DOING状態になっているタスクを日付順に抜き出したい

別シートにDoingという名前を付けて、A1セルに以下のような文を書くと実現できる。

=QUERY('TODO全体'!A:C, "select * where A = 'DOING' order by C asc", -1)

f:id:shiba_yu36:20140913121235p:plain

1引数目に書いてあるのは、どのシートのどの範囲を対象にするかで、2引数目に書いてあるのはSQLっぽい文法でwhereとかで絞り込み、order byで並べ替えしてる。A列がstatusなのでDOINGで絞り込み、C列がDue dateなので昇順で並べ替えることで、実現できた。

別シートに、Due dateを超えたタスクを抜き出したい

これは現在の日付を取得するnow()と、それを日付に変換するtoDate()を利用するとできそうなのでやってみた。

別シートを作り、A1に以下のクエリを書く。

=QUERY('TODO全体'!A:C, "select * where C <= toDate(now())", -1)

f:id:shiba_yu36:20140913121029p:plain

すると上のようになった。普通にうまく行きそうだったが、なぜかうまく行かなかった。軽く理由を調べてみるとなぜかnow()関数が返す値が一ヶ月後の値になっていて、それにより今より1ヶ月後より前が抜き出されるみたいな結果になった。この部分についてはいろいろ調べてみたけどよく分からずうまく行ってない。ちなみにmonth(now())の値は一ヶ月後でなく当月の値になって、さらに分からなかった。普通にバグ??

まとめ

SQLっぽいのかけるといろいろ出来て便利。もちろん今回みたいな感じ以外でも普通にSUMとかAVGとか、group byでのグルーピングとかいろんな関数あるので、もうちょっといろいろできるはず。普通に数字が入っている表を簡単に集計したりするには便利そうだった。