GASで所定労働時間を算出してSpreadSheetに出力する

はじめに

このエントリは Spreadsheets/Excel Advent Calendar 2019 の記事ではないです。
https://adventar.org/calendars/4085
そっちの記事も作ってます。20日にアップします。

内容

作ろうと思った経緯

弊社フルフレックスなのですが、当月の所定労働時間が月の後半にならないとポータルにアナウンスされず、月中に今月の残業時間大丈夫かなとか、休暇取ったけど有給使う必要あるかな、残業からの埋め合わせで大丈夫かなとか色々心配になってしまうため、自分で調べられるようにしました。

アウトプット

これから掲載するコードをコピペして実行するだけで、こんな感じのスプレッドシートを出力できます(背景色とかは手動でつけてください)

画像を見ていて気がついたんですが、2020年って土曜日の祝日がないんですね。
そんな都合のいい話あるか僕のコードかグーグルのカレンダーの値がバグってるんじゃないかと思って別ソースで確認しました。
間違ってなかったです。
土曜日で祝日が消えない、オリンピックがどうとかより10000倍素晴らしい年ですね。

作るまでに考えたこと

最初、GASじゃなくて関数だけで完結させたいと思っていて、
NetWorkdays関数(土日を除いた該当月の日数を出す)とか色々を使おうと思いましたが、
祝日の処理に難儀して結局GASになりました。
※祝日の取得方法についても色々調べたのですが、GAS書いてGoogleカレンダー「日本の祝日」から取ってくるのが一番早いということになり、じゃあ全部GASで書いたほうが早いじゃんと現在の状態になりました。
IMPORTXXX系の関数使ったら取得できたのかな？とか少し思っていたりもします。
もしよりよい方法知っている方いればコメントください。

実際のコード

※コンテナバインドスクリプトで作成しています

ScheduledWorkingHours.gs

function scheduledWorkingHours(){
  //XXXXには、自分で設定したスプレッドシートのシート名を入力
  const sheet = SpreadsheetApp.getActive().getSheetByName("XXXX");
  setHeader(sheet)

  var startDate = Moment.moment("2019-12-01 00:00:00")
  var monthCount=13
  var holidaysList=[]
  const calendar = CalendarApp.getCalendarsByName('日本の祝日')[0];

  var count_cloneDate=startDate.clone()
  holidaysList=holidayList(count_cloneDate,calendar)
  sheet.getRange(2,8,holidaysList.length,4).setValues(holidaysList)  


  for (var i = 0, len = monthCount; i < len; i++) {
    var dateCount=startDate
    var param_daysInMonth=dateCount.daysInMonth()
    var param_weekendInMonth=weekendInMonth(dateCount)
    var param_validHolidayInMonth=validHolidaysInMonth(dateCount,calendar)
    var param_workingDay=param_daysInMonth-(param_weekendInMonth+param_validHolidayInMonth)
    var param_workingTime=param_workingDay*8
    sheet.getRange(2+i,1).setValue(dateCount.format("YYYYMM"))
    sheet.getRange(2+i,2).setValue(param_daysInMonth)
    sheet.getRange(2+i,3).setValue(param_weekendInMonth)
    sheet.getRange(2+i,4).setValue(param_validHolidayInMonth)
    sheet.getRange(2+i,5).setValue(param_workingDay)
    sheet.getRange(2+i,6).setValue(param_workingTime)
    dateCount.add(1,"M").format("YYYYMM")
  }
}

function setHeader(sheet){
  var list=[["対象月","総日数","土日の数","休みが増える祝日数","所定労働日数","所定労働時間","","祝日名称","日付","休みが増える","曜日"]]
  sheet.getRange(1,1,1,list[0].length).setValues(list)
}

function weekendInMonth(dateCount){
  var dateCount_clone=dateCount.clone()
  var holidays=0
  for (var i = 0, len = dateCount_clone.daysInMonth(); i < len; i++) {
    if(dateCount_clone.day()==0 || dateCount_clone.day()==6){
      Logger.log(dateCount_clone.format("YYYY-MM-DD"))
      holidays+=1
    }
   dateCount_clone.add(1,"Days")
  }
  return holidays
  //var countDayOfWeek=dateCount.daysInMonth()+dateCount.day()
  //var saturday=Math.floor(countDayOfWeek/6)
  //var sunday=Math.floor(countDayOfWeek/7)
  //return saturday+sunday
}


function validHolidaysInMonth(dateCount,calendar){
  var start_cloneDate=dateCount.clone()
  var end_cloneDate=dateCount.clone()
  var start=start_cloneDate.toDate()
  var end  =end_cloneDate.add(1,"Months").toDate()

  var events = calendar.getEvents(start,end);
  var holidayCount=0
  for (var i = 0, len = events.length; i < len; i++) {
    if(events[i].getStartTime().getDay()==0 || events[i].getStartTime().getDay()==6){
    }else{
      holidayCount++
    }
  }
  return holidayCount
}

function holidayList(dateCount,calendar) {
  var holidays=[]

  var start_cloneDate=dateCount.clone()
  var end_cloneDate=dateCount.clone()
  var start=start_cloneDate.toDate()
  var end  =end_cloneDate.add(2,"Years").toDate()

  var events = calendar.getEvents(start,end);
  for (var i = 0, len = events.length; i < len; i++) {
    var yyyy=events[i].getStartTime().getFullYear().toString()
    var mm  =events[i].getStartTime().getMonth()+1
    mm=mm.toString()
    if(mm.length===1){
      mm="0"+mm
    }
    var dd  =events[i].getStartTime().getDate().toString()
    if(dd.length===1){
      dd="0"+dd
    }
    var yyyymmdd=yyyy+mm+dd

    var dayOfWeek=dict_NumberToDayOfWeek()[events[i].getStartTime().getDay()]

    var validholiday="○"
    if(events[i].getStartTime().getDay()==0 || events[i].getStartTime().getDay()==6){validholiday="×"}
    holidayParam=[events[i].getTitle(),yyyymmdd,validholiday,dayOfWeek]
    holidays.push(holidayParam) 
  }  
  return holidays
}

おわり

これで休日数を出力することができます。(会社所定の年末年始休暇などはスプレッドシートの数値をもとにまた計算しないといけませんが)
同じようなフルフレックス勤務をされている方などが、効率的に年末年始の計画を建てる助けになれば幸いです。