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

1
votes

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>
2
google-apps-script
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 <option> != ... </option>. - tehhowch
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 <option> != ...</option> line in my HTML Code. Thanks for your help. - Eric K.
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
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.
Am I right in that a SuccessHandler is required to use values called from the specified function? - Eric K.

2 Answers

1
votes

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.

0
votes

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