2020年3月15日 / 最終更新日時 : 2022年7月12日 officeの杜 Google

Google Apps Scriptで二段階で選択するプルダウンを作る【GAS】

Google Apps Scriptにて、様々なウェブアプリケーションを作る際に結構な頻度でぶつかる問題が「プルダウンで値を選択するのが大変」問題。選択肢が10個程度なら普通にプルダウンで作れば良いのですが、それが100個もあったら・・・そこで使うのが入力補完だったりするのですが、これは、利用者が言葉をきちんと断片でも知ってる必要があります。

そこで選択するレコードに分類を設けてフィルタしながらプルダウンを動的に生成して、選んでもらう方式ならば、100個もプルダウンで並べる必要はなくなり、利用者の負担も減ります。今回はこれをスプレッドシート連携で作ってみます。

※今回のコードはV8 Runtimeでテストしています。

1 今回使用するスプレッドシート
2 実行サンプル
3 ソースコード
- 3.1 GAS側コード
- 3.2 HTML側コード
4 関連リンク

今回使用するスプレッドシート

キノコ分類表 - スプレッドシート

2つのシートで構成されており、種類が1個目のプルダウンになります。これを選択するとキノコ名から同じ種類のキノコデータだけを引っ張ってきて、2個目のプルダウンを動的に生成します。

メニューからキノコ情報⇒ピックアップを選ぶとダイアログが出ます。プルダウンを選択すると2個目が動的に生成され、2個目を選ぶと説明文がalert()で表示される仕組みです。

図：キノコを選択は分類に応じて変化する

実行サンプル

<br />

ソースコード

GAS側コード

//入力用ダイアログを表示する
function htmlbox() {
  　var output = HtmlService.createTemplateFromFile('index');
  　var ss = SpreadsheetApp.getActiveSpreadsheet();
  　var html = output.evaluate().setSandboxMode(HtmlService.SandboxMode.IFRAME)
             .setWidth(500)
             .setHeight(150);
  　ss.show(html);    //メッセージボックスとしてを表示する
}

//１個目のプルダウン用
function bunrui(){
  //シートを取得
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var list = ss.getSheetByName("種類").getRange("A2:A").getValues();
  
  //取得データを返す
  return JSON.stringify(list);
}
 
//抽出用プルダウン用
function kinoko(bunrui){
  //シートを取得
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var list = ss.getSheetByName("キノコ名").getRange("A2:C").getValues();
  
  //配列を用意
  var array = [];
  
  //bunruiに基づきフィルタした値を配列にpush
  for(var i = 0;i<list.length;i++){
    //分類に合致したらpush
    if(list[i][0] == bunrui){
      array.push(list[i]);
    }
  }

  //取得データを返す
  return JSON.stringify(array);
}

//入力用ダイアログを表示する

function htmlbox() {

　var output = HtmlService.createTemplateFromFile('index');

　var ss = SpreadsheetApp.getActiveSpreadsheet();

　var html = output.evaluate().setSandboxMode(HtmlService.SandboxMode.IFRAME)

.setWidth(500)

.setHeight(150);

　ss.show(html); //メッセージボックスとしてを表示する

}

//１個目のプルダウン用

function bunrui(){

//シートを取得

var ss = SpreadsheetApp.getActiveSpreadsheet();

var list = ss.getSheetByName("種類").getRange("A2:A").getValues();

//取得データを返す

return JSON.stringify(list);

}

//抽出用プルダウン用

function kinoko(bunrui){

//シートを取得

var ss = SpreadsheetApp.getActiveSpreadsheet();

var list = ss.getSheetByName("キノコ名").getRange("A2:C").getValues();

//配列を用意

var array = [];

//bunruiに基づきフィルタした値を配列にpush

for(var i = 0;i<list.length;i++){

//分類に合致したらpush

if(list[i][0] == bunrui){

array.push(list[i]);

}

//取得データを返す

return JSON.stringify(array);

}

今回はスプレッドシート上のダイアログとしてHTML Serviceを表示するようにしました。
bunruiは種類シートの値を取得してHTML側へ返します。
kinokoはHTML側からの分類を引数で取って、キノコ名シートのデータをフィルタしてデータを返します。

HTML側コード

<head>
  <!-- 外部ライブラリ類を読み込む -->
  <link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons1.css">
  
  <style>
  /* ラベル用のCSS設定 */
  label {
      float: left;
      margin-right: 0.5em;
      color: black;
      font-size: 15px;
  }
  </style>

  <script type="text/javascript">
  //入力補完用データを入れる為のグローバル変数
  var kinokodb = "";
  
  //スプレッドシート側からデータを取得する
  google.script.run.withSuccessHandler(onSuccess).bunrui();
  
  //取得したデータでドロップダウンメニューを作って設置
  function onSuccess(data){
    var json = JSON.parse(data);
    var datalength = json.length;
    
    //ラベルを入れる
    var html = "<label>分類を選択：</label>";
  
    //selectタグの頭を入れる
    html += "<select title='プルダウンより選択' onchange='changeman()' id='kinosyu'><option>分類を選択して下さい</option>";
  
    //HTMLデータの生成
    for(var i = 0;i<datalength;i++){
      //空データの場合ループ脱出
      if(json[i] == ""){
        break;
      }
      
      //オプション項目を追加
      html += "<option>" + json[i] + "</option>"
    }
  
    //selectタグの下を入れる
    html += "</select><p>";
    
    //プルダウンメニューを設置する
    document.getElementById("bunrui").innerHTML = html;
  }
  
  //取得したデータでドロップダウンメニューを作って設置
  function onSuccess2(data){
    //display属性を一時的に変更
    document.getElementById("progress").style.display = "none"
    document.getElementById("pulldown").style.display = "block"
    
    //データを取得する
    var json = JSON.parse(data);
    var datalength = json.length;
    
    //kinokodbにも格納する
    kinokodb = json;
    
    //ラベルを入れる
    var html = "<option selected>キノコを選択して下さい</option>"
    
    //HTMLデータの生成
    for(var i = 0;i<datalength;i++){
      //空データの場合ループ脱出
      if(json[i][0] == ""){
        break;
      }
      
      //オプション項目を追加
      html += "<option>" + json[i][1] + "</option>"
    }

    //プルダウンメニューを設置する
    document.getElementById("kinoko").innerHTML = html;
  }  
  
  //分類を変更したときに発火する
  function changeman(){
    //現在選択されてる分類を取得
    let bunrui = document.getElementById("kinosyu").value
    
    //分類を元にキノコの種類をリビルド
    if(bunrui == "分類を選択して下さい"){
      //何もしない
    }else{
      //display属性を一時的に変更
      document.getElementById("progress").style.display = "block"
      document.getElementById("pulldown").style.display = "none"
      
      //データを取得する
      google.script.run.withSuccessHandler(onSuccess2).kinoko(bunrui);
    }
  }
  
  //キノコを選択した場合に発火
  function kinokoman(){
    //現在選択されているキノコを取得
    let kinopi = document.getElementById("kinoko").value
    
    //キノコ名を元に説明文を表示
    if(kinopi == "キノコを選択して下さい"){
      //何もしない
    }else{
      //キノコの説明情報を探索
      for(var i = 0;i<kinokodb.length;i++){
        if(kinokodb[i][1] == kinopi){
          //アラート表示
          alert(kinokodb[i][2]);
          break;
        }
      }
    }
  }
  
  </script>
</head>

<!-- 一個目のプルダウン設置場所 -->
<div id="bunrui">
  <img border="0" src="https://officeforest.org/wp/library/ProgressSpinner.gif" width="20" height="20">
</div>

<!-- ドロップダウンメニュー設置場所 -->
<div id="selectkinoko">
  <div id="pulldown">
    <label>キノコを選択：</label>
    <select title='プルダウンより選択' onchange='kinokoman()' id="kinoko">
      <option selected>キノコを選択して下さい</option>
    </select>
  </div>
  
  <div id="progress" style="display:none">
    <img border="0" src="https://officeforest.org/wp/library/ProgressSpinner.gif" width="20" height="20">
  </div>
  
</div>

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

<head>

<style>

/* ラベル用のCSS設定 */

label {

float: left;

margin-right: 0.5em;

color: black;

font-size: 15px;

}

</style>

//入力補完用データを入れる為のグローバル変数

var kinokodb = "";

//スプレッドシート側からデータを取得する

google.script.run.withSuccessHandler(onSuccess).bunrui();

//取得したデータでドロップダウンメニューを作って設置

function onSuccess(data){

var json = JSON.parse(data);

var datalength = json.length;

//ラベルを入れる

var html = "<label>分類を選択：</label>";

//selectタグの頭を入れる

html += "<select title='プルダウンより選択' onchange='changeman()' id='kinosyu'><option>分類を選択して下さい</option>";

//HTMLデータの生成

for(var i = 0;i<datalength;i++){

//空データの場合ループ脱出

if(json[i] == ""){

break;

}

//オプション項目を追加

html += "<option>" + json[i] + "</option>"

}

//selectタグの下を入れる

html += "</select><p>";

//プルダウンメニューを設置する

document.getElementById("bunrui").innerHTML = html;

}

//取得したデータでドロップダウンメニューを作って設置

function onSuccess2(data){

//display属性を一時的に変更

document.getElementById("progress").style.display = "none"

document.getElementById("pulldown").style.display = "block"

//データを取得する

var json = JSON.parse(data);

var datalength = json.length;

//kinokodbにも格納する

kinokodb = json;

//ラベルを入れる

var html = "<option selected>キノコを選択して下さい</option>"

//HTMLデータの生成

for(var i = 0;i<datalength;i++){

//空データの場合ループ脱出

if(json[i][0] == ""){

break;

}

//オプション項目を追加

html += "<option>" + json[i][1] + "</option>"

}

//プルダウンメニューを設置する

document.getElementById("kinoko").innerHTML = html;

}

//分類を変更したときに発火する

function changeman(){

//現在選択されてる分類を取得

let bunrui = document.getElementById("kinosyu").value

//分類を元にキノコの種類をリビルド

if(bunrui == "分類を選択して下さい"){

//何もしない

}else{

//display属性を一時的に変更

document.getElementById("progress").style.display = "block"

document.getElementById("pulldown").style.display = "none"

//データを取得する

google.script.run.withSuccessHandler(onSuccess2).kinoko(bunrui);

}

//キノコを選択した場合に発火

function kinokoman(){

//現在選択されているキノコを取得

let kinopi = document.getElementById("kinoko").value

//キノコ名を元に説明文を表示

if(kinopi == "キノコを選択して下さい"){

//何もしない

}else{

//キノコの説明情報を探索

for(var i = 0;i<kinokodb.length;i++){

if(kinokodb[i][1] == kinopi){

//アラート表示

alert(kinokodb[i][2]);

break;

}

</script>

</head>

</div>

<label>キノコを選択：</label>

<option selected>キノコを選択して下さい</option>

</select>

</div>

</div>

グローバル変数kinokodbにはキノコ名シートのデータを格納します。
ダイアログ表示時は自動でbunrui()を実行して、まず分類のプルダウンをonSuccessで生成します。
分類のプルダウンにはchangeman()というonChangeイベントを割り当てています。
changeman発動で、選んだ分類名を引数として取り、二個目のプルダウンデータをkinoko()を実行してonSuccess2で生成します。
キノコ名のプルダウンにはkinokoman()というonChangeイベントを割り当てています。
kinokoman()では、選んだキノコに該当するデータをkinokodbから検索し、ヒットした説明文データをalert()で表示しています。
2個目のプルダウン領域には、progressの表示・非表示を入れていますがこれは、スプレッドシートからデータを取得してプルダウンを組み立てるまでに若干タイムラグがあるので、実運用時は実は大切な処理だったりします。その間にプルダウンをいじらせない為でもあります。