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

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

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

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

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

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

実行結果サンプル

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

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


図:サンプルです。実際に動作します。

ソースコードと解説

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);

}
  • グローバル変数として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>
  • 今回はGoogleがホストしてるライブラリをここで読み込んでいます。
  • jQuery本体、UI用CSS、UI用ライブラリおよび、ドロップダウンメニュー用として、Google提供のCSSを読み込ませています。
  • styleタグ内に、入力補完用の追加のCSSと、Labelタグ用のCSSを書いています。
  • 入力補完で用いるデータの入れ物として、hokanを用意しました。
  • Google Apps ScriptのHTML側特有の関数として、GAS側の関数を叩き、返ってきた値を取得する為に、google.script.run.withSuccessHandlerを使用しています。続けて、onSuccessonSuccess2が値が返ってきたら実行する関数名で、続けて記述されてる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を格納するコードを用意して実行させると良いでしょう。

関連リンク

Google Apps Scriptで入力補完とプルダウンを作る【GAS】” に対して5件のコメントがあります。

  1. 中村 より:

    「二段階で選択するプルダウンを作る」のリンクが切れています。
    正しいリンクは以下かと思います。私はたどり着けましたが他の方のため直して頂けると助かります。
    https://officeforest.org/wp/2020/03/15/google-apps-script%e3%81%a7%e4%ba%8c%e6%ae%b5%e9%9a%8e%e3%81%a7%e9%81%b8%e6%8a%9e%e3%81%99%e3%82%8b%e3%83%97%e3%83%ab%e3%83%80%e3%82%a6%e3%83%b3%e3%82%92%e4%bd%9c%e3%82%8b/

  2. officeの杜 より:

    中村さん

    officeの杜管理人です。
    リンク切れ報告大変ありがとうございました。即時修正いたしました。助かりました。

  3. 中村 より:

    上手く動作できないため、教えていただけますと助かります。
    確認した操作
    「入力補完とプルダウンスプレッドシート」に記載の
    ・入力補完とプルダウンスプレッドシート をクリックしてコピーを作成し、
    コピーしたスプレッドシートを開き、拡張機能→App Scriptから、コード.gsに記載のスプレッドシートのIDを修正し、doGetメソッドを実行することで、フォームが表示され、実行結果サンプルのような動作ができると認識しておりましたが、doGetメソッド実行後、実行完了となるが、スプレッドシート画面上にフォームは表示されませんでした。不足している操作がありましたら教えていただけますと助かります。
    宜しく御願い致します。

  4. officeの杜 より:

    このエントリーのネタは、ウェブアプリケーションとしてデプロイした状態で利用しているものになるので、doGetは実行するものではなく、ウェブアプリを表示する為の特別な関数です。

    https://officeforest.org/wp/2018/11/29/google-apps-script%e3%81%a7%e3%82%a6%e3%82%a7%e3%83%96%e3%82%a2%e3%83%97%e3%83%aa%e3%82%b1%e3%83%bc%e3%82%b7%e3%83%a7%e3%83%b3%e4%bd%9c%e6%88%90%e5%85%a5%e9%96%80/

    スプレッドシート上に表示する場合はダイアログとして表示するものになるので、出力方法が別になります。
    https://officeforest.org/wp/2018/05/12/gas_diag_sidebar/

    1. 中村 より:

      丁寧にご返信いただきましてありがとうございました!

コメントを残す

メールアドレスが公開されることはありません。 が付いている欄は必須項目です

日本語が含まれない投稿は無視されますのでご注意ください。(スパム対策)