htmlgoogle-apps-scriptmetro-ui-css

HTML (Metro 4) and Google Sheets, Loading Data Asynchronously


I have been trying since October to load an array of items into a select. I'm able to do it with synchronous coding in the template, but not with asynchronous coding (Explanation). I have watched videos, read stackoverflow question after question, read google documentation, metro documentation, and just can't figure it out. This is a google apps script project with a .gs file back end and an .html file that's supposed to be used to load a sidebar.

I have this HTML

<!DOCTYPE html>
<html lang="en">
  <head>
    <!-- Required meta tags -->
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0, maximum-scale=1.0, user-scalable=no">

    <!-- Metro 4 -->
    <link rel="stylesheet" href="https://cdn.metroui.org.ua/v4/css/metro-all.min.css">
  </head>
  <body>
    <!-- Title -->
    <h2 style="text-align: center;">Add New Job</h2>

    <!-- Job Name -->
    <p style="text-align: center;"><span class='mif-chat-bubble-outline'></span> Job Name</p>
    <input type="text" data-role="input" data-prepend="Name: ">

    <!-- Creator Name -->
    <p style="text-align: center;"><span class='mif-user-check'></span>  Job Creator</p>
    <select data-role="select" id="mySelectList">
    <optgroup label="Employees">
           <option value="" selected> Loading... </option>     
    </optgroup>
    </select>    

    <nl></nl>
    <p style="text-align: center;">
      <button class="button success cycle " id="confirmButton"><span class="mif-checkmark"></span></button>
    </p>
    
    <!-- Metro 4 -->
    <script src="https://cdn.metroui.org.ua/v4/js/metro.min.js"></script>
    <script>
      $(function() {
        $('#txt1').val('');
        console.log("Running updateSelect");
        google.script.run
          .withSuccessHandler(updateSelect)
          .getEmployeeNames();
      });

      function updateSelect(vA)//array of value that go into the select
      {
        var select = document.getElementById("mySelectList");
        select.options.length = 0;
        for (var i=0; i<vA.length;i++){
          console.log("Creating items %s, %s", vA[i], vA[i]);
          select.options[i] = new Option(vA[i],vA[i]);
          console.log ("select.options[i] = %s, select.options = %s.", select.options[i], select.options);
        }
      }

      console.log("My Code Ran");
    </script>

  </body>
</html>

Which provides this console feedback when ran.

Console Feedback

I have erased all my cookies and cache, disabled all addons, etc, and that did not fix the problem.

Why won't the options change from "Loading..." to the actual new values I've put in? For reference, I have the following code that does work, but it does so in an inefficient way (inside the template itself, instead of being called).

<!-- Creator Name -->
    <? var employees = getEmployeeNames(); ?>

    <p style="text-align: center;"><span class='mif-user-check'></span>  Job Creator</p>
    <select data-role="select"  id="jobCreator">
    <optgroup label="Employees">
     
        <? for (var i = 0; i < employees.length; i++){ ?>
           <option> <?= employees[i] ?></option>
        <? } ?>
      
    </optgroup>
    </select>    

Here is the backend script for getEmployeeNames(). I've tried it returning both a 1 dimensional or 2 dimensional array. While the function gives the expected output, the HTML select still does not update with new options.

function getEmployeeNames(){
  var employeeSheet = SpreadsheetApp.getActive().getSheetByName('Dropdown Lists');
  var names = employeeSheet.getRange(5, 4, employeeSheet.getLastRow(), 1).getValues();

  names = removeBlankEntries(names);

  Logger.log ('Employee Names "%s"', names);

  return names;  
}

function removeBlankEntries(arr){
  
  var output = [];
  arr.forEach(function(value){
    if (value != "" & value != " "){
      output.push(value[0]) // add [0] to make it 1d
    };
  });
  return output;
}

Here's an example Logger.log output.

8:27:00 AM Info Employee Names "[PM, Employee 2, Employee 3, Employee 4]"

Or with it set to give a 2d array.

8:28:00 AM Info Employee Names "[[PM], [Employee 2], [Employee 3], [Employee 4]]"

Additionally, here is how the sidebar is loaded in the code.

function loadNewJobSidebar(){
  var htmlTemplate = HtmlService
            .createTemplateFromFile("addJob");

  var htmlOutput = htmlTemplate.evaluate()
          .setTitle('Add New Job');
  var ui = SpreadsheetApp.getUi();
  ui.showSidebar(htmlOutput);
}

Any guidance would be helpful. I thought the video above was the best, and I copied it three or four times without success. I've been coding for a decade - even though it is only my side gig - and I've never had a problem last so many months.

== Sorry this is becoming a monster thread. Below are exact steps for replication ==

  1. Create a new blank Google Sheet.
  2. Open the script editor and create an HTML file called addJob (it will automatically add the .html to the end)
  3. In the HTML file, copy the code exactly from the first snippet in this post titled "I have this HTML"
  4. In the code.gs file, copy the following code

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  // Or DocumentApp or FormApp.
  ui.createMenu('Manage Sheet')
      .addSubMenu (ui.createMenu('Jobs')
        .addItem('Add New Job', 'loadNewJobSidebar'))
        .addToUi();
}

function getEmployeeNames(){
  return ["Bob", "Jamie", "Ted"];
}


function loadNewJobSidebar(){
  var htmlTemplate = HtmlService
            .createTemplateFromFile("addJob");

  var htmlOutput = htmlTemplate.evaluate()
          .setTitle('Add New Job');
  var ui = SpreadsheetApp.getUi();
  ui.showSidebar(htmlOutput);
}

  1. Run the onOpen() function from the script editor. Allow it permission to run.
  2. Go back to the spreadsheet, and on custom menu, select "Manage Sheet -> Jobs -> Add New Job"
  3. The sidebar will load and say "Loading" but will not use the names from the getEmployee() function.
  4. Press F12 and note the console log statements showing the select options should be loaded.

Solution

  • From your updated question, I could understand your current issue. In the case of your script, how about modifying the function updateSelect as follows?

    Modified script:

    function updateSelect(vA) {
      var select = Metro.getPlugin("#mySelectList", 'select');
      select.data(Object.fromEntries(vA.map(e => [e, e])));
    }
    

    Reference: