最終段階その2
読んでもらうために書いたものではないので、読みにくいと思います。
スプレッドシートを用意する
シートの追加→名前の入力→基準日の入力→データ取得のループ
という流れ
まずはシートの追加
Google Apps Scriptリファレンス: 新規シートを追加する
新しいシートを最後に追加する。
既存のシートが3つあるとき：
スプレッドシートのシート数は3
追懐するシート名は「4」とし、追加する位置は「3（0スタートのため）」になる。
insertSheetの1つ目のオプションはシート名、2つ目のオプションは追加位置になるので、
function addNewPage() {
var SPREADSHEET_ID = '1kyJS7B6JAJzC_tXR2chDWvX8R7o1--C_-96IDFCWqu4';
//URLからコピペ
var ss = SpreadsheetApp.openById(SPREADSHEET_ID);
//スプレッドシートを指定
var num = ss.getNumSheets();
//シート数を取得
ss.insertSheet(num+1,num);
//num+1という名前のシートを最後に追加
}動かん。
num+1がintegerになるがいけないようだ。
var SPREADSHEET_ID = '1kyJS7B6JAJzC_tXR2chDWvX8R7o1--C_-96IDFCWqu4'; //URLからコピペ var ss = SpreadsheetApp.openById(SPREADSHEET_ID); //スプレッドシートを指定 var num = ss.getNumSheets(); //シート数を取得 var sheetname = String(num + 1); //stringでシートの名前を作る ss.insertSheet(sheetname,num); //sheetnameという名前のシートを最後に追加
これでよし。
名前の入力
入力ダイアログを表示させ、入力したものをセルE2に書き込む
まずはダイアログ。
[GAS]入力ダイアログを表示するには : 逆引きGoogle Apps Script
var name = Browser.inputBox('名前を入力してください');
//名前を入力するダイアログ→入力したものはnameになるこれを新たに追加したシートのE2に書き込む
var sheet = ss.getSheets()[num]; //新しく追加したシート sheet.getRange(2,5).setValue(name); //E2にnameを書き込む
動きます。
基準日の入力
1200日のデータ保持ということにします。
表示されるデータは1200-75の1125日となります。（現状より若干減ります）
ミリ秒にして1200日分戻して・・・とかもできるけど（うまく行かなくてドはまりした）、1行で書けることに気づく
sheet.getRange(1,1).setValue('=today()-1200');
//A1に書き込むシンプル・イズ・ベスト。
基準日から今日までのデータを取得
基本的には通常版のデータ取得と同じ。1200日なので、ループ回数は40に決めておいておk。
for(var n=0; n<40; n++) {
var range = sheet.getRange(1, 1);
var yesterday = range.getValue();
//rangeの値（日付の文字列）を取得
var yesterday = Date.parse(yesterday);
//日付形式に変換（ミリ秒）
var target = yesterday + 24*60*60*1000;
//24*60*60*1000で1日ずらす
var target = new Date(target);
//ミリ秒を日付形式に変換
var i_date = target.getDate();
//取得すべき日付
var mod_i = i_date -1;
//書き込み位置を設定
var target_year = target.getYear();
//取得すべき日付の年
var target_month = target.getMonth()+1;
//取得すべき日付の月
var target_month = ("0" + target_month).slice(-2);
//2桁揃え
var responseURL = "http://stats.grok.se/json/ja/" + target_year + target_month + "/" + name;
//取得先のurlを設定
var response = UrlFetchApp.fetch(responseURL);
//データを取得
while(response == ''){
Utilities.sleep(30000);
var response = UrlFetchApp.fetch(responseURL);
};
//返ってきたデータが空だったら30秒後にリトライ
var result = JSON.parse(response.getContentText());
//JSONをパース
var result = result.daily_views;
//パースしたもののうち、daily_viewsだけ使う
// スプレッドシート（データ）への書き込み
for(var i=i_date; i<=Object.keys(result).length; i++) {
var day_number = ("0" + i).slice(-2);
//2桁揃え
var result_views = result[target_year + '-' + target_month + '-' + day_number];
var x = i-mod_i;
// 日付
range.offset(x, 0).setValue(target_year + '-' + target_month + '-' + day_number);
// データ
range.offset(x, 1).setValue(result_views);
}
Utilities.sleep(10000);
}全体をforでくくる。
続きを取得して欲しいのは2行目からなのでハナから指定しておくといい。
B1に値が入力されないので、初日だけ移動平均75日がずれるが、まああんまり気にしない。
次の全体巡回の時に1行目は消えるので問題なし。
だったら
sheet.getRange(1,1).setValue('=today()-1201');
//A1に書き込むこうしておいたほうがいいのか。
とか言うレベルじゃなかったので修正。
for(var n=0; n<40; n++) {
var range = sheet.getRange(1, 1);これだったら同じ場所で40回ループするので
for(var n=0; n<40; n++) {
var lastrow = sheet.getLastRow();
var range = sheet.getRange(lastrow,1);だめだ。1回目が回らない。
1回目だけfor文の外で決める→for文の最後で2回目に適用するものを決める
var range = sheet.getRange(1, 1);
for(var n=0; n<40; n++) {
（中略）
var lastrow = sheet.getLastRow();
var range = sheet.getRange(lastrow,1);
}動いた。けど無効データ（4月31日とか）で止まってしまう。
無効データってどういうことになるかというと
Logger.log(Date.parse(yesterday));
NaNが返ってくる。
つまり、とったものがNaNだったら最終行を削除する、みたいなのを入れておけばいい。
while (Date.parse(yesterday) == NaN){
sheet.deleteRow(lastrow);
var lastrow = sheet.getLastRow();
//最終行を取得
var range = sheet.getRange(lastrow,1);
var yesterday = range.getValue();
var yesterday = Date.parse(yesterday);
}動かない。
与えられた値が数値かどうかの判定 (NaN) - web newbie
＞NaNはどんな数字に対してもイコールではありません。
へえ。
var lastrow = sheet.getLastRow();
//最終行を取得
var range = sheet.getRange(lastrow,1);
var yesterday = range.getValue();
var yesterday = Date.parse(yesterday);
while (isNaN(yesterday)){
//yesterdayがNaNだったら
sheet.deleteRow(lastrow);
//lastrow行目を削除
//lastrowからyesterdayを再評価するために再設定
var lastrow = sheet.getLastRow();
var range = sheet.getRange(lastrow,1);
var yesterday = range.getValue();
var yesterday = Date.parse(yesterday);
}これで大丈夫っぽい。回してみると・・・
ダメだ、デフォルトで1000行になっててそれ以上進まない。
Google Apps Scriptリファレンス: シートに行を挿入する
var sheet = ss.getSheets()[num]; //新しく追加したシート sheet.insertRowsAfter(1,210); //1行目の後ろに210行追加
念のため210行入れておく。
まだダメ。995行目に6月31日が残って止まってしまう。
1000行しかないと認識されてるのではないか？（6月31日が残るのが謎だが。）
追加したあとに再認識してもらう。
var sheet = ss.getSheets()[num]; //新しく追加したシート sheet.insertRowsAfter(1,210); //1行目の後ろに210行追加 var sheet = ss.getSheets()[num]; //もう一回設定する
まだダメ。
急場しのぎでこんなのを書いた。
function addNewPage2() {
var SPREADSHEET_ID = '1R6OCXq_3c6jO0ZFc3bdGN9mARrOJKKyLXECj3rSvhas';
//URLからコピペ
var ss = SpreadsheetApp.openById(SPREADSHEET_ID);
//スプレッドシートを指定
var num = ss.getNumSheets();
//シート数を取得
var sheet = ss.getSheets()[num-1];
//最終シート
var lastrow = sheet.getLastRow();
//最終行を取得
Logger.log(lastrow);
var range = sheet.getRange(lastrow,1);
var yesterday = range.getValue();
var yesterday = Date.parse(yesterday);
while (isNaN(yesterday)){
//yesterdayがNaNだったら
sheet.deleteRow(lastrow);
//lastrow行目を削除
//lastrowからyesterdayを再評価するために再設定
var lastrow = sheet.getLastRow();
var range = sheet.getRange(lastrow,1);
var yesterday = range.getValue();
var yesterday = Date.parse(yesterday);
Logger.log(yesterday);
}
var name = sheet.getRange(2,5).getValue();
//入力しておいた名前を取得
var limit = Browser.inputBox('あと何ヶ月？');
//残りを取得する回数
for(var n=0; n<limit; n++) {
var yesterday = range.getValue();
//rangeの値（日付の文字列）を取得
var yesterday = Date.parse(yesterday);
//日付形式に変換（ミリ秒）
var target = yesterday + 24*60*60*1000;
//24*60*60*1000で1日ずらす
var target = new Date(target);
//ミリ秒を日付形式に変換
var i_date = target.getDate();
//取得すべき日付
var mod_i = i_date -1;
//書き込み位置を設定
var target_year = target.getYear();
//取得すべき日付の年
var target_month = target.getMonth()+1;
//取得すべき日付の月
var target_month = ("0" + target_month).slice(-2);
//2桁揃え
var responseURL = "http://stats.grok.se/json/ja/" + target_year + target_month + "/" + name;
//取得先のurlを設定
var response = UrlFetchApp.fetch(responseURL);
//データを取得
while(response == ''){
Utilities.sleep(30000);
var response = UrlFetchApp.fetch(responseURL);
};
//返ってきたデータが空だったら30秒後にリトライ
var result = JSON.parse(response.getContentText());
//JSONをパース
var result = result.daily_views;
//パースしたもののうち、daily_viewsだけ使う
// スプレッドシート（データ）への書き込み
for(var i=i_date; i<=Object.keys(result).length; i++) {
var day_number = ("0" + i).slice(-2);
//2桁揃え
var result_views = result[target_year + '-' + target_month + '-' + day_number];
var x = i-mod_i;
// 日付
range.offset(x, 0).setValue(target_year + '-' + target_month + '-' + day_number);
// データ
range.offset(x, 1).setValue(result_views);
}
Utilities.sleep(10000);
//連続でデータ取得をしないように10秒待つ
var lastrow = sheet.getLastRow();
//最終行を取得
var range = sheet.getRange(lastrow,1);
var yesterday = range.getValue();
var yesterday = Date.parse(yesterday);
while (isNaN(yesterday)){
Logger.log(isNaN(yesterday));
//yesterdayがNaNだったら
Logger.log(isNaN(lastrow));
sheet.deleteRow(lastrow);
//lastrow行目を削除
//lastrowからyesterdayを再評価するために再設定
var lastrow = sheet.getLastRow();
var range = sheet.getRange(lastrow,1);
var yesterday = range.getValue();
var yesterday = Date.parse(yesterday);
Logger.log(yesterday);
}
}
}根本的な解決になっていない。
しかし別問題に対処する必要が出てきたのでここは一旦置く。
赤﨑千夏問題
去年の7月の時点で諦めていた、積年の恨みをここで晴らそう。
何かというと、wikipediaの項目名が歪んでしまっている場合にどうするべきか、ということ。
赤崎千夏 - Wikipedia←崎の異字体に対応できない
とか、
田中理恵 (声優) - Wikipedia←(声優)が邪魔
とかのこと。
Wikipedia用の名前データを持てばいいだけなので、
新規でデータを取るときには
var name = Browser.inputBox('名前を入力してください');
//名前を入力するダイアログ→入力したものはnameになる
var name_w = Browser.inputBox('Wikipediaの項目名をコピペしてください');
//名前を入力するダイアログ→入力したものはname_w（Wikipedia用）になる
sheet.getRange(2,5).setValue(name);
//E2にnameを書き込む
sheet.getRange(2,4).setValue(name_w);
//D2にnameを書き込む
var responseURL = "http://stats.grok.se/json/ja/" + target_year + target_month + "/" + name_w;
//取得先のurlを設定更新用のコードには
sheet.getRange(1,4,1,2).clear();
//1行目に移動した名前を消去
var name = sheet.getRange(2,5).getValue();
//入力しておいた名前を取得
var name_w = sheet.getRange(2,4).getValue();
//入力しておいた名前を取得（Wikipedia用）
var responseURL = "http://stats.grok.se/json/ja/" + target_year + target_month + "/" + name_w;
//取得先のurlを設定とする。
多分これでどんな名前でも対応できるはず。
テスト
動いている。
そして何もしていないのに6月31日問題をクリアした。
そして7月で止まった。
謎すぎる。
原因がわからんものはどうしようもないので先に進む。
ついでに修正。
無効日付の行数が結構あって結構削除するので追加する行を210行から235行にする。
sheet.insertRowsAfter(1,235); //1行目の後ろに235行追加
カスタムメニューを作る
GoogleAppsScript - GASで文字コード指定してファイルを書き出す - Qiita
でやってたのを真似する。
function onOpen() {
// メニューバーにカスタムメニューを追加
var SPREADSHEET_ID = '1R6OCXq_3c6jO0ZFc3bdGN9mARrOJKKyLXECj3rSvhas';
//URLからコピペ
var ss = SpreadsheetApp.openById(SPREADSHEET_ID);
//URLからコピペ
var entries = [
{
name : "新規データ取得",
//カスタムメニューでの表示名
functionName : "addNewPage"
},
{
name : "新規データ取得が終わらなかった時に",
//カスタムメニューでの表示名
functionName : "addNewPage2"
}
];
};
htmlを生成する
変わらない部分と、変わる部分を分別し、それぞれパートごとにテキストファイルで分けて、Automatorで結合させる。
複数のテキストファイルを結合する方法 - Jekylle+
これでUTF-8になる。出力されるのはtxt。
拡張子を一括置換（for mac）
これで拡張子をhtmlに変える。
AppleScriptで結合から出力まで可能ではあるものの、Shift_JISになってしまうのをどうしてもパスできなかったのでこうなった。
AutomatorにはApplesctiptを実行するという素晴らしいアクションがあるので、名前を記述したファイルの内容を読み込んで、その名前にファイル名を変更するというスクリプトを書く。
平均値と中央値を考える
本当に最終章。
スプレッドシートは新しいのを用意した。（本番系）
ファイル→ウェブに公開→ドキュメント全体
ツール→スクリプトエディタでコードを貼り付け
結局、1シート目の一覧表用データの横に、平均値・中央値の計算をあてることにした。
var lastrow_1 = ss.getSheets()[0].getLastRow();
//1シート目の最終行を取得
ss.getSheets()[0].getRange(2,10).setValue('=average(B2:B' + lastrow_1 + ')');
//1シート目のJ2にB列のAverage
ss.getSheets()[0].getRange(3,10).setValue('=average(C2:C' + lastrow_1 + ')');
//1シート目のJ3にC列のAverage
ss.getSheets()[0].getRange(4,10).setValue('=average(D2:D' + lastrow_1 + ')');
//1シート目のJ4にD列のAverage
ss.getSheets()[0].getRange(5,10).setValue('=average(E2:E' + lastrow_1 + ')');
//1シート目のJ5にE列のAverage
ss.getSheets()[0].getRange(6,10).setValue('=average(F2:F' + lastrow_1 + ')');
//1シート目のJ6にF列のAverage
ss.getSheets()[0].getRange(7,10).setValue('=average(G2:G' + lastrow_1 + ')');
//1シート目のJ7にG列のAverage
ss.getSheets()[0].getRange(8,10).setValue('=average(H2:H' + lastrow_1 + ')');
//1シート目のJ8にH列のAverage
ss.getSheets()[0].getRange(9,10).setValue('=average(I2:I' + lastrow_1 + ')');
//1シート目のJ9にI列のAverage
ss.getSheets()[0].getRange(10,10).setValue('=median(B2:B' + lastrow_1 + ')');
//1シート目のJ10にB列のmedian
ss.getSheets()[0].getRange(11,10).setValue('=median(C2:C' + lastrow_1 + ')');
//1シート目のJ11にC列のmedian
ss.getSheets()[0].getRange(12,10).setValue('=median(D2:D' + lastrow_1 + ')');
//1シート目のJ12にD列のmedian
ss.getSheets()[0].getRange(13,10).setValue('=median(E2:E' + lastrow_1 + ')');
//1シート目のJ13にE列のmedian
ss.getSheets()[0].getRange(14,10).setValue('=median(F2:F' + lastrow_1 + ')');
//1シート目のJ14にF列のmedian
ss.getSheets()[0].getRange(15,10).setValue('=median(G2:G' + lastrow_1 + ')');
//1シート目のJ15にG列のmedian
ss.getSheets()[0].getRange(16,10).setValue('=median(H2:H' + lastrow_1 + ')');
//1シート目のJ16にH列のmedian
ss.getSheets()[0].getRange(17,10).setValue('=median(I2:I' + lastrow_1 + ')');
//1シート目のJ17にI列のmedianこれを追加する。クッソ汚いコード。
あとは場所指定して読みこむだけ。
12人で立ち上げる
平均値と中央値のデータで12行縦に使ってます。
datatableではその列は指定してないのですが、全体で12行以下だとバグってしまうようなので、12行分のデータを用意します。
カスタムメニューからスクリプトを実行してわかること
動かしてみると「起動時間の最大値を超えました」と出て止まった。
なるほどこれが原因か。
5分でアウトになるらしい。
５分以上かかる処理の実装について（タイムアウトエラー対策） - Google グループ
これだったら諦めて時間切れになったら続きを行うということでいいでしょう。仕方ない。
ちょっとだけ修正
表示形式を書き込む時に指定する。整数だったり％だったり小数点二桁だったりするので。
スプレッドシート利用の基本(5/5):初心者のためのGoogle Apps Scriptプログラミング入門
sheet.getRange(5,5).setValue(change2);
//E5に書き込み
sheet.getRange(5,5).setNumberFormat('0.00%')
//小数点第二位まででパーセント表示1行で書いても動くかどうかは確認していない。行けそうな気もする。
とりあえず公開する
動かしながら問題点を探していく。
