populating dropdown list values from google spreadsheet using HTML service

Question

Hi I am trying to get a dropdown list using html and google script. The values for the list should be populated from a google spreadsheet. After searching a lot on stackoverflow I managed the below code. But I am still not able to get the data from spreadsheet as dropdown option. There is no error but i am just getting a blank dropbox as output

Spreadsheet link:https://docs.google.com/spreadsheets/d/1qaxzTWRf4_xDhfkf-_S9Zr2m-M-9blOcpFBt4JyT5do/edit#gid=0

code.gs

function doGet() {
  var template = HtmlService.createTemplateFromFile('Index');

  var htmlOutput = template.evaluate()
      .setSandboxMode(HtmlService.SandboxMode.IFRAME);

  return htmlOutput;
}

html code

< select id = "something" > < option > Choose a option < /option>
</select > < body onload = "addList()" > < /body>
<script>
function addList(){
  var sheet = SpreadsheetApp.openById('1qaxzTWRf4_xDhfkf-_S9Zr2m-M-9blOcpFBt4JyT5do').getSheetByName('Sheet1');
  var lastRow = sheet.getLastRow();
  var myRange = sheet.getRange("C2:C" + lastRow);
  var options = new Array()
  var select = document.getElementById("something");
  var data = myRange.getValues();

  for(var i = 0; i < lastRow; i++) {
    options.push(data[i]);
  }

  for(var i = 0; i < options.length; i++) {
    var opt = options[i];
    var el = document.createElement("option");
    var el = document
    el.text = opt;
    el.value = opt;
    select.appendChild(el);
  }
</script >

look at the htmlService official docs. you are mixing client with server code which will never work. the docs explain well how to do this (to call a server function from the client side, or to include the data as part of the template) — Zig Mandel
possible duplicate of HTML select option to be captured from Google Spreadsheet — Mogsdad

Alan Wells Alan Wells · Accepted Answer · 2015-05-20T15:35:24

You need to totally restructure your code. This is how it should be:

Index.html

<select id="something">
  <option> Choose a option </option>
</select >

<body onload = "addList()"></body>

<script>
  function addList() {
    console.log('addList ran!');

    google.script.run
      .withFailureHandler(onFailure)
      .withSuccessHandler(injectMyContent)
      .getDropDownContent();
  };

  window.injectMyContent = function(argReturnedData) {
    for(var i = 0; i < argReturnedData.length; i++) {
      var opt = argReturnedData[i];
      var el = document.createElement("option");
      var el = document
      el.text = opt;
      el.value = opt;
      select.appendChild(el);
    };
  };

  window.onFailure = function(err) {
    alert('There was an error! ' + err.message);
  };

</script >

Code.gs

function getDropDownContent() {
  var sheet = SpreadsheetApp.openById('Your SS ID').getSheetByName('Sheet1');
  var lastRow = sheet.getLastRow();
  var myRange = sheet.getRange("C2:C" + lastRow);
  var options = new Array()
  var select = document.getElementById("something");
  var data = myRange.getValues();

  for(var i = 0; i < lastRow; i++) {
    options.push(data[i]);
  }

  return options;
};

Note, that this code still has errors. I'm just providing a starting point for you. I'm not providing perfect code or doing all the work for you. The basic structure of the program flow is correct and all the functions get triggered. I didn't work out the details of injecting the drop down. There is an error there. See if you can figure it out. Or maybe someone else can add a comment.