https://script.google.com/macros/s/AKfycbxKnhG0ErVgXSIPxczbst4o-J54dHUKC67nA4SegCnx/dev
I have a form that I am creating for my kids’ school and am having issues with the drop-down list to choose the student name. The list is populated via javascript code, importing a student list from an associated google sheet. I added a console log for each option as they are created to show that they are in fact being created. Developer options on the above link should reveal that.
Once I load the form I am unable to change the student identifier from the default option. I can’t tell what I’ve done wrong. If I hard code the values into the HTML it works fine, but I want the teacher to be able to add and remove students via the spreadsheet because that is a more user-friendly and flexible implementation.
form.html code
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/materialize/1.0.0/css/materialize.min.css">
<link href="https://fonts.googleapis.com/icon?family=Material+Icons" rel="stylesheet">
<style>
body {
background: rgb(244, 235, 234)
}
.outer-field {
border-radius: 15px;
background: white;
height: 150px;
margin: 10px;
padding: 20px;
}
.title {
padding-left: 2%;
font-weight: bold;
}
</style>
</head>
<body>
<div class="row">
<div class="col s8 offset-s2 offset-s2">
<!--Document Header-->
<div class="outer-field" style="height: 100px">
<h4>Golden Apple Reader Book Submissions</h4>
</div>
<!--Form to submit ISBN and autopopulate title and page count-->
<form id="myForm" onsubmit="event.preventDefault(); formSubmit(this) ">
<!--Creates ISBN entry field-->
<div class="outer-field">
<div class="title">Book ISBN</div>
<div class="col s8">
<div class="input-field">
<input type="text" id="ISBN" name="ISBN" class="UUID validate" form="myForm">
<label for="ISBN">ISBN</label>
</div>
</div>
<!--Creates button to check ISBN data-->
<button class="btn waves-effect waves-light" id="btn" style="margin-left: 3%" type="button" onclick="populateDetails(); return false;">Autofill Book Data From ISBN
<i class="material-icons right">send</i>
</button>
</div>
<!--Creates student name entry field-->
<div class="outer-field">
<div class="title">Student Name</div>
<div class="input-field col s12">
<select form="myForm" name="StudentID" id="StudentID" required>
<!--Add student IDs and names here-->
<!--<option value="212702">John</option>
<option value="212703">Henry</option>
<option value="003">003</option>-->
</select>
</div>
</div>
<!--Creates book title entry field-->
<div class="outer-field">
<div class="title">Book Required Information</div>
<div class="col s8">
<div class="input-field">
<input type="text" id="Title" name="Title" class="name" form="myForm" required>
<label for="Title">Book Title</label>
</div>
</div>
<!--Creates book page count entry field-->
<div class="col s4">
<div class="input-field">
<input type="number" id="PageCount" name="PageCount" class="pages" form="myForm" required>
<label for="PageCount">Book Page Count</label>
</div>
</div>
</div>
<!--Creates button to submit data-->
<button class="btn waves-effect waves-light" type="submit" name="action" style="margin-left: 3%" >Submit
<i class="material-icons right">send</i>
</button>
</form>
</div>
</div>
<script src="https://cdnjs.cloudflare.com/ajax/libs/materialize/1.0.0/js/materialize.min.js"></script>
<script>
M.FormSelect.init(document.querySelectorAll('select'));
//function to populate student list element
(function () {
google.script.run.withSuccessHandler(
function (selectList) {
var select = document.getElementById('StudentID');
for( var i=0; i<selectList.length; i++ ) {
//initial attempt commented here for troubleshooting
//var option = document.createElement('option');
//option.value = selectList[i][0];
//option.text = selectList[i][4];
var option = new Option(selectList[i][4], selectList[i][0]);
console.log(option);
select.add(option, undefined);
}
console.log(select)
}
).getSelectList();
}());
//Uses the ISBN to populate the book title and page quantity
function populateDetails(){
isbn=document.getElementById('ISBN').value;
//isbn=9781492680550;//for debugging only
var url = "https://www.googleapis.com/books/v1/volumes?country=US&q=isbn:" + isbn;
var obj
var title="No Entry Identified";
var pageCount=0;
var titleout=document.getElementById('Title');
var pageout=document.getElementById('PageCount');
//fetches URL data from google books API
fetch(url)
.then(res => res.json())
.then(data => obj = data)
.then(
function(settitle){
//Assigns title to variable and text field
title = obj.items[0].volumeInfo.title
titleout.value=title;
titleout.focus();
},
function(titlerror){
})
.then(
function(setpages){
//Assigns page count to variable and text field
pageCount = obj.items[0].volumeInfo.pageCount
pageout.value=pageCount;
pageout.focus();
},
function(pageerror){
})
//In the case that no entry is found in google books API, assigns default values to text fields and deconflicts the overlapping label and value fields
titleout.value=title;
titleout.focus();
pageout.value=pageCount;
pageout.focus();
}
//Submits form data to spreadsheet
function formSubmit (data) {
var dataToSubmit = {
studentID: data.StudentID.value,
title: data.Title.value,
pageCount: data.PageCount.value
}
//Provides a success message to the user
google.script.run.withSuccessHandler(function () {
myForm.reset()
M.toast({html: "Thank you! You have successfully submitted!"})
}).submitData(dataToSubmit)
}
</script>
</body>
</html>
code.gs code
”’
var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(“Submissions”)
var ss2= SpreadsheetApp.getActiveSpreadsheet().getSheetByName(“Students”)
var last=ss2.getLastRow();
var students=ss2.getRange(2,1,last-1,5).getValues();
function getSelectList() {
try {
return students;
}
catch(err) {
Logger.log(err);
}
}
function doGet() {
return HtmlService.createTemplateFromFile('form').evaluate().addMetaTag('viewport', 'width=device-width, initial-scale=1')
}
function submitData (data) {
ss.appendRow([new Date(),data.studentID, data.title, data.pageCount])
}
'''
spreadsheet content:
|Student |ID Number |Student Name |Teacher Name |Grade |Concatenation|
|UNK1 |John |TeacherA |K |Grade: K, Teacher: TeacherA, Name: John
|UNK2 |Henry |TeacherA |K |Grade: K, Teacher: TeacherA, Name: Henry
|UNK3 |Paige |TeacherA |K |Grade: K, Teacher: TeacherA, Name: Paige
|UNK4 |Raelyn |TeacherA |K |Grade: K, Teacher: TeacherA, Name: Raelyn
|UNK5 |Danielle |TeacherA |K |Grade: K, Teacher: TeacherA, Name: Danielle
|UNK6 |Olivia |TeacherA |K |Grade: K, Teacher: TeacherA, Name: Olivia