CODEWITHSUNDEEP
javascripthtmlgoogle-apps-script
Michael
Michael

Reputation: 121

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

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

Upvotes: 0

Views: 254

Answers (1)

Cooper
Cooper

Reputation: 64100

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>

Upvotes: 2

Related Questions