LoginSignup
11
11

More than 5 years have passed since last update.

Google Apps ScriptAdvent Calendar 2014Day 19
@TomohiroShigemasa(Shigemasa Tomohiro)

GAS+GoogleCloudSQL+GoogleChartToolsで簡易BIツールを作ってみる

Last updated at Posted at 2014-12-10

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

きっかけ

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

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

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

仕組み

  1. GASのJDBCServiceでGoogleCloudSQLに格納されているデータを抽出する。
  2. 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のデータを使って、グラフを記載することができました。工夫すれば、様々な切り口より、データ分析を行うことができるツールを作れるのかもしれないなと考えています。

最後に

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

11
11
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
11
11