ビジネス

2019/06/26

【GASデータ活用】スプレッドシートのデータをGoogle Apps Scriptで読み込んで、HtmlService × Chart.jsでグラフを表示してみる

今回はキーワード盛り沢山なタイトルでお送りします。

「スプレッドシートのデータをGoogle Apps Scriptで読み込んで、HtmlService × Chart.jsでグラフを表示してみる」ということで、ビジネス現場でじわりじわりと存在感を増しつつあるGoogle Apps Script、通称GAS（ガス）を使って、簡易的にグラフを作ってみようと思います。

GASという環境自体、本格的なシステム開発に向いている仕組みではないので注意は必要ですが、この延長線上ではWebアプリ的にダッシュボードを作ることも可能で、スプレッドシートを中心としたG-Suiteとの連携はできることの範囲が意外と広いので、現場発の業務改善プロトタイピングにはお勧めです。（GASの特徴を書いた3年前の記事はこちら。）

ということで実際に見ていきましょう。

1.まず「Hello world!」を表示する

この手のプログラミングの最初は「Hello world!」を表示するものと相場が決まっていますので、ご多分に漏れず本投稿でも「Hello world!」を表示してみます。最初に、Google Apps Scriptのファイルを作るため、Googleドライブを開き、画面左上の「新規」ボタンを押します。

（※そもそもGoogleアカウントを持っていない人や、持っているけどドライブってなんやねん？みたいな人は本投稿の対象外なので、ご留意ください。）

続いて、表示されたメニューの「その他」を選び、「Google Apps Script」を押します。（※もし「Google Apps Script」が表示されていない時には、リスト一番下の「アプリを追加」を押して追加してください。）

すると、無題のプロジェクトとしてファイルが立ち上がるので、さっそく名前を付けます。（※ここでは「GASチャート」と名付けます。）

ファイル名をつけたら、さっそく関数を書いてみます。（※なお、初期設定では「myFunction」が書いてありますが、使わないので容赦なく削除します。）

とにかく最初は「doGet」関数を書きます。「doGet」は特殊な関数名で、ここではWebアプリとしてのGoogle Apps Scriptファイルにブラウザ経由でアクセスしたときに実行される関数なのです。生成したhtmlをHtmlServiceを介して戻すことで、ブラウザ画面に表示することができるのが「doGet」の特徴です。

HtmlServiceでHTMLオブジェクトを生成するメソッドはいくつかあります。おそらく使われているメソッドTop2は、HTMLファイルを読み込む「createHtmlOutputFromFile」と、文字列のHTMLを直接読み込む「createHtmlOutput」かと思われます。ここでは動的にHTMLを生成することを念頭に置いて「createHtmlOutput」の方を使います。

なおhtml自体の生成は「getHtml」という別の関数に分けて、シンプルに「Hello world!」をreturnするように書きました。

なお実際のコードはこんな感じです。

//アクセス時に実行する関数
function doGet(e) {
  var html = getHtml(e);
  return HtmlService.createHtmlOutput(html);
}

//画面に表示するHTMLを生成して返す関数
function getHtml(e){
  return 'Hello world!';
}

//アクセス時に実行する関数

function doGet(e) {

var html = getHtml(e);

return HtmlService.createHtmlOutput(html);

}

//画面に表示するHTMLを生成して返す関数

function getHtml(e){

return 'Hello world!';

}

さて、実際に「Hello wolrd!」を表示するには、このGoogle Apps Scriptをウェブアプリケーションとして導入する必要があります。そのため、上メニューの「公開」を選び、「ウェブアプリケーションとして導入」を押します。

すると、こんなポップアップが表示されます。開発時はとくにいじらす「完了」ボタンを押してよいですが、他のユーザーと共有したい際は「アプリケーションにアクセスできるユーザー」を初期設定の「自分だけ」から適宜変更する必要がある点は注意しましょう。

「完了」を押すと、「このプロジェクトをウェブアプリケーションとして導入しました。」と表示されました。ポップアップ中段にあるテキストエリアに表示されたURLがWebアプリのURLなので、コピーしてブラウザのアドレスバーに貼り付けてアクセスしてみます。

すると、こんな形で「Hello world!」が表示されることが確認できました。これがWebアプリ開発の最初の一歩です。

なお、細かい仕様について蛇足を書きます。

実はポップアップ下部に青文字で書かれた「最新のコード」をクリックすると別URLの同じような画面が表示されます。違いは微妙で分かりにくいのですが、例えば今回のプロジェクトで言うと、

現在のウェブアプリケーションのURL（本番環境）

https://script.google.com/macros/s/AKfycbwIkZto32HZRzNih2zKdl0HwHfrpKrcXQaA2k53Dk4Q8od2-WU/exec

最新のコードをテスト（開発環境）

https://script.google.com/macros/s/AKfycbwBHGYeWMBQbV3lHFcbyZpDToMSpvCQEYINYOZhT7c/dev

といった違いがあり、赤文字のID部分が異なるだけでなく、URL末尾も「exec」,「dev」という違いがあり、一目で本番環境なのかテスト環境なのかを区別できるようになっています。（~~ただしdevの方をexecに書き換えても普通にアクセスできてしまうので深い意味はなさそう。~~）

なお、実務上の違いは、Google Apps Scriptのファイルを上書き保存するだけで即座に画面反映される開発環境に対して、本番環境は上書き保存だけでは反映されず、都度「ウェブアプリケーションの公開」にてNewバージョンを指定して再度「公開」することで初めて画面反映される仕様という違いがあります。

原則として本運用は本番環境を使うわけですが、手元で小さく使うだけなら開発環境のまま使ってもよいかも、という感じではあります。

2.次にスプレッドシートのデータを表示する

「Hello world!」の次には、スプレッドシートにアクセスしてデータを抽出して、それを画面に表示するということをやってみたいと思います。

さっそくGoogleドライブに戻り、新規のスプレッドシートを作成します。

スプレッドシートを開くと空なので、Pythonデータ活用の投稿で作った「output_unpivotted.csv」をインポートしつつ、シート名を適当に「data」などと書き換えておきます。（※なおインポートは、左上メニューの「ファイル」から実行できます。）

そして、スプレッドシートのアドレスバーに表示されたURLの反転させた部分がスプレッドシートのIDで、これがデータ連携に必要なのでコピーします。

Google Apps Scriptに戻り、スプレッドシートと連携するために「SpreadsheetApp」オブジェクトを使っていい感じにコードを書きます。

細かい話は割愛しますが、何をやっているかというと、シート上のデータを全て配列として取得して、上から順にHTMLのテーブルとして書き直している感じです。

実際のコードはこんな感じです。

//アクセス時に実行する関数
function doGet(e) {
  var html = getHtml(e);
  return HtmlService.createHtmlOutput(html);
}

//画面に表示するHTMLを生成して返す関数
function getHtml(e){
  
  //スプレッドシートからデータを取得
  var spreadsheetId = '1fUtwY6MQ7ZKkzh8OYnnZIv0R3PWiUubzpjzkds4P9r8';
  var sheetName = 'data';
  var sheet = SpreadsheetApp.openById(spreadsheetId).getSheetByName(sheetName);
  var data = sheet.getDataRange().getValues();
  
  //HTMLを生成して返す
  var html = '';
  for (var iRow = 1; iRow < data.length; iRow++){
    html += '<tr><td>' + data[iRow].join('</td><td>') + '</td></tr>';
  }
  return '<table><tbody>' + html + '</tbody></table>';
  
}

//アクセス時に実行する関数

function doGet(e) {

var html = getHtml(e);

return HtmlService.createHtmlOutput(html);

}

//画面に表示するHTMLを生成して返す関数

function getHtml(e){

//スプレッドシートからデータを取得

var spreadsheetId = '1fUtwY6MQ7ZKkzh8OYnnZIv0R3PWiUubzpjzkds4P9r8';

var sheetName = 'data';

var sheet = SpreadsheetApp.openById(spreadsheetId).getSheetByName(sheetName);

var data = sheet.getDataRange().getValues();

//HTMLを生成して返す

var html = '';

for (var iRow = 1; iRow < data.length; iRow++){

html += '<tr><td>' + data[iRow].join('</td><td>') + '</td></tr>';

}

return '<table><tbody>' + html + '</tbody></table>';

}

さて、コードは書けたのですが、スプレッドシートと連携をするために承認処理を行う必要があります。上段の関数選択プルダウンから適当に「getHtml」などと選んで実行しようとすると「承認が必要です。」と出るので、「許可を確認」を選んで処理を進めます。

「アカウントの選択」を促されるので、今回のGoogleアカウントを選びます。

「このアプリは確認されていません」とな。。気にせずポップアップ左下の「詳細」を押します。

気にせずスクロールして、一番下にある「安全ではないページに移動」を押します。（~~このあたり妙にセキュリティ厳しめです。~~）

「Googleアカウントへのアクセスをリクエストしています」と表示されるので、内容を確認しつつ、そのままスクロールします。

スクロール先の右下に「許可」ボタンがあるので、押します。

ようやく連携が完了したので、もう一度Webアプリの方にアクセスすると、スプレッドシートに取り込んでいたデータがそのまま画面に表示されました。これにてスプレッドシートとのデータ連携は成功です。（※いろいろと面倒なので、これ以降は開発環境の方にアクセスします。）

3.Chart.jsでグラフを書く

とうとう本番です。Chart.jsを使ってこのデータをグラフ化するために、もっとコードを書きます。一気にコードが増えます。

実際のコードはこんな感じです。簡単にだけ書くと、取得したデータを連想配列に変えて、ハードコーディングした項目の順序に応じたデータ配列に変換し、Chart.jsに入れ込むパラメータを生成してJSON変換してJavaScriptとHTMLとを一緒に放り込む感じです。

//アクセス時に実行する関数
function doGet(e) {
  var html = getHtml(e);
  return HtmlService.createHtmlOutput(html);
}

//画面に表示するHTMLを生成して返す関数
function getHtml(e){
  
  //スプレッドシートからデータを取得して連想配列へ
  var spreadsheetId = '1fUtwY6MQ7ZKkzh8OYnnZIv0R3PWiUubzpjzkds4P9r8';
  var sheetName = 'data';
  var sheet = SpreadsheetApp.openById(spreadsheetId).getSheetByName(sheetName);
  var recordList = sheet.getDataRange().getValues();
  var recordMap = {};
  for (var i = 1; i < recordList.length; i++){
    recordMap[recordList[i][0] + '_' + recordList[i][1] + '_' + recordList[i][2]] = recordList[i][3];
  }
  
  //データの項目名を順序付きで定義
  var columnList = [
    '0~4歳','5~9歳','10~14歳','15~19歳','20~24歳','25~29歳','30~34歳','35~39歳',
    '40~44歳','45~49歳','50~54歳','55~59歳','60~64歳','65~69歳','70~74歳','75~79歳',
    '80~84歳','85~89歳','90~94歳','95~99歳','100歳以上'
  ];
  var dataSeriesList = [
    'male','female'
  ];
  var colorList = [
    'rgba(0,0,255,0.4)','rgba(255,0,0,0.4)'
  ];
  var yyyymm = e.parameter.yyyymm ? e.parameter.yyyymm : '201905';
  
  //チャートの値を生成
  var data = {};
  data['labels'] = columnList;
  data['datasets'] = [];
  for (var iDataSeries in dataSeriesList){
    var dataList = [];
    for (var iColumn in columnList){
      dataList.push(recordMap[columnList[iColumn] + '_' + dataSeriesList[iDataSeries] + '_' + yyyymm]);
    }
    data['datasets'].push({
      'label':dataSeriesList[iDataSeries],
      'data':dataList,
      'backgroundColor':colorList[iDataSeries],
      'borderColor':colorList[iDataSeries],
      'fill':'false',
    });
  }
  
  //ライブラリに放り込むパラメータとして作成
  var parameters = {};
  parameters['type'] = 'line';
  parameters['data'] = data;
  
  //css部分を作る
  var headerHtml = '<link href="https://cdnjs.cloudflare.com/ajax/libs/Chart.js/2.8.0/Chart.min.css" rel="stylesheet">';
  
  //body部分を作る
  var bodyHtml = '<p>Term=' + yyyymm + '</p>';
  bodyHtml += '<div style="width:1000px; height:800px;"><canvas id="chart"></canvas></div>';
  bodyHtml = '<body>' + bodyHtml + '</body>'
  
  //JavaScript部分を作る
  var footerHtml = '<script src="https://cdnjs.cloudflare.com/ajax/libs/Chart.js/2.8.0/Chart.min.js"></script>';
  footerHtml += '<script>';
  footerHtml += 'var element = document.getElementById("chart");';
  footerHtml += 'window.onload = function() {'
  footerHtml += 'var chart = new Chart(element,' + JSON.stringify(parameters) + ');';
  footerHtml += '};'
  footerHtml += '</script>';
  
  //HTMLを返して終了
  return headerHtml + bodyHtml + footerHtml;
  
}

//アクセス時に実行する関数

function doGet(e) {

var html = getHtml(e);

return HtmlService.createHtmlOutput(html);

}

//画面に表示するHTMLを生成して返す関数

function getHtml(e){

//スプレッドシートからデータを取得して連想配列へ

var spreadsheetId = '1fUtwY6MQ7ZKkzh8OYnnZIv0R3PWiUubzpjzkds4P9r8';

var sheetName = 'data';

var sheet = SpreadsheetApp.openById(spreadsheetId).getSheetByName(sheetName);

var recordList = sheet.getDataRange().getValues();

var recordMap = {};

for (var i = 1; i < recordList.length; i++){

recordMap[recordList[i][0] + '_' + recordList[i][1] + '_' + recordList[i][2]] = recordList[i][3];

}

//データの項目名を順序付きで定義

var columnList = [

'0~4歳','5~9歳','10~14歳','15~19歳','20~24歳','25~29歳','30~34歳','35~39歳',

'40~44歳','45~49歳','50~54歳','55~59歳','60~64歳','65~69歳','70~74歳','75~79歳',

'80~84歳','85~89歳','90~94歳','95~99歳','100歳以上'

];

var dataSeriesList = [

'male','female'

];

var colorList = [

'rgba(0,0,255,0.4)','rgba(255,0,0,0.4)'

];

var yyyymm = e.parameter.yyyymm ? e.parameter.yyyymm : '201905';

//チャートの値を生成

var data = {};

data['labels'] = columnList;

data['datasets'] = [];

for (var iDataSeries in dataSeriesList){

var dataList = [];

for (var iColumn in columnList){

dataList.push(recordMap[columnList[iColumn] + '_' + dataSeriesList[iDataSeries] + '_' + yyyymm]);

}

data['datasets'].push({

'label':dataSeriesList[iDataSeries],

'data':dataList,

'backgroundColor':colorList[iDataSeries],

'borderColor':colorList[iDataSeries],

'fill':'false',

});

}

//ライブラリに放り込むパラメータとして作成

var parameters = {};

parameters['type'] = 'line';

parameters['data'] = data;

//css部分を作る

var headerHtml = '<link href="https://cdnjs.cloudflare.com/ajax/libs/Chart.js/2.8.0/Chart.min.css" rel="stylesheet">';

//body部分を作る

var bodyHtml = '<p>Term=' + yyyymm + '</p>';

bodyHtml += '<div style="width:1000px; height:800px;"><canvas id="chart"></canvas></div>';

bodyHtml = '<body>' + bodyHtml + '</body>'

//JavaScript部分を作る

var footerHtml = '<script src="https://cdnjs.cloudflare.com/ajax/libs/Chart.js/2.8.0/Chart.min.js"></script>';

footerHtml += '<script>';

footerHtml += 'var element = document.getElementById("chart");';

footerHtml += 'window.onload = function() {'

footerHtml += 'var chart = new Chart(element,' + JSON.stringify(parameters) + ');';

footerHtml += '};'

footerHtml += '</script>';

//HTMLを返して終了

return headerHtml + bodyHtml + footerHtml;

}

Webアプリにアクセスすると、こんな感じに表示されます。いい感じです。さらに、

しれっとURLにパラメータを設定しているので、URLの末尾に「?yyyymm=201812」なんてつけると、2018年12月のデータに切り替えることもできます。コード上でのパラメータの受け渡しは「e.parameter.パラメータ名」にて簡単にできるので、Google Apps ScriptのURLによる画面切り分け手段として有効ですね。

4.まとめ

ということで、「スプレッドシートのデータをGoogle Apps Scriptで読み込んで、HtmlService × Chart.jsでグラフを表示してみる」ということで、実例を踏まえつつ、ダイジェストにてお伝えしてみました。

ダッシュボード的な観点で言うと、複数のデータソースをもとにして複数のチャートやテーブルを並べたり、jQueryを使ってプルダウンデザインのフィルター機能を実装したり、パラメータ指定で集計条件をコントロールできるようにしたり、場合によっては「google.script.run.withSuccessHandler」を使ってサーバー側で集計した結果を非同期でクライアント側に戻して描画したり等、一通りのことは可能です。

まあ、Google Apps Script自体は一度の関数実行時間に制約があったりするのであんまり重負荷な処理を強いると返答なく反応が無くなる点に注意は必要ですが、普通に使っている分にはあまり問題にならないというのが実態です。

そういったところも踏まえて、うまく現場発の業務改善プロトタイピングツールとして使ってみる分にはGoogle Apps Scriptは良いものだと思います、という言葉をもって今回は筆をおきたいと思います。

Facebookで共有する共有する Xで共有する共有する

同じカテゴリーの投稿もどうぞ！