１．目的

Googleフォームとスプレッドシートを利用して、フォームで時間枠の予約を受け付ける仕組みを作ります。予約が入った時間枠はフォームの選択肢からはずれます。

スプレッドシートのコンテナバインドプロジェクトで、Google Apps Scriptにより、時間枠選択肢の更新をします。

２．フォーム

フォームでは、「希望日時」という質問で、予約可能な年月日時間（時間枠）がドロップダウンリストで表示され、選択して回答してもらいます。回答が送信されると、ドロップダウンリストが更新され、予約が入ったは時間枠は選択肢は表示からはずれます。

フォームの作成時には、「希望日時」というタイトルの質問を作り、形式はドロップダウンで、最初は選択肢は一つでも良いので適当に（日時である必要なし）作っておいてください。他の質問も、適宜作成してください。この例では「希望日時」は3番目の質問にしています。

回答はスプレッドシートにリンクしておいてください。

３．スプレッドシート

フォームにリンクしたスプレッドシートに、回答を記録するシートとは別に「list」というシートを作り、これにドロップダウンリストのデータを作成します。年、月、日、時、分をB〜F列に入力し、G列でそれを結合して年月日時分を表示しています。G列が、ドロップダウンリストの選択肢に使われます。データ形式は式・関数のままでよいです。H列は数値で、最初は0を入れておきます。1行目は項目名、2行目は記入例としているので、実際に使用する年月日日時等のデータは3行目から入力します。

４．スクリプト

スプレッドシートのコンテナバインドプロジェクトです。
はじめの部分のフォームとスプレッドシートのID、id_formとid_sheetsは実際に使用するものにしてください。

関数newlistは、シートlistに作成したデータを基にフォームの「希望時間」の選択肢を更新します。listの準備ができたら、最初にこれを実行してドロップダウンリストを作成してください。H列が0のデータが選択肢に使われます。

関数onFormSubmitは、フォームで送信された「希望時間」によりlistを変更し、フォームの選択肢を更新します。listの変更では、送信された「希望時間」と値の一致するシートlistの希望時間（G列）の行の、H列数値に1プラスします。const num = 3は、e.valuesでフォーム回答を取得するとき、「希望時間」の回答が入っている位置です。0始まりで、0にはタイムスタンプが入り、「希望時間」が3番目の質問なので、3です。

関数の最後で、newlistを実行して変更後のlistをもとにフォームの選択肢を更新します。newlistではH列が0のものだけが使用されるので、回答にあった希望時間は除かれていきます。

関数onFormSubmitは、トリガーの設定で、フォームの送信時に実行するよう設定してください。

yoyaku.gs

// 実際に使用するフォームとスプレッドシートのIDをセットすること
const id_form = 'ID';
const id_sheets = 'ID';

// フォームのドロップダウンリストを更新する /////
function newlist() {
  //スプレッドシート
  const mysheets = SpreadsheetApp.openById(id_sheets);
  // シート名 list にドロップダウンリスト用データがある
  const mysheet1name = 'list';
  const mysheet1 = mysheets.getSheetByName(mysheet1name);

  // データの範囲
  const row1 = 3; //データ開始行（3列目）
  const row2 = mysheet1.getLastRow(); //最後の行は何行目か
  const datanum = row2 - row1 + 1; //データの行数
  const col1 = 1; //データ開始列
  const col2 = 8; //データの列数
  // data取得
  const mydata = mysheet1.getRange(row1, col1, datanum, col2).getValues();
  
  //ドロップダウンリストの選択肢を作成
  let options = []; //選択
  for (let i = 0;i < datanum;i++) {
    let row = mydata[i];
    if (row[7] === 0) {
      let option = row[6];
      options.push(option);
    }
  }

  // Google フォームを開く
  const myform = FormApp.openById(id_form); 
  // 更新したい質問のタイトルは
  const mytitle = '希望日時';
  // フォームの全質問を取得
  const items = myform.getItems();
  // 質問を探す
  let targetItem = null; 
  for (let i = 0; i < items.length; i++) {
    let item = items[i];   
    if (item.getTitle() === mytitle) {
      targetItem = item.asListItem(); //ドロップダウンリストを取得
      break;
    }
  }
  
  if (targetItem) {
    // ドロップダウンリストの選択肢を更新
    targetItem.setChoiceValues(options);
  } else {
    Logger.log('更新する質問が見つかりませんでした。');
  } 
}
///// newlistここまで //

// フォームの回答が送られたとき、スプレッドシートを変更しフォームの選択肢を更新 /////
function onFormSubmit(e) {
  // スプレッドシートのIDとシート名を設定
  const mysheet1name = 'list'; //リストのデータのシート名
  const mysheet1 = SpreadsheetApp.openById(id_sheets).getSheetByName(mysheet1name);
 
  // 送信されたフォームデータ
  const num = 3; //送信されたデータで、希望日時は何番目か
  const searchValue = e.values[num]; //今回予約された日時
  // 今回予約された日時で'list'シートを検索し、該当する行は予約済（H列に1プラス）にする
  const row1 = 3; //データ開始行（1始まり3行目）
  const row2 = mysheet1.getLastRow(); //最後の行
  const datanum = row2 - row1 + 1; //データの行数

  // listのデータ取得（G列とH列）
  const listData = mysheet1.getRange(row1, 7, datanum, 2).getValues();
  for (let i = 0;i < datanum;i++) {
    let listGValue = listData[i][0];  // listDataのG列に該当する値
    if (listGValue === searchValue) { // 開始日時が一致した行の
      let listHValue = listData[i][1] + 1; 
      mysheet1.getRange(i + row1, 8).setValue(listHValue); //listのデータ（H列）を書き換え
    }
  }

  // フォームを更新する
  newlist();
}

5．注意

同時にフォームに回答しようとしていた複数の人が、同じ希望時間で回答した場合、ダブって予約を受け付けてしまうことがあります。listのH列の値が2以上の時間枠についてはダブっています。

6. 参考