gasとスプレッドシートの関数利用して、複数のセルの入力時間のうち最初に入力された時刻を返す

概要

先日のびばさんの振り返りオンラインMTGでの気づきを踏まえて振り返り方法を再構築中。

やり方として日々の振り返り作業(KPT / YWT / FDL / 認知行動療法)と活動記録をスプレッドシート上で行っているが、作業開始時刻・終了時刻の記録漏れなどあるので、活動内容を記録すれば自動で作業開始・終了時刻も記録できるようにしたいというモチベーション

そこで、gasとスプレッドシートの関数利用して、複数のセルの入力時間のうち最初に入力された時刻を返してみた

やったこと

1. ツール＞スクリプトエディタからsetCurrentTime()を作成
   　　Utilities.formatDate()で、TIMEVALUE()に合致するフォーマットに整形
2. 戻り値はテキストなので、TIMEVALUEで数値に変換
3. MINIFS()で最小値を出力する
4. 数値から時刻表示に戻す
   　　数値から日付に戻す関数TO_DATE()はある一方で、時刻に戻すTO_TIME()はなかったので書式設定で対応

最終的に緑セルの入力時刻に対して、青セルの最初に入力された出力時刻を返すことができた。

所感

gasを利用するとgoogleのサービスを横断できるので便利そう。 google formからメール送信を行えたり、スプレッドシート上で集計やグラフの作成など。

次やること

入力時刻をgoogleカレンダーに自動で反映させて可視化する。 課題はマイカレンダーを複数運用していて、それを指定できるかどうか。

blitzgate.co.jp

補足

setCurrentTimeのソースは以下

function setCurrentTime(value) {
  if(value){
    var now = new Date(); //現在日時を取得
    var time = Utilities.formatDate(now,'Asia/Tokyo', 'yyyy/MM/dd HH:mm:ss');//取得した現在日時を指定した表示形式に変換
    return time;
  }
}