最終段階
読んでもらうために書いたものではないので、読みにくいと思います。
書き込み位置の修正
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!
ウェイト時間は検討の余地あり。
来る所まで来た感がある。