はじめに
この記事は「Ateam LifeDesign Advent Calendar 2023」で完走賞を狙って25記事書いているうちの17日目の記事です。今年も完走目指して頑張るぞ！
作るもの
今回はGoogleフォームのプルダウンの内容を動的に変更することに挑戦してみようとおもいます。
サンプルとして作るのはイベントの残席数管理システムを想定します。スプレッドシートでこのように各イベントの残席数が記録されており
Googleフォームからイベント参加の申込みが発生するとこの残席数が１ずつ減っていきます。残席数が０になったイベントは選択肢リストから除外されるようにしていきます。
準備
まずは今回使うGoogleフォームを作成しておきます。
イベント予約フォームという名前でフォームを作成し、参加イベントをプルダウンで選択できるようにしています。そして今回はこのGoogleフォームに紐づけてGASを記述していきます。
GASを書いていく
const ss = SpreadsheetApp.openById('SPREADSHEET_ID'); // スプレッドシートのIDに変更する
const sheet = ss.getSheetByName('残席数'); // シート名に応じて変更する
まずはお決まりスプレッドシートシートのIDとシート名からシート情報を取得しておきます。これは以下に記載する２つの処理の中で共通部分となります。
残席数管理に関する処理
フォームが投稿されたときに該当イベントの残席数を１減らす処理を作っていきます。
const lastRow = sheet.getLastRow();
const range = sheet.getRange('A2:A' + lastRow);
const values = range.getValues();
イベント数は柔軟に変更できるようにしたいので特に具体的な数字では指定せずに情報が入っている最終行をgetLastRow()で取得します。そしてA列にイベント名をいれているのでA2から（１行目はヘッダー情報がはいってるから２行目から）先程指定した最終行までの情報を取ってきます。
const itemResponses = e.response.getItemResponses(); // 回答を取得
itemResponses.forEach(function(response) {
// ここに処理が入る
});
続いて送信されたフォームの回答を取得します。getItemResponses()を使うことでフォーム内の回答全てを取得することが出来るので、foreachでループさせながら１つずつ回答を見ていきます。
var questionTitle = response.getItem().getTitle();
var selectedEvent = response.getResponse();
if (questionTitle == '参加イベント') { // '参加イベント'は質問のタイトルに合わせて変更する
// ここに処理が入る
}
１つずつ回答をみていきながらその質問のタイトルをresponse.getItem().getTitle()で確認します。今回は「参加イベント」という名前の質問の回答をとってきたいので取得したタイトルが「参加イベント」のときに処理がはしるように分岐を入れます。
for (let i = 0; i < values.length; i++) {
if (values[i][0] == selectedEvent) {
var target = sheet.getRange('B' + (i + 2));
var limit = target.getValue();
target.setValue(limit - 1); // 残席数を1つ減らす
break;
}
}
valuesにはイベント名が入っているので、イベント名を１つずつ見ていきながら今回回答されたイベント名であるselectedEventと一致したときに残席数を減らす処理がはしるようにします。残席数はB列にはいっているのでsheet.getRange('B' + (i + 2)).getValue()で該当イベントの残席数を取得します。そしてそこから１引いた値を新たな残席数としてsetValueを使ってセルに書き込みます。
ここまでで残席数を減らす処理は完成です。
フォーム内のプルダウンのリストを動的に生成する処理
続いてはスプレッドシートに記載された残席数に応じてフォーム内のプルダウンにでてくるリストを動的に生成していきます。残席が０になったイベントはリストに表示しないようにしていきます。また全てのイベントの残席数がなくなったら、フォームの投稿を締め切るようにします。
const data = sheet.getRange('A2:B' + sheet.getLastRow()).getValues(); // イベント情報を取得
const choices = [];
for (let i = 0; i < data.length; i++) {
const event = data[i][0];
const seats = data[i][1];
if (seats > 0) {
choices.push(event);
}
}
まずsheet.getRange('A2:B' + sheet.getLastRow()).getValues()でイベント名とイベント残席数をすべて取得してきます。そしてその取得した配列をforでループしていきながら残席数seatsが0より大きい場合のみchoicesという配列にそのイベント名を入れるようにします。
const form = FormApp.getActiveForm();
const items = form.getItems();
for (let j = 0; j < items.length; j++) {
var item = items[j];
if(item.getTitle() === '参加イベント'){
// ここに処理を書く
}
}
FormApp.getActiveForm()で現在処理を実行しているフォームを取得します。そしてgetItems()ですべての質問を取得し、先程同様にループで回していきながら「参加イベント」のタイトルの質問のところにプルダウンの中身を動的に生成する処理を追加していきます。
const newChoices = [];
const itemQuestion = item.asListItem();
for (let i = 0; i < choices.length; i++) {
newChoices.push(itemQuestion.createChoice(choices[i]));
}
choicesにはすでに残席数が0のイベントは含まれていないのでchoicesがそのまま新しいプルダウンの選択肢になります。
itemQuestion.createChoice(choices[i]) は、itemQuestion（Googleフォームの質問）に対して、choices配列の要素choices[i]を選択肢として追加するための命令です。これにより、ループを回すごとに、新しい選択肢が作成され、それらがnewChoices配列に追加されていきます。
そして最終的にnewChoices配列にはchoices配列の各要素から作成された選択肢が含まれることになります。そして、このあとの処理でnewChoices配列をitemQuestionにセットすることで、フォームの選択肢が動的に更新される仕組みが実現できます。
if (newChoices.length > 0) {
form.setAcceptingResponses(true);
itemQuestion.setChoices(newChoices); // 新しい選択肢を設定
} else {
form.setAcceptingResponses(false);
}
break;
これは、newChoices配列に新しい選択肢が追加されているかどうかをチェックしています。もし newChoicesに要素（新しい選択肢）が存在する場合、つまり残席数が0より大きいイベントがある場合は、itemQuestion.setChoices(newChoices)で新しい選択肢をセットした上でform.setAcceptingResponses(true)を実行しています。これにより、フォームが回答を受け付ける状態になります。
一方、newChoicesに要素が存在しない場合、つまり残席数が0になった場合は、form.setAcceptingResponses(false)を実行しています。これにより、フォームが回答を受け付けない状態になります。
トリガーの登録
最後にトリガーを登録していきます。
スクリプトを記載していた画面の左側にある「トリガー」を選択します。
「トリガーを追加」を押して新規トリガーの登録を行います。
今回作成した関数を選択して、フォームが送信されたときに実行されるように保存します。
フォームのイベントでは種類として「起動時」のイベントが選択できますが、この起動時というのはフォームへの回答者が回答のフォームを開いたときに発火するのではなく、フォームの作成者がフォーム編集画面を開いたときに発火するイベントなので間違えないよう注意しましょう。
完成品がこちら
GASのコード
const ss = SpreadsheetApp.openById('SPREADSHEET_ID'); // スプレッドシートのIDに変更する
const sheet = ss.getSheetByName('残席数'); // シート名に応じて変更する
function updateFormDropdown() {
const data = sheet.getRange('A2:B' + sheet.getLastRow()).getValues(); // イベント情報を取得
const choices = [];
for (let i = 0; i < data.length; i++) {
const event = data[i][0];
const seats = data[i][1];
if (seats > 0) {
choices.push(event);
}
}
const form = FormApp.getActiveForm();
const items = form.getItems();
for (let j = 0; j < items.length; j++) {
var item = items[j];
if(item.getTitle() === '参加イベント'){
const newChoices = [];
const itemQuestion = item.asListItem();
for (let i = 0; i < choices.length; i++) {
newChoices.push(itemQuestion.createChoice(choices[i]));
}
if (newChoices.length > 0) {
form.setAcceptingResponses(true);
itemQuestion.setChoices(newChoices); // 新しい選択肢を設定
} else {
form.setAcceptingResponses(false);
}
break;
}
}
}
function updateLimit(e) {
const lastRow = sheet.getLastRow();
const range = sheet.getRange('A2:A' + lastRow);
const values = range.getValues();
const itemResponses = e.response.getItemResponses(); // 回答を取得
itemResponses.forEach(function(response) {
var questionTitle = response.getItem().getTitle();
var selectedEvent = response.getResponse();
if (questionTitle == '参加イベント') { // '参加イベント'は質問のタイトルに合わせて変更する
for (let i = 0; i < values.length; i++) {
if (values[i][0] == selectedEvent) {
var target = sheet.getRange('B' + (i + 2));
var limit = target.getValue();
target.setValue(limit - 1); // 残席数を1つ減らす
break;
}
}
}
});
}
function onFormSubmit(e) {
updateLimit(e);
updateFormDropdown();
}
実行結果
まずはじめフォームはこの状態になっていて、一番下のイベントが残席数1にしているので、このイベントを選んで送信します。すると再度フォームを開くと
きちんと残席数が0になったイベントが表示されなくなったのが確認できました。また全てのイベントの残席数が0になると
このような画面になりフォームの投稿ができなくなったことが確認できました。
最後に
今日はGASをつかってGoogleフォームのプルダウンの値を動的に生成する方法についてみていきました。簡単にシンプルな在庫管理システムや予約システムが作れそうですね！