背景

Google Apps Scriptでは6分間の実行時間制限があり、シート全体を検索したりというようなスクリプトはいちいちAPIをたたいているとすぐにタイムアウトしてしまいます。そのため通常はまずシート全体を配列に格納しそれを操作していくという手順を踏みがち。そんななかで自分の中で少しつまずいたのが日付データの扱いです。無駄に悩んでしまったので忘れないように投稿します。

スプレッドシート

こういう恐ろしいスプレッドシートで作られた手作りのカレンダーがあり、今回依頼されたのはこのカレンダーに別のシートから予定を転記するスクリプトです。別のシートからコピーしてくるといった手順は今回のエントリーでは記述しません。

スクリプト

まず、通常はこうするでしょう。

配列に入れる.js

function explain(){
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var values = sheet.getDataRange().getValues();
}

これをログで出力してみるとこうなります。

 [[, , , , , , , ], [, 日, 月, 火, 水, 木, 金, 土], [, Sun Jul 21 00:00:00 GMT+09:00 2019, Mon Jul 22 00:00:00 GMT+09:00 2019, Tue Jul 23 00:00:00 GMT+09:00 2019, Wed Jul 24 00:00:00 GMT+09:00 2019, Thu Jul 25 00:00:00 GMT+09:00 2019, Fri Jul 26 00:00:00 GMT+09:00 2019, Sat Jul 27 00:00:00 GMT+09:00 2019], [, 予定1, 予定2, 予定3, 予定5, 新しいイベント!, , ], [, , , 予定4, 予定6, , 予定8, ], [, , , , 予定7, , , ], [, , , , , , , ], [, Sun Jul 28 00:00:00 GMT+09:00 2019, Mon Jul 29 00:00:00 GMT+09:00 2019, Tue Jul 30 00:00:00 GMT+09:00 2019, Wed Jul 31 00:00:00 GMT+09:00 2019, Thu Aug 01 00:00:00 GMT+09:00 2019, Fri Aug 02 00:00:00 GMT+09:00 2019, Sat Aug 03 00:00:00 GMT+09:00 2019], [, 予定9, 予定10, , 予定13, , 予定14, 予定15], [, , 予定11, , , , , ], [, , 予定12, , , , , ]]

さて、ここで7月21日の座標を得たいとき、通常はindexOf()でSun Jul 21 00:00:00 GMT+09:00 2019を検索すれば事足りるかなと思っていました。

問題発生

どうやらGASではこのような形で配列にいれた日付データは依然そのデータ型を保ち続けていて、文字列しか検索できないindexOf()ではうまく動きませんでした。

何を解決したいか

検索ができるようにしたい
何なら醜いので日付型を見やすくしたい

実際の手順

まず、[object Date]型の日付をyyyy/MM/dd表記にしたうえでstring型に変えるためにこのようなスクリプトを記述しました。

convertDate

function convertDate() {
  //シートを指定、データを配列に格納
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var values = sheet.getDataRange().getValues();
  //date型をstringに変換
  for(var i=0;i<values.length;i++){
    var newValues = values[i].map(
    function(x){
      var type = Object.prototype.toString.call(x);
      if(type == "[object Date]"){
        return x = Utilities.formatDate(x, 'JST', 'yyyy/MM/dd');}
      else{
        return x;}
    });
    values[i]=newValues;
  }
}

シート全体を格納した配列は二次元配列なので、まず各配列内のDate型の要素に対してUtilities.formatDate()で変換を行い、それをforループですべての配列に対して行うという手順です。
ログに出してみるとこんな感じです。

 [[, , , , , , , ], [, 日, 月, 火, 水, 木, 金, 土], [, 2019/07/21, 2019/07/22, 2019/07/23, 2019/07/24, 2019/07/25, 2019/07/26, 2019/07/27], [, 予定1, 予定2, 予定3, 予定5, 新しいイベント!, , ], [, , , 予定4, 予定6, , 予定8, ], [, , , , 予定7, , , ], [, , , , , , , ], [, 2019/07/28, 2019/07/29, 2019/07/30, 2019/07/31, 2019/08/01, 2019/08/02, 2019/08/03], [, 予定9, 予定10, , 予定13, , 予定14, 予定15], [, , 予定11, , , , , ], [, , 予定12, , , , , ]]

見やすくなりましたね!!

使ってみる

配列が使いやすくなったので、これを使って特定の日付(今回は25日)に予定を入れてみます。
手順としては、配列内をindexOf()で検索し、"2019/07/25"に一致するものがあればその一つ下の行にデータを入れるという感じです。

 //検索し、その一つ下にデータを入れる
  var eventDate = "2019/07/25"
  var activeSheet = SpreadsheetApp.getActiveSheet();
  var event = "新しいイベント!"
  for(var i=0;i<values.length;i++){   
    var trial = values[i].indexOf(eventDate);
    if(trial === -1.0){
      continue;
    }
      else{
        var row = i+1;
        var col = values[i].indexOf(sample)+1;
        activeSheet.getRange(row+1,col).setValue(event);
      }
    }

結果

25日に新しいイベントが追加されました!!

まとめ

深夜に書いているのでなんだか要領を得ないような感じになってしまいました。また、もしかしたらほぼ確実にもっといい方法がある気がしているのでどなたか教えてください...。コード自体もmap文の中にif文があったりしてなんだかなあという感じではあるのですが解決したときは小躍りしたのでよしとします。
最後までお読みいただいてありがとうございました。

Google Apps Script で配列に入れた日付データを扱いやすく変換する

背景