Torey Price Torey Price - 3 months ago 22
HTML Question

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>

Answer

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>