Google Apps Script 入門到進階(7) --- 使用Google Visualization API (google.visualization.Query) 畫台指換倉成本

前面幾篇文章透過Google Visualization API (google.visualization.DataTable)與Google Apps Script 繪圖，資料讀取以Google Sheet ID 取得，這樣方式多用在可限制權限讀取的個人Google Sheet，如果想要取得他人分享的Google Sheet就需要使用Google Visualization API (google.visualization.Query)來完成。
google.visualization.Query方法類似Excel VBA的Querytable方法，而且還能取得特定欄資料來使用，可說相當方便。

在Google雲端硬碟中建立一個專案，新增Google Apps Script檔案Code.gs，輸入以下程式碼。

	function doGet() {
	return HtmlService.createTemplateFromFile("index.html")
	.evaluate()
	.setSandboxMode(HtmlService.SandboxMode.IFRAME);
	}
	function include(filename) {
	return HtmlService.createHtmlOutputFromFile(filename)
	.setSandboxMode(HtmlService.SandboxMode.IFRAME)
	.getContent();
	}

view raw Google Visualization API & Google Apps Script & Google Sheet (6) hosted with ❤ by GitHub

新增HTML檔案index.html，輸入以下程式碼。

	<?!= include('getGraphicData'); ?>
	<div id="method" align="center"></div><br/>
	<div id="chart"></div>
	<div id="source1" align="center"></div><br/>
	<div id="auth" align="center"></div><br/>

view raw Google Visualization API & Google Apps Script & Google Sheet (7) hosted with ❤ by GitHub

新增HTML檔案getGraphicData.html，輸入以下程式碼。

	<script src="https://ajax.googleapis.com/ajax/libs/jquery/1/jquery.min.js"></script>
	<script type="text/javascript" src="https://www.google.com/jsapi"></script>
	<script type="text/javascript">
	google.load("visualization", "1", {packages:["corechart"]});
	function drawVisualization() {
	// To see the data that this visualization uses, browse to
	var url = "<Put your own Google Sheet sharing URL here, with editing enabled>";
	$("#method").html("以下使用google.visualization.Query方法來處理數據");
	var query = new google.visualization.Query(url);
	query.setQuery('select A, E, H, Z');
	// Send the query with a callback function.
	query.send(handleQueryTXFResponse);
	$("#auth").html("免責聲明：以上圖片內容與數據皆為教學使用，請勿作為投資之依據，所有內容將不負任何投資盈虧之責!!!");
	}
	function handleQueryTXFResponse(response)
	{
	//create data table object
	var dataTable = new google.visualization.DataTable();
	//define columns
	dataTable.addColumn('string', "日期"); //日期
	dataTable.addColumn('number', "收盤價"); //收盤價
	dataTable.addColumn({type: 'string', role: 'annotation'});
	dataTable.addColumn('number', "台指換倉成本"); //台指換倉成本
	dataTable.addColumn({type: 'string', role: 'annotation'});
	dataTable.addColumn('number', "未平倉量"); //未平倉量
	var trade, closeprice, averageprice, OI;
	var data = response.getDataTable();
	var rows = data.getNumberOfRows();
	for (var i = 0; i < rows; i++)
	{
	trade = data.getValue(i, 0);
	closeprice = parseFloat(data.getValue(i, 1));
	averageprice = parseFloat(data.getValue(i, 3));
	OI = parseInt(data.getValue(i, 2));
	if(i != rows - 1)
	dataTable.addRow([trade, closeprice, null, averageprice, null, OI]);
	else if(i == rows - 1)
	dataTable.addRow([trade, closeprice, String(closeprice), averageprice, String(averageprice), OI]);
	}
	var options = {
	width: 1500,
	height: 480,
	title: '台指未平倉量與成本',
	hAxis: {
	title: "日期",
	format: 'y/d/m'
	},
	vAxes: {
	0:{ title: '台指指數'},
	1:{ title: '台指未平倉量', maxValue: 160000}
	},
	series:{
	0:{type: "line", targetAxisIndex:0},
	1:{type: "line", targetAxisIndex:0},
	2:{type: "bars", targetAxisIndex:1}
	},
	crosshair: { trigger: 'both' }, // Display crosshairs.
	tooltip: { trigger: 'selection' } // Display tooltips on selection.
	};
	var chart_div = document.getElementById('chart');
	var chart2 = new google.visualization.ComboChart(chart_div);
	google.visualization.events.addListener(chart2, 'ready', function () {
	chart2.setSelection([{row:99, column:1}]); // Select one of the points.
	png = '<a href="' + chart2.getImageURI() + '">Printable version</a>';
	console.log(png);
	});
	chart2.draw(dataTable, options);
	var content = '資料來源：<a href=\"http://www.taifex.com.tw/\" target=\"_blank\" title=\"台灣期貨交易所\">台灣期貨交易所</a>';
	$("#source1").html(content);
	}
	google.setOnLoadCallback(drawVisualization);
	</script>

view raw Google Visualization API & Google Apps Script & Google Sheet (8) hosted with ❤ by GitHub

執行結果：

參考資料：

• Query form for Google spreadsheets