GASでスプレッドシートにレーダーチャートを作成

1. 今回やりたいこと・構成図と流れ

Googleフォームで集計したアンケートを点数化し、動的に結果を取得してレーダーチャートを作成します。

①Google formで集計したアンケートをGoogle sheetに保存
②Google Sheetの結果をGASで解析してデータ取得
③データを元に、HTMLでレーダーチャートの描画
④描画したチャートを画像化
⑤画像をGoogle sheetに挿入
⑥画像をアンケートに答えたユーザーへメール送信

本記事では③〜⑥にかけて説明します。

2. Debug方法

今回はGASでgsファイルとhtmlファイルの両方を使用します。挙動するタイミングとファイルによって、出力方法が異なります。

• 「実行ボタン」を押してからhtmlファイルが呼ばれるまではconsole.log()で「実行ログ」に出力されます

• htmlファイルのスクリプトタグ内ではconsole.log()で、GoogleChromeだとディベロッパーツールのコンソールに出力されます

• htmlファイル呼び出し後のgsファイルではLogger.log()で、GASの実行数の項目で確認可能です。ここでは、正しい挙動ができていない場合に、関数が実行されているかどうかの確認もできます。

  

3. レーダーチャートの描画

Google Chartsのリファレンスに記載のあるVegaChartを使って、レーダーチャートを描画します。下記URLにアクセスし、コードをコピーしてください。

GASに戻り任意のhtmlファイルを作成します。本記事ではcreate_chart.htmlとしています。
そこに先ほどコピーしたコードを貼り付けてください。

次は.gsファイルを作成し、実際にレーダーチャートを描画していきます。

ウェブサイトに表示

1. 下記コードを任意の.gsファイルへ記載します

   function doGet(){
       return HtmlService.createHtmlOutputFromFile('create_chart');
   }
   

   
   
2. ウェブアプリとしてデプロイし、取得したURLを開けばレーダーチャートが描画されます
3. doGetメソッドは、このあと使用しないので、消去もしくはコメントアウトしておいてください

（↓doGetメソッドについて丁寧にまとめてくれている記事を共有します）

Google Sheetのモーダルダイアログに表示

これは、上記のウェブサイトに表示する方法とは異なり、Google Sheet上にレーダーチャートを表示させることができます。
さらにデプロイをする必要がなく、GASで「実行ボタン」を押下した後に、対象のGoogle Sheetを開くことで表示が可能です。
つまり、動的に値を取得してレーダーチャートを表示させるのに適した方法だと言えます。

注意
「実行ボタン」を押下してから2,3秒以内にGoogle Sheetを開かないと、モーダルダイアログは表示されません。

1. 下記コードを任意の.gsファイルへ記載します。本記事ではmain.gsとしています。

   main.gs

   	function createChartInSpreadsheet() {
   		var html = HtmlService.createHtmlOutputFromFile('create_chart')
   			.setWidth(400)
   			.setHeight(1000);
   	
   		SpreadsheetApp.getUi().showModalDialog(html, 'ダイアログの題名');
   	}
   

   
   
2. 「実行ボタン」を押し、対象のGoogle Sheetを開くことで、レーダーチャートが描画されます。今回は幅400px, 高さ1000pxで設定しています。

（↓ダイアログを表示させるメソッドについて丁寧にまとめてくれている記事を共有します）

4. 動的なデータでチャートを作成

これまではリファレンスのコードをコピー＆ペーストし、固定のデータを使用していました。ここでは、動的にデータを取得し、それに応じたレーダーチャートが表示されるようにします。

.gsファイルからデータを取得

現状はhtmlファイル内でデータを宣言していますが、アンケート結果を点数化したものを.gsファイルから取得したいです。

create_chart.htmlを書き換えます。定数で記載されているデータを消去し、下記コードへ変更します。

create_chart.html

	function loadCharts() {
	  google.script.run.withSuccessHandler(function(result) {
	    lasagna = result.lasagna;
	    pork = result.pork;
	    melon = result.melon		
	    addChart(lasagna[0][2], lasagna, "#B82E2E", 1);
	    addChart(pork[0][2], pork, "#6633CC", 10);
	    addChart(melon[0][2], melon, "#109618", 20);
	  }).getData();
	}

コード解説

• google.script.run ：.gsファイルにある関数の呼び出し
• .getData() ：.gsファイルにある関数。後述します。
• .withSuccessHandler(function(result)) :getData()関数の実行が成功したら()内が挙動します。result引数の中にgetData()関数の戻り値が格納されています。

次はmain.gsに下記コードを追加します。
この状態で実行しても、Google sheetのモーダルダイアログにレーダーチャートが正しく表示されているはずです。

main.gs

    function getData() {	
		const lasagna = [
			["Protein",0.1308,"Lasagna, cheese, frozen, prepared"],
			["Carbohydrates",0.05032727272727273,"Lasagna, cheese, frozen, prepared"],
			["Vitamin C",0.228,"Lasagna, cheese, frozen, prepared"],
			["Calcium",0.08538461538461538,"Lasagna, cheese, frozen, prepared"],
			["Zinc",0.11375,"Lasagna, cheese, frozen, prepared"],
			["Sodium",0.18933333333333333,"Lasagna, cheese, frozen, prepared"]
		  ];
		
		  const pork = [
			["Protein",0.2638,"Pulled pork in barbecue sauce"],
			["Carbohydrates",0.06814545454545454,"Pulled pork in barbecue sauce"],
			["Vitamin C",0.002666666666666667,"Pulled pork in barbecue sauce"],
			["Calcium",0.033846153846153845,"Pulled pork in barbecue sauce"],
			["Zinc",0.23125,"Pulled pork in barbecue sauce"],
			["Sodium",0.444,"Pulled pork in barbecue sauce"]
		  ];
	
		  const melon = [
			["Protein",0.0168,"Melons, cantaloupe, raw"],
			["Carbohydrates",0.029672727272727274,"Melons, cantaloupe, raw"],
			["Vitamin C",0.4893333333333334,"Melons, cantaloupe, raw"],
			["Calcium",0.006923076923076923,"Melons, cantaloupe, raw"],
			["Zinc",0.0225,"Melons, cantaloupe, raw"],
			["Sodium",0.010666666666666666,"Melons, cantaloupe, raw"]
		  ];
	
		return { lasagna, pork, melon };
	}

動的にデータを取得する

そのために、GASのプロパティサービスを利用します。

これにより

1つのドキュメントをスコープとする Key-Value ペアで単純な データを保存

することができます。

つまり、htmlファイルを呼び出す前に生成したデータを、保持しておくことが可能です。
またリファレンスに記載があるように、データ形式は文字列となりますので、格納および取得の際には注意が必要です。

main.gs

	function main(){
        // データの形式は必要に応じて変更してください。
		let data = calData();
		PropertiesService.getScriptProperties().setProperty('result', JSON.stringify(data));
		createChartInSpreadsheet();
	}
	
	function createChartInSpreadsheet() {
		var html = HtmlService.createHtmlOutputFromFile('create_chart')
			.setWidth(400)
			.setHeight(1000);
	
		SpreadsheetApp.getUi().showModalDialog(html, 'ダイアログの題名');
	}
	
	function getData(){
		let data = PropertiesService.getScriptProperties().getProperty('result');
		
		// プロパティサービスの使用上dataは文字列です。一度ログに出力してから、データ管理を行うことを推奨します。
		Logger.log(data);
		
		// 以下省略
	}

余談：　データ取得の失敗例

手取り早く先に進みたい方は読み飛ばしていただいて結構です。続きはこちらから移動できます。

NG: Google Sheetから取得

当初は、.gsファイルでデータを処理した際に、Google Sheetへデータを格納し、.htmlファイルを呼び出した後に、Google Sheetにあるデータを取得しようとしていました。

今回のプログラムは.gsファイルと.htmlファイルを行き来します。
その中で、.htmlファイルを呼び出した後にGoogle Sheetから値を取得しようとすると、挙動速度の影響か、データを取得する前にレーダーチャートを生成しようとしてしまっていました。

そのため、getData()関数においてLogger.logでdata変数を参照しても空の状態であり、モーダルダイアログにもチャートは描画されませんでした。

NG: グローバル変数の利用

.gsファイルでdata変数をグローバル変数として管理する手法を考えました。しかし、html呼び出し前に生成したデータを、html呼び出し後に参照しても、空のままでした。下記サイトにて学びましたが、GASにはjavascriptのようなグローバル変数の使い方ができないとのことです。

では本題に戻ります。

5. レーダーチャートの設定

Vega Chartのoptionsの設定をします。

• レーダーチャートで扱う数値の幅を設定
  • scalesの2つ目の要素内にあるdomainの数値を変更することで設定が可能です

  	"scales": [
  	{
  	  "name": "angular",
  	  "type": "point",
  	  "range": {"signal": "[-PI, PI]"},
  	  "padding": 0.5,
  	  "domain": {"data": "table", "field": "key"}
  	},
  	{
  	  "name": "radial",
  	  "type": "linear",
  	  "range": {"signal": "[0, radius]"},
  	  "zero": true,
  	  "nice": false,
  	  "domain": [0,0.5], // この箇所の右側の数値を変更
  	}
    ],
  

• レーダーチャートを45度回転させる設定
  • 上記にあるscalesのrangeを変更します
  • 変更前： "range": {"signal": "[-PI, PI]"},
  • 変更後： "range": {"signal": "[-PI/4, PI*7/4]"},

• 表記される数値の設定
  • marksの2つ目の要素にあるenterのtextを変更することで設定可能です
  • 本記事では%とという単位と小数点表記をやめたかったため下記のように変更しました
    • 変更前：　"text": {"signal": "format(datum.datum.value,'.1%')"}
    • 変更後： "text": {"signal": "format(datum.datum.value, '.0f')"}

      {
  		"type": "text",
  		"name": "value-text",
  		"from": {"data": "category-line"},
  		"encode": {
  		  "enter": {
  			"x": {"signal": "datum.x + 14 * cos(scale('angular', datum.datum.key))"},
  			"y": {"signal": "datum.y + 14 * sin(scale('angular', datum.datum.key))"},
  			"text": {"signal": "format(datum.datum.value,'.1%')"}, // ここで設定可能です
  			"opacity": {"signal": "datum.datum.value > 0.01 ? 1 : 0"},
  			"align": {"value": "center"},
  			"baseline": {"value": "middle"},
  			"fontWeight": {"value": "bold"},
  			"fill": {"value": color},
  		  }
  		}
  	}
  

• レーダーチャートと数値を近づける設定
  • 上記encodeのenterにあるx, yを変更します
  • 変更前： "x": {"signal": "datum.x + 14 * cos(scale('angular', datum.datum.key))"
  • 変更後： "x": {"signal": "datum.x + 7 * cos(scale('angular', datum.datum.key))"
  • yも同様に変更してください

6. レーダーチャートの画像化

いままではレーダーチャートをhtml上に描画していましたが、そこから画像へと変換し、Google Sheetへの挿入やメールへの添付を行っていきます。

Google ChartsのリファレンスにgetImageURI()メソッドが用意されているとあります。

しかし実際のページを確認すると、CoreChartとGeoChartのみ対応しているとのことです。

VegaChartはgetImageURI()メソッドを使用して画像の取得ができないため、下記方法で画像化します。

1. loadCharts()およびaddChart()関数の変更

create_chart.html

	function loadCharts() {
		google.script.run.withSuccessHandler(function(result) {
		  test_A = result.test_A;
		  test_B = result.test_B;
		  test_C = result.test_C;
		
		  addChart(test_A[0][2], test_A, "#B82E2E");
		  addChart(test_B[0][2], test_B, "#6633CC");
		  let chart = addChart(test_C[0][2], test_C, "#109618");
		  google.visualization.events.addListener(chart, 'ready', captureAndSendImage);
		}).getData();
	}

    function addChart(title, data, color){
        // 省略
        return charts; // 追記
    }

addChart()関数で作成したchart変数の情報をloadCharts()関数へ戻し、canvasを使って画像化するcaptureAndSendImage()関数を呼び出しています。
その際、google.visualization.events.addListener(chart, 'ready', captureAndSendImage);という呼び出し方法をとっています。

チャートの描画は非同期で行われるため、描画の完了を待たずに次の処理に進むと、期待通りに動作しないことがあります。
イベントリスナーおよびreadyイベントを使用することで、描画完了後に次の処理を確実に実行できます。

2. 画像化する関数を追記

htmlの要素を画像化できるhtml2canvasというライブラリを使用します。

下記では、画像をURLへ変換、整理しinsertChartToSpreadsheet関数へ渡しています。

create_chart.html

<script src="https://cdnjs.cloudflare.com/ajax/libs/html2canvas/1.4.1/html2canvas.min.js"></script>
<script>
 	function captureAndSendImage() {
		const chartDiv = document.getElementById('chart-area');
		
		const scale = 1.0;
		html2canvas(chartDiv, {
		  scale: scale,
		  useCORS: true,
		}).then(canvas => {
		  const base64Image = canvas.toDataURL().split(',')[1];
		  google.script.run
			.withSuccessHandler(() => google.script.host.close())
			.insertChartToSpreadsheet(base64Image);
		});
	}
</script>

7.　Google Sheetへの挿入とメール送信

画像の作成ができたので、Google Sheetへの挿入およびメールの送信を行います。下記を.gsファイルへ追記します。

main.gs

	function insertChartToSpreadsheet(base64Image) {
		var blob = Utilities.newBlob(Utilities.base64Decode(base64Image), 'image/png', 'chart.png');
		
		var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
		sheet.insertImage(blob, 6, 1);
		sendMail_withImage(blob)
	  }
	  
	function sendMail_withImage(blob) {
		let sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
		let address = sheet.getRange(2, 4).getValue();	// Google Sheetの任意の場所から取得
		let title = "タイトル";
		let text = "本文。<img src='cid:inlineImg'>";
		let options = {
		  "htmlBody":text,
		  "inlineImages":
			{
			  inlineImg:blob
			}
		};
		 MailApp.sendEmail(address, title, text, options);
	}

これで「実行ボタン」を押すと、レーダーチャートを画像化し、Google Sheetへ挿入されるはずです。

8. おわりに

今回、記事にまとめたのも、レーダーチャートの使用に関して有用な記事があまりなく、開発にかなりの時間がかかりました。試行した例を下記に示します。おそらくこれらは実装ができません。(できる方いたら教えてほしいです)

• Vegachartを.gsファイルでの使用を試みたが困難（外部ライブラリの使用はhtmlファイルでないと不可能）
• トリガーを起点に挙動する場合、htmlファイルで実行した値を取得するなど動的な挙動が困難

最終的に「実行ボタン」を押したら、ループ文を使用して、複数のデータ処理ができるようになったのでよかったです。
GASのプロパティサービスに大助かりしました。

読者の方のお力になれれば幸いです。ありがとうございました。