Googleフォーム・スプレッドシート・GASで予約システムを作る

簡単な予約システムを作ろうとした時、申込画面の作成はGoogleフォームも候補になると思います。

ただ、Googleフォームだけだと、空き状況に応じた対応が人力になってしまいます。
(定員を超過していたら、予約不可の連絡をしたり、フォームの選択肢から削除したり・・・)

今回は、Googleフォームに加えて、スプレッドシートとGoogle Apps Scriptを利用することで、下記のようなことを目指しました。

  • 申込受けつけ後、定員以内かどうかを自動判断して、予約結果のメールを送る
  • 予約状況に応じて、フォーム内の説明文やラジオボタンの選択肢を変更する
  • 全ての予約枠が埋まったら、フォームをクローズする

*このアプリは執筆者の趣味の一環で作成したものです。これを参考にしたことにより、ご利用者様、または第三者に損害・トラブル等が発生した場合でも、一切の責任を負いません。自己責任の上でのご利用をお願いいたします。

完成した予約システム

こんな感じのものが完成しました。

フォームから申し込む

申込フォーム

スクリプトで予約可否チェック・予約データ登録

スクリプトで日程シートをチェックして、定員以内であれば予約OKとします。
予約OKの場合は、予約済数が+1されます。

日程の選択肢と予約状況を管理するシート

予約OKの場合は、予約シートにもデータが追加されます。

成立した予約を記録するシート

スクリプトでフォームの内容を修正

予約OKとなった場合、スクリプトでGoogleフォームの内容を修正します。

スクリプトによって自動的に修正されたフォーム

スクリプトで予約結果メールを送信

スクリプトで、予約の結果をメール通知します。

予約結果メール(OK)
予約成功時のメール
予約結果メール(NG)
予約失敗時のメール

作成手順

フォームとスプレッドシートを作成する

まずは、フォームとスプレッドシートを用意します。
さっきのスクショの通りなので特に解説はありません。

設定 > 全般 > メールアドレスを収集する は ON にしておきます。

スクリプトを作成する

フォーム編集画面の右上の三点ボタン > <>スクリプトエディタから、スクリプトエディタを起動します。

スクリプトエディタが開いたら、3行のスクリプト(myfunction)が初期設定されていますが、これは削除して、白紙の状態にします。

下記のコードを貼り付けます。

const form = FormApp.getActiveForm();
const spreadSheet = SpreadsheetApp.openById("xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx");
const reserveSheet = spreadSheet.getSheetByName("予約");
const listSheet = 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);
        return "OK";
      } else {
        return "NG";
      }
    }
  }

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

}

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

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

  if ( result == "OK" ){
    mailBody = "予約が完了しました。\n"
             + `予約日:${preferredDate}`
  } 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`;
    }
  }

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

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

*2行目のxxxxxxxxxxxxxxx…の部分には、ご自身で作成されたスプレッドシートのIDを入力してください。

スクリプトに権限を与える

このスクリプトは、メールを送信したり、スプレッドシートを更新したりするので、それらを許可する必要があります。

スクリプトエディタの▷実行をクリックすると、許可を求める画面が表示されますので、許可してあげてください。

なお、無料のGoogleアカウントでは、このアプリはGoogleで確認されていませんというインパクトのある画面が表示されるのですが、詳細 > 安全ではないページに移動をクリックしてください。

トリガーを設定する

フォームから申込が送信された時、先ほどのスクリプトが実行されるように設定します。

スクリプトにトリガーを設定する方法
トリガーの設定内容
イベントの種類は「フォーム送信時」

これで作業完了です。
フォームから申込のテストをしてみましょう。

気になる点・注意点

簡易的なアプリとしては使えるのですが、いくつか気になる点や注意点がありますので、記載しておきます。

メールの送信制限

Gmailは1日に送信可能な件数などに制限があり、申込件数が多そうなケースに対応するのは厳しいです。詳細は、Googleのヘルプをご確認ください。

フォーム入力中に予約が埋まった場合

フォームにアクセスしてから送信ボタンを押すまでの間に、希望日程の予約が埋まってしまう可能性があります。

その場合は申込不可のメールが届きますが、人によっては、「送信ボタンを押したら予約ができている」と早とちりするかもしれません。(そういう人はメールも見なかったりする)

また、このパターンの場合は、既に削除された選択肢を選んだことになります。
Googleフォームの仕様上、削除された選択肢を選んでいた場合は、何も選んでいないのと同じ状態になるようで、スクリプトからも、どの日程を選んだのかを取得できませんでした。

予約不可メールに「〇月〇日は予約できませんでした」と記載したかったのですが、これは不可能なようです。

フォーム入力中にフォームがクローズされた場合

フォームにアクセスしてから送信ボタンを押すまでの間に、全ての予約枠が埋まってフォームがクローズする可能性があります。

この場合、送信ボタンを押すと、フォームが既に閉じている旨のメッセージが表示されます。
このパターンだと、申込の送信は無効になってしまうため、スクリプトも起動しません。

よって、予約不可のメールも送信されないため、予約できたと誤解される可能性があるかもしれません。
フォームの説明文を工夫する必要があるかもしれません。

コメント

タイトルとURLをコピーしました