Google form x GoogleSpreadSheet x GAS で簡易的な予約システム構築！

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

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

# 目次
<!-- タイトルとアンカー名を編集 -->
1. [セットアップ](#Chapter1)
1. [GASで制御する](#Chapter2)
1. [付録](#Chapter3)
1. [参考文献](#reference)

<!-- 各チャプター -->
<a id="#Chapter1"></a>
# セットアップ
まず、GoogleFormを作成します。 [GoogleFormはこちら！](https://www.google.com/intl/ja_jp/forms/about/)
今回はこのような形で、作成してみます。

![スクリーンショット 2024-07-04 9.58.44.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1600285/c4b7f10e-0579-f89a-8719-82aa218c71f2.png)

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


Formで集める回答
- 名前
- 入場時間
- 予約人数

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

Formに回答されると、管理画面でもその結果を見ることができます。
![スクリーンショット 2024-07-02 18.58.02.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1600285/90ad6fa2-f56c-2f3b-0b89-017676a4f5a4.png)

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

↓今回はこんな感じになりました
![スクリーンショット 2024-07-04 9.14.05.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1600285/9a4b515b-0b40-af94-4383-a7032821b3ba.png)

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

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


<a id="#Chapter2"></a>
# GASでFormを制御する
本章では、GASを用いてFormを制御していきます。
以下に、詳しく解説します。

## GASの書き方
 GASでは関数単位で実行やトリガー紐付けなどが可能です。
 今回は、mainという関数で記述していきます。
```javascript:main.gs
function main() {
 // ここに書く
}
```

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

```javascript
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を連携することによって一行で連携可能です↓(便利ですよね)
```javascript
const ss = SpreadsheetApp.getActive();
```
<a id="#form_data"></a>
## スプレッドシートからデータ取得
`sheet.getRange()`では、取得したい範囲の`左上:右下`のセル番号を指定します。
`sheet.getLastRow()`では、現在のシートのデータがある最終行のidを取得できます。
```javascript
var sheet_data = sheet.getRange('C2:D' + sheet.getLastRow()).getValues();
```

## 現時点での人数の計算
現時点の合計人数を取得します。ここで、注意したいのが`sheet_data`に入っているのは上記で指定した範囲のみの行列です。
そのため、今回はC~Dを取得しており、[c,d]のように入っているはずなので、それぞれrow[0],row[1]で取得できます。
今回は、C列が時間、D列が人数です。
```javascript
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](https://developers.google.com/apps-script/reference/forms/item-type?hl=ja)
(ラジオボタンだとSCALE,MULTIPLE_CHOICEになるのですが取得できなかったのでプルダウンにしました...なんで??)
```javascript
var form_fields = form.getItems(FormApp.ItemType.LIST);
````
## 更新する枠を決定
最大受け入れ人数と比較して、まだ受け入れ可能な枠のみ`new_dates`に保存します。
```javascript
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の情報を取得](#form_data)でListオブジェクトを取ってきているのですが、他にListオブジェクトがある場合もあります。そのような時は、`getTitle()`で特定のfieldのみに対応させます。
```javascript
  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(今回記述した関数)
イベントのソース：スプレッドシートから
イベントの種類：変更時
![スクリーンショット 2024-07-04 9.07.37.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1600285/09d441c7-6fdb-894c-c307-795aa658e2a4.png)

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

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

<a id="#Chapter3"></a>
# 付録 -　コード全体

```javascript: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);
      }

    }
  }
  )
}
```
<a id="#reference"></a>