Spreadsheetに日付や時刻を入力してもらうとき、必ずと言っていいほど全角数字で放り込んでくる輩がいますよね。(笑)
そういう雑音を取り除きながら、Date型どうしの演算の仕方をまとめたいと思います。(半分ぐらいは、もはやGASの話というよりかは、JavaScriptの話になります...)
合わせて読みたいSpreadsheetの日付・時刻と、Google Apps Scriptでのnew Date()
まず、Data型やString型の判別方法から
セルの値をgetValue()
で取得(もしくはgetValues()
で配列で取得)したとして、その値の型を判別してから処理を行う必要があります。(例えば、String型でないのに、replace()
するとエラーになるので)
判別には、すべてのオブジェクトが持っているObject.prototype.toString( )
メソッドを使用します。これに続けてCall(hoge)
とすることで、オブジェクトhogeの型を調べることができます。
そもそも入力時にValidationチェックをかける
可能であれば、onEdit()
でチェックして、日付や時刻として有効な(意味をなす)入力をさせたり、全角や半角を入れさせないというのがよいと思います。(もしくは、勝手に置換してしまう)
以下に、時刻を入力させるときのonEdit()
でのValidationチェック方法をいくつか挙げてみます。
なぞに入り込んだ空白を消す
どこからコピーしてきたのか、もしくは手元が滑ったのか、なぞに空白が入っている場合は、正規表現で半角スペース、全角スペースを置換して黙って消してあげます。
全角数字を半角数字に置換する
個人的には、英数字を全角で書いちゃう人と一緒に仕事するのは苦手です。(笑)
JavaScriptで全角を半角に変換するには、英数字の全角と半角の文字コードの並びが同じことを利用して、StringクラスがもっているcharCodeAt()
で取得したUTF-16コードから65248引くと半角英数になります。
時刻を表す有効な文字列になっているかチェックする
上記の二つの処理で入力された文字列を整えた後、最後にそもそも時刻として有効かどうかをチェックします。
つまり、"12:60"分のようなありえない入力は、「エラーだよ」と教えてあげます。
時刻どうしの計算
ようやく正しいDate型の値が取得することができました。いよいよ本題です。
勤怠システムのようなものをGoogle Spreadsheetで作っているとして、開始時刻、終了時刻、休憩時間の引き算を例に説明したいと思います。
二つの時刻の引き算
こちらの記事に書いたように、"10:00"のように時刻だけを手で入力すると、実態としては"1899/12/30 10:00:00"という値が入っています。
従って、(概念として)"18:00" - "9:00"
のような引き算は、同じ1899/12/30の時刻どうしの計算ということで特になにも意識する必要はありません。
この引き算された結果はミリ秒でもっているので、これを時刻に変換する必要があります。
では、時刻に変換する際に、年月日はどうしたらよいのでしょう?
我々としては特定の年月日にするつもりはありません。単なる時間差として、セルに計算式を書いた場合と同じようにsetValue()
したいのです。
答えは、先ほど書いたように、"1899/12/30"付けの時刻にすればOKです。
三つの時刻の引き算
休憩時間を引いた労働時間を算出したい場合、「終了時刻 - 開始時刻 - 休憩時間」という三つのセルの値の引き算になりますよね。
Spreadsheet上のセルの計算式だとそのままこの計算式を組めばいいのですが、GASで書くには少しだけ工夫が必要です。
前述のように、二つのDate型を引き算した時点で、その値は実態としてはミリ秒になります。
(冒頭のObject.prototype.toString.call(diff)
でチェックすると[object Number]
が返ってくる)
従って、三つ目の引き算は、Number型からDate型を引くことになり、結果としてはまったく意図しない値が返ってくることになります。
では、どうするか?
答えは、
「休憩時間から、Google Spreadsheetの日付の起点である"1899/12/30 00:00:00"を引いてミリ秒を事前に算出しておく」です。
もしくは、「終了時刻、開始時刻、休憩時間すべてから起点"1899/12/30 00:00:00"を事前に引き算したミリ秒どうしで計算する」でもいいでしょう。
さいごに
いかがでしたでしょうか?
実際に、簡単な勤怠管理システムをSpreadsheet+GASで作成したときに出会ったトピックをまとめてみました。
そのツールでは、メニューを押すと現在時刻が打刻されたり、チャットに飛んでいったりさせました。(逆に、チャットに投げたらそれを拾ってSpreadsheetに書くこともできますが、きちんと運用できないのでやめました)
あと、それぞれのユーザが自分の行しか触れない(代理で手入力できない)、というRANGE保護を自動でかけていく処理もいれましたが、これは日付や時刻とは関係ないので別記事でTipsをまとめたいと思います。