Google Apps Script Advent Calendar 2014の19日目の記事になります。

きっかけ

　グラフを表示するに際して、GoogleのSpreadSheetのグラフはなかなかのスグレモノで、下手にGASのChartServiceで構築するよりもグラフの種類も豊富だし、また、こっちの方が起動も早い。つまり、こねくり回してGASを構築するよりも、素直にSpreadSheetを使った方が無難ではあると思います。

　ただ、SpreadSheetのグラフを普通に使うには
　■SpreadSheet上に値があること
　という当たり前の条件があるため、CloudSQL等のDBを使用している場合は、表現がめんどくさいです。

GASのUi上でSpreadSheetのグラフ相当のものが何とかできないかと、格闘しているうちに何とかできてしまったので、半分は自分の備忘録として、そしてもう半分（以上？）はたどたどしいコーディングが見られることで磨かれてゆくことを期待して、事例紹介したいと思います。

仕組み

GASのJDBCServiceでGoogleCloudSQLに格納されているデータを抽出する。
GASのHTMLServiceの中にGoogleChartToolsのライブラリを組み込み、グラフ化する。

です。

コード

(gas側)

doGet.gs

function doGet(){
  var app=HtmlService.createTemplateFromFile('page').evaluate().setTitle('xxx').setSandboxMode(HtmlService.SandboxMode.NATIVE);
  return app;}

sql_connect.gs

function sql_connect(){

  var db = Jdbc.getCloudSqlConnection('jdbc:google:rdbms://（project_id）/(instance_name)');
  var db_connect = db.createStatement().executeQuery('select * from table名');
  var array =[];
  var i = 0;
 while(db_connect.next()){
      array[i] = db_connect.getInt('aaa');
      i++;
      array[i]= db_connect.getInt('bbb');
      i++;}
  return array;}

(html側)

page.html


<script type="text/javascript" src="https://www.google.com/jsapi"></script>
<script type="text/javascript">
  google.load("visualization", "1", {packages:["corechart"]});
  google.setOnLoadCallback(hogera);

  //GASからCloudSQLのデータを抽出する
  google.script.run.withSuccessHandler(hogera).sql_connect();

  //グラフ化
  function hogera(hoge) {

    var array = hoge;
    var leng = array.length/2;
    var i = 1;
    var ii = 0;
    var array2 =[];
    while(1){
      array2.push([i+'日',array[ii],array[ii+1]]);
      i++;
      ii+=2;
      if(i>leng){
        break;}
      }

    //ComboChartを作成する
    var Chart = 
      function(title,div_name,piyo){

        //当月の最終日を取得する
        var i = 1;
        var a1 = 0;
        var a2 = 0;
        var date = new Date();
        var f_date = new Date(date.getFullYear(),date.getMonth()+1,0).getDate(); 

        //DataTableにデータを流し込む
        var array1 = [['日付','aaa','bbb']];
        var q = 0;
        var length = piyo.length;
        while(1){
        if(q>=length){
          break;}
        array1.push(piyo[q]);
        q++;}
        var data = google.visualization.arrayToDataTable(array1);

        //グラフのオプション
        var option = {
          title : title,
          vAxis: {titleTextStyle:{bold:true , fontSize:14 , italic:false}},
          hAxis: {title: "水平の題名" , titleTextStyle:{bold:true , fontSize:14 , italic:false } ,textStyle:{fontSize:10}, maxTextLines:30 , maxAlternation:2},
          seriesType: "bars",
          series: {1: {type: "line"}}};

        //チャート化
        var chart = new google.visualization.ComboChart(document.getElementById(div_name));
        chart.draw(data,option);
       return chart;}

    Chart('題名','chart_div1',array2);}

</script>

  <div>
    <div>グラフ</div>
      <table>
        <tbody>
          <tr>
            <td id="chart_div1"></td>
          </tr>
        </tbody>
      </table> 
  </div>

解説

■gas側のHTMLServiceで、HTMLを呼び出す処理を行います。

:gas
function doGet(){
  var app=HtmlService.createTemplateFromFile('page').evaluate().setTitle('xxx').setSandboxMode(HtmlService.SandboxMode.NATIVE);
  return app;}

　　
■HTML側でGoogle AJAX APIsの共通ライブラリを読み込みます。
■Google AJAX APIsのgoogle.loadでGoogle Visualization API（GoogleChartTools）を読み込みます。
■読み込みが完了したらsetOnloadCallbackでfunctionの読み込み先(hogera)を指定します。

html


<script type="text/javascript" src="https://www.google.com/jsapi"></script>
<script type="text/javascript">
  google.load("visualization", "1", {packages:["corechart"]});
  google.setOnLoadCallback(hogera);

　　　　
■グローバル関数で宣言を行います。
■google.script.runでHTMLからGASのfunction(sql_connect)を呼出し、処理を行います。
■GASのfunction(sql_connect)からの戻り値をfunction(hogera)に引き込みます。

html


  google.script.run.withSuccessHandler(hogera).sql_connect();

　
■gasのJdbcServiceより、GoogleCloudSQLに接続します。
■MySQLのクエリ文を入力し、検索結果を抽出します。
■変数arrayにデータを収納し、HTML側のfunction(hogera)に戻します。
（どうも、2次元配列だと、取り出す際に上手くいかなかったので、無理矢理、1次元配列で格納。もっとうまくやる方法があるとは思う。）

gas

function sql_connect(){

  var db = Jdbc.getCloudSqlConnection('jdbc:google:rdbms://（project_id）/(instance_name)');
  var db_connect = db.createStatement().executeQuery('select * from table名');
  var array =[];
  var i = 0;
 while(db_connect.next()){
      array[i] = db_connect.getInt('aaa');
      i++;
      array[i]= db_connect.getInt('bbb');
      i++;}
  return array;}

　
■HTML側で日付を加え、配列し直します。
（無駄なコーディングをしている気がする・・・。）

html

  function hogera(hoge) {

    var array = hoge;
    var leng = array.length/2;
    var i = 1;
    var ii = 0;
    var array2 =[];
    while(1){
      array2.push([i+'日',array[ii],array[ii+1]]);
      i++;
      ii+=2;
      if(i>leng){
        break;}
      }

　
■チャート作成をfunction化し、複数の図形描画に対応できるようにします。
■DataTableへの流し込みはいくつか方法があるようですが
2次元配列での記載方法が多いと思います。
[[日付,'aaa','bbb'],
[1,121,120],
[2,116,120],
[3,124,120]...]
というようなデータをgoogle.visualization.arrayToDataTableでDataTableに流し込みます。

html

    var Chart = 
      function(title,div_name,hogera){

        //当月の最終日を取得する
        var i = 1;
        var a1 = 0;
        var a2 = 0;
        var date = new Date();
        var f_date = new Date(date.getFullYear(),date.getMonth()+1,0).getDate(); 

        //DataTableにデータを流し込む
        var array1 = [['日付','aaa','bbb']];
        var q = 0;
        var length = piyo.length;
        while(1){
        if(q>=length){
          break;}
        array1.push(piyo[q]);
        q++;}
        var data = google.visualization.arrayToDataTable(array1);

　
■グラフのオプションを設定します。
（この辺は色々な方がまとめてらっしゃると思うので、そちらをご覧下さいませ。）

html

        var option = {
          title : title,
          vAxis: {titleTextStyle:{bold:true , fontSize:14 , italic:false}},
          hAxis: {title: "xxx" , titleTextStyle:{bold:true , fontSize:14 , italic:false } ,textStyle:{fontSize:10}, maxTextLines:30 , maxAlternation:2},
          seriesType: "bars",
          series: {1: {type: "line"}}};

　
■チャート化します。

html

        var chart = new google.visualization.ComboChart(document.getElementById(div_name));
        chart.draw(data,option);
       return chart;}

　
■Chartに引数を入れ、実行します。

html

    Chart('題名','chart_div1',array2);}

　
■HTMLでチャートを表示する場所を指定します。

html

  <div>
    <div>グラフ</div>
      <table>
        <tbody>
          <tr>
            <td id="chart_div1"></td>
          </tr>
        </tbody>
      </table> 
  </div>

という感じで、構築してGAS上に、CloudSQLのデータを使って、グラフを記載することができました。工夫すれば、様々な切り口より、データ分析を行うことができるツールを作れるのかもしれないなと考えています。

最後に

文法上の誤り等もあるかと思います。もし、お気づきになりましたら、そっと呟いて頂けると大変助かります。