I am relatively new to this topic, but I'm seeking a solution to populate an Apps Script Web App HTML dropdown form with names directly from a Google Spreadsheet. At the moment, I've managed to retrieve an array of names from column A in my spreadsheet. Additionally, the "Populates Form" section of the JavaScript code effectively fills the HTML form.
However, I'm struggling to connect these two parts. I have attempted to replace the hardcoded array in the latter portion of the JavaScript code with the function getColleagueList(), as well as removing the function altogether and leaving only the variables. Unfortunately, neither of these approaches resulted in the form being populated. I believe there is a simple solution to this issue, but I'm unsure of what steps to take. Thank you for your help in advance.
<!DOCTYPE html>
<html>
<head>
</head>
<body>
<select id="selectColleague">
<option disabled selected value="">
Reviewer's Name
</option>
</select>
<script type="text/javascript">
// Retrieves 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 = ["Option 1", "Option 2", "Option 3", "Option 4", "Option 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>