Google Apps Scriptでスプレッドシートに設定している選択肢をGoogleフォームのプルダウンに設定する方法

概要

Googleフォームのプルダウンにたくさんの項目（例えば、都道府県など）を設定するのは作業効率が悪いです。

そこで、Google Apps Script（GAS）でスプレッドシートに準備してある項目を設定する方法を説明します。

今回は、概要の例えで出した、都道府県のプルダウンを作成する例で説明を進めます。

前準備

スプレッドシートに項目リストの準備

スプレッドシートに都道府県の一覧を作成します。シート名も「都道府県」と名前をつけておきます。

Googleフォームの準備

Googleフォームの準備をします。今回は質問に「都道府県」と入力して、プルダウンを選択しておきます。

ソースコード

スクリプトエディタを開く

Googleフォームのスクリプトエディタを開きます。

ソースコード

以下のソースコードをスクリプトエディターに貼り付けてください。

/**
 * スプレッドシートより、都道府県をプルダウンに設定します。
 */
function setPrefecturesChoice() {

  // スプレッドシートID
  const SPREADSHEET_ID = PropertiesService.getScriptProperties().getProperty("SPREADSHEET_ID");

  // シート名
  const SHEET_NAME = PropertiesService.getScriptProperties().getProperty("SHEET_NAME");

  // Googleフォームでの質問名
  const QUESTION_NAME = "都道府県";

  // ------------------------------------------------------------------------

  // 都道府県が設定されているスプレッドシートを取得
  const spreadsheet = SpreadsheetApp.openById(SPREADSHEET_ID);
  
  // 都道府県が設定されているシートを取得
  const sheet = spreadsheet.getSheetByName(SHEET_NAME);

  // 配列で設定されている都道府県を取得（2行目から、設定されている最終行-1まで）
  const prefecturesList = sheet.getRange(2, 1, sheet.getLastRow() - 1).getValues();

  // ------------------------------------------------------------------------

  // Googleフォームを開く
  const form = FormApp.getActiveForm();

  // リストタイプの質問だけを取得
  var listItems = form.getItems(FormApp.ItemType.LIST);

  //　取得したリストタイプの数だけ繰り返し
  listItems.forEach(function(item){

    // 質問のタイトルでリストを設定する対象の質問かチェック
    if(item.getTitle() == QUESTION_NAME) {

      // 新しい選択肢を詰める配列の準備
      var choices = [];
      
      // 選択肢の作成 ---------------------------------------------------------
      // スプレッドシートより取得した値の数だけ繰り返し
      prefecturesList.forEach(function(prefectures) {

        // 値が空ではない時
        if(prefectures != "") {

          // 新しい選択肢を作成し、配列に詰めていく
          choices.push(item.asListItem().createChoice(prefectures));
        }
      });

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

実行方法

プロパティの設定

1. 都道府県の一覧を設定したスプレッドシートのIDを確認します。

2. プロパティに、確認したスプレッドシートIDを「SPREADSHEET_ID」というキーで、スプレッドシートのシート名を「SHEET_NAME」というキーで設定します。

実行

メニューで「setPrefecturesChoice」が選択されていることを確認して、「実行」をクリックします。

実行後のプルダウンの確認

実行に成功すると、Googleフォームのプルダウンには以下のように、選択肢が設定されます。

トリガーの設定方法

スプレッドシートに設定している選択肢の修正のたびにスクリプトを実行するのは大変なので、フォームを開いた時に選択肢を設定し直すようにトリガーを設定する方法です。

1. スクリプトエディタの左側のメニューにある「トリガー」をクリックして、
   右下の「＋トリガーを追加」ボタンをクリックします。

2. 「実行する関数を選択」で「setPrefecturesChoice」を選択、
   「イベントのソースを選択」で「フォームから」を選択、
   「イベントの種類を選択」で「起動時」を選択して、「保存」ボタンをクリックします。

3. 保存したトリガーが一覧に追加されていたらトリガーの設定の成功です。