javascript - Dynamic Dropdown List in HTML form using Google App Script

After a long hunt (I've been living and breathing StackOverflow lately), I thought I'd take my problem to you all.

I'm still a newbie to Google Script, so bear with me. I'm trying to write a dynamic dropdown list as an input in an HTML form. My hope is to get the list to populate options with values from a google sheet. The function, getList, which returns the array values works as expected, but the function to add option tags and their values to the list is failing...

Check out what I have below and let me know if you have any insight or could steer me in the right direction. Thanks!!

//basic function that builds the form using indexEvent.html as the template
function doGet(e) {
     return HtmlService
     .createTemplateFromFile('index')
     .evaluate()
     .setSandboxMode(HtmlService.SandboxMode.NATIVE);
}

//Simple function to find the wanted sheet by it's independent ID
function getSheetById(id) {
   return SpreadsheetApp.getActive().getSheets().filter(
      function(s) {return s.getSheetId() === id;}
   )[0];
}

//Gets existing values in Column "B" and lump into 1-D array
function getList() {
    var ss = SpreadsheetApp.openById('sheet ID');
    var sheet = getSheetById(240411081); 
    var list  = sheet.getRange(2, 2, sheet.getLastRow()-1, 1).getValues();
    var values  = list.toString().split(",");
    
    return values;
}

<!DOCTYPE html>
<html>
<head>
  <base target="_top">
</head>
  <body>
    <form id="Form" onload="addList()">
      <div>
        <label for="List">Select Value:</label><br>
        <input list="list" name="list" placeholder="Choose Value" required> -->
          <datalist id="dropdownList">
          <!-- 
          This is where I am trying to dynamical add <option> tags 
          EXAMPLE:
            <option value="values[0]" />
            <option value="values[1]" />
            <option value="values[2]" />
            ... and so on
          -->
          </datalist>
      </div>
    </form>
  </body>
  
  <script>
    function addList() {
      google.script.run
        .withFailureHandler(onFailure)
        .withSuccessHandler(addListValues)
        .getList();
    }

    function addListValues(values) {
      var list = document.getElementById('dropdownList');   
        for (var i = 0; i < values.length; i++) {
          list.appendChild('<option value="' + values[i] + '" />');
        }
    }

    function onFailure(err) {
      alert('There was an error!' + err.message);
    }
  </script>
  
<html>

After a long hunt (I've been living and breathing StackOverflow lately), I thought I'd take my problem to you all.

Check out what I have below and let me know if you have any insight or could steer me in the right direction. Thanks!!

//basic function that builds the form using indexEvent.html as the template
function doGet(e) {
     return HtmlService
     .createTemplateFromFile('index')
     .evaluate()
     .setSandboxMode(HtmlService.SandboxMode.NATIVE);
}

//Simple function to find the wanted sheet by it's independent ID
function getSheetById(id) {
   return SpreadsheetApp.getActive().getSheets().filter(
      function(s) {return s.getSheetId() === id;}
   )[0];
}

//Gets existing values in Column "B" and lump into 1-D array
function getList() {
    var ss = SpreadsheetApp.openById('sheet ID');
    var sheet = getSheetById(240411081); 
    var list  = sheet.getRange(2, 2, sheet.getLastRow()-1, 1).getValues();
    var values  = list.toString().split(",");
    
    return values;
}

<!DOCTYPE html>
<html>
<head>
  <base target="_top">
</head>
  <body>
    <form id="Form" onload="addList()">
      <div>
        <label for="List">Select Value:</label><br>
        <input list="list" name="list" placeholder="Choose Value" required> -->
          <datalist id="dropdownList">
          <!-- 
          This is where I am trying to dynamical add <option> tags 
          EXAMPLE:
            <option value="values[0]" />
            <option value="values[1]" />
            <option value="values[2]" />
            ... and so on
          -->
          </datalist>
      </div>
    </form>
  </body>
  
  <script>
    function addList() {
      google.script.run
        .withFailureHandler(onFailure)
        .withSuccessHandler(addListValues)
        .getList();
    }

    function addListValues(values) {
      var list = document.getElementById('dropdownList');   
        for (var i = 0; i < values.length; i++) {
          list.appendChild('<option value="' + values[i] + '" />');
        }
    }

    function onFailure(err) {
      alert('There was an error!' + err.message);
    }
  </script>
  
<html>

Share Improve this question asked Dec 13, 2018 at 21:21 Alex Boothe 211 gold badge1 silver badge2 bronze badges

Add a ment |

4 Answers 4

Sorted by: Reset to default 1

How about this modification?

Modified script:

There is no onload event at form tag.
- In this modified script, I modified to <body onload="addList()">.
There are some issues at datalist of HTML and Javascript.
- At input tag, modify to list="dropdownList".
- At Javascript, when the values are appended to datalist, it uses document.createElement("option").

Modified script:

<!DOCTYPE html>
<html>
<head>
  <base target="_top">
</head>
  <body onload="addList()"> <!-- Modified -->
    <form id="Form"> <!-- Modified -->
      <div>
        <label for="List">Select Value:</label><br>
        <input list="dropdownList" name="list" placeholder="Choose Value" required> --> <!-- Modified -->
          <datalist id="dropdownList">
          <!-- 
          This is where I am trying to dynamical add <option> tags 
          EXAMPLE:
            <option value="values[0]" />
            <option value="values[1]" />
            <option value="values[2]" />
            ... and so on
          -->
          </datalist>
      </div>
    </form>
  </body>

  <script>
    function addList() {
      google.script.run
        .withFailureHandler(onFailure)
        .withSuccessHandler(addListValues)
        .getList();
    }

    function addListValues(values) {
      var list = document.getElementById('dropdownList');   
      for (var i = 0; i < values.length; i++) {
        var option = document.createElement("option"); // Modified
        option.value = values[i]; // Modified
        list.appendChild(option); // Modified
      }
    }

    function onFailure(err) {
      alert('There was an error!' + err.message);
    }
  </script>

<html>

Note:

In this modified script, it supposes that the script at GAS side works fine.

References:

datalist
appendChild()

Try this;

html:

google.script.run//I often put this sort of thing in the ready function or windows.load
  .withSuccessHandler(function(vA) {
    $('#sel1').css('background-color','#ffffff');
    updateSelect(vA);
  })
  .getSelectOptions();

function updateSelect(vA,id){//the id allows me to use it for other elements
  var id=id || 'sel1';
  var select = document.getElementById(id);
  select.options.length = 0; 
  for(var i=0;i<vA.length;i++)
  {
    select.options[i] = new Option(vA[i],vA[i]);
  }
}

gs:

function getSelectOptions()
{
  sortOptions();
  var ss=SpreadsheetApp.openById(getGlobal('gSSID'));
  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;
}

I found the issues and have made the proper modifications:

Return Value The returned value using a success handler can NOT return any value other than a string. So, the way around this (found on another StackOverflow page) is to use:

GAS:

// GAS 

function getList() {
    var ss = SpreadsheetApp.openById('sheet ID');
    var sheet = getSheetById(240411081); 
    var list  = sheet.getRange(2, 2, sheet.getLastRow()-1, 1).getValues();
    var values  = list.toString().split(",");
    
    return JSON.stringify(values); //Modified
}

The JSON stringify function converts the array to string values, which is legal to return. In the HTML, you must parse the JSON to reform the array. Otherwise, the other scripts will work.

NOTE: I've added JQuery to simplify calling HTML elements

HTML:

<body onload="addList();">
 <form id="form">
    <div>
       <input list="List" name="eventList" placeholder="-- Select Event --" required>
          <datalist id="List"> 
             <option>Loading...</option>       
          </datalist>
    </div>     
 </form>
 </body>

 <script>
  function addList() {
         google.script.run.withSuccessHandler(writeList).withFailureHandler(onFail) 
           .getEventList();
  }

  function writeList(list) {
      var dropdown = $("#List");
      dropdown.empty();
      var options = JSON.parse(list); //Modified
      var sortOpt = options.sort();
         if (options.length > 0) {
             for ( var i = 0; i < sortOpt.length; i++) {
                 if (i == 0) {dropdown.append('<option>CUSTOM</option>');}
                   dropdown.append('<option>' + sortOpt[i] + '</option>');
             }
         }
   }
  
  function onFail() {
    alert("This action failed to plete.");
  }
 </script>
 <script src="https://ajax.googleapis./ajax/libs/jquery/3.2.1/jquery.min.js"> 
 </script>
 </html>

Thank you to those who've provided solutions to my question.

2024 and found this solution to my own issue. works great, but this line didn't work for me:

var dropdown = $("#List");

had to use:

var list = document.getElementById('List');

working function:

function addListValues(values)
{     
    var list = document.getElementById('List');  
    var options = JSON.parse(values);
    for (var i = 0; i < options.length; i++) {
        var option = document.createElement("option"); 
        option.value = options[i]; 
        list.appendChild(option); 
    }
}

科技改变生活-雨落星辰 - 所有的伟大,都源于一个勇敢的开始

javascript - Dynamic Dropdown List in HTML form using Google App Script - Stack Overflow

4 Answers 4

Modified script:

Modified script:

Note:

References:

与本文相关的文章

评论列表(0)