Google Sheets – Multiple Auto-Populated Select Boxes Script UI

google sheetsgoogle-apps-script

I found an example of how to auto-populate a drop-down select box from a Google Sheets range, for a Google Apps Script UI that enters new rows in the spreadsheet. I have so far been unable to figure out how to create multiple drop-down select boxes in the UI form. I would appreciate some hints on how to accomplish this.

I placed an example at Test UI Form that inserts new row that can be copied and tested. (Much appreciation to the fellow who shared the tutorials on his blog. I bought his ebook.)

In code.gs, the revelent code is

function getValuesForRngName(rngName) {
  var rngValues = SpreadsheetApp.getActiveSpreadsheet().getRangeByName(rngName).getValues();
  return rngValues.sort();
}

//Expand the range defined by the name as rows are added
function setRngName() {
  var ss = SpreadsheetApp.getActiveSpreadsheet(),
    sh = ss.getSheetByName('DataLists'), firstCellAddr = 'C2',
    dataRngRowCount = sh.getDataRange().getLastRow(),
    listRngAddr = (firstCellAddr + ':C' + dataRngRowCount),
    listRng = sh.getRange(listRngAddr);
  ss.setNamedRange('Cities', listRng);   
}

The html file looks like this:

<div>
  <form>
    <table>
      <tr>
        <td>Select item</td><td><select name="city_list" id="city_list"></select></td>
      </tr>            
      <tr>
        <td><br/>
  <br />


   <script type="text/javascript">
    // Client-side JavaScript that uses the list returned by
    // GAS function "getValuesForRngName()" to populate the dropdown.
    // This is standard client-side JavaScript programming that uses
    // DOM manipulation to get page elements and manipulate them.
    function onSuccess(values) {
      var opt,
          dropDown;
        for(i = 0;i < values.length; i +=1){
          dropDown = document.getElementById("city_list");
          //dropDown = document.getElementsByClassName('city_list'); ///remove this
          opt = document.createElement("option");
          dropDown.options.add(opt);
          // Remember that GAS Range method "GetValues()" returns
          // an array of arrays, hence two array indexes "[i][0]" here.
          opt.text = values[i][0];
          opt.value = values[i][0];    
       }    
    }        
    function populate() {
      google.script.run.withSuccessHandler(onSuccess).getValuesForRngName('Cities');
    }
    </script> 

    First name: <input id="firstname" name="firstName" type="text" /><br/><br/>
    Last name: <input id="lastname" name="lastName" type="text" /><br/><br/>

    <input onclick="formSubmit()" type="button" value="Add Row" />
    <script type="text/javascript">

    function formSubmit() {
      google.script.run.getValuesFromForm(document.forms[0]);
    }
    </script>

        </td><td><br/><br/><input onclick="google.script.host.close()" type="button" value="Exit" /></td>
      </tr>
    </table>
  </form>
</div>

<script>
  // Using the "load" event to execute the function "populate"
  window.addEventListener('load', populate);
</script>

Best Answer

Add a new select with a new id, and assign a set of values the same way.

This should give a good idea how to fill multiple drop-downs from one data array where each column sets the drop-down value.

function onSuccess(values) {
//values is a 2d array.

      var opt,
      dropDown;

    for(i = 0;i < values.length; i++){
      dropDown_one = document.getElementById("id_one");
      if (values[i][0]) { //check if there actually is a value
      opt = document.createElement("option");
      dropDown.options.add(opt);
      opt.text = values[i][0]; //0 is first column
      opt.value = values[i][0]; 
      dropDown_one.options.add(opt);

      }

      dropDown_two = document.getElementById("id_two");
      if (values[i][1]) { //check if there actually is a value
      opt = document.createElement("option");
      opt.text = values[i][1]; //1 is second column
      opt.value = values[i][1]; 
      dropDown_two.options.add(opt);

      }
   }    
}
Related Topic