0

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

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

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.