GASでGoogleFormの選択肢を動的に表示する

[Atrae Advent Calendar 2020](https://qiita.com/advent-calendar/2020/atrae)の7日目を担当するアガツマです。
普段は、ビジネス版マッチングアプリ yenta でコミュニティマネージャーとして働いています。

# はじめに

今回は、イベントの申込フォームなどに使えるGASの実装を紹介していきます。
イベントを連日開催するような場合、各日程に定員があり、満席になった日程を選択肢を消していくようなことをやっていきたいと思います。

日程ごとに、GoogleFormを用意して、満席になったらフォームを閉じるようにするなどど行った他の方法もありますが、
１つのフォームで管理できるようになるので参考にしていただければ幸いです。


# やること
以下の手順で解説していきます。

- 日程と定員のマスターデータの作成
- プルダウンの選択項目の設定
- フォームの閉じ方


## 日程と定員のマスターデータの作成
以下のようなスプレッドシートのファイルを用意しましす
- シート名を「マスター」
- 日程と定員を以下のように記入

![スクリーンショット 2020-12-07 20.11.26.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/266462/792dbf1b-0bbb-8f90-b18e-853999f00d28.png)

## Google Formを作成
- 選択肢を動的にしたい項目を「プルダウン」に
- その項目のタイトルを「日程候補」に
![スクリーンショット 2020-12-07 20.18.49.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/266462/9ffa5ae8-a247-a064-1f6f-58d31712957d.png)

## スクリプトエディタでGASを編集

![スクリーンショット 2020-12-07 20.24.29.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/266462/ae387f72-e412-1bde-290b-e62e28705aa2.png)

### 回答のスプレッドシートを用意する
以下のボタンから回答用のスプレッドシートを作成
![スクリーンショット 2020-12-07 20.36.52.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/266462/b79392a3-1893-e570-353d-de94024aadec.png)
回答のシートの名前を「フォームの回答」に変更
![スクリーンショット 2020-12-07 20.37.55.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/266462/d2805bd6-54c4-9519-eeab-92e925cd23ae.png)



### 使う変数を記述
※`d/`と`/edit`の間のxxxがspreadSheetID

```js:GAS
//https://docs.google.com/spreadsheets/d/xxx/editのxxxの部分
//マスター
var masterSpreadSheetID = 'xxx';
//回答用
var answerSpreadSheetID = 'yyy';

// シート名
var masterSheetName = 'マスター';
var answerSheetName = 'フォームの回答';

// 選択肢を動的にするプルダウンのタイトル
var questionName = '日程候補';
```

### シートから定員を読み込む
```js:GAS
  // スプレッドシートIDでシートを取得
  var masterSheets = SpreadsheetApp.openById(masterSpreadSheetID);
  var masterSheet = masterSheets.getSheetByName(masterSheetName);

  var answerSheets = SpreadsheetApp.openById(answerSpreadSheetID);
  var answerSheet = answerSheets.getSheetByName(answerSheetName);

  // マスターシートのA行の2行目から下の値を配列で取得
  var sheetLastRow = sheet.getLastRow();
  if (sheetLastRow > 1) {
    var candidate = sheet.getRange(2, 1, sheetLastRow - 1, 2).getValues();
  } else {
    return;
  }

  // 回答シートの状況も取得（A行の2行目から）
  var answerSheetLastRow = answerSheet.getLastRow();
  if (answerSheetLastRow > 1) {
    var questionNames = answerSheet.getRange(1, 1, 1, answerSheet.getLastColumn()).getValues();
    var colCount = questionNames[0].indexOf(questionName);    
    var answerData = answerSheet.getRange(2, colCount + 1, answerSheetLastRow - 1).getValues();    
  }
```


### プルダウンの選択肢を更新 
```js:GAS
  // フォーム取得→項目も取得
  var form = FormApp.getActiveForm();
  var item = form.getItems();

  // 動的に選択肢を作成
  items.forEach(function(item){
    // 名前が"日程候補"のプルダウンの項目を
    if(item.getTitle() === questionName){
      var listItemQuestion = item.asListItem();
      var choices = [];

      candidate.forEach(function(nameAndCapacity){        
        if(nameAndCapacity[0] != ""){
          // 定員が0(null) or 無回答 の場合は選択肢を表示
          if (answerData == null || nameAndCapacity[1] == 0 || nameAndCapacity[1] == ""){
            choices.push(listItemQuestion.createChoice(nameAndCapacity[0]));
          } else {
            var counter = 0;
            // 何人埋まってるか確認
            for(var i = 0; i < answerData.length; i++){
              if (nameAndCapacity[0] == answerData[i]){
                counter++;
              }
            }
            // 埋まってなければ選択肢を表示
            if (counter < nameAndCapacity[1]){
              choices.push(listItemQuestion.createChoice(nameAndCapacity[0]));
            }
          }
        }
      });

      //選択肢の残数によってFormを受け付けるかどうか判断
      if (choices.length > 0) {
        // フォームの回答を受け付ける
        form.setAcceptingResponses(true);
        listItemQuestion.setChoices(choices);
      } else {
        // 回答受付終了
        form.setAcceptingResponses(false);        
      }
      return;
    }
  });
```

### スクリプトのトリガーを設定する
「編集＞現在のプロジェクトのトリガー」を選択
![スクリーンショット 2020-12-07 20.57.51.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/266462/fcf2337c-d1a4-4c4d-c8b9-9eeececf3f3f.png)
トリガーを追加し、設定画面を開く
「フォーム送信時」にトリガーを設定
![スクリーンショット 2020-12-07 20.58.12.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/266462/742b6bed-34cb-a075-5b1c-34eb728e8b76.png)
トリガーが追加されたことを確認
![スクリーンショット 2020-12-07 20.58.23.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/266462/d7cc74fc-45c2-c457-974e-ef835f576f76.png)


# 実行結果
### スクリプト実行後
選択肢がマスターデータと同じく３つになっている
![スクリーンショット 2020-12-07 21.02.44.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/266462/d1914993-3a09-5bd0-b19a-a16b0fdf75c5.png)
### 定員が埋まった場合
２つの日程が５回以上選択された場合残り１枠になっている
![スクリーンショット 2020-12-07 21.03.06.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/266462/0d08c19c-1ce4-340c-5ae3-204b52fbf814.png)
### すべての日程が埋まった場合
フォームが閉じられる
![スクリーンショット 2020-12-07 21.03.23.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/266462/f48912fe-5132-d534-0be0-424b6ff6a488.png)


# おわりに
Google Form を便利にする方法を今回は紹介しました。
定員があるイベントを複数日程開催している方などはGASを使って効率よく管理してみてはいかがですか？

参考にさせていただいた事例として、ラジオボタンで同じようなことをされている記事がありました。
[Googleフォームで回答数により選択肢を変更する](https://yukaobu.wordpress.com/2016/10/23/googleform-2/)
[Googleフォームで回答数を制限する方法](https://yukaobu.wordpress.com/2016/04/09/googleformlimit/)