Google カレンダーから隙間時間のみを抽出する

Question

【環境】
マックブックPro

【したいこと】
１週間の9時から17時の間で会議が入ってない合計時間をスプレッドシートに抽出したい。
現状時間までは取得できるのですが、下記二つができません。ご教授お願い致します。

・9時から17時の間のみの指定
・数式に変更して会議以外の空き時間を数字として出す
【コード】

const CALENDAR_ID = 'xxx; //カレンダーID

function getCalendarEvents() {

  const calendar = CalendarApp.getCalendarById(CALENDAR_ID);  
  const startTime = new Date('2022/05/09 00:00:00');
  const endTime = new Date('2022/05/13 00:00:00');
   
  const events = calendar.getEvents(startTime, endTime);
  
  const values = [];
  for(const event of events){
    const record = [
      event.getTitle(),
      event.getStartTime(),
      event.getEndTime(),
       ];
    values.push(record);
  }
  
  SpreadsheetApp.getActiveSheet().getRange(2, 1, values.length, values[0].length).setValues(values);
  
}

【出力】

Comments

[k.a_teratail]

> const endTime = new Date('2022/05/13 00:00:00');
こちらに関しては、平日1週間を想定の場合は「2022/05/13 23:59:59」で設定しないと
05/13の9時〜17時のイベントが取得できませんが、問題ないでしょうか？

-----
追記
回答にコード記載したので削除します。

[k.a_teratail]

回答に記載したので、ここも削除します

Answer 1

ありがとうございます！
欲しかったデータが取れました！

Answer 2

イメージとしてはこのような感じでしょうか？
この結果になるコードは下記になります。

const CALENDAR_ID = 'xxx' //カレンダーID

function getCalendarEvents() {

  const calendar = CalendarApp.getCalendarById(CALENDAR_ID);  
  const startTime = new Date('2022/05/09 00:00:00');
  const endTime = new Date('2022/05/13 23:59:59');
   
  const events = calendar.getEvents(startTime, endTime);

  const values = [];
  for(const event of events){
    // 会議開始時間の時と分
    let startTimeHours = event.getStartTime().getHours()
    let startTimeMinutes = event.getStartTime().getMinutes()

    // 会議終了時間の時と分
    let endTimeHours = event.getEndTime().getHours()
    let endTimeMinutes = event.getEndTime().getMinutes()

    // 開始時間が9時00分より大きい場合、true
    let startTimeFlg = (startTimeHours >= 9 && startTimeMinutes >= 0) ? true : false
    // 終了時間が17時00分ちょうどの場合、true
    let endTimeFlg = (endTimeHours == 17 && endTimeMinutes == 0) ? true : false
    if (!endTimeFlg) {
      // 終了時間が16時59分以内の場合、true
      endTimeFlg = (endTimeHours <= 16 && endTimeMinutes <= 59) ? true : false
    }

    // 9時から17時のスケジュールのみ取得
    if (startTimeFlg && endTimeFlg) {

      const record = [
        event.getTitle(),
        event.getStartTime(),
        event.getEndTime()
        ];
      values.push(record);
    }
  }

  SpreadsheetApp.getActiveSheet().getRange(2, 1, values.length, values[0].length).setValues(values).setNumberFormat('yyyy/MM/dd H:mm:ss');

  // イベント時間を算出する数式設定
  let formulaRow = values.length
  let formulaCol = 1
  let formulaValue = []

  for (let i = formulaCol; i <= formulaRow; i++) {
    formulaValue.push([`=C${i+1}-B${i+1}`])
  }

  // D列に数式を記載
  SpreadsheetApp.getActiveSheet().getRange(2, 4, formulaValue.length, formulaValue[0].length).setFormulas(formulaValue).setNumberFormat('H:mm:ss')

  // 集計時間と空き時間の算出と数式設定
  // シートの最終行を取得
  let lastRow = SpreadsheetApp.getActiveSheet().getLastRow()  
  let aggreAndFreeTimeValues = []
  aggreAndFreeTimeValues.push(['="時間集計"', `=SUM(D2:D${lastRow})`])
  aggreAndFreeTimeValues.push(['="空き時間"', `=("40:00:00"-D${lastRow+1})`])

  // 最終行+1のC、D列に数式を記載
  SpreadsheetApp.getActiveSheet().getRange(lastRow + 1, 3, aggreAndFreeTimeValues.length, aggreAndFreeTimeValues[0].length).setFormulas(aggreAndFreeTimeValues).setNumberFormat('[h]:mm:ss')  
}

ご確認の方、よろしくお願いします。