プログラミング初心者が、Google Apps Scriptで「◯月◯日週」という出力をするためにがんばったこと

まずは Google Apps Script について「どんなもんか知る」ところから始めた

• けつろんこれがいちばん手っ取り早かったな：ドットインストールのGoogle Apps Script入門 (全16回)
• けっこう勉強してからでないと活用できないかも：Googleの公式ドキュメント
• これもよかった：Google Apps Scriptでプログラミングを学ぶ

そもそも JavaScript について基本的なことを理解していないとツライかもと思った

• やっぱり：ドットインストールのJavaScript入門（全24回）
• Kindle でこの本も買った：確かな力が身につくJavaScript「超」入門

とりあえずつくってみた

前提

• A列に2行目から「2015/11/21,2015/11/22,2015/11/23...」といった日付が並んでいる
• 各週を月曜日はじまりとして、各日にちが何日週なのか、E列に日付を出力する

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();

function setWeek(){
  for (var i = 2; i <= sheet.getMaxRows(); i++){
    var checkdate = sheet.getRange(i, 1).getValue();
    var checkday = checkdate.getDay();
    if (checkday === 1){
      sheet.getRange(i, 5).setValue(checkdate);
    }else{
      var thisweek = sheet.getRange(i-1, 5).getValue();
      sheet.getRange(i, 5).setValue(thisweek);
    }
  }
}

これだとA列の日にちが月曜日の日にちではじまり、降順になっていることが前提となっており、けっこういろんなケースで不具合が生じそう。

ちょっと面倒だけど曜日ごとの処理に変えてみた

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();

function setWeek(){
  for (var i = 2; i <= sheet.getMaxRows(); i++){
    var d = sheet.getRange(i, 1).getValue();
    var year = d.getYear();
    var month = d.getMonth()+1;
    var day = d.getDay();
    if(day===0){
      var date = d.getDate()-6;
    }else if(day===1){
      var date = d.getDate();    
    }else if(day===2){
      var date = d.getDate()-1;    
    }else if(day===3){
      var date = d.getDate()-2;    
    }else if(day===4){
      var date = d.getDate()-3;    
    }else if(day===5){
      var date = d.getDate()-4;    
    }else{
      var date = d.getDate()-5;    
    }
    var fulldate = year + '/' + month + '/' + date ;
    sheet.getRange(i, 2).setValue(fulldate);
  }
}

これだと「2015/12/1」とかは-1されて「2015/12/0」という意味分かんないことになってしまう。

けっきょく日付をミリ秒に変換して計算（そうするしかないんじゃないかとうすうす気づいていた…）

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();

function changeToWeek(){
  for (var i = 2; i < sheet.getMaxRows(); i++){
    var orgDate = sheet.getRange(i, 1).getValue();
    var orgDay = orgDate.getDay();
    var orgTime = orgDate.getTime();
    if (orgDay === 0){
      var newTime = orgTime - 84600000 * 6;
    }else if (orgDay === 1){
      var newTime = orgTime;
    }else if (orgDay === 2){
      var newTime = orgTime - 84600000 * 1;
    }else if (orgDay === 3){
      var newTime = orgTime - 84600000 * 2;
    }else if (orgDay === 4){
      var newTime = orgTime - 84600000 * 3;
    }else if (orgDay === 5){
      var newTime = orgTime - 84600000 * 4;
    } else {
      var newTime = orgTime - 84600000 * 5;
    }
    var weekDate = new Date(newTime);
    sheet.getRange(i, 2).setValue(weekDate);
  }
}

なんかちょっと着地感ない…

• もっとスマートな書き方があるんではないか？（余計な計算してんじゃね？）
• 結果はちゃんと返ってくるんだけど、「TypeError: オブジェクト で関数 getDay が見つかりません。（行 13、ファイル「」）」っていうエラーが表示されるし。

参考になったサイト

• JavaScript による日付・時刻・時間の計算・演算のまとめ
• 意外と知られていないJavaScriptのnew Date()の使用方法

けつろん

「日付の処理って面倒なんすよ〜」というエンジニアの言葉の背景がより実感を伴って理解できた気がする♪

11/24追記）Math floor とかやらないとダメや…

この方法だとデータ自体が以下のようなかんじになってしまうことがわかった。

• 11/16：2015/11/16
• 11/17：2015/11/16 0:30:00
• 11/18：2015/11/16 1:00:00
  これだとグルーピングできない！
  もう少しがんばろ。

11/25追記）Math floor は関係なかった...

getFullYear()、getMonth()、getDate()で抜き出したら解決した。
こんなかんじ。

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();

function changeToWeek(){
  for (var i = 2; i < sheet.getMaxRows(); i++){
    var orgDate = sheet.getRange(i, 1).getValue();
    var orgDay = orgDate.getDay();
    var orgTime = orgDate.getTime();
    if (orgDay === 0){
      var newTime = orgTime - 84600000 * 6;
    }else if (orgDay === 1){
      var newTime = orgTime;
    }else if (orgDay === 2){
      var newTime = orgTime - 84600000 * 1;
    }else if (orgDay === 3){
      var newTime = orgTime - 84600000 * 2;
    }else if (orgDay === 4){
      var newTime = orgTime - 84600000 * 3;
    }else if (orgDay === 5){
      var newTime = orgTime - 84600000 * 4;
    } else {
      var newTime = orgTime - 84600000 * 5;
    }
    var weekDate = new Date(newTime);
    var newYear = weekDate.getFullYear();
    var newMonth = weekDate.getMonth() + 1;
    var newDate = weekDate.getDate();
    sheet.getRange(i, 2).setValue(newYear + '/' + newMonth + '/' + newDate);
  }
}

とりあえずこれで動くようにはなったんだけど、もっと賢い書き方があるんだろうな〜。

12/1追記）間違いを見つけた！

間違っていたので訂正しておきます。

getMaxRows()ではなく、getLastRow()が正しい

ちゃんと確認するべきでした。

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();

function testday () {
  var endrows = sheet.getMaxRows();
  Browser.msgBox(endrows);
}

と

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();

function testday () {
  var endrows = sheet.getLastRow();
  Browser.msgBox(endrows);
}

を実行してみれば明らか。
getMaxRows()はデータのあるなしを無視して最後の行数をとってきてしまうが、getLastRow()はデータが入っている最後の行数をとってくる。

ゆえに以下が正解。

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();

function changeToWeek(){
  for (var i = 2; i <= sheet.getLastRow(); i++){
    var orgDate = sheet.getRange(i, 1).getValue();
    var orgDay = orgDate.getDay();
    var orgTime = orgDate.getTime();
    if (orgDay === 0){
      var newTime = orgTime - 84600000 * 6;
    }else if (orgDay === 1){
      var newTime = orgTime;
    }else if (orgDay === 2){
      var newTime = orgTime - 84600000 * 1;
    }else if (orgDay === 3){
      var newTime = orgTime - 84600000 * 2;
    }else if (orgDay === 4){
      var newTime = orgTime - 84600000 * 3;
    }else if (orgDay === 5){
      var newTime = orgTime - 84600000 * 4;
    } else {
      var newTime = orgTime - 84600000 * 5;
    }
    var weekDate = new Date(newTime);
    var newYear = weekDate.getFullYear();
    var newMonth = weekDate.getMonth() + 1;
    var newDate = weekDate.getDate();
    sheet.getRange(i, 2).setValue(newYear + '/' + newMonth + '/' + newDate);
  }
}

さらにgetValue()を、getValues()に変更すると処理速度が格段に改善する

***Google Apps Script で 「スクリプトによるメソッド Range.getValue の使用頻度が高すぎます」って叱られたの巻***に書いたとおり、Valueの取得は配列でまとめてとったほうがよいらしいので、そんなかんじに変更してみた。

確かに、最終行まであっという間に処理が完了する。

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();

function changeToWeek2(){
  var startrows = 2;
  var endrows = sheet.getLastRow();
  var cols = 1;
  var orgDays = sheet.getRange(startrows, cols, endrows, 1).getValues();
  for (var i = 0; i < orgDays.length-1; i++){
    var checkdate = new Date(orgDays[i]);
    var checkday = checkdate.getDay();
    var orgTime = checkdate.getTime();
    if (checkday === 0){
      var newTime = orgTime - 84600000 * 6;
    }else if (checkday === 1){
      var newTime = orgTime;
    }else if (checkday === 2){
      var newTime = orgTime - 84600000 * 1;
    }else if (checkday === 3){
      var newTime = orgTime - 84600000 * 2;
    }else if (checkday === 4){
      var newTime = orgTime - 84600000 * 3;
    }else if (checkday === 5){
      var newTime = orgTime - 84600000 * 4;
    } else {
      var newTime = orgTime - 84600000 * 5;
    }
    var weekDate = new Date(newTime);
    var newYear = weekDate.getFullYear();
    var newMonth = weekDate.getMonth() + 1;
    var newDate = weekDate.getDate();
    sheet.getRange(i+startrows, 2).setValue(newYear + '/' + newMonth + '/' + newDate);
    }
}

さらにオマケとしてスプレ上で関数っぽく使えるバージョンをつくってみた

けっきょく、これがいちばん汎用性が高いかもと思っている。
処理速度はやや遅いけどいちいち対象セルを指定し直さなくてよいしね。

function whenWeek (orgDate) {
  var checkdate = new Date(orgDate);
  var checknum = checkdate.getDay();
  var orgTime = checkdate.getTime();
  if (checknum === 0){
    var newTime = orgTime - 84600000 * 6;
  }else if (checknum === 1){
    var newTime = orgTime;
  }else if (checknum === 2){
    var newTime = orgTime - 84600000 * 1;
  }else if (checknum === 3){
    var newTime = orgTime - 84600000 * 2;
  }else if (checknum === 4){
    var newTime = orgTime - 84600000 * 3;
  }else if (checknum === 5){
    var newTime = orgTime - 84600000 * 4;
  } else {
    var newTime = orgTime - 84600000 * 5;
  }
  var weekDate = new Date(newTime);
  var newYear = weekDate.getFullYear();
  var newMonth = weekDate.getMonth() + 1;
  var newDate = weekDate.getDate();
  return new Date(newYear + '/' + newMonth + '/' + newDate);
}

これをスクリプトエディタに書いておけば、例えばスプレッドシート上に...

=whenWeek(A2)

って書くと、A2のセルに書いてある日付に対して、その日付の週の月曜日の日にちが出力されます。

ー12/1追記ここまでー