Google Spreadsheets を簡易 SQL DB に！「Google Visualization API」
皆さん、 Google Docs のガジェット機能はもう使ってみましたでしょうか。データをさまざまな方法で可視化するガジェットをシート上に配置できるというもので、このガジェットは自作することもできます（iGoogle ガジェットベース）。その際にスプレッドシートの情報を取得するために使われるのが、本日ご紹介する Google Visualization API です。
この Google Visualization API 、なぜかあまり注目されていませんが、実はとても強力で画期的な API です。なんと、 Google Spreadsheets のデータに対して SQL に似た構文 (Google Visualization API Query Language) で問い合わせが実行できます。そう、 Google Spreadsheets を簡易データベースとして活用できるんです！
使える命令は SELECT のみで JOIN などの複雑な機能は省かれていますが、 WHERE, ORDER BY, GROUP BY, LIMIT, OFFSET あたりは使えます。シート内のデータから必要なものだけを選別して取得できるわけです。また、 GROUP BY との組み合わせで count, avg, min, max, sum といった集計も行えます。サーバーを用意することなしにこれだけのデータ処理が行えるというのは、素晴らしいことではないでしょうか。本日はその使い方を 詳しくご紹介しますので、 Web アプリケーションの制作にぜひお役立てください！
Google Visualization API の使い方
Query Language を利用するには、当然ながら Google Visualization API の使い方を知らなくてはなりません。ただ、ここまで説明していると一回では終わらなくなってしまうので、詳細はこちらの記事を参照してください。ここでは、ごく簡単に流れだけをご紹介するにとどめ、 Query Language のほうに専念したいと思います。
単純なデータの読み込み
それでは、 Google Visualization API を使って単純にシートのデータを読み込んでみましょう。まずは API をページに読み込む方法があります。 Google Visualization API は Google AJAX APIs のひとつなので、最初に Google AJAX APIs の JavaScript ファイルを読み込み、次にその API を利用して Google Visualization API を読み込むという、 2 段階の方法をとります。
< script type="text/javascript" src="http://www.google.com/jsapi">
< script type="text/javascript">
google.load("visualization", "1");
そして、読み込みたいシートに対応するデータソース URL （後述）と、データを受信した際に呼ばれるコールバック関数（ここでは handleRequest としています）を指定して、問い合わせを実行します。これはページ読み込みが完了した後でないと実行できないので、 google.setOnLoadCallback で設定したコールバック関数内で行うのが普通です。
var query = new google.visualization.Query(データソースURL);
query.send(handleResponse);
データを受信するとコールバック関数が呼び出されるので、よしなに処理します。ここでは手抜きな CSV 形式で alert に渡します（＾＾；
function handleResponse(response)
{
var data = response.getDataTable();
var csv = [];
for (var row = 0; row < class="me1">getNumberOfRows(); row++)
{
var line = [];
for (var col = 0; col < class="me1">getNumberOfColumns(); col++)
line.push(data.getFormattedValue(row, col));
csv.push(line.join(","));
}
alert(csv.join("\n"));
}
だいぶはしょりましたが、以上でスプレッドシートの内容が取得できます。とても簡単ですね！
Query Language を指定する
それでは、 Query Language を利用した問い合わせはどのようにするのでしょうか。実はとても簡単、 query.send の直前に一行追加するだけです。
var query = new google.visualization.Query(データソースURL);
query.setQuery("select * where A > 100");
query.send(handleResponse);
この setQuery メソッドに渡している文字列が Query Language です。ここでは、一番左のカラムが 100 以上の行だけを取得するように指定しています。 SQL を使ったことのある方なら、お馴染みの構文ですよね。もちろん、もっと多彩な条件を指定することも可能です。後にリファレンスを掲載していますので、参照してください。
データソース URL について
先ほどは流してしまいましたが、 Google Visualization API でデータをフェッチするには、データソース URL を指定する必要があります。これは実際にシートにガジェットを追加して、そのメニューで「Get query data source url…」を選択すれば取得できます。ただ、そんなことをするまでもなく、以下の形式で与えれば OK です（長いので二行に分けています）。
http://spreadsheets.google.com/tq?key=ドキュメントID
&gid=シート番号&range=取得範囲&pub=公開フラグ
それぞれのパラメータの意味は以下のようになっています。
- ドキュメントID
- ドキュメントファイル固有の ID です。ドキュメント編集ページの URL の "key" パラメータと同じです。
- シート番号
- 取得するシートの 0 から始まる通し番号です。
- 取得範囲
- 取得するカラムを "A1:C6" のように指定します。省略するとシート全体が対象になります。
- 公開フラグ
- アクセスにログインを必要とする場合は 0 、匿名アクセスの場合は 1 です。匿名アクセスではドキュメント自体が公開されていなければなりません。省略すると 0 として扱われます。
ちなみに、 Google Apps 版の Google Docs にも同様にアクセス可能です。ただし、公開されていないデータにアクセスする際 (pub=0) はベース URL を
http://spreadsheets.google.com/a/ドメイン名/tq
としなければなりません。逆に匿名アクセス (pub=1) のときは "a/ドメイン名" を入れるとエラーになるようです。ややこしい（´ー｀；
使ってみよう
Query Language をよりよく理解していただけるよう、簡単に問い合わせを発行できる iGoogle ガジェットを作ってみました。
| Query Viewer | |
 | Gadgets powered by Google |
データソースにはデフォルトで私が作成した県名リストが指定されていますので、後は「Query」に Query Language を入力して送信すれば、問い合わせ結果が表示されます。以降のリファレンスで掲載している Query Language の例も、このデータを前提にしています。もしご自分で作成したシートで試したい場合は、「Data Source」のところにデータソース URL を入力してください。
ちなみに県名リストの県コードは JIS X 0401 、地域コードは適当です（笑）。人口（単位は千）は総務省の統計データから、県民の日は Wikipediaから持ってきました。
リファレンス
それでは、今回のキモ、 Query Language の詳細をご紹介しましょう。基本的には SQL のサブセットなので、 SQL をご存知の方はすんなり理解できるでしょう。 SQL に慣れていなくても、幸か不幸か複雑な部分はことごとく省かれているので（＾＾；）、それほど難しくはありません。頑張ってください。
基本構文
Query Language の基本的な構文は以下のようになっています。
SELECT カラムリスト
「カラムリスト」は主に取得するカラムを限定するもので、スプレッドシートの列名をコンマ区切りで記述します。 "*" は特殊な指定で、すべてのカラムを取得することを示します。以下は県名リストから県名と地域名のみを取得する例です。
SELECT B, E
また、 sum, avg, min, max, count といった集計関数も利用できます。多くの場合、これらは後述の GROUP BY と併用します。以下は人口の合計と最大値を取得する例です。
SELECT sum(G), max(G)
さらに、これらの構文の後に以下の指定（SQL では「句」と呼びます）を付加できます。
| 句 | 機能 |
|---|---|
| FROM | テーブルの指定 |
| WHERE | 取得する行の条件 |
| GROUP BY | 集計時のグループ分け |
| PIVOT | 結果を横一列に並べる |
| ORDER BY | 行の並べ替え |
| LIMIT | 取得する最大行数 |
| OFFSET | 取得の開始行 |
| LABEL | カラムの見出しを指定 |
| FORMAT | 時刻や数値の出力形式の指定 |
| OPTIONS | 雑多なオプション指定 |
以降、それぞれについて詳しくご紹介します。なお、これらの句は同時に複数指定できますが、必ず上記の順番で書く必要があります。ご注意ください。
FROM 句
取得するテーブルを指定する句ですが、現在の Google Docs では無視されるようです。
WHERE 句
取得する行の条件を指定します。例えば、人口が 5,000 以上の県を取得するには以下のようにします。 G は人口カラムのカラム名です。
SELECT * WHERE G > 5000
使える演算子は <, ⇐, >, >=, =, !=, <> （!= と <> は同じ意味）, IS NULL, IS NOT NULL で、数値だけでなく、文字列や日時なども比較できます（後述のデータ型を参照）。 IS NULL, IS NOT NULL はカラムが空かどうかを判定します。さらに AND, OR, NOT で複数の条件を並べる事ができ、括弧で優先順位の指定もできます。これらを組み合わせれば、「県民の日がなく、かつ人口が 1,000 〜 5,000 の間にあるものを除く」なんて条件も指定できます。
SELECT * WHERE H IS NULL AND NOT (G > 1000 AND G <>
残念ながら、現在は Query Language に日本語を含めると無条件でエラーになってしまい、日本語の文字列を条件に含めることができません。これはぜひ改善してほしいところです。
GROUP BY 句
集計関数を使う際に、特定のカラムの内容で結果をグループ分けする際に使用します。地域ごとに人口を合計するには以下のようにします。
SELECT E, sum(G) GROUP BY E
PIVOT 句
通常の SQL にはない構文ですが、集約関数の結果を一行に展開してます。これに関しては、実際に実行してみるのが一番わかりやすいと思います。
SELECT sum(G) PIVOT E
私にはいまいち利点がわからないのですが、一行に収まるから取得するのが楽、とかですかね？（＾＾；
ORDER BY 句
指定したカラムをキーにして行をソートします。人口の少ない順に取得するには以下のようにします。
SELECT * ORDER BY G
キーとなるカラムはコンマ区切りで複数指定できます。地域ごとに人口順に並べるにはこう。
SELECT * ORDER BY D, G
それぞれのカラムの後に "DESC" を付けると、降順になります。
SELECT * ORDER BY D DESC, G DESC
LIMIT 句、 OFFSET 句
それぞれ、取得する最大行数と、取得開始位置を指定します。人口順に並べて、 3 番目から 5 行取得するには、以下のようにします。
SELECT * ORDER BY G LIMIT 5 OFFSET 3
いわゆる「ページネーション」というやつは、これで実現されています。
LABEL 句
通常はスプレッドシートの一番上の行が見出しとして使われるのですが、 LABEL 句でそれを変更できます。例えば、県コードのラベルを "code" にするには、以下のようにします。
SELECT * LABEL A 'code'
前述のとおり Query Language に日本語を含めることができないので、ラベルを日本語にすることもできません。
FORMAT 句
数値や日付の表示フォーマットを指定します。数値のフォーマット指定の詳細はこちら、日付はこちらにあります。
地域ごとの平均人口を小数第二位まで表示します。
SELECT E, avg(G) GROUP BY E FORMAT avg(G) '#.00'
県民の日を "月/日" で表示します。
SELECT * FORMAT H 'MM/dd'
OPTIONS 句
主に不要なデータをレスポンスから削除してデータ量を削減するためのオプションです。以下の指定が可能です。
| 指定 | 機能 |
|---|---|
| no_format | 各カラムのフォーマット指定を省略します |
| no_values | 実際の各カラムの値を省略します |
例えば、以下のように no_values を指定すると、各カラムの値が省略されるためにレスポンスがかなり小さくなります。
SELECT * OPTIONS no_format
もちろん getFormattedValue メソッドなどでカラムの値を取得することはできません。 ID やラベル、フォーマットなどのスキーマ情報(?)のみが必要な場合に指定すると良いでしょう。
データ型について
各カラムのデータは、すべて string, number, date, timeofday, datetime のいずれかの型を持っています（リファレンスに は boolean 型もありますが、 Google Docs では使われていないように思います）。データの比較なども型に基づいて行われるため、 WHERE 句などに数値 (number) 型以外のデータを記述する場合は、データ型を明示するための構文を使う必要があります。といっても、そんなに複雑ではないので、以下にまとめておきます。
- string （文字列）
- 文字列は必ずシングルクォートかダブルクォートで囲います。
例：'foo' "This is a string" - number （数値）
- 数値はそのまま記述すれば OK です。
例：10 -100 6.7 - date （日付）
- date は時刻なしの日付を表します。「date "年-月-日"」 と記述します。
例：date "2008-3-26" - timeofday （時刻）
- timeofday は日付なしの時刻を表します。「timoofday "時:分:秒"」と記述します。
例： timeofday "13:05:10" - datetime （日時）
- datetime は日付と時刻の両方を持った完全な日時を表します。「datetime "年-月-日 時:分:秒"」もしくは「timestamp "年-月-日 時:分:秒"」と記述します。
例：datetime "2008-3-26 13:05:10"
サーバーサイドでの利用も可能
Firebug などでちょっと調べればわかりますが、 Google Visualization API の内部的なやり取りは、非常に単純な REST + JSONP の形式です。例えばリクエストに関しては、データソース URL に以下のパラメータを追加して GET リクエストを投げているだけです。
| パラメータ | 内容 |
|---|---|
| tq | Query Language そのもの |
| tqx | reqId:リクエストごとにインクリメントする数値 |
実際、以下のリンクをクリックすると、県リストの最初の 1 行（北海道）のデータが表示されます。これを見ていただければ、レスポンスの形式に関してもだいたいの察しはつくでしょう。
このようにとてもシンプルなプロトコルなので、サーバーサイドで利用してもなかなか便利です。 SQL に慣れている方も多いかと思いますので、 Query Language を活用するのも容易でしょう。 Google Spreadsheets とのマッシュアップがより身近になりますね。
以上、本日は JavaScript で Google Spreadsheets のデータにアクセスできる Google Visualization API についてご紹介しました。単体でも非常に興味深い API ですが、外部データのインポート機能やフォーム生成機能などと組み合わせれば、さらに可能性が広がります。ぜひ活用してください！
参考
- 公式リファレンス （Google Code）
- Google Visualization APIを早速使ってみた （ZDNet builder）
- Google Visuaization API リファレンス （当サイトの記事）
![[livedoor clip!]](http://webos-goodies.jp/template/imgs/bookmark/livedoorclip.gif){kind=small}
![[はてなブックマーク]](http://webos-goodies.jp/template/imgs/bookmark/hatena.gif){kind=small}
![[del.icio.us]](http://webos-goodies.jp/template/imgs/bookmark/delicious.gif){kind=small}
この記事へのコメント
1. Posted by ホツォ 2008年10月27日 0:1
ガジェットのタイトルをアルファベットに変えたら、プレビュー画面までは、spreadsheetのデータを引っ張り出せるようになりました。
あとは私がウェブページを置いているサーバーとの関係かもしれません。
2. Posted by ホツォ 2008年10月27日 0:3
ウェッブページでも使えるようになりました。
3. Posted by ホツォ 2008年10月27日 0:19
spreadsheetのデータをigoogle上のガジェットに呼びたすところまでは、成功しました。
ところがこのガジェットを自分のウェブページに追加するとデータを呼び出せません。
「このガジェットについて」→「このガジェットを埋め込む」と進むとプレビュー画面がでてくるのですが、すでに、そこでデータが読み込めていません。
データソースになっているspreadsheetも「公開」、URLの「公開フラグ」も"&pub=0"にしています。
Google Spreadsheets を簡易 SQL DB にすることができるのはigoogle上だけのことでしょうか。
4. Posted by 伊藤 2008年10月28日 0:17
ホツォさん、コメントありがとうございます!
週末ちょっと外出していまして、質問にお答えできず申し訳ありません。
記事でも書いていますが、 Query Language に日本語が使えないなど、
国際化対応はまだ完全でないようです。
うまくいかないときは、まずそのあたりを疑うと良いかもしれません。
ではでは、今後ともよろしくお願いします!
5. Posted by ホツォ 2008年10月31日 0:49
こちらこそよろしくお願いします。
spreadsheetsのデータですが、igoogleにも,Chrome にも引き出せるようになりました。ところが、IE7.0では、引き出せず「読み込み中」が表示されたままになってしまいます。
このあたりはいったいどうなっているのでしょう。
6. Posted by 伊藤 2008年11月1日 0:18
ホツォさん、こんにちは。
IE はブラウザ自身の JavaScript API が他のブラウザとけっこう違うので、「IEだけ動かない」というのは Google Gadget のグループでもよく質問されています。 JavaScript のエラーが出ていないか、 JavaScript デバッガなどで確認してみてはいかがでしょうか。
IE はデバッグ環境が非常に貧弱で問題の追跡も大変だと思いますが、頑張ってください。
7. Posted by ホツォ 2008年11月14日 12:2
おそくなりましたがレスありがとうございます。プログラムそのものは、伊藤さんのをほぼそのまま使っていたので、私にはどうしてよいか分からず、
Spreadsheetの空白行を無くしてみたり、ひとつのセルに2行入っていたところを一行にしてみたり、といろいろしてみましたが問題解決しませんでした。さいご、
csv.push(line.join("
を
csv += line;
に、
el.innerHTML=csv.join("\n");
を
el.innerHTML += csv;
にしたところで、IEでも表示されました。
8. Posted by ホツォ 2008年11月14日 12:14
↑補足ですが、私はSpreadsheet上の、一行の中のデータをガジェットの中で、縦にリスト表示させようとしていました。
9. Posted by 伊藤 2008年11月17日 0:51
ホツォさん、わざわざご報告ありがとうございます。
無事に動いたようで、私も安心しました。
push, join は Array クラスのメソッドなので、 csv が文字列の場合は機能しません。逆に += による追加は csv が文字列である必要があります。
innerHTML については、 = による代入では既存の内容を削除して csv の内容に差し替えるのに対して、 += だと追加になります（正確には、 既存の内容に csv の内容を追加した HTML 形式の文字列を生成し、それによって内容を再構築します）。
このあたりの微妙な違いで、エラーになっていたのでしょう。 Firefox などはエラーコンソールに情報が表示されるのですが、 IE だと難しいですね。
ではでは、今後ともよろしくお願いします。
No comments:
Post a Comment