【GAS】WebアプリやAPIも作れちゃう!HtmlServiceについて、できることをまとめてみた。 ~ その② FusionTablesとの連携 ~

HtmlServiceまとめシリーズ

前回はHtmlServiceを使ってWebアプリケーションを作成する方法と、スプレッドシートとの連携についてご紹介しました。

今回は、Google Driveで使えるFusionTablesのご紹介と、HtmlServiceとの連携を書いていきたいと思います。

Fusion Tablesとは?

スプレッドシートをDBに見立てて開発をしていると、どうしても辛くなってくるときがあるかと思います。具体的には、SQLでデータ操作したいというケースも多々あるでしょう。そんなときには、Google Driveで提供されているFusion  Tablesがとても便利です。

Fusion Tablesは、Google Driveで利用できるデータベースです。公式ヘルプは以下となります

https://support.google.com/fusiontables/answer/2571232?hl=en

Fusion Tablesのセットアップ

Google Driveでアプリを追加する必要があります。

まずは、Google Driveを開き、「新規」→「その他」→「アプリを追加」を選択します。

次に、検索窓に「Fusion Tables」と入力すると、

Fusion Tablesを選択する画面が現れるので、「接続」を押します。

これで、無事Google DriveでFusion Tablesを使えるようになりました。

再度、「新規」→「その他」を見てみると、Fusion Tablesを選択できるようになっています。

DBを作ってみる

Fusion Tablesを立ち上げてみましょう。そうすると、まずは以下のような画面になると思います。

「From this computer」では、ローカルのPCにあるファイルからDBを作成することができます。公式のヘルプによると、対応するファイルは以下の通りです。

  • 何らかの区切り文字テキストファイル (.csv, .tsv, etc…)
  • KML (.kml)
  • スプレッドシート (.xls, .xslx, .ods)

「Google Spreadsheets」では、Google DriveにあるスプレッドシートからDBを作成することができます。

「Create empty table」は空のDBですね。

ここでは、スプレッドシートからDBを作成してみたいと思います。前回のブログのDrive使用容量管理を使用することとします。ウィザードに従っていけば、以下のようにDBを作成することができると思います。

Google Apps Scriptとの連携

やっとここまで来ました。。。

GASから、Fusion Tablesにアクセスし、HtmlServiceでブラウザ表示してみましょう。

GASでFusion Tablesにアクセスするためには、ライブラリを追加登録する必要があります。まずは、GASのエディタにて、「リソース」→「Google の拡張サービス」を選択します。

「Fusion Tables API」をONにした後、Google API コンソールへのリンクを選択します。

ライブラリを選択し、

検索窓に「fusion」と入力すると、

Fusion Tables APIを見つけることができます。これを選択すると、

APIを有効にするための画面が出現するので、有効にするを選択してください。

ここまで設定すると、GAS側でFusion Tablesを操作できるクラスを扱えるようになります。

それでは、GASのコードを書いてみましょう。単純にSELECTするだけであれば、以下のようにかけます。

function getFusionTablesData() {
  var tableId = 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX';
  var sql = 'SELECT * FROM ' + tableId;
  var res = FusionTables.Query.sql(sql);
  
  Logger.log(res.rows);
}

これで、ログにはFusion Tablesに格納されているデータの配列が記録されているはずです。

ちょっとアプリっぽくしてみる

Driveの使用容量管理で、日付を指定してデータを閲覧できるようにします。GAS側のコードは以下となります。

function doGet(e) {
  return HtmlService.createTemplateFromFile("index").evaluate();
}

var tableId = "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX";

function getFusionTablesData() {
  var sql = "SELECT * FROM " + tableId;
  var res = FusionTables.Query.sql(sql);

  return res.rows;
}


function getFusionTablesData_withDate(startDate, finishDate) {
  var sql = "SELECT * FROM " + tableId + " WHERE '日付' >= '" + startDate + "' AND '日付' <= '" + finishDate + "'";
  var res = FusionTables.Query.sql(sql);

  return res.rows;
}

WHEREを用いて、範囲を絞れるようにしています。
index.htmlは以下のようになります。

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <style>
     table,td,th{
       border: 1px solid black;
     }
     td {
       padding: 10px;
     }
    </style>
  </head>
  <body>
    <div>
      <h3>期間指定</h3>
      <label for="start-date">開始日付:
        <input id="start-date" type="date" />
      </label>
      <label for="finish-date">終了日付:
        <input id="finish-date" type="date" />
      </label>
      <button onclick="execQuery();">検索実行</button>
    </div>
    <?
      var data = getFusionTablesData();
    ?>
    <br>
    <h3>データ</h3>
    <div id="data">
      <table>
        <tr>
          <th>日付</th>
          <th>使用容量 [GB]</th>
          <th>残容量 [GB]</th>
        </tr>
          <?
            for(var i = 0, l = data.length; i < l; i++) {
              output.append('<tr>');
              output.append('<td>' + Utilities.formatDate(new Date(data[i][0]), 'Asia/Tokyo', 'yyyy/MM/dd') + '</td>');
              output.append('<td>' + data[i][1] + '</td>');
              output.append('<td>' + data[i][2] + '</td>');
              output.append('</tr>');
            }
          ?>
      </table>
    </div>
    <script>
      function execQuery() {
        var startDate = document.getElementById("start-date").value;
        var finishDate = document.getElementById("finish-date").value;
        
        // GASコード(getFusionTablesData_withDate())の実行
        google.script.run
        .withSuccessHandler(function(data) {
          // GAS正常終了時の処理
          refreshTable(data);
        })
        .withFailureHandler(function(data) {
          // GASエラー時の処理
          console.log(data);
        })
        .getFusionTablesData_withDate(startDate, finishDate);
      }
      
      
      function refreshTable(data) {
        var elem = document.getElementById("data");
        elem.innerHTML = "";
        
        var html = "";
        html += "<table>";
        html += "<tr>";
        html += "<th>日付</th>";
        html += "<th>使用容量 [GB]</th>";
        html += "<th>残容量 [GB]</th>";
        html += "</tr>";
        for(var i = 0, l = data.length; i < l; i++) {
          html += "<tr>";
          html += "<td>" + data[i][0] + "</td>";
          html += "<td>" + data[i][1] + "</td>";
          html += "<td>" + data[i][2] + "</td>";
          html += "</tr>";
        }
        html += "</table>";
        elem.innerHTML = html;
      }
    </script>
  </body>
</html>

これで、日付の範囲を指定できるようになりました。
これをスプレッドシートをDBに見立てた場合の構成でやろうとすると、ちょっと辛いですね。

また、index.htmlの54行目あたりにに

google.script.run・・・

という記述があったかと思います。
これは、ブラウザ側のJavaScriptから、GASを実行するためのコードになります。「withSuccessHandler」はGAS正常終了時の、「withFailureHandler」はGAS異常終了時のコールバックとなります。

 

まとめ

HtmlServiceというよりかは、Fusion Tablesのお話になってしまった気がしますが、、、笑

次回はスプレッドシートにちょっとしたUIをつけるためのHtmlServiceの活用についてお話ししたいと思います。