Google カレンダー と Google Apps Script（スプレッドシート）で稼働計算表を作ろう！

function getCalenderEvent() {

  let sheet = getSheet();

  const CALENDAR_ID = sheet.getRange("C1").getValue();
  let myCalendar = CalendarApp.getCalendarById(CALENDAR_ID);

  const SHEETSTARTROW = 7;
  const SHEETSTARTCOL = 0;

  let lastRow = sheet.getLastRow();

  if(lastRow - SHEETSTARTROW >= 1){
    sheet.deleteRows(SHEETSTARTROW, lastRow - SHEETSTARTROW + 1);
  }

  let startDateRange = sheet.getRange("A4");
  let startDateValue = startDateRange.getValue();

  let endDateRange = sheet.getRange("B4");
  let endDateValue = endDateRange.getValue();
  console.log(startDateValue);

  let startDate = new Date(startDateValue);
  let endDate = new Date(endDateValue);
  endDate.setDate(endDate.getDate() + 1);

  let myEvent = myCalendar.getEvents(startDate, endDate);

  let sumTime = 0;

  let wordRange = 'C4';
  let searchWord = sheet.getRange(wordRange).getValue().toUpperCase();

  let eventArr = [];

  for(let i = 0 ; i < myEvent.length ; i++ ){

      let eventTitle = myEvent[i].getTitle();
      let eventTitleForSearch = eventTitle.toUpperCase()
      let startTime = myEvent[i].getStartTime();
      let endTime = myEvent[i].getEndTime();

      let timeDiff = endTime.getTime() - startTime.getTime();
      let useTime = timeDiff / (60*60*1000);

      if(eventTitleForSearch.indexOf(searchWord) > -1 && useTime !== 24){
        sumTime = sumTime + useTime;

        eventArr.push({
          'startTime' : startTime,
          'useTime' : useTime,
          'eventTitle' : eventTitle,
        });
      }

  }

  sheet.getRange("D4").setValue(sumTime);

  for(let j = 0; j < eventArr.length; j++ ){
    var setrow = j + SHEETSTARTROW;
    sheet.getRange(setrow, SHEETSTARTCOL + 1).setValue(eventArr[j]['startTime']);
    sheet.getRange(setrow, SHEETSTARTCOL + 2).setValue(eventArr[j]['useTime']);
    sheet.getRange(setrow, SHEETSTARTCOL + 3).setValue(eventArr[j]['eventTitle']);
  };

}

/*
* シートを取得する
*/
function getSheet(){
  let spreadsheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()
  return spreadsheet;
}

/*
* 日本時間に合うようにする
*/
function toJP(date){
  const TIMEZONE = 'Asia/Tokyo';
  return date.toLocaleString({ timeZone: TIMEZONE });
}