最終段階
読んでもらうために書いたものではないので、読みにくいと思います。
書き込み位置の修正
F列から横に書いていくのは効率が悪いので
E2に名前
E3に最新の値
E4に前週比
E5に前週比(%表示)
E6にtwpro
E7にニコニコ動画
とする。
修正箇所。間は省略する。
//1行目を削除する〜ここから
sheet.deleteRow(1);
//1行目を削除
var rangeToCopy = sheet.getRange(1,5,5,1);
//2行目になければならないもの
var targetToCopy = sheet.getRange(2,5,5,1);
//2行目の場所
rangeToCopy.copyTo(targetToCopy);
//貼り付け
sheet.getRange(75,3,sheet.getLastRow(),3).clear();
//C列〜E列に入っている移動平均の計算式を消しておく（あとで入力）
//1行目を削除する〜ここまで
var name = sheet.getRange(2,5).getValue();
//入力しておいた名前を取得
var today = sheet.getRange(lastrow,2).getValue();
//最新の値
sheet.getRange(3,5).setValue(today);
//E3に書き込み
var lastweek = sheet.getRange(lastrow-7,2).getValue();
//最新の7日前の値
var change1 = today - lastweek;
//引き算
sheet.getRange(4,5).setValue(change1);
//E4に書き込み
var change2 = change1/lastweek;
//前週比
sheet.getRange(5,5).setValue(change2);
//E5に書き込み
sheet.getRange(6,5).setValue(result_t);
//E6に書き込み
sheet.getRange(7,5).setValue(result_n);
//E7に書き込み
jQueryも変更
_ciyn3がF列
_ckd7gがG列
_clrrxがH列
_cyevmがI列
_cztg3がJ列
だったので、
_ciyn3が[0]_chk2m
_ckd7gが[2]_chk2m
_clrrxが[3]_chk2m
_cyevmが[4]_chk2m
_cztg3が[5]_chk2m
になって
$(function(){
$.getJSON("https://spreadsheets.google.com/feeds/list/1kyJS7B6JAJzC_tXR2chDWvX8R7o1--C_-96IDFCWqu4/od6/public/values?alt=json", function(json){
$(".name").append(json.feed.entry[0].gsx$_chk2m.$t);
$(".value").append(json.feed.entry[1].gsx$_chk2m.$t);
$(".twpro_value").append('twpro検索数: ' + json.feed.entry[4].gsx$_chk2m.$t);
$(".nico_value").append('タグ「' + json.feed.entry[0].gsx$_chk2m.$t + '」' + 'が付けられた動画: ' + json.feed.entry[5].gsx$_chk2m.$t + '件');
var change_value = json.feed.entry[3].gsx$_chk2m.$t.toString()
if(change_value == 0){
$(".change_lead").append('前週比').addClass("even");
$(".change1").append('±' + json.feed.entry[2].gsx$_chk2m.$t).addClass("even");
$(".change2").append('(' + '±' + json.feed.entry[3].gsx$_chk2m.$t + ')').addClass("even");
}
else{
if(change_value.indexOf('-') !== -1){
$(".change_lead").append('前週比').addClass("down");
$(".change1").append(json.feed.entry[2].gsx$_chk2m.$t).addClass("down");
$(".change2").append('(' + json.feed.entry[3].gsx$_chk2m.$t + ')').addClass("down");
}
else{
$(".change_lead").append('前週比').addClass("up");
$(".change1").append('+' + json.feed.entry[2].gsx$_chk2m.$t).addClass("up");
$(".change2").append('(' + '+' + json.feed.entry[3].gsx$_chk2m.$t + ')').addClass("up");
}
}
});
});これでおk。
Last updatedを消す。
これで1ページ分の動的な生成ができるようになりました。
スプレッドシートのjsonのシートの指定
https://spreadsheets.google.com/feeds/list/1kyJS7B6JAJzC_tXR2chDWvX8R7o1--C_-96IDFCWqu4/od6/public/values?alt=json
のod6は1シートのみの場合はそれでいいものの、他のシートを指定する時によくわからないらしい。
試してみると、1シート目は1、2シート目は2で動くようなので、1シート目を参照する場合は
https://spreadsheets.google.com/feeds/list/1kyJS7B6JAJzC_tXR2chDWvX8R7o1--C_-96IDFCWqu4/1/public/values?alt=json
となる。
一覧表を作る
今は1ファイル：1シート：1人というデータだが、人数が増えることを見越して複数シートで運用する。
1シート目に各シートのE2からE7を書いておくと、そのシートを参照するだけで良くなる。
Datatablesに放り込みたい！ということで
How to grab Datatables data from a Google Spreadsheet - DataTables forums
英語の勉強のお時間です。そんな難しい話じゃないけど。
Datatables使ったページのサンプルが必要なので配置する。
データボード - Lisiograph
1. Google Spreadsheet set upと、2. You'll get a URL that looks like thisはjsonのurlを取得する話なので省略。
使うjsonは
https://spreadsheets.google.com/feeds/list/1kyJS7B6JAJzC_tXR2chDWvX8R7o1--C_-96IDFCWqu4/1/public/values?alt=json
今使っているのDatatablesは
<script>
$(document).ready(function(){
$('#myTable').dataTable({"paging": false});
});
</script> となっている。これを3. Initialize dataTables as follows:によって、
<script type="text/javascript" charset="utf-8">
$(document).ready(function() {
$('#myTable').dataTable( {
"bServerSide":false,
"bProcessing":true,
"sAjaxDataProp": "feed.entry",
"sAjaxSource": "<a href="https://spreadsheets.google.com/feeds/list/0Ah5i4flVQwqEdHVRU0ZFWnVDSjVIaXdyNjJQR2ZtbEE/od6/public/values?alt=json"" target="_blank" rel="nofollow">https://spreadsheets.google.com/feeds/list/0Ah5i4flVQwqEdHVRU0ZFWnVDSjVIaXdyNjJQR2ZtbEE/od6/public/values?alt=json"</a>,
"aoColumns": [
{ "mDataProp": "gsx$carmake.$t" },
{ "mDataProp": "gsx$model.$t" },
{ "mDataProp": "gsx$year.$t" },
]
} );
} );
</script> 動かん。
修正箇所は
"sAjaxSource": "<a href="https://spreadsheets.google.com/feeds/list/0Ah5i4flVQwqEdHVRU0ZFWnVDSjVIaXdyNjJQR2ZtbEE/od6/public/values?alt=json"" target="_blank" rel="nofollow">https://spreadsheets.google.com/feeds/list/0Ah5i4flVQwqEdHVRU0ZFWnVDSjVIaXdyNjJQR2ZtbEE/od6/public/values?alt=json"</a>,
"aoColumns": [
{ "mDataProp": "gsx$carmake.$t" },
{ "mDataProp": "gsx$model.$t" },
{ "mDataProp": "gsx$year.$t" },
]sAjaxSourceはなんでこんな書き方をしているのか不明だが、とりあえず普通にjsonを指定すればいい。
aoColumnはjsonのkeyを指定すればいい
<script type="text/javascript" charset="utf-8">
$(document).ready(function() {
$('#myTable').dataTable( {
"bServerSide":false,
"bProcessing":true,
"sAjaxDataProp": "feed.entry",
"sAjaxSource": "https://spreadsheets.google.com/feeds/list/1kyJS7B6JAJzC_tXR2chDWvX8R7o1--C_-96IDFCWqu4/1/public/values?alt=json",
"aoColumns": [
{ "mDataProp": "gsx$_cn6ca.$t" },
//A列
{ "mDataProp": "gsx$_cokwr.$t" },
//B列
{ "mDataProp": "gsx$_cre1l.$t" },
//D列
{ "mDataProp": "gsx$_chk2m.$t" },
//E列
{ "mDataProp": "gsx$_ciyn3.$t" },
//F列
]
} );
} );
</script>htmlのテーブルは中身空っぽにしておかないと混線するようなので
<table id="myTable" class="display text-right"></table>
これで取りあえず動く。
が、ヘッダがない。
JSONデータの連携 - シュンツのつまづき日記
sTitleってかけば何とかなるんじゃない？
{ sTitle : "名称","mDataProp": "gsx$_cn6ca.$t" },
//A列
{ sTitle : "WikipediaPV","mDataProp": "gsx$_cokwr.$t" },
//B列
{ sTitle : "騰落率（前週比）","mDataProp": "gsx$_cre1l.$t" },
//D列
{ sTitle : "twpro","mDataProp": "gsx$_chk2m.$t" },
//E列
{ sTitle : "ニコニコ動画 タグ","mDataProp": "gsx$_ciyn3.$t" },
//F列動いたぞ。
名前にリンクが張れていない。
これはちょっと悩んだが単純だった。
セルの中に<a href="http://lisiograph.webcrow.jp/detail/%E5%A4%A7%E4%B9%85%E4%BF%9D%E7%91%A0%E7%BE%8E.html">大久保瑠美</a>と直接書き込んでしまう
強引すぎて笑えるが動くのでよし。
WTRとWNRとNT%がないので、あとで各シートで計算しておく。
平均、中央値、単相関、順位相関についてはあとで考える。
月次・週次について
今のところ生成はローカルになっても仕方ないと考えています。
とりあえずは日々更新したデータをcsvでとっておくことが必要。
GoogleAppsScript - GASで文字コード指定してファイルを書き出す - Qiita
function outputSheetToCsvFile() {
// 定数
var fileName = "μ's.csv";
var contentType = "text/csv";
var charSet = "Shift_JIS";
var lineDelimiter = ",";
var newLineChar = "\r\n";
// 開いているシートからデータを取得
var range = SpreadsheetApp.getActiveSheet().getDataRange();
var values = range.getValues();
// 2次元配列になっているデータをcsvのstringに変換
var csvString = underscoreGS._map(
values,
function(row){return row.join(lineDelimiter);}
).join(newLineChar);
// Shift_JISなBlobに変換
var blob = Utilities.newBlob("", contentType, fileName).setDataFromString(csvString, charSet);
// Blobをファイルに出力
DocsList.createFile(blob);
}もう一つの方はカスタムメニューを追加してそこから動かすためのものなので不要。
このサンプルだけ見て動かそうとして、UnderscoreGSとかいうやつのおかげでどうしても動かなくて困った。
ライブラリを導入してないと動かないらしい。
GoogleAppsScript - GASのライブラリを使って楽したい① とりあえず使ってみる_(:3」∠)_ - Qiita
修正箇所は
・ファイル名は日付にしたい
・UTF-8のほうが良い
・データの取得場所
ということで直したものがこちら
// 定数
var date = new Date();
var formattedDate = Utilities.formatDate(date, "JST", "yyyy-MM-dd");
var fileName = formattedDate + ".csv";
var contentType = "text/csv";
var charSet = "UTF-8";
var lineDelimiter = ",";
var newLineChar = "\r\n";
// 開いているシートからデータを取得
var range = sheet.getDataRange();
var values = range.getValues();
// 2次元配列になっているデータをcsvのstringに変換
var csvString = underscoreGS._map(
values,
function(row){return row.join(lineDelimiter);}
).join(newLineChar);
// Blobに変換
var blob = Utilities.newBlob("", contentType, fileName).setDataFromString(csvString, charSet);
// Blobをファイルに出力
DocsList.createFile(blob);とりあえずこれを用意しておけばあとで使えるでしょう。
その他修正
WTR/WNR/NT%の追加
var wtr = sheet.getRange(3,5).getValue()/sheet.getRange(6,5).getValue(); sheet.getRange(8,5).setValue(wtr); //E8にWTRの書き込み var wnr = sheet.getRange(3,5).getValue()/sheet.getRange(7,5).getValue(); sheet.getRange(9,5).setValue(wnr); //E9にWNRの書き込み var nt = sheet.getRange(6,5).getValue()/sheet.getRange(7,5).getValue(); sheet.getRange(10,5).setValue(wnr); //E10にNT%の書き込み
各シート1行目を削除した時に1シート目に指定したものがずれていくのを修正。もう直接書き込むことにする。
ss.getSheets()[0].getRange(2,1).setValue('<a href="http://lisiograph.webcrow.jp/detail/%E5%A4%A7%E4%B9%85%E4%BF%9D%E7%91%A0%E7%BE%8E.html">' + name + '</a>');
//1シート目のA2に名前（リンク）を書き込む
ss.getSheets()[0].getRange(2,2).setValue(today);
//1シート目のB2に最新の値
ss.getSheets()[0].getRange(2,3).setValue(change1);
//1シート目のC2に前週比
ss.getSheets()[0].getRange(2,4).setValue(change2);
//1シート目のD2に騰落率
ss.getSheets()[0].getRange(2,5).setValue(result_t);
//1シート目のE2にtwpro
ss.getSheets()[0].getRange(2,6).setValue(result_n);
//1シート目のF2にニコニコ動画タグ
ss.getSheets()[0].getRange(2,7).setValue(wtr);
//1シート目のG2にWTR
ss.getSheets()[0].getRange(2,8).setValue(wnr);
//1シート目のH2にWNR
ss.getSheets()[0].getRange(2,9).setValue(nt);
//1シート目のI2にNT%これにともなって一覧表も修正。ページングも使わないので設定しておく。
"paging": false,
"aoColumns": [
{ sTitle : "名称","mDataProp": "gsx$_cn6ca.$t" },
//A列
{ sTitle : "WikipediaPV","mDataProp": "gsx$_cokwr.$t" },
//B列
{ sTitle : "騰落率（前週比）","mDataProp": "gsx$_cre1l.$t" },
//D列
{ sTitle : "twpro","mDataProp": "gsx$_chk2m.$t" },
//E列
{ sTitle : "ニコニコ動画 タグ","mDataProp": "gsx$_ciyn3.$t" },
//F列
{ sTitle : "WTR","mDataProp": "gsx$_ckd7g.$t" },
//G列
{ sTitle : "WNR","mDataProp": "gsx$_clrrx.$t" },
//H列
{ sTitle : "NT%","mDataProp": "gsx$_cyevm.$t" },
//I列
平均値/中央値は出したい
相関関数はもういらないかなーと思っています。（面倒なので）
当然のことながら、平均値や中央値を導くためにはすべてのデータを把握する必要があります。
Google ドキュメント、スプレッドシート、スライドのサイズ制限 - ドライブ ヘルプ
より、1つのスプレッドシートに対して「データを含むセルが200万個まで」のようです。
1200日分のデータを保持するとすると、各シート1200g行×5列の6000セルを使用するので、333シートは使えるみたい。
データ数が出揃ってきてからもう1回考えます。
過去データをどうするべきか？
結局のところDatatablesはjsonを読んでるだけなので、csvとしてとっておいたものをjsonにすれば後々使えるということになります。
現状はデータごとに1ページ生成してますが、保存したjsonを1つのページから叩けるようになれば素敵かなと思っています。
これも後で考える。
複数データを巡回できるようにする
全体をfor文でくくる。
シート数-1回のループをするので、
Google Apps Scriptリファレンス: スプレッドシート内のシート数を取得する
for(var n=1; n<ss.getNumSheets(); n++) {
var sheet = ss.getSheets()[n];
//n番目のシートを指定
（中略）
ss.getSheets()[0].getRange(n-1,1).setValue('<a href="http://lisiograph.webcrow.jp/detail/%E5%A4%A7%E4%B9%85%E4%BF%9D%E7%91%A0%E7%BE%8E.html">' + name + '</a>');
//1シート目のA列に名前（リンク）を書き込む
ss.getSheets()[0].getRange(n-1,2).setValue(today);
//1シート目のB列に最新の値
ss.getSheets()[0].getRange(n-1,3).setValue(change1);
//1シート目のC列に前週比
ss.getSheets()[0].getRange(n-1,4).setValue(change2);
//1シート目のD列に騰落率
ss.getSheets()[0].getRange(n-1,5).setValue(result_t);
//1シート目のE列にtwpro
ss.getSheets()[0].getRange(n-1,6).setValue(result_n);
//1シート目のF列にニコニコ動画タグ
ss.getSheets()[0].getRange(n-1,7).setValue(wtr);
//1シート目のG列にWTR
ss.getSheets()[0].getRange(n-1,8).setValue(wnr);
//1シート目のH列にWNR
ss.getSheets()[0].getRange(n-1,9).setValue(nt);
//1シート目のI列にNT%
Utilities.sleep(10000);
//10000ミリ秒のウェイト
}おk！
ウェイト時間は検討の余地あり。
来る所まで来た感がある。
