グラフを描くぜ！
読んでもらうために書いたものではないので、読みにくいと思います。
ドットインストールで同じようなことやってたから余裕だぜ！
って思ってたらこれだよ。
Google スプレッドシートでのガジェット サポートの終了 - ドキュメント エディタ ヘルプ
20分ぐらい悩みましたが、スプレッドシートからデータを引っ張って配列に組み込んで
既存のグラフ描画のスクリプトに流し込むという方法をとってみることにした。
今どうなっているか。
<script type="text/javascript" src="//www.google.com/jsapi"></script>
<script type="text/javascript">
google.load('visualization', '1.1', {packages: ['corechart', 'controls']});
</script>
<script type="text/javascript">
function drawVisualization() {
var dashboard = new google.visualization.Dashboard(
document.getElementById('dashboard'));
var control = new google.visualization.ControlWrapper({
'controlType': 'ChartRangeFilter',
'containerId': 'control',
'options': {
// Filter by the date axis.
'filterColumnIndex': 0,
'ui': {
'chartType': 'LineChart',
'chartOptions': {
'chartArea': {'width': '90%'},
'hAxis': {'baselineColor': 'none'}
},
// Display a single series that shows the closing value of the stock.
// Thus, this view has two columns: the date (axis) and the stock value (line series).
'chartView': {
'columns': [0, 3]
},
// 1 day in milliseconds = 24 * 60 * 60 * 1000 = 86,400,000
'minRangeSize': 86400000
}
},
// Initial range:
'state': {'range': {'start': new Date(2014, 5, 30), 'end': new Date(2014, 11, 27)}}
});
var chart = new google.visualization.ChartWrapper({
'chartType': 'ComboChart',
'containerId': 'chart',
'options': {
// Use the same chart area width as the control for axis alignment.
'chartArea': {'height': '80%', 'width': '90%'},
'hAxis': {'slantedText': false},
'vAxis': {'viewWindow': {'min': 0, 'max': 'auto'}},
seriesType: "bars",
series : {1: {type: "line"},2: {type: "line"},3: {type: "line"}},
'legend': {'position': 'none'}
},
// Convert the first column from 'date' to 'string'.
'view': {
'columns': [
{
'calc': function(dataTable, rowIndex) {
return dataTable.getFormattedValue(rowIndex, 0);
},
'type': 'string'
}, 1, 2, 3, 4]
}
});
var data = new google.visualization.DataTable();
data.addColumn('date', 'Date');
data.addColumn('number', 'PageView');
data.addColumn('number', '5-day SMA');
data.addColumn('number', '25-day SMA');
data.addColumn('number', '75-day SMA');
data.addRows([
[new Date(2011,10,14),725,906.80,976.92,902.43],
.
.
.
.
.
[new Date(2014,11,26),487,457.20,384.00,392.49],
]);
dashboard.bind(control, chart);
dashboard.draw(data);
}
google.setOnLoadCallback(drawVisualization);
</script>今こんな感じで動いてます。（ただし仕組みはわからん。）
だからdata.addRowsの内部を何とかできればどうにかなるんじゃなかろうか、ということです。
スプレッドシートの内容を読み込む -
これは後々も重要になってくるパート。
Simple example of retrieving JSON feeds from Spreadsheets Data API - Google Data APIs — Google Developers
GoogleスプレッドシートのデータをJSONで取得してみた | アライドアーキテクツのクリエイターブログ
この辺りを使う。
http://spreadsheets.google.com/feeds/cells[key]/[worksheetId]/public/values?alt=json
より
http://spreadsheets.google.com/feeds/list/[key]/[worksheetId]/public/values?alt=json
の方がサイズが小さいです。必要なところも見れるので下で十分です。
てことでテストデータがこれ。
https://spreadsheets.google.com/feeds/list/1kyJS7B6JAJzC_tXR2chDWvX8R7o1--C_-96IDFCWqu4/od6/public/values?alt=json
75日移動平均が算出できる75行目以降のデータを用います。
無効データ（apiが出力してしまう2月30日とか9月31日とか）は手動で消しています。これの対応は後で考える。
これをjQueryで読みます。
jQuery.getJSON( url, data, callback ) - jQuery 日本語リファレンス
のですが、その前に、JSONの構造を確認しておく。
function myFunction2() {
var response = UrlFetchApp.fetch("https://spreadsheets.google.com/feeds/list/1kyJS7B6JAJzC_tXR2chDWvX8R7o1--C_-96IDFCWqu4/od6/public/values?alt=json");
var result = JSON.parse(response.getContentText());
Logger.log(うんこ);
}例によってデバッグモードで確認。
クッソ重い。ちょっとデータがでかすぎた。
何とかして解読。
function myFunction2() {
var response = UrlFetchApp.fetch("https://spreadsheets.google.com/feeds/list/1kyJS7B6JAJzC_tXR2chDWvX8R7o1--C_-96IDFCWqu4/od6/public/values?alt=json");
var result = JSON.parse(response.getContentText());
var result1 = Object.keys(result.feed.entry).length;
var result2 = result.feed.entry[73].gsx$_cn6ca.$t;
var result3 = result.feed.entry[73].gsx$_cokwr.$t;
var result4 = result.feed.entry[73].gsx$_cpzh4.$t;
var result5 = result.feed.entry[73].gsx$_cre1l.$t;
var result6 = result.feed.entry[73].gsx$_chk2m.$t;
Logger.log(うんこ);
}result1はデータの個数
result2から6まではresult.feed.entryという配列の73番目（これが75行目に対応している。何故かはわからん）
のA列からE列を表している。
ただ、A列を.gsx$_cn6caとしているのが汎用性があるのかないのかが謎である。
とりあえず進む。
htmlを作る。
<html>
<head>
<meta charset="UTF-8">
<title>Document</title>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.1/jquery.min.js"></script>
<script>
$(function(){
$.getJSON("https://spreadsheets.google.com/feeds/list/1kyJS7B6JAJzC_tXR2chDWvX8R7o1--C_-96IDFCWqu4/od6/public/values?alt=json", function(json){
console.log(json.feed.entry[73].gsx$_cn6ca.$t);
});
});
</script>
</head>
<body>
</body>
</html>動いた。
これが出来れば前日比を出すのは簡単である。が、今はそこではない。
その前に、配列を外部で定義した場合にグラフは描けるのか
多分いけるけど、やり方を確認しておく。
data.addRows([ [new Date(2011,10,14),725,906.80,976.92,902.43], . . . . . [new Date(2014,11,26),487,457.20,384.00,392.49], ]);
これの中の配列を外から持ってくることにする。
var graphArr = [ [new Date(2011,10,14),725,906.80,976.92,902.43] . . . [new Date(2014,11,26),487,457.20,384.00,392.49] ]; data.addRows(graphArr);
いけるやん！（そりゃそうだ）
ってことはjson読み込んでgraphArrにしてしまえばいいだけである。
jQueryでスプレッドシートを読む
<script>
$(function(){
$.getJSON("https://spreadsheets.google.com/feeds/list/1kyJS7B6JAJzC_tXR2chDWvX8R7o1--C_-96IDFCWqu4/od6/public/values?alt=json", function(json){
console.log(json.feed.entry[73].gsx$_cn6ca.$t);
});
});
</script>これをいじっていく。
・A列からE列まで取得
var result2 = result.feed.entry[73].gsx$_cn6ca.$t; var result3 = result.feed.entry[73].gsx$_cokwr.$t; var result4 = result.feed.entry[73].gsx$_cpzh4.$t; var result5 = result.feed.entry[73].gsx$_cre1l.$t; var result6 = result.feed.entry[73].gsx$_chk2m.$t;
これの要領でやればいいんだろ？。
・ループさせて最後まで取得
var result1 = Object.keys(result.feed.entry).length;
とfor文使えばいいんだろ？
・配列にする
そういう風に定義すればいいんだろ？
<script>
$(function(){
$.getJSON("https://spreadsheets.google.com/feeds/list/1kyJS7B6JAJzC_tXR2chDWvX8R7o1--C_-96IDFCWqu4/od6/public/values?alt=json", function(json){
for(var i=73; i<=Object.keys(json.feed.entry).length; i++) {
var result1 = json.feed.entry[i].gsx$_cn6ca.$t;
var result2 = json.feed.entry[i].gsx$_cokwr.$t;
var result3 = json.feed.entry[i].gsx$_cpzh4.$t;
var result4 = json.feed.entry[i].gsx$_cre1l.$t;
var result5 = json.feed.entry[i].gsx$_chk2m.$t;
var graphArr = ['new Date(' + result1 + ')',result2,result3,result4,result5]
console.log(graphArr)
}
});
});
</script>書きながらミスってるのはわかってた。
・日付は分解しなければいけない
文字列を切り出す [Java Script - (substring) - 文字列 - Tips]
var result1_year = result1.substring(0, 4);
こういう感じ。
・日付のうち、月は-1しなければならない
文字列を数値に変換する [Java Script - (Number、parseInt、parseFloat) - 文字列 - Tips]
文字列になっているので、数値変換して1引く
var result1_month = result1.substring(5, 7); //切り出して var result1_month = Number(result1_month) - 1; //数値変換して1引く
・iごとのgraphArrをもう1つ配列作ってそこの末尾に追加していく必要がある
Javascript/配列操作 - 俺の基地
var graphArr = [];
//空の配列を作る
var jsonArr = ['new Date(' + result1_year,result1_month,result1_day + ')',result2,result3,result4,result5]
//1回の取得データを配列にする
graphArr.push(jsonArr);
//graphArrの末尾にjsonArrを1回ごとに追加するということで、これをテストページに放り込む。
<script type="text/javascript" src="//www.google.com/jsapi"></script>
<script type="text/javascript">
google.load('visualization', '1.1', {packages: ['corechart', 'controls']});
</script>
<script type="text/javascript">
function drawVisualization() {
//ここから追加
$.getJSON("https://spreadsheets.google.com/feeds/list/1kyJS7B6JAJzC_tXR2chDWvX8R7o1--C_-96IDFCWqu4/od6/public/values?alt=json", function(json){
var graphArr = [];
for(var i=73; i<=Object.keys(json.feed.entry).length; i++) {
//console.log(Object.keys(json.feed.entry).length)
var result1 = json.feed.entry[i].gsx$_cn6ca.$t;
var result1_year = result1.substring(0, 4);
var result1_month = result1.substring(5, 7);
//月は数値変換して1を引く
var result1_month = Number(result1_month) - 1;
//日は切り出すだけ
var result1_day = result1.substring(9, 11);
//console.log(result1_year);
//console.log(result1_month);
//console.log(result1_day);
var result2 = json.feed.entry[i].gsx$_cokwr.$t;
var result3 = json.feed.entry[i].gsx$_cpzh4.$t;
var result4 = json.feed.entry[i].gsx$_cre1l.$t;
var result5 = json.feed.entry[i].gsx$_chk2m.$t;
var jsonArr = ['new Date(' + result1_year,result1_month,result1_day + ')',result2,result3,result4,result5]
graphArr.push(jsonArr);
console.log(graphArr)
}
});
//ここまで追加
var dashboard = new google.visualization.Dashboard(
document.getElementById('dashboard'));
var control = new google.visualization.ControlWrapper({
'controlType': 'ChartRangeFilter',
'containerId': 'control',
'options': {
// Filter by the date axis.
'filterColumnIndex': 0,
'ui': {
'chartType': 'LineChart',
'chartOptions': {
'chartArea': {'width': '90%'},
'hAxis': {'baselineColor': 'none'}
},
// Display a single series that shows the closing value of the stock.
// Thus, this view has two columns: the date (axis) and the stock value (line series).
'chartView': {
'columns': [0, 3]
},
// 1 day in milliseconds = 24 * 60 * 60 * 1000 = 86,400,000
'minRangeSize': 86400000
}
},
// Initial range:
'state': {'range': {'start': new Date(2014, 5, 30), 'end': new Date(2014, 11, 27)}}
});
var chart = new google.visualization.ChartWrapper({
'chartType': 'ComboChart',
'containerId': 'chart',
'options': {
// Use the same chart area width as the control for axis alignment.
'chartArea': {'height': '80%', 'width': '90%'},
'hAxis': {'slantedText': false},
'vAxis': {'viewWindow': {'min': 0, 'max': 'auto'}},
seriesType: "bars",
series : {1: {type: "line"},2: {type: "line"},3: {type: "line"}},
'legend': {'position': 'none'}
},
// Convert the first column from 'date' to 'string'.
'view': {
'columns': [
{
'calc': function(dataTable, rowIndex) {
return dataTable.getFormattedValue(rowIndex, 0);
},
'type': 'string'
}, 1, 2, 3, 4]
}
});
var data = new google.visualization.DataTable();
data.addColumn('date', 'Date');
data.addColumn('number', 'PageView');
data.addColumn('number', '5-day SMA');
data.addColumn('number', '25-day SMA');
data.addColumn('number', '75-day SMA');
data.addRows(graphArr);
dashboard.bind(control, chart);
dashboard.draw(data);
}
google.setOnLoadCallback(drawVisualization);
</script>ReferenceError: graphArr is not definedになります。
data.addRows(graphArr);が$.getJSONより先に実行されてしまうのが悪いっぽい。
jQueryで、順番に実行が出来る .when() から .done() が便利だったのでメモ | あらかぜ手帖
JavaScript - jQuery.Deferredを使って楽しい非同期生活を送る方法 - Qiita
$.when(
//先に終わらせたい処理
).done(function() {
//後に実行したい処理
});こうするらしい。ってことは
$.when(
$.getJSON以下略
).done(function() {
var data = new google.visualization.DataTable();以下略
});いやだめだ。
他に原因がある。
console.log(graphArr[2]);
を見てみると、
Array [ "new Date(2011", "11", "08)", "1044", "801.6", "639.08", "803.96" ]
・数値が文字列になってる。
Numberで解決。
・"new Date(2011", "11", "08)"が単なる文字列になってる
文字列を入れればいいと思ってたけど、Date オブジェクトを入れなきゃダメでした。
以上2つより、
var jsonArr = [new Date(result1_year,result1_month,Number(result1_day)),Number(result2),Number(result3),Number(result4),Number(result5)]
よっしゃこれで動・・・
ReferenceError: graphArr is not defined
かねえ！！when done 効いてねえ！！！
$.when(
$.getJSON以下略
).done(function() {
var data = new google.visualization.DataTable();以下略
});多分これが違う。やっぱりこっちじゃねーか。
done(function()に渡さなきゃダメなんだと思う。
whenの中はgetJSONだけにしてそれをdoneに渡して処理すればいい。
・日付がおかしい。
桁数が変わってくるのが不味い。
2014/10/29も2014/8/3も同じようにsubstringしてるからずれている。
var result1 = json.feed.entry[i].gsx$_cn6ca.$t;
var result1 = result1.split('/');
// スラッシュで区切って配列にする
var result1_year = Number(result1[0]);
//配列の先頭を数値変換して年にする
var result1_month = Number(result1[1])-1;
//配列の2つ目を数値変換して1を引いて月にする
var result1_day = Number(result1[2]);
//配列の3つ目を数値変換して日にするこれらを修正。
できた。
できた！！！！！！！！！！！！！！
// Initial range:
'state': {'range': {'start': new Date(2014, 5, 30), 'end': new Date(2014, 11, 27)}}これだけ動かせるようにしておく。
期間の末尾が今日で、期間は180日になるようにする。
が、もとよりそれほど厳密ではなかったのでどうでも良い。
プログラミングお役立ちメモ
//
var today = new Date();
var today_year = today.getFullYear();
var today_month = today.getMonth();
var today_day = today.getDate();
var initial_date = new Date( today.getTime() - 180*24*60*60*1000 );
var initial_year = initial_date.getFullYear();
var initial_month = initial_date.getMonth();
var initial_day = initial_date.getDate();
//
// Initial range:
'state': {'range': {'start': new Date(initial_year, initial_month, initial_day), 'end': new Date(today_year, today_month, today_day)}}よっしゃ！
<script type="text/javascript">
function drawVisualization() {
$.when(
$.getJSON("https://spreadsheets.google.com/feeds/list/1kyJS7B6JAJzC_tXR2chDWvX8R7o1--C_-96IDFCWqu4/od6/public/values?alt=json")
)
.done(
function(json) {
var graphArr = [];
for(var i=73; i<=Object.keys(json.feed.entry).length-1; i++) {
//console.log(Object.keys(json.feed.entry).length)-1にしないと何故かオーバーする
var result1 = json.feed.entry[i].gsx$_cn6ca.$t;
var result1 = result1.split('/');
// スラッシュで区切って配列にする
var result1_year = Number(result1[0]);
//配列の先頭を数値変換して年にする
var result1_month = Number(result1[1])-1;
//配列の2つ目を数値変換して1を引いて月にする
var result1_day = Number(result1[2]);
//配列の3つ目を数値変換して日にする
var result2 = json.feed.entry[i].gsx$_cokwr.$t;
var result3 = json.feed.entry[i].gsx$_cpzh4.$t;
var result4 = json.feed.entry[i].gsx$_cre1l.$t;
var result5 = json.feed.entry[i].gsx$_chk2m.$t;
var jsonArr = [new Date(result1_year,result1_month,Number(result1_day)),Number(result2),Number(result3),Number(result4),Number(result5)]
graphArr.push(jsonArr);
}
var dashboard = new google.visualization.Dashboard(
document.getElementById('dashboard'));
//日付け取得関連
var today = new Date();
var today_year = today.getFullYear();
var today_month = today.getMonth();
var today_day = today.getDay();
var initial_date = new Date( today.getTime() - 180*24*60*60*1000 );
var initial_year = initial_date.getFullYear();
var initial_month = initial_date.getMonth();
var initial_day = initial_date.getDay();
//
var control = new google.visualization.ControlWrapper({
'controlType': 'ChartRangeFilter',
'containerId': 'control',
'options': {
// Filter by the date axis.
'filterColumnIndex': 0,
'ui': {
'chartType': 'LineChart',
'chartOptions': {
'chartArea': {'width': '90%'},
'hAxis': {'baselineColor': 'none'}
},
// Display a single series that shows the closing value of the stock.
// Thus, this view has two columns: the date (axis) and the stock value (line series).
'chartView': {
'columns': [0, 3]
},
// 1 day in milliseconds = 24 * 60 * 60 * 1000 = 86,400,000
'minRangeSize': 86400000
}
},
// Initial range:
'state': {'range': {'start': new Date(initial_year, initial_month, initial_day), 'end': new Date(today_year, today_month, today_day)}}
});
var chart = new google.visualization.ChartWrapper({
'chartType': 'ComboChart',
'containerId': 'chart',
'options': {
// Use the same chart area width as the control for axis alignment.
'chartArea': {'height': '80%', 'width': '90%'},
'hAxis': {'slantedText': false},
'vAxis': {'viewWindow': {'min': 0, 'max': 'auto'}},
seriesType: "bars",
series : {1: {type: "line"},2: {type: "line"},3: {type: "line"}},
'legend': {'position': 'none'}
},
// Convert the first column from 'date' to 'string'.
'view': {
'columns': [
{
'calc': function(dataTable, rowIndex) {
return dataTable.getFormattedValue(rowIndex, 0);
},
'type': 'string'
}, 1, 2, 3, 4]
}
});
var data = new google.visualization.DataTable();
data.addColumn('date', 'Date');
data.addColumn('number', 'PageView');
data.addColumn('number', '5-day SMA');
data.addColumn('number', '25-day SMA');
data.addColumn('number', '75-day SMA');
data.addRows(graphArr);
dashboard.bind(control, chart);
dashboard.draw(data);
});
}
google.setOnLoadCallback(drawVisualization);
</script> を使ってサンプルページが動いています。
今回はここまで。
