update dropdown from spreadsheet in apps script

Question

I'm trying to learn Google's HTML Service UI service and am struggling to figure out how to update a dropdown list in a UI from data in a spreadsheet. I copied the following code from this Google Tutorial, which works fine. However, if I want to populate a dropdown using and to replace and

below, it doesn't seem to work.

<p>List of things:</p>
<ul id="things">
    <li>Loading...</li>
</ul>

<script
src="//ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js">
</script>
<script>
// The code in this function runs when the page is loaded.
$(function() {
  google.script.run.withSuccessHandler(showThings)
      .getLotsOfThings();
});

function showThings(things) {
  var list = $('#things');
  list.empty();
  for (var i = 0; i < things.length; i++) {
    list.append('<li>' + things[i] + '</li>');
  }
}
</script>

Answer 1

The following Apps Script project files use Spreadsheet data to fill a drop-down select box in the UI. In the main Apps Script project file (default name is Code.gs), include:

function doGet(request) {
  return HtmlService.createTemplateFromFile('DropDown')
         .evaluate().setSandboxMode(HtmlService.SandboxMode.NATIVE); 
}


function getMenuListFromSheet() {
  return SpreadsheetApp.openById(*SPREADSHEET_ID*).getSheets()[0]
         .getRange(2,1,4,1).getValues();  
}

You will need to replace *SPREADSHEET_ID* with the ID of the spreadsheet containing the data you want to use to fill the select box. This example takes the data in the first sheet's A2:A5 range as the data to use (defined in the getRange() function).

Note also that this example uses NATIVE sandbox mode, which is more forgiving than the default EMULATED mode.

This example also needs an HTML file in the Apps Script project(named 'DropDown.html' here):

<p>List of things:</p>
<ul id="things">
    <li>Loading...</li>
</ul>

<select id="menu">
  <option></option>
  <option>Google Chrome</option>
  <option>Firefox</option>
</select>


<script
src="//ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js">
</script>
<script>
// The code in this function runs when the page is loaded.
$(function() {
  google.script.run.withSuccessHandler(showThings)
      .getMenuListFromSheet();
  google.script.run.withSuccessHandler(showMenu)
      .getMenuListFromSheet();
});

function showThings(things) {
  var list = $('#things');
  list.empty();
  for (var i = 0; i < things.length; i++) {
    list.append('<li>' + things[i] + '</li>');
  }
}

function showMenu(menuItems) {
  var list = $('#menu');
  list.find('option').remove();  // remove existing contents

  for (var i = 0; i < menuItems.length; i++) {
    list.append('<option>' + menuItems[i] + '</option>');
  }
}

</script>

This HTML file consists of a single list and a single select box, both with default contents. When the page is loaded, the contents of both will be replaced with the contents provided by the getMenuListFromSheet() function, which draws its returned value from the spreadsheet.

You can create these Apps Script project files bound to a Spreadsheet container, and then publish them as a web app.