Googleフォームで予約受付をしているけど、予約がダブルブッキングして困ったという経験はありませんか？
予約が重複してしまうと、せっかく予約をしてくれたお客様に迷惑をかけてしまったり、クレームの原因となったりしてしまいます
こーすけ先生そんな悩みを解消するために、今回はGAS（Google Apps Script）を駆使して、Googleフォームとスプレッドシートを効果的に連携させる方法をご紹介します
これにより、予約が満席になった瞬間も見逃さず、リアルタイムで更新が可能となり、ダブルブッキングのリスクを大幅に軽減できます
この記事では、GAS（Google Apps Script）を駆使して、Googleフォームとスプレッドシートの強力な連携を実現する方法をご紹介します
こーすけ先生リアルタイムで予約情報をGoogleフォームに自動反映できるのが偉すぎる
この記事を読めば、Googleフォームからの予約情報をスプレッドシートに反映させ、スプレッドシートから空き情報を読み取り、リアルタイムで予約可能日を更新する方法を学ぶことができます！めちゃくちゃ便利なので、是非読んでいってください！
こーすけ先生予約管理の手間を大幅に削減し、スムーズな運用を実現できるので、ぜひ読んでみてください
GASを極めたい方や、業務の効率化を図りたい方は、ぜひこの記事を読んください！
難しいことはGASに任せて、我々人間は楽しちゃいましょう！
こーすけ先生当ブログでは実際に仕事でGASを扱っている私が、GASの魅力について徹底的に取り上げていきます！
【お題】Googleフォームとスプレッドシートを連携してフォームの選択肢を自動更新する
【事前準備】Googleフォームとスプレッドシートを準備する
こーすけ先生準備が不要な方は【STEP1】までジャンプしてください！
こーすけ先生今回の起動元となるフォームを作成していきましょう！
どのようなフォームでもよいのですが予約受付フォームっぽく以下のようなフォームを作成しました
| 項目名 | タイプ | 必須 |
|---|---|---|
| 予約者氏名 | 記述式 | |
| 予約者メールアドレス | 記述式 | |
| 予約日 | プルダウン |
参考に私が作成したフォームは以下となります
こーすけ先生Googleフォームを作成したら、右上の「プレビュー」ボタンを押下して、一度回答してみましょう！のちほど回答データを取得するためだけなので、どのような回答でも構いません
回答が１つもないと、エラーが起こってしまうので、必ず何かしら回答をしておいてくださいね！
予約日空き状況を管理するスプレッドシートを作成
Googleスプレッドシートを用いて、予約日空き状況を管理するシートを作成しましょう
どんな様式でも構いませんがこのシートのカラム順を前提に今回のプログラムは作成します
こーすけ先生私は以下のように作成しました
GASエディタ上で使うので、作成したスプレッドシートのIDを事前に取得しておきましょう！
こーすけ先生一応、スプレッドシートのリンクも載せておきます
必要に応じてコピペし、自分のスプレッドシートに貼り付けてください
【STEP1】Googleフォームのフォーム項目をGASで取得する
準備したGoogleフォームからコンテナバインド型でGASプログラムを記述していきましょう
こーすけ先生Googleフォームからコンテナバインド型でGASを開くやり方が分からない！という場合は、こちらを参考にしてください！
ひとまず、Googleフォームの回答内容を読み取れるか確認するため、以下のプログラムを実行してみてください
（回答がないとエラーになってしまうため、１つで良いので適当に回答してみてから実行してください）
function gaslog_formSubmit(e) {
let itemResponses;
// フォームの回答をイベントオブジェクトまたはフォーム自身から取得する。
if (e !== undefined) {
itemResponses = e.response.getItemResponses();
} else {
const wFormRes = FormApp.getActiveForm().getResponses();
itemResponses = wFormRes[wFormRes.length-1].getItemResponses();
}
console.log(itemResponses);
}21:02:40 お知らせ 実行開始
21:02:40 情報 [ { toString: [Function],
getFeedback: [Function],
getItem: [Function],
setFeedback: [Function],
…
getResponse: [Function],
getScore: [Function],
setScore: [Function] } ]
21:02:41 お知らせ 実行完了これだとどんな値がとれているかはわかりませんが、ばっちりとオブジェクトは取得できていますので準備万端です！
今回もdayjsライブラリを使って日付の操作を行うため、ライブラリを追加しておいてください
Day.jsをライブラリに追加する
こーすけ先生必要に応じて上記記事を参考にしてね！
【STEP2】Googleフォームから取得した値を用いてスプレッドシートを更新する
次に、回答内容を用いて予約日空き状況シートを更新していきましょう
こーすけ先生では、どのように更新していくかを考えてみます
- 回答内容の予約日と一致する予約受付日を探す
- 予約受付日の空き枠が予約済を超えていないかを確認する
- 超えていなければ予約可とし、予約済をインクリメントする
こーすけ先生こんなカンジになるかと思います！
// スプレッドシート_予約空き状況
const SPREAD_ID = '**事前準備で取得しておいたIDを記述**';
// スプレッドシート_予約空き状況シートのカラム情報
const COL = {
DAY:1,
FREE:2,
RESERVE:3
};
/**
* 予約受付フォームが送信された際のトリガーメソッド
* @param {EventObject} e - イベントオブジェクト
*/
function gaslog_formSubmit(e) {
const activeForm = FormApp.getActiveForm()
, wSheet = SpreadsheetApp.openById(SPREAD_ID).getSheets()[0]
, vals = wSheet.getDataRange().getValues()
let itemResponses;
// フォームの回答をイベントオブジェクトまたはフォーム自身から取得する。
if (e !== undefined) {
itemResponses = e.response.getItemResponses();
} else {
const wFormRes = activeForm.getResponses();
itemResponses = wFormRes[wFormRes.length-1].getItemResponses();
}
vals.shift();
// 回答から空き状況を更新する
itemResponses.forEach(function(itemResponse){
switch (itemResponse.getItem().getTitle()) {
case '予約日':
vals.forEach(function(val, idx){
// 予約日と台帳上の日付を比較して同日を判定
if (dayjs.dayjs(val[COL.DAY-1]).isSame(dayjs.dayjs(itemResponse.getResponse()), 'd')) {
// 空き枠 > 予約済なら予約成功
if (!val[COL.RESERVE-1] || Number(val[COL.FREE-1])>Number(val[COL.RESERVE-1])) {
val[COL.RESERVE-1]++;
wSheet.getRange(idx+2, COL.RESERVE).setValue(val[COL.RESERVE-1]);
}
}
});
break;
default:
break;
}
});これを実行すると、先ほどお試し送信した回答に応じて予約日空き状況シートの予約済が更新されます
ここでのポイントは
vals.shift();とし、wSheet.getDataRange().getValues();で取得した値リストの1行目（ヘッダー）を削除しています
こーすけ先生こうすると、ヘッダーかどうかをループ処理の中で判定する必要がなくなるのでおすすめです
【STEP2】の補足説明
こーすけ先生準備が不要な方は【STEP3】までジャンプしてください！
// スプレッドシート_予約空き状況シートのカラム情報
const COL = {
DAY:1,
FREE:2,
RESERVE:3
};
COL オブジェクト: 予約情報を格納するスプレッドシートの各列の位置情報が入りますDAY 列は予約受付日、FREE 列は空き枠の数、RESERVE 列は予約済の数を示しています
こーすけ先生セルを定数化する方法は便利なので、是非覚えてね！
let itemResponses;
// フォームの回答をイベントオブジェクトまたはフォーム自身から取得する。
if (e !== undefined) {
itemResponses = e.response.getItemResponses();
} else {
const wFormRes = activeForm.getResponses();
itemResponses = wFormRes[wFormRes.length-1].getItemResponses();
}e は関数の引数として渡されているもので、これは予約受付フォームが送信されたときにトリガーされるイベントオブジェクトを指します！
もし e が undefined である場合、つまり直接関数が呼び出された場合、最新のフォームの回答情報を取得します
e が undefined の場合、最新の回答情報から各質問に対する回答を取得し、itemResponses 変数に代入します（wFormRes.length-1 は、配列の最後の要素のインデックスを指す）
itemResponses.forEach(function(itemResponse){
switch (itemResponse.getItem().getTitle()) {
case '予約日':
vals.forEach(function(val, idx){
// 予約日と台帳上の日付を比較して同日を判定
if (dayjs.dayjs(val[COL.DAY-1]).isSame(dayjs.dayjs(itemResponse.getResponse()), 'd')) {
// 空き枠 > 予約済なら予約成功
if (!val[COL.RESERVE-1] || Number(val[COL.FREE-1])>Number(val[COL.RESERVE-1])) {
val[COL.RESERVE-1]++;
wSheet.getRange(idx+2, COL.RESERVE).setValue(val[COL.RESERVE-1]);
}
}
});
break;
default:
break;
}
});この部分は、フォームの回答情報を元にして、スプレッドシート内の予約台帳を更新するための処理です！
switch文を使い、フォームの回答に対する質問タイトルに基づいて処理を分岐しています
vals.forEach(function(val, idx) スプレッドシート内の各行に対して処理を行いますval はスプレッドシート内の1行分のデータを表し、idx はその行のインデックスです
// 予約日と台帳上の日付を比較して同日を判定
if (dayjs.dayjs(val[COL.DAY-1]).isSame(dayjs.dayjs(itemResponse.getResponse()), 'd')) フォームから送信された回答の「予約日」と、スプレッドシート上の台帳の各行の「予約日」を比較して、同じ日であるかを判定します
そして、予約日が同じである場合、以下の処理を実行します
if (!val[COL.RESERVE-1] || Number(val[COL.FREE-1])>Number(val[COL.RESERVE-1])) {
val[COL.RESERVE-1]++;
wSheet.getRange(idx+2, COL.RESERVE).setValue(val[COL.RESERVE-1]);このコードは、以下の条件に基づいて予約済みの数を更新します
!val[COL.RESERVE-1]: 予約済みの数が0または未定義（undefined）である場合- Number(val[COL.FREE-1])>Number(val[COL.RESERVE-1]): 空き枠の数が予約済みよりも多い場合
いずれかの条件が満たされると、以下の処理が実行されます
val[COL.RESERVE-1]++;: 予約済みの数を1増やします- wSheet.getRange(idx+2, COL.RESERVE).setValue(val[COL.RESERVE-1])
スプレッドシート上の対応するセルに新しい予約済みの数をセットします
（idx+2は、スプレッドシート上の行番号に対応）
予約が成功した場合、val[COL.RESERVE-1] がインクリメントされ、対応するセルに新しい予約済の数がセットされます
こーすけ先生予約済みの数を適切に更新し、スプレッドシート上に最新の予約情報を反映することができるよ！
こーすけ先生「Day.js」はこんなカンジでめちゃくちゃ使い勝手良いライブラリなので、是非活用してみてくださいね～！
【STEP3】スプレッドシートの内容を用いてGoogleフォームを更新する
こーすけ先生では今回の本題であるGoogleフォームの更新をしていきましょう
フォームを更新する場合や項目を新規に作成する場合は、どういった項目を使うのかを定義することがポイントです
更新をする場合
FormApp.getActiveForm().getItems().forEach(function(item) {
item.asXXXXItem().setYYYYYY();
});
新規作成する場合
FormApp.getActiveForm().addXXXXItem().setTitle('AAAAAAAAA')
.setYYYYYY();今はざっくりとしたイメージを持つだけで構いません！
次回の記事で各種項目の新規作成・更新方法をお伝えします
// スプレッドシート_予約空き状況
const SPREAD_ID = '**事前準備で取得しておいたIDを記述**';
// スプレッドシート_予約空き状況シートのカラム情報
const COL = {
DAY:1,
FREE:2,
RESERVE:3
};
/**
* 予約受付フォームが送信された際のトリガーメソッド
* @param {EventObject} e - イベントオブジェクト
*/
function gaslog_formSubmit(e) {
const activeForm = FormApp.getActiveForm()
, wSheet = SpreadsheetApp.openById(SPREAD_ID).getSheets()[0]
, vals = wSheet.getDataRange().getValues()
, dateLst = new Array();
let itemResponses;
// フォームの回答をイベントオブジェクトまたはフォーム自身から取得する。
if (e !== undefined) {
itemResponses = e.response.getItemResponses();
} else {
const wFormRes = activeForm.getResponses();
itemResponses = wFormRes[wFormRes.length-1].getItemResponses();
}
vals.shift();
// 回答から空き状況を更新する
itemResponses.forEach(function(itemResponse){
switch (itemResponse.getItem().getTitle()) {
case '予約日':
vals.forEach(function(val, idx){
// 予約日と台帳上の日付を比較して同日を判定
if (dayjs.dayjs(val[COL.DAY-1]).isSame(dayjs.dayjs(itemResponse.getResponse()), 'd')) {
// 空き枠 > 予約済なら予約成功
if (!val[COL.RESERVE-1] || Number(val[COL.FREE-1])>Number(val[COL.RESERVE-1])) {
val[COL.RESERVE-1]++;
wSheet.getRange(idx+2, COL.RESERVE).setValue(val[COL.RESERVE-1]);
}
}
});
break;
default:
break;
}
});
// 空き枠がある日付のみでフォームを更新する
vals.forEach(function(val){
if (!val[COL.RESERVE-1] || Number(val[COL.FREE-1]) > Number(val[COL.RESERVE-1])) {
dateLst.push(dayjs.dayjs(val[COL.DAY-1]).format('YYYY/MM/DD'));
}
});
if (dateLst.length > 0) {
activeForm.getItems().forEach(function(item) {
if (item.getTitle() == '予約日') {
item.asListItem().setChoiceValues(dateLst);
}
});
} else {
// 全日程が受付終了となれば予約フォーム自体の受付を終了
activeForm.setAcceptingResponses(false);
}
}上記のようにすると以下のような機能が実現できます
- まだあまりがある日程のみがフォームで選択できる
- 全日程の予約が埋まった場合（dateLstの要素が0の場合）、フォームの受付を終了する
こーすけ先生実行例はこんなカンジです！
予約空き状況が以下の通りだった場合…
こーすけ先生フォームを更新すると、空き枠がない「1/2、1/3、1/6」がプルダウンから消えていることが確認できます！
【STEP3】の補足説明
こーすけ先生準備が不要な方は【STEP4】までジャンプしてください！
// 空き枠がある日付のみでフォームを更新する
vals.forEach(function(val){
if (!val[COL.RESERVE-1] || Number(val[COL.FREE-1]) > Number(val[COL.RESERVE-1])) {
dateLst.push(dayjs.dayjs(val[COL.DAY-1]).format('YYYY/MM/DD'));
}
});
if (dateLst.length > 0) {
activeForm.getItems().forEach(function(item) {
if (item.getTitle() == '予約日') {
item.asListItem().setChoiceValues(dateLst);
}
});
} else {
// 全日程が受付終了となれば予約フォーム自体の受付を終了
activeForm.setAcceptingResponses(false);
}ここの部分は予約フォームの更新処理を行っています！
vals.forEach(function(val){
if (!val[COL.RESERVE-1] || Number(val[COL.FREE-1]) > Number(val[COL.RESERVE-1])) {
dateLst.push(dayjs.dayjs(val[COL.DAY-1]).format('YYYY/MM/DD'));
}
});空きがある日付の場合、dayjs.dayjs(val[COL.DAY-1]) を使用してスプレッドシートから取得した日付データを dayjs ライブラリを使って処理し、’YYYY/MM/DD’ フォーマットで dateLst 配列に追加します
if (dateLst.length > 0) {
activeForm.getItems().forEach(function(item) {
if (item.getTitle() == '予約日') {
item.asListItem().setChoiceValues(dateLst);
}
});
} else {
// 全日程が受付終了となれば予約フォーム自体の受付を終了
activeForm.setAcceptingResponses(false);
}そして、この部分でdateLst 配列に格納された予約可能な日付のリストを使用して、予約フォームを更新する処理を行っています
if (item.getTitle() == '予約日') {
item.asListItem().setChoiceValues(dateLst);Googleフォームの「予約日」の選択肢に、予約可能な日付 (dateLst) をセットします
こーすけ先生これにより、ユーザーはフォームで選択可能な日付が更新され、予約可能な日だけが選択肢として表示されます
// 全日程が受付終了となれば予約フォーム自体の受付を終了
activeForm.setAcceptingResponses(false);フォームに対して、受付を終了するメソッドを呼び出します
こーすけ先生これにより、新たな回答の受け付けが停止されます
【STEP4】予約受付結果をメールで送信する
こーすけ先生これはもうものすごく簡単ですね！
// スプレッドシート_予約空き状況
const SPREAD_ID = '**事前準備で取得しておいたIDを記述**';
// スプレッドシート_予約空き状況シートのカラム情報
const COL = {
const COL = {
DAY:1,
FREE:2,
RESERVE:3
};
/**
* 予約受付フォームが送信された際のトリガーメソッド
* @param {EventObject} e - イベントオブジェクト
*/
function gaslog_formSubmit(e) {
const activeForm = FormApp.getActiveForm()
, wSheet = SpreadsheetApp.openById(SPREAD_ID).getSheets()[0]
, vals = wSheet.getDataRange().getValues()
, dateLst = new Array;
let itemResponses;
// フォームの回答をイベントオブジェクトまたはフォーム自身から取得する。
if (e !== undefined) {
itemResponses = e.response.getItemResponses();
} else {
const wFormRes = activeForm.getResponses();
itemResponses = wFormRes[wFormRes.length-1].getItemResponses();
}
vals.shift();
// 回答から空き状況を更新する
let replyAddress, reserveErr;
itemResponses.forEach(function(itemResponse){
switch (itemResponse.getItem().getTitle()) {
case '予約者メールアドレス':
replyAddress = itemResponse.getResponse();
break;
case '予約日':
vals.forEach(function(val, idx){
// 予約日と台帳上の日付を比較して同日を判定
if (dayjs.dayjs(val[COL.DAY-1]).isSame(dayjs.dayjs(itemResponse.getResponse()), 'd')) {
// 空き枠 > 予約済なら予約成功
if (!val[COL.RESERVE-1] || Number(val[COL.FREE-1])>Number(val[COL.RESERVE-1])) {
val[COL.RESERVE-1]++;
wSheet.getRange(idx+2, COL.RESERVE).setValue(val[COL.RESERVE-1]);
reserveErr = false;
} else {
reserveErr = true;
}
}
});
break;
default:
break;
}
});
// 空き枠がある日付のみでフォームを更新する
vals.forEach(function(val){
if (!val[COL.RESERVE-1] || Number(val[COL.FREE-1])>Number(val[COL.RESERVE-1])) {
dateLst.push(dayjs.dayjs(val[COL.DAY-1]).format('YYYY/MM/DD'));
}
});
if (dateLst.length>0) {
activeForm.getItems().forEach(function(item) {
if (item.getTitle()=='予約日') {
item.asListItem().setChoiceValues(dateLst);
}
});
} else {
// 全日程が受付終了となれば予約フォーム自体の受付を終了
activeForm.setAcceptingResponses(false);
}
// 予約エラーとなった方には予約できなかった旨をメール送信
if (reserveErr) {
sendEmailEx(
replyAddress,
'【自動送信】予約不可',
'予約を受け付けることができませんでした。'
);
} else {
sendEmailEx(
replyAddress,
'【自動送信】予約可',
'予約を受け付けました。'
);
}
}
/**
* メール送信の裏技メソッド
* @param {String} _recipient - 送信先
* @param {String} _subject - 件名
* @param {String} _body - 本文
* @param {Object} _option - 送信オプション
*/
function sendEmailEx(_recipient, _subject, _body, _option) {
// 引数の内容でメールを下書き保存する
const mailDraft = GmailApp.createDraft(_recipient, _subject, _body, _option);
// 下書き保存したメールIDから下書きを取得し、メール送信を依頼する
GmailApp.getDraft(mailDraft.getId()).send();
}Googleフォームを回答がある度に更新しているため、基本的には回答を送信すれば予約を受け付けることが可能です
こーすけ先生そのため、念のため、プログラムの中でも予約の可否を判断しています
まとめ
今回は「GASを用いてGoogleフォーム内の項目を自動更新しよう」ということで、Googleフォーム内の項目に設定するリストをスプレッドシートで管理し、回答を受け付けるごとにGoogleフォームを更新するということを実現しました
こーすけ先生次回は、各項目の新規作成・更新方法をお伝えします
今回と次回予定のテクニックはGoogleフォームを業務活用する中で必須のテクニックとなります
ぜひ、皆さんも実業務での活用に取り組んでみてください
引き続き、GASを楽しんでいきましょう！！
こーすけ先生X（旧：Twieer）にて、ブログの更新やQiita記事の更新、GAS情報をお届けしますので、是非フォローしてください！
こーすけ先生おかげさまで今年５月に起業しました！
GASやGoogleサービス、プログラミング全般のご相談承ります！
コメント