How to Populate HTML Service Select Options from Range in Google Sheet?

Question

I am trying to populate my HTML Service drop down/select option list with all entries included in column A of my Vendor Database tab in the Google Sheet I am working with. It is currently showing up blank, though, when running. Any suggestions?

APPS SCRIPT:

 function getVendors() {
  var active = SpreadsheetApp.getActive();
  var sheet = active.getSheetByName("Vendor Database");
  var lastRow = sheet.getLastRow();
  var myRange = sheet.getRange("A2:A" + lastRow); 
  var data    = myRange.getValues();
  var optionsHTML += "";
  for (var i = 0; i < data.length;i+=1) {
   optionsHTML = '<option>' + data[i][0] + '</option>';
  };
  return optionsHTML;
 }

HTML:

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
   <body>
 <form>
 <div>
   <select>
       <option> != google.script.run.getVendors(); </option>
      </select>
</div>
</form>
  </body>
</html>

`data[i]` is an array. Use `data[i][0]`, to access the first element in the array located at the i-th index of data. Additionally, your for loop overwrites all previous values of `optionsHtml`, because it uses `=` (assignment operator) instead of `+=` (append operator). I'm also not sure what you're trying to do with the line ``. — tehhowch, Mar 30 '18 at 16:47
Thanks, Tehhowch! I have updated the code above to reflect your suggestions, and have included them in my actual file, as well. I am still getting a blank drop down list my UI, though. Last, I am trying to call the values gotten by the getVendors function into my html UI drop-down with the line in my HTML Code. Thanks for your help. — Eric K., Mar 30 '18 at 19:26
You missed a very important recommendation I made about the operator you use. I also recommend you review the Apps Script documentation on `google.script.run`, especially with regards to how to use values the called function provides. — tehhowch, Mar 30 '18 at 19:46
The amendment I made to the operator may have been in the wrong line. I added to the var optionHTML line, which is coming up as a bug when I run de-bugger against it. It should be in the 2nd line of the for loop, though? I am headed to read up on google.script.run now. — Eric K., Mar 30 '18 at 19:52
Am I right in that a SuccessHandler is required to use values called from the specified function? — Eric K., Mar 30 '18 at 20:34

score 2 · Accepted Answer · answered Mar 31 '18 at 03:55

When initializing optionsHTML that should be direct assignment, not +=. Instead, use the += in the for loop as you'll otherwise be replacing the contents of optionsHTML rather than appending to it.

function getVendors() {
  var active = SpreadsheetApp.getActive();
  var sheet = active.getSheetByName("Vendor Database");
  var lastRow = sheet.getLastRow();
  var myRange = sheet.getRange("A2:A" + lastRow); 
  var data    = myRange.getValues();
  var optionsHTML = "";
  for (var i = 0; i < data.length; i+=1) {
    optionsHTML += '<option>' + data[i][0] + '</option>';
  };
  return optionsHTML;
}

Make sure you're correctly evaluating the HTML. Because of the way you've set this up, you need to treat your HTML file (I'm assuming it's called Index.html) as a template.

function doGet() {
  return HtmlService.createTemplateFromFile('Index').evaluate()
}

Finally, in the HTML file, looks like you're using incomplete anchors. Should be <?!= ... ?> and then call the function directly. (Also, remove the surrounding <option></option> tags as getVendors() already provides those.)

<!DOCTYPE html>
<html>
<head>
  <base target="_top">
</head>
<body>
  <form>
    <div>
      <select>
        <?!= getVendors(); ?>
      </select>
    </div>
  </form>
</body>
</html>

Once you have that working, and if it makes sense to put some more time and care into this, refactor to follow the best practices and load data asynchronously, not in templates by using a client-side script inside the HTML as mentioned by @Cooper.

Thanks, Diego. I feel like this gets me closer, but am still getting blanks in the drop down list within the html ui. Not sure what we're missing. — Eric K., Apr 02 '18 at 16:04

score 0 · Answer 2 · answered Mar 30 '18 at 17:05

Here's something similar that I've done in the past.

Here's the Javascript in the html file:

$(function() {
        $('#txt1').val('');
        google.script.run
          .withSuccessHandler(updateSelect)
          .getSelectOptions();
      });

    function updateSelect(vA)
    {
      var select = document.getElementById("sel1");
      select.options.length = 0; 
      for(var i=0;i<vA.length;i++)
      {
        select.options[i] = new Option(vA[i],vA[i]);
      }
    }

Here's the pertinent HTML:

<select id="sel1"  class="control" style="width:255px;height:35px;margin:10px 0 10px 0;">
      <option value="" selected></option>
   </select>

Here's the google script:

function getSelectOptions()
{
  sortOptions();
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName('Options');
  var rg=sh.getDataRange();
  var vA=rg.getValues();
  var options=[];
  for(var i=0;i<vA.length;i++)
  {
    options.push(vA[i][0]);
  }
  return vA;
}

I think that you should include that it's required to load the jQuery library b/c the OP HTML code doesn't include it. By the other hand I think that in this case the same result could be achieved without using jQuery. — Rubén, Apr 10 '18 at 16:15

How to Populate HTML Service Select Options from Range in Google Sheet?

2 Answers2

Linked