CODEWITHSUNDEEP
javascriptjqueryhtmlgoogle-apps-script
Torey Price
Torey Price

Reputation: 397

Populate HTML form from Google Sheets

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>

Upvotes: 2

Views: 3527

Answers (2)

cwatson1988
cwatson1988

Reputation: 106

@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!

Upvotes: 1

Hari Das
Hari Das

Reputation: 10864

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>

Upvotes: 4

Related Questions