2

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>
4

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

      }
   }    
}
  • 1
    I copied and pasted everything very carefully with new id, and got it working. – ImproperUsername Aug 27 '15 at 22:10
  • How would that, code wise, look for other visitors? Can you highlight the essentials please? – Jacob Jan Tuinstra Aug 28 '15 at 8:36
  • Welcome Riël, nice to have a Google Developer Expert around !! – Jacob Jan Tuinstra Aug 28 '15 at 8:45
  • 1
    You are welcome Jacob :) Ofcourse I want to help where I can. I try to look trough the Apps Script questions as often as I manage. I was on mobile, and figured that comment might get him into the right direction. If you have a second column with other dropdowns, you can run the same functoin for the second column on another ID. I will put it in the anwers – Riël Aug 28 '15 at 9:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.