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

Last updated at 2022-04-15Posted at 2020-12-07

Atrae Advent Calendar 2020の7日目を担当するアガツマです。
普段は、ビジネス版マッチングアプリ yenta でコミュニティマネージャーとして働いています。

はじめに

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

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

やること

以下の手順で解説していきます。

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

日程と定員のマスターデータの作成

以下のようなスプレッドシートのファイルを用意しましす

シート名を「マスター」
日程と定員を以下のように記入

Google Formを作成

選択肢を動的にしたい項目を「プルダウン」に
その項目のタイトルを「日程候補」に

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

回答のスプレッドシートを用意する

以下のボタンから回答用のスプレッドシートを作成

回答のシートの名前を「フォームの回答」に変更

使う変数を記述

※d/と/editの間のxxxがspreadSheetID

GAS

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

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

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

シートから定員を読み込む

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();    
  }

プルダウンの選択肢を更新

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;
    }
  });