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

by

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.

More Related Contents:

  1. Refresh data retrieved by a custom function in Google Sheet
  2. How do you create a “reverse pivot” in Google Sheets?
  3. ultimate short custom number formatting – K, M, B, T, etc., Q, D, Googol
  4. “Sign in with Google temporarily disabled for this app” error when trying to authorize a script
  5. Where is my iframe in the published web application/sidebar?
  6. Google Apps Script: How to set “Use column A as labels” in chart embedded in spreadsheet?
  7. Get Google Document as HTML
  8. Retrieving Google Sheets cell border style programmatically
  9. Determining the last row in a single column
  10. Cannot find method getRange(number,number)?
  11. Adding Buttons To Google Sheets and Set value to Cells on clicking
  12. Permanently delete file from google drive
  13. Custom function throws a “You do not have the permission required to setValue” error
  14. Can not run trivial app script bound to new blank document – get “This app is blocked”
  15. Margins parameters for Google spreadsheet export as PDF
  16. Script to convert .XLSX to Google Sheet and move converted file
  17. In Google Apps Script, how would I get all subfolders and all subsubfolders and all subsubsub folders etc.?
  18. Hidden field in a Google Form
  19. Apply multiple font colors to the text in a single Google Sheets cell
  20. How to evaluate a spreadsheet formula within a custom function?
  21. Google Sheet use Importxml error could not fetch url [duplicate]
  22. On Edit doesnt work if cell auto updates – Need a workaround Google Sheets
  23. How to download Google Slides as images?
  24. Adding a property to an Html template gives error “Object does not allow properties to be added or changed”
  25. TypeError: Cannot read property ‘getRange’ of null (line 6, file “Code”) in google sheet script [duplicate]
  26. .setFormula() and received error missing ) argument list
  27. How to trigger a Google Apps Script once an email get in the inbox?
  28. Passing cell references to spreadsheet functions
  29. Download file from Google Drive to local folder from Google Apps Script
  30. How to copy content and formatting between Google Docs?

Leave a Comment