Googleカレンダーから空き日時を抽出する

Googleカレンダーから複数の対象者との打ち合わせの候補日生成するプログラムです。

「指定した時間内」で「指定した移動時間」を考慮した、全員共通の空き時間(予定のない時間)を算出します。

結果シートには「全員の共通の空き時間」と「個人の空き時間」が表示されます。

カレンダーに登録されていない対象者がいる場合はエラーになります。

スプレットシートの作成

スプレットシートを起動し、必要な情報を入力するセルを作成します。

<入力シート>に対象のメンバーにチェックを入れ、実行すると<結果シート>に全員共通の空き時間と、個人別の空き時間が出力されます。

<名簿シート>には名前とメールアドレスと記入します。

<入力シート>

<結果シート>

<名簿シート>

スプレットシートに入力された情報を抽出

getActiveSpreadsheet()はアクティブなスプレットシートから入力内容を取り込みます。

〇アクティブなシートを取得
const sheet = SpreadsheetApp.getActiveSpreadsheet();
const list = sheet.getSheetByName("シート名");

シートの取得方法にはopenById(id)やopenByUrl(url)もあります。

下記のソースでスプレットシートに入力された内容を、プログラム内に取り込みます。

  // シート情報取得
  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);  // 指定日数

分単位での時間の比較が必要なため、”/(1000*60*60*24)”で時間の変換を行っています。

スプレットシートには見やすいHH:mmで入力・表示し、比較するには分単位での形式を合わせなくてはいけないので形式の変換に気を付ける必要があります。

Googleカレンダーから予定を抽出 ⇒計算用配列に挿入

カレンダーの予定を取得するには、カレンダーの取得 ⇒イベントの取得という流れになります。

〇カレンダーIDを指定してカレンダーの取得
var cal = CalendarApp.getCalendarById( カレンダーID )

〇日付を指定してイベントの取得
var events = cal.getEventsForDay(日付)

カレンダーの取得方法にはgetCalendarsByName()もあります。

下記の記述で指定期間内 ベント情報(events)から予定の開始時間と終了時間を、それぞれ計算用の配列に入れ直します。

  /* 全員共通の空き時間出力 */ 
  // 一日ずつの予定を抽出
  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())  // 個人の予定終了時間を配列に追加
              }
            }
          }
        }

空き時間を計算

スプレットシートに入力された「時間の指定」の「開始時間」と「終了時間」の間に空き時間が算出されるように、計算用配列に挿入します。

※指定時間が「開始時間が10:00」「終了時間が16:00」の場合

配列1(開始時間)  配列2(終了時間)  どちらも時間でソート
                          10:00 ←ここに挿入
11:00           12:00 
13:00           14:00 
15:00           16:00 
15:30           18:00 
17:00←ここに挿入
      // 指定開始・終了時間を設定
      var start_time_con = start_time.substr(0,2)// 1日の指定開始時間
      var opening  = date.setHours(start_time_con, 0, 0, 0)
      var end_time_con  = end_time.substr(0,2)   // 1日の指定終了時間
      var closing =   date.setHours(end_time_con, 0, 0, 0);

      opening = date.setMinutes(start_time.substr(3,2));
      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));  //1日の開始時間
      cal_starttime.sort();

計算用の配列に挿入されている予定の開始時間と終了時間をそれぞれ比較することで、空き時間の計算を行います。

配列1(開始時間)  配列2(終了時間)  どちらも時間でソート
              10:00(指定開始時間)
11:00           12:00  
13:00           14:00  
15:00           16:00  
15:30           18:00  
17:00(指定終了時間)

処理
11:00と10:00を比較して11:00の方が大きいので10:00~11:00は空き
13:00と12:00を比較して13:00の方が大きいので12:00~13:00は空き
15:00と14:00を比較して15:00の方が大きいので14:00~15:00は空き
15:30と16:00を比較して15:30の方が小さいので空きなし
17:00と18:00を比較して17:00の方が小さいので空きなし

計算用の配列1(開始時間)と配列2(終了時間)を比較し、 見やすいフォーマットに整えてから表示用の配列(ok_time)に挿入します。

      // 開始時間と終了時間を比較して空き時間を選出 
      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)])
          }
        }
     }

祝日・曜日の設定

  • 祝日の指定

パラメータで渡された日付(date)が、入力シートに祝日のチェック(holiday)が入っている日かつ、祝日の場合はfalseを返します。

//祝日を判定してフラグを設定する
function holiFlag(date,holiday){
  var setFlag = true     
  var calendarholi = CalendarApp.getCalendarById('ja.japanese#holiday@group.v.calendar.google.com')
  var holiEvents = calendarholi.getEventsForDay(date);
  if(holiday == 0 && holiEvents.length > 0){
    setFlag = false       
  }
  return setFlag
}
  • 曜日の判定

パラメータで渡された曜日(weekNum)が入力シートの曜日のチェック(list_data[i][8])が入った曜日ならば、trueで返します。

//曜日の指定と実際の曜日を判定してフラグを設定する
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 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); //形式を(時:分)に
    }
}
  • 時間の形式変換(mm⇒HH:mm)
//時間の形式を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 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();
}

出力

「空き時間の計算」で作成した配列を空き時間をスプレットシートに出力にします。

  • 全員の共通した空き時間の出力
    //全員共通の空き時間を出力
    for (var j=3, k=0 ;k<ok_time.length;j++,k++) {
      list2.getRange(j,1).setValue(ok_time[k])
    } 
  • 出力結果

「個人の空いている時間」については、全員共通の空き時間と同じ内容を一人に絞り、
出力しております。

ソースコードのダウンロード

下記からソースコード全体をダウンロードして頂けます。