見出し画像

【GAS活用術⑦-1】Googleフォームのプルダウンの選択肢をスプレッドシートから自動生成・人名リスト編

Hoshi-nok（ほし のっく）

Google Apps Script (GAS)を、もっと身近に、日々の暮らしに。

前回に小ネタとして「スプレッドシートに独自メニューを追加する」話をしましたが、今回は、Googleフォームに関連する中ネタ（？）の話です。

Googleフォームでは、リストから値を選択する「プルダウン」の項目を設定できます。

このように、例えば人の名前としてあらかじめ選択肢を作成しておくと、実際の入力フォームでは

こんな風にリストで表示されて選ぶだけでいいので入力が楽になります。

今回は、このプルダウンの選択肢を、スプレッドシートの担当者のリストをもとに自動生成する方法についてご紹介していきます。

スプレッドシート側で「名前付き範囲」を定義しておく

いろいろな方法がありますが、今回は「名前付き範囲」を使用する方法を紹介します。

フォームに紐づいたシートと同じスプレッドシート内に、シートを追加して、担当者の一覧表を作成します。シートの名前は「担当者一覧」とします。

次に、スプレッドシート上で、対象データ（ここでは担当者のリスト）に名前をつけます。手順は以下の通りです。

  • スプレッドシートで、対象データ（ここでは担当者の一覧）を選択

  • メニューから データ ＞ 名前付き範囲 をクリック

  • 名前付き範囲で、名前を入力して、完了ボタンをクリック

簡単ですね。一度名前をつければ範囲を選択した時に、名前ボックスに名前が表示されるようになります。

スプレッドシート側にGASを設定

フォーム側にも記述できますが、今回はスプレッドシート側のGASにします。メニューバーから、拡張機能 > Apps Script でApps Scriptを起動し、以下のスクリプトをコピペします。

function updatePeopleList(){
  //アクティブなスプレッドシートから名前付き範囲"担当者名"の値を一次元配列で取得
  let ss = SpreadsheetApp.getActiveSpreadsheet();
  let people = ss.getRangeByName("担当者名").getValues().flat();
  
  //フォームの対象項目のプルダウンの選択肢を配列の値で更新
  let form = FormApp.openByUrl(ss.getFormUrl());  //連携しているフォームを取得 
  let items = form.getItems();                //フォーム内の全アイテムを取得
  let item = items[4];                        //0番目から数えて何番目のアイテムか指定
  item.asListItem().setChoiceValues(people);  //配列peopleの値でプルダウンの選択肢を更新
}

上記のスクリプトで変更が必要なのは以下の二箇所です。

  • 名前付き範囲の名前（ss.getRangeByName("担当者名")の担当者名）
    名前付き範囲の名前を"担当者名"以外にした場合は、スクリプトのss.getRangeByNameの後の (" ")の中を変更してください。

  • 何番目のアイテムかの指定（items[4]の4の数字）
    フォームで、選択肢を更新する対象のアイテムが何番目にあるか、数えます。上から0、１、2、、と0番目からスタートして数えてください。
    この数え方で今回、私の対象のアイテムは、4番目なので、items[4]にしています。必要に応じて変更してください。

基本、これだけです。

まだ名前を付けていない場合は、「無題のプロジェクト」をクリックして、プロジェクト名に変更し、ツールバーの保存アイコンで保存します。

スクリプトエディタから実行する

スクリプトエディタのツールバーに、updatePeopleList関数が表示されていることを確認したら、実行ボタンをクリックします。

「承認が必要です」と言われた場合は、【GAS活用①-3】のページを参考にして「実行権限の承認」を行ってください。その後、再度、実行ボタンをクリックしてみましょう。

対象のフォームの編集画面を開き、選択肢がシートから自動生成されたか、確認します。

ちゃんと選択肢が変わっていますね。ちなみに、選択肢の順番はスプレッドシートと同じ順なので、例えば、シート側であいうえお順に並べ替えて

その後、再度、updatePeopleList関数を実行すれば、フォームの選択肢もあいうえお順になります。

その他、人を増やしたり減らしたり、名前を変更したり、といろんなパターンを試してみてください。

実行方法について

今回、担当者のリストを使用しましたが、当然、人が増えたり減ったり、名前が変わったりということが考えられます。

そのような場合では、スプレッドシートの担当者のリストを変更し、updatePeopleList関数を実行して、フォーム側にその値を反映させればよい訳です。

こういう時にお勧めなのが、【GAS活用術⑥】で紹介した、独自メニューに追加して関数を実行する方法です。

「シートで人の名前の変更をして、シートの独自メニューから、フォームのプルダウンの選択肢に反映させる関数を実行する」という流れは、スプレッドシート内で完結するのでわかりやすいですよね。

他にも、スプレッドシートが変更されたら自動で実行されるように、「スプレッドシート編集時」のイベントのトリガーを設定する、などの方法はありますが、個人的にはこの場合は明示的に実行した方がよいと思います。

コード解説

名前付き範囲の値を取得する時は「どのシート」は不要

少々細かい話ですが、備忘録を兼ねて。通常、スプレッドシートのGASでは、

  • 「どのスプレッドシート」（getActiveSpreadsheet）の

  • 「どのシート」（getSheets()[0]やgetSheetByName("シート名")）の

  • 「どの範囲」（getRangeやgetDataRange）の

  • 値を取得する（getValuesやgetDisplayValues）

と段階を踏んで、値を取得します。ですが、名前付き範囲の場合は、「どのシート」の指定をせずに、getRangeByNameメソッドを使っています。

  //アクティブなスプレッドシートから名前付き範囲"担当者名"の値を一次元配列で取得
  let ss = SpreadsheetApp.getActiveSpreadsheet();
  let people = ss.getRangeByName("担当者名").getValues().flat();

実は、名前付き範囲はスプレッドシート全体でユニーク（一意）となるので、「どのスプレッドシート」かさえ指定すれば「どのシート」かは指定不要なのです。

flatメソッドでデータを取り扱いやすく

getValuesの後のflatメソッドについても補足していきます。今回の担当者名はスプレッドシートでは１列方向に並んでいるので、GASで値を読み込むと二次元配列として読み込みます。

二次元配列は、【GAS活用術①-7】で紹介したように行のインデックス番号、列のインデックス番号をもちます。

今回は、列のインデックス番号は0だけになります。マンションにたとえるなら、各階に部屋が一つの、縦に細長～いマンションになります。flatメソッドはこれを平屋（一次元配列）に変えてくれます。

二次元配列はインデックス番号が行と列の２つあり、なにかと扱いにくいので、flatメソッドで一次元配列しておくと便利です。

今回のプルダウンの選択肢を作成するsetChoiceValuesでは、縦に細長い二次元配列を指定しても問題ありませんでしたが、念のため、一次元配列にしてあります。

スプレッドシートに連携しているフォームを特定する方法

スプレッドシートから、連携しているフォームを開くときには、メニュー ＞ ツール ＞ フォームを編集 または 実際のフォームを開く を選択しますよね。

ということは、スプレッドシートに連携しているフォームは一つに決まるので、GASで取得できるはず、と思っていました。ですが、しばらくその方法がわかりませんでした。

で、最近ようやくわかったのがこちらです。

  let form = FormApp.openByUrl(ss.getFormUrl());  //連携しているフォームを取得

一旦、スプレッドシートに連携しているフォームのURLを取得（getFormUrl）し、そのURLからフォームを取得（openByUrl）しています。これでスプレッドシートに連携しているフォームの取得ができます。

この方法を使わない場合は、スプレッドシートからは以下のようにフォームのIDを指定してフォームを取得する必要があります。

let form = FormApp.openById('★フォームのID★');

このフォームのIDは、フォームの編集画面のURLの一部を指定します。

もちろんフォームのIDを調べてopenByIdで指定してもよいのですが、「連携しているフォームって言えばそれでわかるよね？！」と強気に出て、今回はopenByUrl(sheet.getFormUrl())を使っています。

次回は日付の選択肢

「フォームのプルダウンの選択肢をスプレッドシートから自動生成する方法・人名リスト編」をご紹介しました。今後、紹介するGAS活用システムの中でもちょいちょいこの方法が出てくる予定です。

次回は、【GAS活用術⑦-2】としてスプレッドシートから日付の選択肢を自動生成する時のポイントを紹介したいと思います。


この記事が気に入ったらサポートをしてみませんか？