Blog

【GoogleAppScript+Moment】自動で労働時間を計算してくれるスプレッドシート

Yoshiko Hisatome
Yoshiko Hisatome プログラマ

先日第二子の育休から復帰しました久留です。

復帰してからしばらくは子供のお迎えなどでフレックス勤務の要件が満たせない可能性があるので、給与計算を時間給にしてもらいました。とってもありがたい…
※弊社は1日平均7時間以上(月単位)、コアタイムには出勤というルールのフレックス勤務です

しかし自由の効く働き方をさせてもらっていると、勤務時間管理のソフトでは時間の計算ができない事態が発生。総務の方に相談したところ、「どうせなので時給フレックスの人用の勤務表を作ってみては」という話に。せっかくなのでGoogleスプレッドシートで勤務表を作ってみました。

求める機能

  1. 出勤時間、退勤時間、休憩開始時間、休憩終了時間を元に1日の勤務時間を計算する
  2. 休憩時間は1日2回まで入力できる
  3. 月の労働時間の合計を計算して表示する
  4. 勤務が有給・半休・夏休みなどの場合にはそれに応じた勤務時間(だったはずの時間)を労働時間としてカウントする

スプレッドシートだけでできないかな…と思ったんですが、時刻の計算は出来ないみたいだったのでGAS(GoogleAppScript)でMomentを使って計算する事にしました。

↑スプレッドシートだけでもやれる方法知ってるよ、という博識な方が居たらご一報ください!

成果物

出来たものがこちら。

ダミーの勤務を入力した状態のスプレッドシートです。下は31日まで同様に続いてるだけなので割愛します。
このデータを元に計算をしてくれるGASのコードはこんな感じ

/** @OnlyCurrentDoc */

function myFunction() {
  var spreadsheet = SpreadsheetApp.getActive();
  
  let fullHolidayCount = 0; //月の有給取得回数
  let halfHolidayCount = 0; //月の半休取得回数
  let totalWorkMinuteCount = 0; //月の合計勤務時間(分単位)
  
  for(let i=8;i<=38;i++){
    
    //値を取得
    const startTime = spreadsheet.getRange('D' + i).getValue();  //出勤時刻
    const endTime = spreadsheet.getRange('E' + i).getValue(); //退勤時刻
    const startRestTime1 = spreadsheet.getRange('F' + i).getValue(); //休憩1開始時刻
    const endRestTime1 = spreadsheet.getRange('G' + i).getValue(); //休憩1終了時刻
    const startRestTime2 = spreadsheet.getRange('H' + i).getValue(); //休憩2開始時刻
    const endRestTime2 = spreadsheet.getRange('I' + i).getValue(); //休憩2終了時刻
     
    //実労働時間を計算
    const work = calcTimeToTime(startTime, endTime);
    const rest1 = calcTimeToTime(startRestTime1, endRestTime1);
    const rest2 = calcTimeToTime(startRestTime2, endRestTime2);
    const totalDayWork = work -rest1 -rest2;
    
    //実労働時間出力
    let totalWork = spreadsheet.getRange('J' + i);
    totalWork.setValue(totalDayWork);
    
    totalWorkMinuteCount += totalDayWork; //合計の実労働時間に追加
    
    //有給数えたり背景色変えたり
    const workType = spreadsheet.getRange('C' + i).getValue(); 
    let row = spreadsheet.getRange('B' + i+ ':J' + i);
    
    switch(workType){
      case '公休': 
        row.setBackground('#dddddd');
        break;
      case '特別休暇'||'有給':
        row.setBackground('#6495ed');
        fullHolidayCount++;
        break;
      case '半休':
        row.setBackground('#87cefa');
        halfHolidayCount++;
        break;
      default:
        row.setBackground("#ffffff");
        break;
    }
    // switch終わり
  }
  // for終わり
  
  // 実労働時間・有給・半休の合計を出力
  spreadsheet.getRange('D3').setValue(totalWorkMinuteCount);
  spreadsheet.getRange('D4').setValue(fullHolidayCount);
  spreadsheet.getRange('D5').setValue(halfHolidayCount);
};


// Moment使って時間を計算
function calcTimeToTime(start,end){
  if(start && end){
    const endTime = Moment.moment(end);
    const startTime = Moment.moment(start)
    return endTime.diff(startTime,'m');
  }else{
    return 0;
  }
};

実行

昨年くらいからGASの実行がスプレッドシート側からもできるようになっています。

実行!!!!

出来ました!
一番上の合計時間はスプレッドシート側で割り算の商(QUOTIENT)、余り(MOD)を使って出しています。

入社1年半にして初めてブログ更新しました。ちゃんと表示されるのかしら…

参考サイト