１．目的
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は、トリガーの設定で、フォームの送信時に実行するよう設定してください。
// 実際に使用するフォームとスプレッドシートの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以上の時間枠についてはダブっています。