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

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

しかし、普通にGoogleフォームを使うだけだと、下記のようなことは自動ではできません。

  • 各日程の空き状況(残り○名)をフォーム内に掲載する
  • とある日程が定員に達したら、その日程は申し込みできないようにする
  • すべての日程が定員に達したら、フォームをクローズする

・・・ということで、Googleフォームに加えて、スプレッドシートGoogle Apps Scriptを利用することで、これらを実現してみましたので、紹介させていただきます。

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

完成した予約システム

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

利用者がフォームから申し込む

申込フォーム

予約可否チェック・予約データ登録

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

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

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

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

フォームの内容を自動的に修正

予約OKとなった場合、Googleフォームの記載内容や選択肢が自動的に変更されます。

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

予約結果メールを自動送信

予約の結果が自動的にメール送信されます。

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

作成手順

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

まずは、フォームとスプレッドシートを用意します。先ほど掲載したスクリーンショットを参考にしてください。

スプレッドシート

先述の例と同じように、予約シートと日程シートの2つのシートを作成してください。

フォーム

先述の例と同じようにフォームを作成してください。

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

スクリプトを作成する

右上の三点メニューから、スクリプトエディタを起動します。

三点メニュー > スクリプトエディタ

あらかじめ入力されているコード(myFunction)を削除し、当記事のコードを貼り付けます。
貼り付け後、保存してください。

スクリプトを貼り付けて保存する
不要なコードを削除し、当記事のコードを貼り付けて保存ボタンを押す

貼り付けるコードは下記のとおりです。

const form = FormApp.getActiveForm();
const ss = SpreadsheetApp.openById("1vO-kbPd0-CZhxRJ6KhFCpUDkwcD7jkIH4jRyPySTQvI");
const reserveSheet = ss.getSheetByName("予約");
const datesSheet = ss.getSheetByName("日程");

/**
 * フォーム送信時の処理
 */
function receivedApplication(e) {

  // フォームの送信内容を取得
  const email = e.response.getRespondentEmail();
  const items = e.response.getItemResponses();
  const reserveDate = items[0].getResponse();

  // 予約処理
  const result = updateDatesSheet(reserveDate);
  if (result === "success"){
    reserveSheet.appendRow([email, reserveDate]);   // 予約シートに追記
    updateForm();                                   // フォームの「空き状況」を更新
  }

  // 結果メール送信
  sendEmail(email, reserveDate, result); 
}

/**
 * 日程シート更新処理
 */
function updateDatesSheet(reserveDate){

  // 日程シートから該当日のデータを取得
  const table = datesSheet.getDataRange().getValues();
  const index = table.findIndex(row => row[0] === reserveDate);
  const target = {
    rowNum: index + 1,
    date: table[index][0],
    cap: table[index][1],
    reserved: table[index][2],
  }

  // 定員超過しなければ「予約済」に加算する
  if (target.reserved < target.cap) {
    datesSheet.getRange(target.rowNum, 3).setValue(target.reserved + 1);
    return "success"
  } else {
    return "error"
  }
}

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

  const mailTitle = "予約結果について";
  const mailBody = (result === "success")
    ? "予約が完了しました。\n" +
      `予約日:${preferredDate}`
    : "定員超過のため予約できませんでした。\n" +
      "下記のフォームから再度申請してください\n" +
      form.getPublishedUrl();

  // 結果メール送信
  GmailApp.sendEmail(email, mailTitle, mailBody);
}

/**
 * フォームを更新する
 */
function updateForm(){

  // 日程シートのデータを取得
  const table = datesSheet.getDataRange().getValues().slice(1);
  const options = table.map(row => {
    return {
      date: row[0],
      cap: row[1],
      reserved: row[2]
    };
  });
  
  // 空き状況, 参加希望日 の値を作成
  let infoText = "";    // 「空き状況」テキスト
  const choices = [];   // 「参加希望日」選択肢
  for (const option of options){
    if (option.reserved < option.cap){
      infoText += `${option.date} : 残り ${option.cap - option.reserved} 名\n`;
      choices.push(option.date);
    } else {
      infoText += `${option.date} : 満員(申込不可)\n`;
    }
  }

  // 空いている日程が1つもない場合、フォームをクローズ
  if (!choices.length) {
    form.setAcceptingResponses(false);
  }

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

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

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

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

スクリプトエディタの▷実行をクリックし、下記のとおりに進めてください。

「承認が必要です」というダイアログが表示されますので、権限を確認 をクリックします。
* この部分の解説画像は、他の記事から流用しているため、実際のものと若干異なる場合があります。

このスクリプトを使うGoogleアカウントを選択します。

(個人のGoogleアカウントの場合のみ)左下の 詳細 をクリックします。

(個人のGoogleアカウントの場合のみ)左下の プロジェクト名(安全ではないページ)に移動 をクリックします。

右下の 許可 をクリックします。

トリガーを設定する

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

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

これで作業完了です。フォームから申込のテストをしてみて、問題なく動作するか確認してみてください。

気になる点・注意点

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

メールの送信制限

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

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

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

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

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

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

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

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

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

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

コメント

  1. 上田博憲 より:

    「Googleフォーム・スプレッドシート・GASで予約システムを作る」内の予約受付フォームの「空き状況」、「注意事項」の追加方法を教えてください。よろしくお願いいたします。

  2. rie より:

    申込がキャンセルにり定員を修正したい場合

  3. rie より:

    スプレットシートの「定員」を修正すればよいのでしょうか?

  4. omi より:

    ①スクリプト実行時のエラー解消はどうすればよいですか?
    TypeError: Cannot read property ‘response’ of undefined(行 12、ファイル「コード」)
    ②空き状況をフォームに反映するにはどうすればよいですか?

  5. Misaki より:

    フォームは動く。メールも届く。
    しかし、スプレッドシートの「予約」「日程」のシートは反映されず、
    「フォームの回答1」が作成され、データはそこに書かれる。
    スプレッドシートのID(シートのIDではない)を入れても、
    そこに書き込みしている形跡がないのはなぜなのでしょうか。

    • Misaki より:

      すみません、自己解決できました。
      1.空き状況、参加希望日、クラス、名前・・・・ という順番を守らなくてはならず、
        クラス、名前、空き状況、参加希望日 にしてしまうと動かない
      2.参加希望日に日付のデータ型を入れてしまうと表示がおかしくなる。
      というのが原因でした。

    • ichi3270 ichi3270 より:

      2行目の「xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx」に入力いただいたIDは、「予約」「日程」があるスプレッドシートのIDになっていますでしょうか。

  6. t より:

    日程のシートが更新されず、どんな選択肢を選んでも定員超過になってしまいます対処法はあるでしょうか

    • ichi3270 ichi3270 より:

      シート名やシートの構成は、当記事と同じになっていますでしょうか。(シート名が違ったり、列がずれていたりするとうまく動かないと思います。
      また、B列の「定員」やC列の「予約済」は、数値が入力されていて、書式も「数値」になっていますでしょうか。(予約済者が0名の場合も、空白ではなく、0を入れておいた方が良さそうです)

      • t より:

        記事のとおりになっています、日程、予約のシートにフォームに入力したものが入らず 、フォームの回答のシートにデータが入力されている状態です。
        (希望日時を希望時刻にしています。)

        • ichi3270 ichi3270 より:

          スクリプトを貼り付けた後に、ソースコードの2行目のxxxxxxxxxxxxxxx…の部分を修正する必要がありますが、スプレッドシートのIDはあっていますでしょうか。
          「日程」、「予約」のシートが存在するスプレッドシートのIDです。

        • t より:

          今のスプレットシートには「フォームの回答1」、「予約」、「日程」のシートがあり、そのシートIDをスクリプトに入れています。
          夜分遅くに何度もすいません

  7. gretsch より:

    大変参考になるコードをありがとうございます。
    このコードを参考に、ラジオボタンでの申し込みではなく、チェックボックスでの申し込みをしようと試みているのですが、どこを修正してよいやら皆目見当がつきません。
    大変恐縮なのですが、チェックボックスでの申し込み(それぞれ規定数を超過したら申し込みできないようにする)はどうすれば実装できるか、そのコードをご教示願えませんでしょうか。

    • gretsch より:

      すみません。自己解決いたしました。
      こちらのコードを参考にさせていただき、ラジオボタンを増やし条件分岐させることで希望の挙動を行うGoogleフォームを作成することができました。おかげさまで、大変勉強になりました。
      改めてお礼申し上げます。

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