動的 Google フォーム！- Google カレンダー を使わない！！三者面談予約 Ver.3 & Ver.4

const form = FormApp.getActiveForm();
const spreadSheet = SpreadsheetApp.openById('');//ここは、スプレッドシートのIDを入れる
const reserveSheet = spreadSheet.getSheetByName('予約');
const listSheet = spreadSheet.getSheetByName('日程');
const calender = spreadSheet.getSheetByName('教師カレンダー');

// *--------------------*
//   フォーム送信時の処理
// *--------------------*
function receivedApplication(e) {

  // フォームの送信内容
  const email = e.response.getRespondentEmail();
  const items = e.response.getItemResponses();
  const preferredDate = (items[0].getItem().getTitle() === "希望日時" ? items[0].getResponse() : "");
  let result = "";

  if (preferredDate) {
    // 日程シートチェック＆更新
    result = checkAvailability(preferredDate);
    // 予約シートに申込内容を書き込み
    if (result = "OK") { reserveSheet.appendRow([email, preferredDate]); }
    // フォームを更新
    editForm();
  } else {
    result = "NG";
  }

  // メール送信
  sendEmail(email, preferredDate, result);

}

// *---------------------*
//   予約状況をチェックする
// *---------------------*
function checkAvailability(preferredDate) {

  // listシートを配列に格納
  const list = listSheet.getDataRange().getValues();
  list.shift();

  // リストから予約日を探し、定員を確認する
  for (let i = 0; i < list.length; i++) {
    if (list[i][0] == preferredDate) {
      // 予約済 < 定員であればOK
      if (list[i][2] < list[i][1]) {
        listSheet.getRange(i + 2, 3).setValue(list[i][2] + 1);

        // 回答から日付と時間帯を取得する例
        var data = calender.getDataRange().getValues();
        var dateAndTime = preferredDate.split(" ");
        var date = dateAndTime[0];
        var time = dateAndTime[1];
        Logger.log(date);
        Logger.log(time);
        //該当の日時を検索して、そのカラム番号、列番号を取得
        for (var k = 0; k < data.length; k++) {
          for (var j = 0; j < data[k].length; j++) {
            if (data[k][j] == date) {
              dataColumn = j + 1;
            }
            else if (data[k][j] == time) {
              dataRow = k + 1;
            }
          }
        }

        calender.getRange(dataRow, dataColumn).setValue('予約あり');

        return "OK";
      } else {
        return "NG";
      }
    }
  }

  // 希望日がリストに存在しなかった場合はNG
  return "NG";

}

// *----------------------*
//   予約結果をメール送信する
// *----------------------*
function sendEmail(email, preferredDate, result) {

  const mailTitle = "予約結果について";
  let mailBody;

  if (result == "OK") {
    mailBody = "予約が完了しました。\n"
      + `予約日：${preferredDate}`
      + '\n\nよろしくお願いします。'
  } else {
    mailBody = "定員超過のため予約できませんでした。\n"
      + "下記のフォームから再度申請してください\n"
      + form.getPublishedUrl();
  }

  // 結果メール送信
  GmailApp.sendEmail(email, mailTitle, mailBody);

}

// *-----------------*
//   フォームを更新する
// *-----------------*
function editForm() {

  let infoText = "";      // 「空き状況」部分のテキスト
  let choiceValues = [];  // 「参加希望日」部分の選択肢

  // 日程シートを配列に格納
  const list = listSheet.getDataRange().getValues();
  list.shift();

  // 日程シートのデータからフォームの内容を作成する
  for (const record of list) {
    if (record[2] < record[1]) {
      // 空きがある日程は「空き状況」に記載＋選択肢として設定
      infoText += `${record[0]} ： 残り ${record[1] - record[2]} 名\n`;
      choiceValues.push(record[0]);
    } else {
      // 空きがない日程は「空き状況」に満員を記載＋選択肢にはしない
      infoText += `${record[0]} ： 満員（申込不可）\n`;
    }
  }

  // 選択肢が１つもない場合、選択肢は「全日程申込不可」としフォームをクローズ
  if (!choiceValues.length) {
    choiceValues.push("全日程申込不可");
    form.setAcceptingResponses(false);
  }

  // フォームに変更を反映
  const items = form.getItems();
  items[0].setHelpText(infoText);
  items[1].asListItem().setChoiceValues(choiceValues);

}

const spreadSheet = SpreadsheetApp.openById('');//ここは、スプレッドシートのIDを入れる

function onOpen() {
  SpreadsheetApp.getActiveSpreadsheet().addMenu('リセットメニュー', [
    { name: 'フォームの選択肢をリセット', functionName: 'updateFormList' },
    { name: 'フォーム内の回答全削除', functionName: 'alldelete' },
    { name: '日程・予約シートのリセット', functionName: 'cellrst' },
  ]);
}

function updateFormList() {
  //フォームの質問番号
  var questionNo = 2;
  //フォームID
  var formId = '';//フォームのIDを入れます
  //シートID
  var sheetId = '';//このスプレッドシートのIDを入れます。
  //シート名
  var sheetName = '日程';
  //データ配置列名
  var colName = 'A';
  //データ開始行
  var rowNum = 2;

  //フォームの取得
  var form = FormApp.openById(formId);
  //全質問項目を取得
  var items = form.getItems();
  //questionNoで指定した質問項目を指定（配列は0から始まるので-1）
  var item = items[questionNo - 1];

  //スプレッドシートを取得
  var sheet = SpreadsheetApp.openById(sheetId).getSheetByName(sheetName);
  //シートの最終行を取得
  var lastRow = sheet.getLastRow();

  //リスト内容の取得
  var arrList = sheet.getRange(colName + rowNum + ':' + colName + lastRow).getValues();


  let infoText = "";      // 「空き状況」部分のテキスト

  // 日程シートを配列に格納
  const list = sheet.getDataRange().getValues();
  list.shift();

  // 日程シートのデータからフォームの内容を作成する
  for (const record of list) {
    // 空きがある日程は「空き状況」に記載＋選択肢として設定
    infoText += `${record[0]} ： 残り 1 名\n`;
  }

  //選択肢をフォームに反映
  items[0].setHelpText(infoText);
  items[1].asListItem().setChoiceValues(arrList);
}

function alldelete() {

  var Form = FormApp.openByUrl('');//フォームの編集画面の時のURLを入れます。
  Form.deleteAllResponses();
}

function cellrst() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sht = ss.getSheetByName('日程');
  var sht2 = ss.getSheetByName('予約');
  var sht3 = ss.getSheetByName('教師カレンダー');
  var lastrow = sht.getLastRow();
  var lastrow2 = sht2.getLastRow();
  var lastrow3 = sht3.getLastRow();
  var lastcolumn3 = sht3.getLastColumn();
  for (let i = 2; i <= lastrow; i++) {
    sht.getRange(i, 3).setValue('0');
  }
  sht2.getRange(2, 1, lastrow2, 2).clearContent();//１行目、５列目から、、、１行目３列目までを削除
  //スクリプトに紐づいたスプレッドシートのアクティブなシートを読み込み
  sht3.getRange(2, 2, lastrow3, lastcolumn3).clearContent();

}

  //フォームID
  var formId = '';//フォームのIDを入れます
  //シートID
  var sheetId = '';//このスプレッドシートのIDを入れます。

  var Form = FormApp.openByUrl('');//フォームの編集画面の時のURLを入れます。

const form = FormApp.getActiveForm();
const spreadSheet = SpreadsheetApp.openById("");
const reserveSheet = spreadSheet.getSheetByName("予約");
const listSheet = spreadSheet.getSheetByName("日程");
const calender = spreadSheet.getSheetByName("教師カレンダー");
//保護者カレンダーを作りたい時↓
const sheet = SpreadsheetApp.openById('').getSheetByName('保護者カレンダー');

// *--------------------*
//   フォーム送信時の処理
// *--------------------*
function receivedApplication(e) {

  // フォームの送信内容
  const email = e.response.getRespondentEmail();
  const items = e.response.getItemResponses();
  const preferredDate = (items[0].getItem().getTitle() === "希望日時" ? items[0].getResponse() : "");
  let result = "";
  const name = items[3].getResponse();

  if (preferredDate) {
    // 日程シートチェック＆更新
    result = checkAvailability(preferredDate, name);
    // 予約シートに申込内容を書き込み
    if (result = "OK") { reserveSheet.appendRow([email, preferredDate]); }
    // フォームを更新
    editForm();
  } else {
    result = "NG";
  }

  // メール送信
  sendEmail(email, preferredDate, result);

}

// *---------------------*
//   予約状況をチェックする
// *---------------------*
function checkAvailability(preferredDate, name) {

  // listシートを配列に格納
  const list = listSheet.getDataRange().getValues();
  list.shift();

  // リストから予約日を探し、定員を確認する
  for (let i = 0; i < list.length; i++) {
    if (list[i][0] == preferredDate) {
      // 予約済 < 定員であればOK
      if (list[i][2] < list[i][1]) {
        listSheet.getRange(i + 2, 3).setValue(list[i][2] + 1);

        // 回答から日付と時間帯を取得する例
        var data = calender.getDataRange().getValues();
        var dateAndTime = preferredDate.split(" ");
        var date = dateAndTime[0];
        var time = dateAndTime[1];
        Logger.log(date);
        Logger.log(time);
        //該当の日時を検索して、そのカラム番号、列番号を取得
        for (var k = 0; k < data.length; k++) {
          for (var j = 0; j < data[k].length; j++) {
            if (data[k][j] == date) {
              dataColumn = j + 1;
            }
            else if (data[k][j] == time) {
              dataRow = k + 1;
            }
          }
        }

        calender.getRange(dataRow, dataColumn).setValue(name);
        sheet.getRange(dataRow, dataColumn).setValue('予約あり');

        return "OK";
      } else {
        return "NG";
      }
    }
  }

  // 希望日がリストに存在しなかった場合はNG
  return "NG";

}

// *----------------------*
//   予約結果をメール送信する
// *----------------------*
function sendEmail(email, preferredDate, result) {

  const mailTitle = "予約結果について";
  let mailBody;

  if (result == "OK") {
    mailBody = "予約が完了しました。\n"
      + `予約日：${preferredDate}`
      + '\n\nよろしくお願いします。'
  } else {
    mailBody = "定員超過のため予約できませんでした。\n"
      + "下記のフォームから再度申請してください\n"
      + form.getPublishedUrl();
  }

  // 結果メール送信
  GmailApp.sendEmail(email, mailTitle, mailBody);

}

// *-----------------*
//   フォームを更新する
// *-----------------*
function editForm() {

  let infoText = "";      // 「空き状況」部分のテキスト
  let choiceValues = [];  // 「参加希望日」部分の選択肢

  // 日程シートを配列に格納
  const list = listSheet.getDataRange().getValues();
  list.shift();

  // 日程シートのデータからフォームの内容を作成する
  for (const record of list) {
    if (record[2] < record[1]) {
      // 空きがある日程は「空き状況」に記載＋選択肢として設定
      infoText += `${record[0]} ： 残り ${record[1] - record[2]} 名\n`;
      choiceValues.push(record[0]);
    } else {
      // 空きがない日程は「空き状況」に満員を記載＋選択肢にはしない
      infoText += `${record[0]} ： 満員（申込不可）\n`;
    }
  }

  // 選択肢が１つもない場合、選択肢は「全日程申込不可」としフォームをクローズ
  if (!choiceValues.length) {
    choiceValues.push("全日程申込不可");
    form.setAcceptingResponses(false);
  }

  // フォームに変更を反映
  const items = form.getItems();
  items[0].setHelpText(infoText);
  items[1].asListItem().setChoiceValues(choiceValues);

}