Googleフォームのプルダウンにスプレッドシートの内容を反映させる #JavaScript

備忘録としてメモします。

はじめに

Googleフォームのプルダウン内にスプレッドシートの内容を反映させたい要望があったので対応しました。
（手打ちでスプレッドシートの内容をGoogleフォームに入力するのって面倒ですよね、、、）

やったこと

スプレッドシート側で値の変更をして保存した場合、
今回作成したスクリプト（スプレッドシートの特定の列の内容をGoogleフォームのプルダウンに反映させる）が
実行される。

手順

①スプレッドシート側に適当にデータを入力する
②Googleフォーム側に適当に項目を追加する
③スクリプトを作成する
④実行する
⑤スプレッドシート側の値が変更されたら自動でスクリプトが実行されるように設定する

①スプレッドシート側に適当にデータを入力する

以下のように適当にデータを入力します。

②Googleフォーム側に適当に項目を追加する

以下のように適当に項目を追加しました。

③スクリプトを作成する

①で作成したスプレッドシートを開き以下の手順でスクリプトエディタを開きます。
ツール>スクリプトエディタ
そして、
以下のコードをコピペして下さい。
（※スプレッドシートとGoogleフォームのIDは、自分のものに変更して下さい）

function overwriteDjList() {

  /**
  // スプレッドシートの情報を取得する
  // 
  **/

  //スプレッドシートのID　→「https://docs.google.com/spreadsheets/d/△△△/edit#gid=0」の△△△を↓に記述
  var sheets = SpreadsheetApp.openById('△△△').getSheets();

  // シート1の情報を取得
  //（デフォルトのスプレッドシートの場合、シート1という名称になっています）
  var sheet = sheets[0];

  // スプレッドシートのA1のセルが「DJ」の場合
  if("DJ" == sheet.getRange("A1").getValue()){

    // A行の2行目からコンテンツをもつ最後の行までの値を配列で取得する
    var colA = sheet.getRange(2, 1, sheet.getLastRow() - 1).getValues();
  }

  /**
  // Googleフォームのプルダウン内の値を上書きする
  // 
  **/

  // GoogleフォームのIDを設定　→「https://docs.google.com/forms/d/〇〇〇/edit」の〇〇〇を↓に記述
  var form = FormApp.openById('〇〇〇');

  // 質問項目がプルダウンのもののみ取得
  var items = form.getItems(FormApp.ItemType.LIST);

  items.forEach(function(item){
    // 質問項目が「好きなDJを選択して下さい」を含むものに対して、スプレッドシートの内容を反映する
    if(item.getTitle().match(/好きなDJを選択して下さい.*$/)){
      var listItemQuestion = item.asListItem();
      var choices = [];

      colA.forEach(function(name){
        if(name != ""){
          choices.push(listItemQuestion.createChoice(name));
        }
      });

      // プルダウンの選択肢を上書きする
      listItemQuestion.setChoices(choices);
    }
  });

}

↓こんな感じ

④実行する

実行ボタンを押すと、
許可を求められる画面が出てくるかと思いますが、許可して下さい。

うまく実行できたら、
Googleフォーム画面を確認してみましょう。
下記のように、プルダウン内にスプレッドシートの内容が反映されていれば成功です。

⑤スプレッドシート側の値が変更されたら自動でスクリプトが実行されるように設定する

スクリプトを作成したファイルを開きます。
以下のボタンを押してトリガーの設定を行います。

その後以下のようにしてトリガーの設定を行って下さい。
・トリガーが設定されていません。今すぐ追加するにはここをクリックしてください。
をクリックする
↓
・以下の通りに設定を行う
実行：
　"overwriteDjList"
イベント：
　"スプレッドシートから"
　"値の変更"
↓
・保存

以上で、スプレッドシート側で値の変更をして保存した場合、
今回作成したスクリプト（スプレッドシートの特定の列の内容をGoogleフォームのプルダウンに反映させる）が実行されます。

おわり

ちなみに、僕はSteve Aokiが好きです。

Googleフォームのプルダウンにスプレッドシートの内容を反映させる