Fusion Table活用法

ScriptDbが廃止されてしまい、Spreadsheetの制限が緩和されて、有料のCloud SQLやBigQueryを使ってくれ的な方向に誘導されつつある今日このごろですが、正直Google Apps Scriptという世界ではCloud SQLやBig Queryは決して使いやすいものでもなく、また、オーバースペックです。また、緩和がされたからといってもSpreadsheetはDBの代わりにならないわけで（特定のレコードの高速抽出や、アップデートなどなどデータベースで用いる様々なメソッドがまるで用意されていない。自分で実装すると物凄く遅い）。

ということで、まだ試験運用中！？らしいのですが、Fusion Tablesを使えないだろうか？と考えています。しかし、例の如くAPIはあるのですが、Google DeveloperにはJavaScriptやGoogle Apps Scriptでのサンプルコードやリファレンスがまるで用意されていません。Developer Consoleにはメソッドの紹介と中身らしきものがあるのですが、サンプルコードなどもありません。ちょっとの情報を手がかりに、少しづつGASから使えるデータベースとして期待はしているので、ここに記述していきたいと思っています。

事前準備

ファイルを準備する

Google Apps Scriptの他のAPIやメソッドと違って非常にクセのある独特なメソッドと使い回しが多い印象のFusion Tablesですが、まずは使えるようにすることと、ファイルの準備が必要です。Google Driveの標準アプリにはなっていませんので、まずは、以下の手順でアプリケーションのインストールとファイルの準備が必要です。

  1. Google Driveの「作成」から、【アプリを追加】をクリックします。
  2. 検索ボックスに tables と入れて検索すると出てきますので、接続しアプリケーションを追加します。
  3. もう一度Google Driveの作成から、今度はFusion Tablesを選択します。
  4. 以下の３種類の作成方法があります。
    • From this Computer - PCからCSV, TSV, TXT, KMLの４種類のファイルからテーブルを作成します。
    • Google Spreadsheet - Google Drive上にあるスプレッドシートからテーブルを作成します。
    • Create Empty Table - 空のFusion Tablesを作成します。テーブルを自分で定義しなければなりません。
  5. 今回は、予め用意してあるスプレッドシートをインポートする形でテーブルを作成しますので、Spreadsheetを選択します。
  6. あとは適当にインポートの指示を出します。殆どは何もしないで次へ進んで頂いて結構です。
  7. すると、テーブルが作成されて、使用することが出来るようになります。
  8. 共有したい場合には、共有設定を行います。その場合共有対象じゃない人はアクセスが制限されます。

Fusion Tablesは通常のテーブルの他にも以下の機能が備わっていて、世間一般では主にGoogle Mapsのマッピングが目的で使っている人が殆どのようです。

  1. ただのテーブルビュー
  2. カード型ビュー（HTMLでレイアウトを作れる。これはコレで使えるかも）
  3. サマリー型ビュー（グラフやデータを同時に表示するタイプ）
  4. チャート（手軽にグラフを作成する）
  5. マップ（緯度経度もしくは住所データが必要）

今回は、マッピングではなく真のデータベースとしての利用を考えているので、特にここでは何もしません。インポートが完了したら準備は完了です。

スクリプトで使えるようにする

Fusion Tablesは、そのままではGoogle Apps Scriptでは使用できません。また、１日のリクエスト件数は25,000件となっており、それ以上は課金対象となります。もちろん課金登録してなければ、そこで停止されてしまいます。故に、メソッドの無駄使いは極力減らしたいのと、直接の読み書きを頻繁に行うのではなく、スプレッドシートを踏み台にして、メソッド発行回数を減らす努力が必要です。Google Apps Scriptで使用出来るようにするためには、以下の手順でAPIを有効にする必要があります。尚、リクエスト件数はプロジェクト単位になっているようなので、別のプロジェクトであるならば、そちらはそちらで別途25,000件使えるようです。

  1. Fusion Tables APIを使用する予定のスプレッドシートなどを用意する
  2. スクリプトエディタで中に入って、「リソース」 -> 「Googleの拡張サービス」を開く
  3. まず、Googleの拡張サービスダイアログでFusion Tablesを有効にする
  4. その後、下のリンクにあるGoogle デベロッパーコンソールをクリックする
  5. デベロッパーコンソール側でも、Fusion Tables APIを有効にする

これで、Fusion Tables APIが利用可能になりました。この作業は用意するスプレッドシート毎に必要になります。

各種メソッド

メソッドはそれなりに用意されていますが、リファレンスがないので、細かなオプションなどの指定方法に不明な点が多いです。分かり次第追加していこうと思ってます。本来は、URLにオプションを続けて投げるのが使い方のようなのですが、Google Apps Script上では、メソッドで投げますのでちょっと流儀が異なるようです。オプション指定は、{}の中に、オプション名とコロン、そしてそれに数字を入れて１つで構成するようです。

テーブル関係のメソッド

FusionTables.Table.list

自分のアカウントのGoogle Drive上にあるFusion Tablesファイルをリストアップするメソッドです。テーブル名やIDの取得などを目的に使用します。オプション指定として以下の要素があるようです。maxResults以外はメソッドチェーンのほうで取得できるので、別に指定は必要ないと思います。

  • maxResults = 最大表示件数
  • pageToken = OAuth2によるトークンを指定するようですが、今回は指定していません。
  • fields = フィールドに関する様々な属性情報を取得するようです。値の型であったりカラムIDなどなど非常に細かく指定できるようです。

メソッドチェーンとして、以下の要素があるようです。

  • items = テーブルに関する情報を取得します。さらにメソッドチェーンをつなげると、フィールドの様々な属性値を取得することが出来ます。
  • kind = 多分、引数に文字列を入れると、検索結果から絞込ができるのだと思います。
  • nextPageToken = 認証系のものだと思うのですが、不明です。

使用例（自分のドライブのFusion Tablesをリストアップしてダイアログでお知らせする）

function listTables() {
  var sheet = SpreadsheetApp.getUi();

//最大件数5件のオプション指定で取得

  var tables = FusionTables.Table.list({maxResults:5});
  var tableinfo = "";
  
  if (tables.items) {
    for (var i = 0; i < tables.items.length; i++) {
      var table = tables.items[i];
      //\r\nで改行してリストアップデータを変数に追加
      tableinfo = tableinfo + "テーブル名：" + table.name + "\r\nID：" + table.tableId + "\r\n\r\n";
    }
  } else {
    tableinfo = "テーブルなかったよ";
  }
  
  sheet.alert(tableinfo);
}

クエリー関係のメソッド

FusionTables.Query.sqlGet

指定されたIDのFusion Tablesより、組み立てたSQLを投げてデータを取得するためのメソッドです。このメソッドでは使えるSQL文が限定されていて、SELECT/SHOW/DESCRIBEの３つが使用できます。オプション指定もありますが、大抵は決まったオプションしか使わないと思います。オプション指定は以下の通りです。

  • sql = 引数として渡しますので、オプション指定では使いません。
  • hdrs = カラム名を含めるか否かを指定します。デフォルトではtrueですが、通常はfalseを指定します。trueにすると１行目がロックされます。
  • typed = JSON形式で返すかどうかを指定します。デフォルトはtrueです。falseにするとKMLで帰ってくるようですが、普通指定しません。
  • fields = フィールドに関する様々な属性情報を取得するようです。値の型であったりカラムIDなどなど非常に細かく指定できるようです。

メソッドチェーンとして以下の要素があるようです。

  • columns = カラム要素を取得します。
  • kind = 多分、引数に文字列を入れると、検索結果から絞込ができるのだと思います。
  • rows = レコード要素を取得します。

使用例（特定のFusion Tablesからテーブルデータを100レコード持ってきて貼り付ける）

function runQuery() {

var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

var ui = SpreadsheetApp.getUi();

  var tableId = "ここにFusion TablesのIDを入れる";

//SQL文を組み立てる

  var sql = 'SELECT * FROM ' + tableId + ' LIMIT 100';
  var result = FusionTables.Query.sqlGet(sql, {
    hdrs: false
  });
  if (result.rows) {
    var sheet = spreadsheet.getActiveSheet();
    // カラムを貼り付け
    sheet.appendRow(result.columns);
    // レコードを貼り付け
    sheet.getRange(2, 1, result.rows.length, result.columns.length)
        .setValues(result.rows);

ui.alert("取得完了")

  } else {
    ui.alert("レコードなかったよ")
  }
}

Fusion Tableで作ったマップを貼ってみた

※要Fusion Table有効化

Googleドキュメントには現在試験運用中のサービスとして、Fusion Tableというものがあります。Google Apps for Businessでも管理者が許可していれば使用することができます。これはスプレッドシートとは違い、データテーブルとして利用のできるデータベースファイルで、データの蓄積・読み書きの出来るサービスとなっています。これが使えるようになると、スプレッドシートの運用もかなり変わってくるのですが、まだまだバギーなところもないわけじゃないです。

しかし、このFusion Tableは簡単にデータをマッピングできる機能がついており、おまけにKMLのインポートに対応しています。ちょっとインポートの仕方に癖があったり、インポート後に編集が必要だったり、また、KMLの段階で作っておかないとできないこともあったりするのですが、非常に興味深い機能です。また、共有すればJavascript付きでシェアのできるコードが吐出されるので、それをガジェット化してここに貼り付けています。

Fusion Tableで作ったマップを貼ってみた

下のマップは、自分が昔やっていたサイトで公開してたH18年全国乳牛飼養頭数の統計データをshp2kmlやshape2earthを使用してシェープファイルをKMLに変換したものをインポートしたものです。シェープファイルはフリーでダウンロード出来るところがたくさんあるので、それらに値をFusion Table上で値を付加して、色分けをしています。もちろん、ポイントデータでも行けますし、スプレッドシートからインポートもできるすぐれものです。早く、正式リリースしてほしい。その時は挿入からFusion Tableで行けるようになるかもしれません。

使用したFusion Tableは公開しています。

インポート時のコツ

  • マイマップから吐き出したKMLの場合、結構素直にインポートできる。
  • Google Earthから吐き出したKMLは、単体ではなく、大元（親フォルダ）毎エクスポートしないとうまくいかない。

参考リンク

注意事項

  • 正直、現時点ではScriptDbの代わりにはなりません。思っているほど早くもない、API体系が複雑、結構色々エラー出るなどなど。
  • Fusion Tablesはデータベースではありますが、複数のテーブルを内部に持つことができません。
  • また、Fusion Tablesはその為、RDBMSにはなれません。本当に入れ物としての単一データベースです。
  • RDBMSのようにマスタをいくつも持って、データストア用のテーブルをと考えると非常に扱いにくいものです。そこまで必要な場合には、自前のMySQLサーバやGoogle Cloud SQLの利用が必要でしょう。

参考URL