Google Apps Scriptで複数人のうち誰か一人でも空いている時間を出力したい

Question

下記記事をもとに、それぞれの人の空き時間と、全員が空いている時間は出力できたのですが、少なくとも誰か一人が空いている時間の出力の仕方がわからなかったため、ご教示いただきたいです

https://www.shanaidx.com/calendar_akijikan/

function main() {
  clear()//初期化
  // シート情報取得
  const sheet = SpreadsheetApp.getActiveSpreadsheet()
  const list = sheet.getSheetByName("入力")
  const list_data  = list.getDataRange().getValues()
  const list2 = sheet.getSheetByName("結果")
  const list3 = sheet.getSheetByName("名簿")
  const list3_data = list3.getDataRange().getValues()
  const start_day   = Utilities.formatDate(list_data[2][2] , "Asia/Tokyo", "yyyy/MM/dd 00:00:00"); // 対象開始日
  const end_day     = Utilities.formatDate(list_data[2][3] , "Asia/Tokyo", "yyyy/MM/dd 00:00:00"); // 対象終了日
  const holiday     = list_data[9][8] // 祝日
  const  minAssuringMinutes = sheet.getRange("G3").getValue()  //確保したい時間（分）
  const  travelTime1        = sheet.getRange("J3").getValue()   //移動時間（前）
  const  travelTime2        = sheet.getRange("K3").getValue()   //移動時間（後）
  var    wantTimes          = minAssuringMinutes + travelTime1 + travelTime2　//必要な時間（分）
  var ok_time = [];  // 空き時間
  var add = []; // 対象アドレス
  for (var i=2, j=0; i<list_data.length; i++, j++){
    if(list_data[i][1] == 1 ){
      add.push(list3_data[j][2])
    }
  }
  
  /* 全員共通の空き時間出力 */ 
  const startdate = new Date(start_day); // 指定開始日
  const enddate = new Date(end_day);     // 指定終了日
  var day_count = (enddate - startdate) / (1000 * 60 * 60 * 24);  // 指定日数

  // 一日ずつ空き時間を計算
  for(var c=0;c<=day_count;c++){
      var start_time  = Utilities.formatDate(list_data[2][4] , "Asia/Tokyo", "HH:mm") // 指定開始時間
      var end_time    = Utilities.formatDate(list_data[2][5] , "Asia/Tokyo", "HH:mm") // 指定終了時間
      var cal_starttime = []; // 予定開始時間
      var cal_endtime = [];   // 予定終了時間

    // 一人ずつ予定を抽出  
    for(var i=0; i<add.length; i++){
      var date = new Date(Date.parse(start_day) + (c * 60 * 60 * 24 * 1000));
      var cal = CalendarApp.getCalendarById(add[i]);
      var events = cal.getEventsForDay(date)
      var setFlag = setFlag = holiFlag(date,holiday)//祝日の判定
      if(events.length > 0 ){
          if(setFlag == true){ //祝日ではないor祝日チェックなし
              for(var e=0; e<events.length; e++){// 取得した予定を配列に入力
              if (events[e].isAllDayEvent() == false) {     //終日予定は避ける
                var start1       =  events[e].getStartTime()// 予定開始時間
                var end1         =  events[e].getEndTime()  // 予定終了時間
                cal_starttime.push(start1.getTime()) // 個人の予定開始時間を配列に追加
                cal_endtime.push(end1.getTime())  // 個人の予定終了時間を配列に追加
              }
            }
          }
        }   
       
      //　指定開始・終了時間を設定
      var start_time_con = start_time.substr(0,2)// 1日の指定開始時間
      var opening  = date.setHours(start_time_con, 0, 0, 0)
      var opening = date.setMinutes(start_time.substr(3,2));
      var end_time_con  = end_time.substr(0,2)   // 1日の指定終了時間
      var closing =   date.setHours(end_time_con, 0, 0, 0);
      var closing = date.setMinutes(end_time.substr(3,2));
      
      cal_endtime.push(date.setHours(23, 59, 59, 999)); //　1日の終了時間
      cal_endtime.unshift(opening);
      cal_endtime.sort();

      cal_starttime.push(closing);  
      cal_starttime.unshift(date.setHours(0, 0, 0, 0));  //１日の開始時間
      cal_starttime.sort();


      // 開始時間と終了時間を比較して空き時間を選出　
      for (var i=0;i<cal_starttime.length-1;i++) {  
        var dateString = convert2String(cal_starttime[i+1], false);
        var week1      = new Date(cal_starttime[i+1])
        var week_add   = weekAdd(week1) 
        var weekNum = week1.getDay();
        setFlag = weekFlag(weekNum,list_data)  //曜日の指定と実際の曜日を判定してフラグを設定する   

        if(setFlag  == true){　
          if(cal_starttime[i+1] - cal_endtime[i]  >= wantTimes * 60 * 1000 && cal_starttime[i+1] != cal_endtime[i]){
            var startTime       = cal_endtime[i] + travelTime1 * 60 * 1000
            var endTime         = cal_starttime[i+1] - travelTime2 * 60 * 1000
            var startTimeString = convert2String(startTime)
            var endTimeString   = convert2String(endTime)
            ok_time.push([dateString+'(' + week_add + ')' + Utilities.formatString("%s-%s", startTimeString, endTimeString)])
          }
        }
      }
    }
    //全員共通の空き時間を出力
    for (var j=3, k=0 ;k<ok_time.length;j++,k++) {
      list2.getRange(j,1).setValue(ok_time[k])
    } 
  }

  /* 個人の空き時間出力 */
  // 一人ずつの予定
  for(var i=0; i<add.length; i++){
    var cal = CalendarApp.getCalendarById(add[i]);
    var address = add[i]   //出力の時に使用

  // 一日ずつ空き時間を計算
  for(var c=0;c<=day_count;c++){
    var events = cal.getEventsForDay(date)
    var cal_starttime = []; // 個人の予定開始時間
    var cal_endtime = [];   // 個人の予定終了時間
    var setFlag = holiFlag(date,holiday)//祝日の判定 
    if(events.length > 0 ){
        if(setFlag == true){
        // 取得した予定を配列に入力
            for(var e=0; e<events.length; e++){
              if (events[e].isAllDayEvent() == false) {     //終日予定は避ける
                var start1       =  events[e].getStartTime()// 予定開始時間
                var end1         =  events[e].getEndTime()  // 予定終了時間
                cal_starttime.push(start1.getTime()) // 個人の予定開始時間を配列に追加
                cal_endtime.push(end1.getTime())  // 個人の予定終了時間を配列に追加
            }
          }
        }
    }   
    //　指定開始・終了時間を設定
      cal_endtime.unshift(opening);
      cal_endtime.push(date.setHours(23, 59, 59, 999)); //一日の終了時間
      cal_endtime.sort();

      cal_starttime.push(closing);        
      cal_starttime.unshift(date.setHours(0, 0, 0, 0)); //一日の開始時間
      cal_starttime.sort();
     
      // 開始時間と終了時間を比較して空き時間を選出
      for (var l=0;l<cal_starttime.length-1;l++) {      
        var dateString = convert2String(cal_starttime[l+1], false);
        var week1      = new Date(cal_starttime[l+1])
        var week_add   = weekAdd(week1) 
        var weekNum = week1.getDay();
        setFlag = weekFlag(weekNum,list_data)//曜日の指定と実際の曜日を判定してフラグを設定する
    
        if(setFlag  == true){　
          if(cal_starttime[l+1] - cal_endtime[l]  >= wantTimes * 60 * 1000 && cal_starttime[l+1] != cal_endtime[l]){
            var startTime       = cal_endtime[l] + travelTime1 * 60 * 1000
            var endTime         = cal_starttime[l+1] - travelTime2 * 60 * 1000
            var startTimeString = convert2String(startTime)
            var endTimeString   = convert2String(endTime)
            ok_time.push([dateString+'(' + week_add + ')' + Utilities.formatString("%s-%s", startTimeString, endTimeString)])
          }
        }
      }
    }
    //個人の出力処理
    var empty = [];
    var name  = nameAdd(address)
    ok_time.unshift(name) //アドレスを追加
    for (var n=2,p=i+2,q=0;q<ok_time.length;n++,q++){
      list2.getRange(n,p).setValue(ok_time[q])
    }
     ok_time = empty  //配列を空にする
  }
}
/*
 *　共通処理
 */
//形式を変換する
function convert2String(date, withTime) {                    
    if (typeof withTime === "undefined") withTime = true;    //withTimeが初期値の場合、trueを入れる
    　var dateTime = new Date(date);    //日付取得
  　if (!withTime) {                    //withTimeに何も入ってなかったら
        var month = dateTime.getMonth()+1;   //月 + 1
        var day = dateTime.getDate();        //日 

        return Utilities.formatString("%d/%d", month, day);　　//形式を(月/日)に
    } else {
        var hours = dateTime.getHours();　　//時
        var minutes = padding(dateTime.getMinutes());　//分
        return Utilities.formatString("%d:%s", hours, minutes);　//形式を(時：分)に
    }
}

//時間の形式を00：00とする
function padding(num) {                       
  return ('00' + num).slice(-2)
}

//曜日の表示を配列に入れる
function weekAdd(week1) {
  const week_list = new Array('日', '月', '火', '水', '木', '金', '土');//曜日の配列
  const weekNum = week1.getDay();  // 曜日を表す数値
  const week = week_list[weekNum];// 曜日取得
  return(week)
}

//曜日の指定と実際の曜日を判定してフラグを設定する
function weekFlag(weekNum,list_data) {
  var setFlag = true
  for(i=1,j=0;i<9,j<7;i++,j++)
  if(list_data[i][8] == 0 && weekNum == j){
    setFlag   = false
    break
  }
  return setFlag
}

//祝日を判定してフラグを設定する
function holiFlag(date,holiday){
  var setFlag = true     
  var calendarholi = CalendarApp.getCalendarById('ja.japanese#[e-mail]')
  var holiEvents = calendarholi.getEventsForDay(date);
  if(holiday == 0 && holiEvents.length > 0){
    setFlag = false                        //フラグをfalseにする
  }
  return setFlag
}

//名簿から名前を取得
function nameAdd(address) {
  const sheet = SpreadsheetApp.getActiveSpreadsheet();
  const list3 = sheet.getSheetByName("名簿");
  let   list3_data  = list3.getDataRange().getValues();

  for (a in list3_data) {
    if(address == list3_data[a][2]) {
　　　　var fullname = (list3_data[a][0]+'　'+list3_data[a][1])
    }
  }

  if(fullname == ''){
    fullname = address
  }
  return fullname;
}

/*
 *　クリア
 */
//結果出力欄のクリア
function clear() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet();
  const list3 = sheet.getSheetByName("結果");
  
  var range = list3.getRange("A2:Z50");
  range.clearContent();

}

//入力チェック欄のクリア
function clear_check() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet();
  const list = sheet.getSheetByName("入力");
  
  var range = list.getRange("B3:B25");
  range.clearContent();

}
/*
*　バーの追加
*/
function onOpen(event) {
  var ui     = SpreadsheetApp.getUi()
  var menu   = ui.createMenu('空き時間の算出')

  menu.addItem('空き時間の算出', 'main')　// 空き時間算出処理
  menu.addItem('結果のクリア', 'clear')　// 空き時間算出処理

  menu.addToUi()
}

Comments

[退会済みユーザー]

「それぞれの人の空き時間」が出力できているのならば、それら自体が「少なくとも誰か一人が空いている時間」に該当しています。
データとしては「それぞれの人の空き時間」を列挙すれば十分のはずです。

具体的に質問者さんは、「少なくとも誰か一人が空いている時間」をどう表現したいのでしょうか？

たとえば
Aさんの空き時間：　10:00~11:00
Bさんの空き時間：　10:30~12:00
Cさんの空き時間：　10:50~15:00
となっているとき、

・「全員の空き時間」は「10:50~11:00」です。

・一方、「少なくとも誰か一人が空いている時間」は
10:00~11:00、10:30~12:00、10:50~15:00
ということもできます。

しかし、これらを併合して、つまり「重なっている部分は除外してつなげて」
「10:00～15:00」
と変換したい、ということでしょうか？

[rutsubo]

見ていただき、ありがとうございます。
言葉足らずで申し訳ございません、qnoir様のご認識の通りです。

背景を記載いたしますと、先方との日程調整を行うにあたり、4人のうちの誰かが対応できれば良いため、少なくとも一人が空いている日程をコピペで送ることができるように、重複を除いてスプレッドシートに出力したいと考えています。

その他ご不明点ございましたら、何なりとお申し付けください。

[退会済みユーザー]

回答しましたが、まだ不明な点があります。
各担当者の途中での交代は許容されている、という理解でよろしいのでしょうか。

というのは、たとえば、
・Ａさんの空き時間が 10:30～11:00
・Ｂさんの空き時間が 11:00～11:30
・確保したい時間が25分
・移動時間は0
と仮定した場合、
質問者さんの先のコメントの意図通りに回答するならば 「誰か１人以上空いている時間帯」は「10:30～11:30」となります。（回答プログラムもそのようにしています）

ここで、「少なくとも一人が空いている日程」を「10:30～11:30」というように案内しまって、本当に大丈夫なのでしょうか？

得られた「10:30～11:30」というのは、あくまでも「誰か１人以上空き時間となっている時間帯の集合から重なりを除外してつなげた表現」に過ぎませんので
「10:30～11:30」の範囲内であれば誰か1人が必ず対応できるというわけではありません。

途中で交代が許容されていないのであれば、たとえば「10:50~11:15」 の会議には誰も対応できません。

途中での交代が許容されていない場合、対応可能な時間帯を「10:30～11:00と11:00～11:30」として案内するというのが適切かと思います。
これは冒頭で申し上げた通り、各人の空き時間の列挙で代替できます。

Answer 1

「正常に、各人の空き時間が取得できている」とのことですので、その前提で下記を付け加えます。

(※下記は、変更をなるべく最小限にしたものであり、その他諸々は、すべて元のコードに拠ります。
元のコードは動作確認していません。
あくまで「正常に、各人の空き時間が取得できている」という前提で、考え方だけ記載したものです）

まず、全員分の空き時間を集約するための配列を用意します。

(前半略）
+ let all_freetimes = [];  // 全員分の空き時間を集約するための配列
  /* 個人の空き時間出力 */
  // 一人ずつの予定
  for(var i=0; i<add.length; i++){
......
...
以下略

個人の空き時間を計算する処理の所で、この配列に計算結果(開始時刻、終了時刻）を取り込んでいきます。

......
...
        setFlag = weekFlag(weekNum,list_data)//曜日の指定と実際の曜日を判定してフラグを設定する
    
        if(setFlag  == true){　
          if(cal_starttime[l+1] - cal_endtime[l]  >= wantTimes * 60 * 1000 && cal_starttime[l+1] != cal_endtime[l]){
            var startTime       = cal_endtime[l] + travelTime1 * 60 * 1000
            var endTime         = cal_starttime[l+1] - travelTime2 * 60 * 1000
+           all_freetimes.push([startTime, endTime]);     // 追加
            var startTimeString = convert2String(startTime)
            var endTimeString   = convert2String(endTime)
            ok_time.push([dateString+'(' + week_add + ')' + Utilities.formatString("%s-%s", startTimeString, endTimeString)])
          }
        }
      }
    }
    //個人の出力処理
......
...
（以下略）

このようにすることで、 all_freetimesは最終的に
[[開始時間、終了時間], [開始時間、終了時間], [開始時間、終了時間],...]
のように「全員分の空き時間が入った2次元配列」になります。

あとは各[開始時間、終了時間]の区間について、他の区間と重なる部分をひとつづきにすればよい（マージする）、ということになります。

次のような関数を用意します。（上記のような2次元配列を受け取り、重なる区間をひとつづきにした2次元配列を返す関数）

/*
* times: 2次元配列 
*  　[[開始時間、終了時間], [開始時間、終了時間], [開始時間、終了時間],...]
*  　　（各時間は整数のタイムスタンプ）
*  返り値：重なる区間ををマージした [[開始時間、終了時間],...]の2次元配列
*/
function mergeTimes(times) {
  if (!times || times.length === 0) return [];
  times.sort((a, b) => a[0] - b[0]);
  const result = [];
  let start = times[0][0]
  let end = times[0][1];
  for (let i = 1; i < times.length; i++) {
    if (end < times[i][0]) {
      result.push([start, end]);
      [start, end] = times[i];
      continue;
    }
    if (end < times[i][1]) {
      end = times[i][1];
    }
  }
  result.push([start, end])
  return result;
}

あとは、main関数の末尾に、出力処理を付け加えて完成となるはずです。

(略）
......
...
     ok_time = empty  //配列を空にする
  }

  /*------------------------------- ここから追加------------------------------*/
  // ※ all_freetimes変数が絡む部分以外、
  // 質問文に記載されている出力処理のコードをほぼそのまま使用。
  // 最適化や整理はしていない。
  all_freetimes = mergeTimes(all_freetimes);
  
  ok_time.unshift('誰か一人でも空いている時間');

  for (var l = 0; l < all_freetimes.length; l++) {
    var dateString = convert2String(all_freetimes[l][0], false);
    var week1 = new Date(all_freetimes[l][0])
    var week_add = weekAdd(week1) 
    var weekNum = week1.getDay();
    //曜日の指定と実際の曜日を判定してフラグを設定する
    setFlag = weekFlag(weekNum, nyuuryoku_data)
    if (setFlag == true) {
      var startTimeString = convert2String(all_freetimes[l][0]);
      var endTimeString = convert2String(all_freetimes[l][1]);
      ok_time.push([dateString + '(' + week_add + ')' + Utilities.formatString("%s-%s", startTimeString, endTimeString)])
    }
  }
  // 結果シートに書き込む。
  for (var n = 2, p = list2.getLastColumn()+1, q = 0; q < ok_time.length; n++, q++) {
    list2.getRange(n, p).setValue(ok_time[q]);
  }

  /*------------------------------- 追加ここまで------------------------------*/

}   // main関数の末尾。

/*
 *　共通処理
 */
//形式を変換する
function convert2String(date, withTime) {  
......
...
（以下略）

---

補足

なお、上記はコメントでのヒヤリングに基づいていますが、このコードで得られる時間帯は、あくまでも「各人の空き時間の集合を併合した表現」に過ぎません。
「得られた時間帯の範囲内であればいつでも誰か1人以上対応できる」というわけではないことに注意が必要です。
（以下は「途中での交代が許容されていない」というのが前提です。途中での交代がＯＫならばこの限りではありません）

たとえば、
・Ａさんの空き時間が 10:30～11:00
・Ｂさんの空き時間が 11:00～11:30
・確保したい時間が25分
・移動時間は0
と仮定した場合、
上記のプログラムでは 「誰か１人以上空いている時間帯」は「10:30～11:30」と表示されます。

しかし、「業務がこの『10:30～11:30』の範囲内にあれば、必ず誰か1人以上が対応できる」というわけではありません。
（途中交代が許容されていない場合）たとえば、「10:50~11:15」 の業務には、誰も対応できません。