GoogleスプレッドシートでSQLが使えるってマジ？
スプレッドシートにあるデータを集計する必要が出てきて、普段使い慣れない関数を調べて試行錯誤していました。
Zennを見ているエンジニアの方の中には「SQLだったらすぐできるのにな」と思いながら関数を調べた経験のある人も多いのではないでしょうか。実はそれ、できるんです。
週末に偶然知って感動したのでここにシェアします。
SpreadsheetにはQUERYという関数が用意されています。
これを使うと、スプレッドシートの特定の表に対してSQLを書くことができます。素敵。
| A | B | C | |
|---|---|---|---|
| 1 | month | category | price |
| 2 | 1 | お菓子 | 100 |
| 3 | 1 | お菓子 | 200 |
| 4 | 1 | お菓子 | 50 |
| 5 | 1 | 文具 | 500 |
| 6 | 1 | おもちゃ | 2500 |
| 7 | 2 | おもちゃ | 850 |
| 8 | 2 | お菓子 | 100 |
| 9 | 2 | 文具 | 90 |
| 10 | 2 | お菓子 | 350 |
| 11 | 3 | お菓子 | 100 |
| 12 | 3 | 文具 | 900 |
| 13 | 3 | おもちゃ | 5000 |
こんな表があって、1月のお菓子のpriceの合計値が出したい時は以下のように書きます。
=QUERY(A1:C13, "select sum(C) where A = 1 and B = 'お菓子'", -1)
すると
| sum price |
|---|
| 350 |
という結果が得られます。いい感じっぽいです。普通のSQLと違ってfromは不要であったり、日付などを扱う時は書き方が少し独特なのでその辺はリファレンスを参照すると良いと思います。
また、関数のドキュメントには列名を指定した表の範囲から推測してくれるとあるのですが、自分が試した限りではうまくいきませんでした。そのため第3引数に-1を与えることで列名の推測をしないようにしています。
その場合は、スプレッドシート上の列名(A, B, C...)がそのままクエリ内で列名として使えます。
ちなみにQUERY関数は列名も一緒に出してくれちゃうので、これが不要な時はINDEX関数というのを使って1行目を削るようにしてあげると良いです。
=INDEX(QUERY(A1:C13, "select sum(C) where A = 1 and B = 'お菓子'", -1), 2)
こんな感じです。(プログラミング言語の配列とは異なり1が1行目なので値の入っている行だけを取り出したいときは2を指定します)
上記は簡単な例でしたが、実際には別の関数を組み合わせてSQLを組み立てたりすることももちろんできるので、関数調べるよりSQL書くほうが早いというエンジニア各位には便利な機能なのではないでしょうか。
とはいえ、SQL書けない人も使うシートでこれを多用すると誰もメンテナンスできないシートが出来上がりそうなので使い所には注意した方が良いかもしれません。
おしまい。
Discussion