試行錯誤ダイアリー

新卒エンジニアが日々の技術的な学び,働き方,日々感じたこと等を書きます

【Google Apps Script】スプレッドシートと連携時の日付の引き算について

先日google apps scriptでスプレッドシートと連携させたタスクシートを作りました.

記事:【Google Apps Script】スプレッドシート連携してタスク管理 - 試行錯誤ダイアリー

そのとき,スプレッドシートにおける日付の引き算をしたときのフォーマットの違いに困ったのでまとめました.

スプレッドシートでの日付

スプレッドシートでは,セルを右クリックして一番したまでスクロールすると
「データの入力規則」という項目があり

f:id:appli-in:20180619005743p:plain:w400

こちらをクリックして,出てきたダイアログの条件を日付にすると,

f:id:appli-in:20180619010118p:plain:w450

セルをクリックするだけでカレンダーが表示され,日付を選択して入力することができます.

f:id:appli-in:20180619010543p:plain:w300

こちらの記事によると
参考記事:Spreadsheetでの日付と、Google Apps ScriptでのnewDate()

スプレッドシートでは時刻の基準が1899年12月30日 00:00:00になっているようです. 確認できなかった.

google apps scriptでの日付

google apps scriptでは,言語がほぼjavascriptのため,日付を扱うときはDateクラスを使用します.
Class DateField  |  Apps Script  |  Google Developers

Dateクラスはこのような仕様のようです.

Date オブジェクトは、1970 年 1 月 1 日 (UTC) から始まるミリ秒単位の時刻値を基準としています。

引用:Date - JavaScript | MDN

実際に試してみると

function test(){
  var date = new Date(1970, 0, 1);//1970年1月1日
  Browser.msgBox(date3.getTime());
}

f:id:appli-in:20180619015408p:plain:w500

となりました,これは日本時間で+9時間されているためです(32400秒は9時間).

Dateオブジェクトでは以下のようにいくつかのフォーマットでインスタンスを生成できます(こちらは一部で他にもあります).

function test(){
  var date1 = new Date();//現在の時刻(時分もはいります)
  var date2 = new Date('2018/6/16');//2018年6月16日
  var date3 = new Date(2000, 3, 15);//2000年4月15日
  
  Browser.msgBox("date1 :" +date1+"\\n"+"date2 :" +date2+"\\n"+"date3 :" +date3);
}

出力はこんな感じ

f:id:appli-in:20180619012814p:plain:w500

ここで気をつけてほしいのは,new Date();だと,完全に現在の時刻で時分も入ってしまっていることと,new Date(2000, 3, 15)のフォーマットで書いたときは,月が0から始まることです.ここでは"3"にしているので4月の"Apr"になっていることです.

問題点

それぞれを引き算すると思わぬエラーが発生してしまうことがあります.というのも,google apps scriptではnew Date();は時分も入力されますが,スプレッドシートでは日付だけなので,引き算すると一日単位の値が返ってきません.なので,日にちに関わる処理を書くときは気をつけなければなりません.

実際に試してみます

使うスプレッドシートはこれです.1行3列目に=today()で今日の日付を入力してあります.

f:id:appli-in:20180619020656p:plain:w700

それぞれgoogle apps scriptで取得したtodayと9行5列目の6月18日の引き算.スプレッドシートので取得したtodayと9行5列目の6月18日の引き算をしています.

function test(){
  Browser.msgBox("GAS-スプレッドシート:"+(today - sheet.getRange(9,5).getValue())+"\\n"+"スプレッドシート-スプレッドシート:"+(sheet.getRange(1,3).getValue() - sheet.getRange(9,5).getValue()));
}

結果はこちらです.

f:id:appli-in:20180619021022p:plain:w500

出力される値が一日単位のものではなく中途半端な値になっています.

スプレッドシートの=today()で取得した数値は時分のない日付のみの値になっているため,引き算をすると一日のミリ秒である86400000が計算結果として出ます.

まとめ

google apps scriptとスプレッドシートの日付の説明をして,現在の日付を取得する際のフォーマットの違いについて,実際に数値を出力して見ることで確認しました.

google apps scriptとスプレッドシートを連携させて,今日の日付を取得し算出した値同士を比較する際は十分気をつけなければいけません.

【Google Apps Script】スプレッドシート連携してタスク管理 - 試行錯誤ダイアリー のタスク管理シートでは,今日の日付をgoogle apps scriptで取得せず,スプレッドシートの方で取得することでこの問題を回避しています.