Reading data in Google Sheets and passing data to custom dropdown menu

Question

I have a custom Dropdown menu in Google Sheets. I'd like to pass information from a sheet into the dropdown. (see the screenshot for data)

After updating my code thanks to @Cooper, and running the displayDropdown function, I am successfully logging the data. So it IS reading the data... But, it is not being populated in the dropdown. What (obvious) step am I missing here?!

myDropdown.html:

<!DOCTYPE html>
<html>
<head>
  <base target="_top">
</head>
<body>
  <form>
    <div class="select-style">
      <select id="dropJob"> 
        <?!= getDays(); ?>
      </select>
    </div>
  </form>
</body>
</html>

Code.gs:

function displayDropdown()
{
     var htmlDlg = HtmlService.createHtmlOutputFromFile('myDropdown')
              .setSandboxMode(HtmlService.SandboxMode.IFRAME)
              .setWidth(350)
              .setHeight(250);              
     SpreadsheetApp.getUi()
               .showModalDialog(htmlDlg, "title");
     return HtmlService.createTemplateFromFile('myDropdown').evaluate();    
}

function getDays() 
{
  var ui = SpreadsheetApp.getUi();
  var active = SpreadsheetApp.getActive();
  var sheet = active.getSheetByName("Days");
  var myRange = sheet.getRange("A1:A7"); 
  var data    = myRange.getValues();
  var optionsHTML = "";
  for (var i = 0; i < data.length; i++) {
    optionsHTML += '<option>' + data[i][0] + '</option>';
  };
  Logger.log(optionsHTML);
  return optionsHTML;

google sheet data

@Cooper - I edited my code and issue. Could you give it a quick once over and tell me what I'm missing? It would be greatly appreciated. — Michael, Sep 17 '19 at 20:52

score 2 · Answer 1 · answered Sep 17 '19 at 21:33

I didn't use a templated approach. This is what my html for the just the select tag would look like and it included the resources for jQuery.

<!DOCTYPE html>
<html>
  <head>
  <base target="_top">
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
  <link rel="stylesheet" href="//code.jquery.com/ui/1.12.1/themes/base/jquery-ui.css">
  <script src="https://code.jquery.com/ui/1.12.1/jquery-ui.js"></script>
  <script>
    $(function() {//this function runs after the DOM loads which goes and get the dropdown stuff from a spreadsheet and returns it to the .withSuccessHandler(updateSelect) which then calls updateSelect and pass the data array to updateSelect.
        $('#txt1').val('');
        google.script.run
          .withSuccessHandler(updateSelect)
          .getSelectOptions();
      });

    function updateSelect(vA) {//this is where the dropdown gets loaded
      var select = document.getElementById("sel1");//sel1 is the id of the select tag
      select.options.length = 0; 
      for(var i=0;i<vA.length;i++)
      {
        select.options[i] = new Option(vA[i],vA[i]);
      }
    }
  </script>
  </head>  
  <body>
    <select id="sel1"  class="control" style="width:255px;height:35px;margin:10px 0 10px 0;">
      <option value="" selected></option>
   </select>
  </body>
</html>

Yeah I saw that. Haven't really ever used jQuery, so I tried setting it up with the first response on that post. I'll give this a try. Thanks! — Michael, Sep 17 '19 at 21:47
The reason I like doing it this way is because it opens up the possibility of updating the selection list interactively. The templated approach is evaluated on the server so it's fixed after the page starts running. — Cooper, Sep 17 '19 at 21:51

Reading data in Google Sheets and passing data to custom dropdown menu

1 Answers1