Populate HTML form from Google Sheets

Question

I am fairly new to all this but am looking to populate an Apps Script Web App HTML dropdown form entry with names directly from a Google Spreadsheet. So far I have been able to return an array of the names from column A of my spreadsheet. Also, the "Populates Form" section of the JS successfully populates the HTML form.

My question is how do I connect the two? I have tried replacing the hard coded array in the latter part of the JS with the function getColleagueList() as well as removing the function and only leaving the variables and the form is still not populating. I feel like its a simple solution but don't know what to do. Thanks in advance.

<!DOCTYPE html>
<html>
<head>
</head>
  <body>


  <select id="selectColleague">
    <option disabled selected value="">
      Reviewer's Name
    </option>
  </select> 

  <script type="text/javascript">

  //Gets Names
  function getColleagueList() {
     var s1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Roster Import');
     var range = s1.getRange(2, 1,s1.getLastRow()-1, 1).getValues();
     return range;
  }


  //Populates Form
  var select = document.getElementById("selectColleague");
  var options = ["1", "2", "3", "4", "5"];
    for(var i = 0; i < options.length; i++) {
      var opt = options[i];
      var el = document.createElement("option");
       el.textContent = opt;
       el.value = opt;
       select.appendChild(el);
  }
 </script>

Is your HTML in a non-Google website? A sidebar to a Google spreadsheet? An Apps Script Web App? A Google Site? — Alan Wells, Aug 27 '16 at 23:24

score 4 · Accepted Answer · answered Aug 28 '16 at 04:31

getColleagueList() should be a server side function. You can put it in Code.gs file. Then call the server side function from JavaScript as follow: You can learn more here: https://developers.google.com/apps-script/guides/html/communication

<script>
          function onSuccess(values) {
            var select = document.getElementById("selectColleague");
            var options = values[0]; //Two dimensional array
            for(var i = 0; i < options.length; i++) {
                var opt = options[i];
                var el = document.createElement("option");
                el.textContent = opt;
                el.value = opt;
                select.appendChild(el);
            }
          }

          google.script.run.withSuccessHandler(onSuccess)
              .getColleagueList();
</script>

score 1 · Answer 2 · answered Aug 29 '16 at 10:33

@HariDas has given a great answer with the back-end .gs code and is all working fine. Your web app code is also fine it just has a little change to be made:

    var options = values[0]; 
    //change it to:
    var options = values;

This will now loop through the array and give all the results as seen here: Web App

Hope that helps :) Good luck!

Populate HTML form from Google Sheets

2 Answers2

Linked