Google Sheets – Dropdown Lists Values from Spreadsheet Ranges

google sheetsgoogle-apps-script

Using Google Sheets, I wanted to populate drop down lists in a form displayed by HtmlService with range values from a Sheet. In the example below, I wanted to display a drop down list with values from G6:H8 and the next drop down list will be filtered based on the corresponding value in B6:D11.

Currently I'm using the code below but I'm looking for an option that allows drop down values to be updated just by modifying the Spreadsheet and not the HTML file. 

<form onSubmit="handleSubmit(this)">
  <select name="cuisine">
        Type of Cuisine
        <option value="Indonesian">Indonesian</option>
        <option value="Vietnamese">Vietnamese</option>
        <option value="Philippine">Philippine</option>
   </select>
   <select name="food">
        Type of Cuisine
        <option value="Adobo">Adobo</option>
        <option value="Nasi Goreng">Nasi Goreng</option>
        <option value="Bami Goreng">Bami Goreng</option>
        <option value="Pho">Pho</option>
        <option value="Pancit">Pancit</option>
        <option value="Lumpia">Lumpia</option>
   </select>
</form>

Best Answer

Your script should access the spreadsheet by its URL or Id, get the values from there, and include them in HTML. Here is an example: 

function doGet() {
  var ss = SpreadsheetApp.openByUrl("url");
  var sheet = ss.getSheetByName("Sheet1");
  var values = sheet.getRange("C6:C11").getValues();
  var options = values.map(function(row) {
    return '<option value="' + row[0] + '">' + row[0] + '</option>';
  });
  var html = '<form onSubmit="handleSubmit(this)"> <select name="food"> Type of Cuisine' + options.join('') + '</select></form>';
  return HtmlService.createHtmlOutput(html);
}
Related Topic