スプレッドシートの予約情報をGoogleカレンダーに自動登録する方法【コピペでOK】

/**カレンダー登録の確認工程を追加*/
function confirm() {

  let msg = Browser.msgBox('確認メッセージ','カレンダー登録を実行します。よろしいですか？',Browser.Buttons.OK_CANCEL);

  if (msg == 'ok') {
    createSchedule()
  }
  else{
    Browser.msgBox('キャンセルされました');
  }
}


/**
 * カレンダーに予定を作成する
 * 
 *参考資料
 *  https://technical.verybestcbp.com/afterdaytime/
 */
function createSchedule() {
 
  // 連携するアカウント
  //const gAccount = "〇〇〇〇@gmail.com";  // ★★ここに連携するグーグルアカウントを入れる
  // 連携するカレンダーIDをセット
  const CalenderID = "@〇〇〇〇〇〇〇〇group.calendar.google.com"// ★★ここに連携するカレンダーのIDを入れる

     // 読み取り範囲（表の始まり行と終わり列）
  const topRow = 6;
    const lastCol = 40;


  //0始まりで列を指定しておく
  oheyakimeCellNum = 1;
  kaitoCellNum = 2;
  statusCellNum = 3;
  None4CellNum = 4;
  None5CellNum = 5;
  yoyakubiCellNum = 6;
  memoCellNum = 7;
  tokkiCellNum = 8;
  yoyakunoCellNum = 9;
  None10CellNum = 10;
  inCellNum = 11;
  outCellNum = 12;
  hakusuCellNum = 13;
  startCellNum = 14;
  endCellNum = 15;
  arrivaltimeCellNum = 16;
  oheyaCellNum = 17;
  nameCellNum = 18;
  nendaiCellNum = 19;
  zenninzuCellNum = 20;
  menCellNum = 21;
  womenCellNum = 22;
  numchildCellNum = 23;
  agechildCellNum = 24;
  mealCellNum = 25;
  massegeCellNum = 26;
  jpmassegeCellNum = 27;
  prefeCellNum = 28;
  countryCellNum = 29;
  addressCellNum = 30;
  telnoCellNum = 31;
  emailCellNum = 32;
  heyatitleCellNum = 33;
  tokkititleCellNum = 34;
  None35CellNum = 35;
  None36CellNum = 36;
  None37CellNum = 37;
  childtitleCellNum = 38;
  ninzumatomeCellNum = 39;


  // 今開いているシートを取得
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
 
  // シートの最終行を取得
  var lastRow = sheet.getLastRow();
  
  //シートの一覧を取得
  var contents = sheet.getRange(topRow, 1, sheet.getLastRow(), lastCol).getValues();
 
  // googleカレンダーの取得
  //var calender = CalendarApp.getCalendarById(gAccount);
  var calender = CalendarApp.getCalendarById(CalenderID)
 
  //順に予定を作成（今回は正しい値が来ることを想定）
  //先頭行から最後の行までを繰り返す
  for (i = 0; i <= lastRow - topRow; i++) {
 
    //カレンダー入力欄で「済」や「未」の場合は飛ばす
    var status = contents[i][statusCellNum];
    if (
      status == "済" ||
      status == "済み" ||
      status == "未" ||
      contents[i][inCellNum] == ""
    ) {
      continue;
    }

    // //回答状況が「OK」でなければとばす」
    // var status = contents[i][kaitoCellNum];
    // if (
    //   status !=  "OK"||
    //   contents[i][inCellNum] == ""
    // ) {
    //   continue;
    // } 
    
     //回答状況が「OK」でなければカレンダー入力はしない。もし回答状況が「NG」ならCal入力欄に「NG」をいれる。
     var kaito = contents[i][kaitoCellNum];
    if (
      kaito == "NG"
    ) {
     sheet.getRange(topRow + i, 4).setValue("NG");
      continue;
    }   
    else if(
    	kaito !=  "OK"||
       contents[i][inCellNum] == ""
     ) {
       continue;
     }   
 
    // 値をセット 日時はフォーマットして保持
    var inday = new Date(contents[i][inCellNum]);
    var outday = new Date(contents[i][outCellNum]);
    var startTime = contents[i][startCellNum];
    var endTime = contents[i][endCellNum];

    // タイトルをセット
    var title = contents[i][heyatitleCellNum] + contents[i][nameCellNum] +contents[i][ninzumatomeCellNum]+contents[i][tokkititleCellNum];



     // 日時の表示形式をフォーマット
   var ckin = Utilities.formatDate(contents[i][inCellNum], 'JST', 'yy-MM-dd');
   var ckout = Utilities.formatDate(contents[i][outCellNum], 'JST', 'yy-MM-dd');
   var avtime = Utilities.formatDate(contents[i][arrivaltimeCellNum], 'JST', 'HH:MM');
   var yoyakubi = Utilities.formatDate(contents[i][yoyakubiCellNum], 'JST', 'yy-MM-dd');
   

    // 場所と詳細をセット
    var options = {description:
      '【予約No.】'+contents[i][yoyakunoCellNum]+'\n'
      +'【イン】'+ ckin +'【アウト】'+ ckout +'\n'　// 改行('\n')
      +'【到着時刻】'+ avtime +'\n'
      +'【部屋希望】'+contents[i][oheyaCellNum]+'\n'
      +'【部屋回答】'+contents[i][oheyakimeCellNum]+'\n'
      +'【男】'+contents[i][menCellNum]+'【女】'+ contents[i][womenCellNum]
      +'【年代】'+ contents[i][nendaiCellNum] +'\n'
      +'【子供数】'+contents[i][numchildCellNum]+'\n'
     +'【子供年齢】'+contents[i][agechildCellNum]+'\n'
 　　  +'【食事】'+contents[i][mealCellNum]+'\n'
      +'【メール】'+contents[i][emailCellNum]+'\n'
      +'【電話】'+contents[i][telnoCellNum]+'\n'
      +'【都道府県/国】'+contents[i][prefeCellNum]+contents[i][countryCellNum]+'\n'
      +'【住所】'+contents[i][addressCellNum]+'\n'
      +'【要望等】'+'\n'
      +contents[i][massegeCellNum]+'\n'
      +contents[i][jpmassegeCellNum]+'\n'
      +'【特記事項】'+contents[i][tokkiCellNum]+'\n'
     +'【予約日】'+ yoyakubi +'\n'
    +'【メモ】'+ contents[i][memoCellNum] +'\n'
    };
    
    try {
      // １泊ならチェックイン日の全日予定を作成
      if (contents[i][hakusuCellNum]<=1) {
        //予定を作成
        calender.createAllDayEvent(
          title,
          new Date(inday),
          options
        );
        
      // 開始終了時間があれば範囲で設定
      } else {
        // 開始日時をフォーマット
        var startDate = new Date(inday);
        //startDate.setHours(startTime.getHours())
        //startDate.setMinutes(startTime.getMinutes());
        // 終了日時をフォーマット
        var endDate = new Date(outday);
        //endDate.setHours(endTime.getHours())
        //endDate.setMinutes(endTime.getMinutes());
        // 予定を作成
        calender.createEvent(
          title,
          startDate,
          endDate,
          options
        );
      }
 
      //無事に予定が作成されたら毛レンダー入力欄を「済」にする
      sheet.getRange(topRow + i, 4).setValue("済");
 
    // エラーの場合（今回はログ出力のみ）
    } catch(e) {
      Logger.log(e);
    }
    
  }
  // ブラウザへ完了通知
  Browser.msgBox("登録完了\\n※「回答状況」欄が「OK」かつ、「Cal入力」欄が空欄の件のみ。");
}