はじめに

GoogleAppsScriptは、Googleのサービスを簡単に連携・自動化することができたりと何かと便利なものです。
今回は、GoogleフォームとGoogleスプレッドシートをGASに連携させて、入場希望時間の枠を自動管理するスクリプトを紹介します。このスクリプトは、スプレッドシートに記録されたデータを元に、各時間枠の受け入れ数を(大雑把に)チェックし、フォームの選択肢を動的に更新します。学園祭の展示物に入場制限をかけ、密を回避するために作成しまいた。
随分前に作成したものですが(2年前)、記録のために公開します。2024.7.4現在でも動作は確認済みです。

この予約サイトは、大雑把に予約を管理するものです。

目次

セットアップ
GASで制御する
付録
参考文献

セットアップ

まず、GoogleFormを作成します。 GoogleFormはこちら！
今回はこのような形で、作成してみます。

今回、反映させるのは、2個目の『入場希望時間を入力』する箇所です。
この項目は、プルダウン形式にしています。(ラジオボタンだと、なぜか動きませんでした...)

Formで集める回答

名前
入場時間
予約人数

今回は入場時間毎に定員を設け、上限に達したら選択肢を(動的に)消していきます！

Formに回答されると、管理画面でもその結果を見ることができます。

ですが、今回はGASと紐付けるためにGoogleSpreadSheetに保存します！
『上記のスプレッドシートに表示』を押してください。
これで、スプレッドシートに回答が保存されます！

↓今回はこんな感じになりました

スプレッドシートを作成したら、上のタブから『拡張機能』→『Apps Script』を選択します！
これで、Form x GoogleSpreadSheet x GASの連携ができました！

この方法で、連携するとGAS上でスプレッドシートを呼びやすくなります

GASでFormを制御する

本章では、GASを用いてFormを制御していきます。
以下に、詳しく解説します。

GASの書き方

GASでは関数単位で実行やトリガー紐付けなどが可能です。
今回は、mainという関数で記述していきます。

main.gs

function main() {
 // ここに書く
}

変数の定義

まず、変数を定義します。max_numは上限。date_cntは今回は、時間ごとに定員を決めるということで17:00から10分刻みに作成しています。(任意のものに設定可能です)

  var max_num = 5
  var date_cnt = {
    '17:00': 0, '17:10': 0, '17:20': 0, '17:30': 0,
    '17:40': 0, '17:50': 0, '18:00': 0, '18:10': 0,
    '18:20': 0, '18:30': 0
  };

連携

次にシート、formと連携します。

const ss = SpreadsheetApp.getActive();
var sheet = ss.getSheetByName("<sheet_name>");
var form = FormApp.openById("<YOUR_FORM_ID>");

sheet_nameは、sheetをひらいた時、左下に表示される名前を入力します。

ex)　フォームの回答１

<YOUR_FORM_ID>は、フォーム編集するときのurlのhttps://docs.google.com/forms/d/から /editの間の文字列ですを入力してください。

 https://docs.google.com/forms/d/<YOUR_FORM_ID>/edit#settings

Spread SheetからGASを連携することによって一行で連携可能です↓(便利ですよね)

const ss = SpreadsheetApp.getActive();

スプレッドシートからデータ取得

sheet.getRange()では、取得したい範囲の左上:右下のセル番号を指定します。
sheet.getLastRow()では、現在のシートのデータがある最終行のidを取得できます。

var sheet_data = sheet.getRange('C2:D' + sheet.getLastRow()).getValues();

現時点での人数の計算

現時点の合計人数を取得します。ここで、注意したいのがsheet_dataに入っているのは上記で指定した範囲のみの行列です。
そのため、今回はC~Dを取得しており、[c,d]のように入っているはずなので、それぞれrow[0],row[1]で取得できます。
今回は、C列が時間、D列が人数です。

sheet_data.forEach(function (row) {
  key = row[0];
  value = row[1];
  date_cnt[value] += parseInt(value);
});

parseIntは、文字列から数字を抽出して整数(int)に変換してくれます！便利！！

formの情報を取得

form.getItems(FormApp.ItemType.LIST)とすることによって、formの中でlist形式のデータを取得します。今回で言うところの、入場希望時間を回答するプルダウンの部分になります。ItemTypeに関するDoc
(ラジオボタンだとSCALE,MULTIPLE_CHOICEになるのですが取得できなかったのでプルダウンにしました...なんで??)

var form_fields = form.getItems(FormApp.ItemType.LIST);

更新する枠を決定

最大受け入れ人数と比較して、まだ受け入れ可能な枠のみnew_datesに保存します。

dates = Object.keys(date_cnt);
values = Object.values(date_cnt);
 var new_dates = []
 for (i = 0; i < dates.length; i++) {
    if (values[i] < max_num) {
      new_dates.push(dates[i]);
    }
  }

Formに反映する

new_datesをGoogleFormに反映します。formの情報を取得でListオブジェクトを取ってきているのですが、他にListオブジェクトがある場合もあります。そのような時は、getTitle()で特定のfieldのみに対応させます。

  form_fields.forEach(function (item) {
    if (item.getTitle() == '入場希望時間を入力してください') {
      var listItemQuestion = item.asListItem();
      var choices = [];
      if (new_dates.length) {
        new_dates.forEach(function (date) {
        if (date != "") {
            choices.push(listItemQuestion.createChoice(date));
          }
        });
        listItemQuestion.setChoices(choices);
      } else {　
      //可能枠がなくなったら閉じる
        form.setAcceptingResponses(false);
      }

    }
  }
  )

ちなみに、予約枠を追加する時にはmax_numを変更してGASから実行すれば閉じた選択肢もちゃんと再表示されます。

GASのトリガーを設定する

Formからスプレッドシートに書き込まれたときに、このスクリプトが動くように設定します。
GASのコード=>トリガー(左側)=>トリガーを追加(右下)　
を開き、以下のように選択します。

実行する関数：main(今回記述した関数)
イベントのソース：スプレッドシートから
イベントの種類：変更時

保存を押したら、完了です！

最後に

今回は、GAS+αを用いて簡易的な予約システムを構築しました。
私事ですが今回が初めてのqiita記事投稿になりますので、記事全体についての『苦情、アドバイス,etc...』大歓迎です。
コメント欄まで！お待ちしています。

付録 -　コード全体

main.gs

function main() {
  var max_num = 35
  var date_cnt={'17:00':0,'17:10':0,'17:20':0,'17:30':0,'17:40':0,'17:50':0,'18:00':0,'18:10':0,'18:20':0,'18:30':0}
  const ss = SpreadsheetApp.getActive();
  var sheet = ss.getSheetByName("<sheet_name>");
  var form = FormApp.openById("<YOUR_FORM_ID>");
  var sheet_data = sheet.getRange('C2' + ':H' + sheet.getLastRow()).getValues()
  var form_fields = form.getItems(FormApp.ItemType.LIST);
  sheet_data.forEach(function (i) {
    date_cnt[i[0]] +=  parseInt(i[1]);
  })
  dates = Object.keys(date_cnt);
  values = Object.values(date_cnt);
  var new_dates = []
  for (i = 0; i < dates.length; i++) {
    if (values[i] <= max_num) {
      new_dates.push(dates[i]);
    }
  }
  form_fields.forEach(function (item) {
    if (item.getTitle() == '入場希望時間') {
      var listItemQuestion = item.asListItem();
      var choices = [];
      console.log(new_dates)
      // 枠に残りがあるか
      if (new_dates.length) {
        new_dates.forEach(function (date) {
          if (date != "") {
            choices.push(listItemQuestion.createChoice(date));
          }
        });
        listItemQuestion.setChoices(choices);
      } else {
        // 解答を締め切る
        form.setAcceptingResponses(false);
      }

    }
  }
  )
}