2019年5月23日 / 最終更新日時 : 2023年4月16日 officeの杜 Google

Google Apps Scriptで入力補完とプルダウンを作る【GAS】

Google Apps Script + HTML Serviceで作るお手製フォームに関する問い合わせも結構増えてきたので、いつものようにアッサリしたものではなく、もうちょっと細かく説明を加えたフォームの部品について解説をしようと思います。

今回はスプレッドシートのデータを利用して、入力補完やプルダウンメニューを作ってみます。動的なので、一度設置した後は、スプレッドシートにデータを追加するだけで、入力補完やプルダウンメニューの中身が代わります。地味ながら、スプレッドシート連携の基本になります。

1 今回使用するスプレッドシート他
2 実行結果サンプル
3 ソースコードと解説
- 3.1 GAS側コード
- 3.2 HTML側コード
4 ポイント
5 関連リンク

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

二段階でプルダウンを作りたいという人は、二段階で選択するプルダウンを作るを参照してください。

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

実行結果サンプル

今回は、スプレッドシート上のダイアログではなく、フォームとして表示しています。これをウェブアプリケーションとして作ったフォームなどで使用すると効果バツグンです。

※入力補完のほうは、「新宿」と入れると補完データが出てきます。プルダウンはそのままクリックして選ぶだけです。また、例えばコードと商品名の2列で商品名を選ぶとコードが入るといったようなトリッキーな作り方も可能です。

<br />
図：サンプルです。実際に動作します。

ソースコードと解説

GAS側コード

//このスプレッドシートのID
var sheet = "ここにスプレッドシートのIDを入力";

//HTMLで作ったフォームを表示する
function doGet(){
  var output = HtmlService.createHtmlOutputFromFile("index")
                .setSandboxMode(HtmlService.SandboxMode.IFRAME)
                .setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
                
  return output;
}

//入力補完用データをHTML側に返してあげる関数
function hokanman(){
  //シートを取得
  var ss = SpreadsheetApp.openById(sheet);
  var list = ss.getSheetByName("でーた").getRange("A2:A").getValues();
  
  //取得データを返す
  return JSON.stringify(list);
}

//ドロップダウン用データをHTML側に返してあげる関数
function dropman(){
  //シートを取得
  var ss = SpreadsheetApp.openById(sheet);
  var list = ss.getSheetByName("でーた").getRange("B2:B").getValues();
  
  //取得データを返す
  return JSON.stringify(list);

}

//このスプレッドシートのID

var sheet = "ここにスプレッドシートのIDを入力";

//HTMLで作ったフォームを表示する

function doGet(){

var output = HtmlService.createHtmlOutputFromFile("index")

.setSandboxMode(HtmlService.SandboxMode.IFRAME)

.setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);

return output;

}

//入力補完用データをHTML側に返してあげる関数

function hokanman(){

//シートを取得

var ss = SpreadsheetApp.openById(sheet);

var list = ss.getSheetByName("でーた").getRange("A2:A").getValues();

//取得データを返す

return JSON.stringify(list);

}

//ドロップダウン用データをHTML側に返してあげる関数

function dropman(){

//シートを取得

var ss = SpreadsheetApp.openById(sheet);

var list = ss.getSheetByName("でーた").getRange("B2:B").getValues();

//取得データを返す

return JSON.stringify(list);

}

グローバル変数としてsheetにこのスプレッドシートのIDを直接入れています。ダイアログボックスの場合には必要ないのですが、フォームとして表示する場合、SpreadsheetApp.getActiveSpreadsheet()では値を取得できない為です。
フォーム表示は必ず、doGet()でコードを書きます。1個しか使えません。
.setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL)は本来不要です。これは、G Suite外のページにGASアプリを貼り付ける場合に使うオプションです。
hokanmanという入力補完用データを送る関数と、dropmanというドロップダウンメニュー用のデータを送る関数を作っています。
どちらも殆ど同じコードです。グローバル変数であるsheetに入ってるIDを使ってスプレッドシートを取得し、「でーた」というシート内の範囲を指定して、getValuesで値を取得しています。取得した値は配列になっています。
範囲指定が、B2:Bとなっていたりするのは、常にB列の値を下限なしで取得する為です。B2からBの一番下まで取得してくれるので、終わりのセル番地指定は数字が付きません。この指定方法をすることで、スプレッドシートに値を追加するだけで勝手に拾ってくれるようになります。コードの修正が必要ありません。
最後に素直にreturnではなく、JSON.stringifyという関数で処理して返してるのは、そのままでは配列データをHTML側へ送れない為です。単一の値ならばこの処理は必要ないですが、配列の場合には必須となっています。

HTML側コード

<head>
  <!-- jQueryのライブラリ類を読み込む -->
  <script src="//ajax.googleapis.com/ajax/libs/jquery/1.10.2/jquery.min.js"></script>
  <link rel="stylesheet" href="//ajax.googleapis.com/ajax/libs/jqueryui/1.10.2/themes/smoothness/jquery-ui.css" />
  <script src="//ajax.googleapis.com/ajax/libs/jqueryui/1.10.2/jquery-ui.min.js"></script>
  <link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons1.css">
  
  <style>
  /* 入力補完用のCSS設定 */
  .ui-autocomplete {
      max-height: 200px;
      overflow-y: auto;
      overflow-x: hidden;
      padding-right: 20px;
  }
  
  /* ラベル用のCSS設定 */
  label {
      float: left;
      margin-right: 0.5em;
      color: black;
      font-size: 15px;
  }
  </style>

  <script type="text/javascript">
  //入力補完用データを入れる為のグローバル変数
  var hokan = "";
  
  //スプレッドシート側からデータを取得する
  google.script.run.withSuccessHandler(onSuccess2).dropman();
  google.script.run.withSuccessHandler(onSuccess).hokanman();
  
  //入力補完を作成する
  function onSuccess(data){
      var json = JSON.parse(data);
      hokan = json;
   
      //入力補完データを入れる配列
      var word = [];
      //hokanよりデータを取得して配列に格納
      for(var i = 0;i<hokan.length;i++){
         word.push(String(hokan[i]));
      }
   
      //配列データを元にテキストボックスに入力補完を追加
      $( "#kinoko" ).autocomplete({
        source: word
      });
  }
 
  //取得したデータでドロップダウンメニューを作って設置
  function onSuccess2(data){
    var json = JSON.parse(data);
    var datalength = json.length;
    
    //ラベルを入れる
    var html = "<label>選択欄：</label>";
  
    //selectタグの頭を入れる
    html += "<select title='プルダウンより選択'><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("takenoko").innerHTML = html;
  
  }
  </script>
</head>

<!-- 入力補完用のテキストボックス -->
<div class="ui-widget">
    <p>
    <label for="jquery-ui-autocomplete-label">入力欄：</label>
    <input id="kinoko" /></p>
</div>

<!-- ドロップダウンメニュー設置場所 -->
<!-- ドロップダウンメニュー設置場所 -->
<div id="takenoko">
  <img border="0" src="https://officeforest.org/wp/library/ProgressSpinner.gif" width="20" height="20">
</div>

<head>

<style>

/* 入力補完用のCSS設定 */

.ui-autocomplete {

max-height: 200px;

overflow-y: auto;

overflow-x: hidden;

padding-right: 20px;

}

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

label {

float: left;

margin-right: 0.5em;

color: black;

font-size: 15px;

}

</style>

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

var hokan = "";

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

google.script.run.withSuccessHandler(onSuccess2).dropman();

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

//入力補完を作成する

function onSuccess(data){

var json = JSON.parse(data);

hokan = json;

//入力補完データを入れる配列

var word = [];

//hokanよりデータを取得して配列に格納

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

word.push(String(hokan[i]));

}

//配列データを元にテキストボックスに入力補完を追加

$( "#kinoko" ).autocomplete({

source: word

});

}

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

function onSuccess2(data){

var json = JSON.parse(data);

var datalength = json.length;

//ラベルを入れる

var html = "<label>選択欄：</label>";

//selectタグの頭を入れる

html += "<select title='プルダウンより選択'><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("takenoko").innerHTML = html;

}

</script>

</head>

<p>

</div>

</div>

今回はGoogleがホストしてるライブラリをここで読み込んでいます。
jQuery本体、UI用CSS、UI用ライブラリおよび、ドロップダウンメニュー用として、Google提供のCSSを読み込ませています。
styleタグ内に、入力補完用の追加のCSSと、Labelタグ用のCSSを書いています。
入力補完で用いるデータの入れ物として、hokanを用意しました。
Google Apps ScriptのHTML側特有の関数として、GAS側の関数を叩き、返ってきた値を取得する為に、google.script.run.withSuccessHandlerを使用しています。続けて、onSuccessやonSuccess2が値が返ってきたら実行する関数名で、続けて記述されてるhokanman()やdropman()がGAS側の関数名です。
hokanman()からリターンされて来たデータをonSuccessの引数であるdataで受け取っています。
そのままではdataは扱えないので、JSON.parseで配列に戻してあげています。
別に用意したwordという配列にString関数で変換したhokanmanのデータを入れてあげています。Stringしてあげないと上手く入力補完されません。
idがkinokoであるテキストボックスに対して、autocomplete機能をjQueryで追加してあげています。sourceはもちろん先ほど用意したword配列です。
HTMLデータの生成部分で、selectのoptionタグ部分を作っています。json変数にdataが入っていますので、取り出しつつ、html変数に追加しています。
ローディング中っぽく見せる為に、ドロップダウンメニュー用のDIVエリアには、プログレスサークルとなるGIF画像を置いておきました。

ポイント

今回はプルダウンメニューはGoogleのAddon CSSを利用して表示していますが、これもjQueryのセレクトメニューで作ってみるのも良いと思います。
前回の入力補完の記事作成時(2016年頃）はまだ、sandboxmode.iFrameが存在していなかった頃なので、現在は省略可能です。
プルダウンメニューの作成はGAS側でやらせ、塊だけをHTML側に送る手もありますが、HTML側でやる場合、処理時間はクライアントのマシンパワーに依存します。
今回、使用するスプレッドシートのIDはコード内で直書きしていますが、スクリプトプロパティに格納しておき、呼び出す手法がスマートでお勧めです。最初のセットアップ時だけスプレッドシート起動時にonOpenでシートのIDを格納するコードを用意して実行させると良いでしょう。