I have an html form that will populate the dropdown menu if I use the code. I am wanting to populate the dropdown menu with the information on the "Customers" sheet. The code below will populate the dropdown menu as I want, but then it doesn't transfer the name to the "Job" sheet. It will, however, transfer any text in the "Note" field as well as the hidden fields.
[QLF Jobs Sheet]https://docs.google.com/spreadsheets/d/1vMADNpqCyKuuXKNHUaCc7u3kyW5WHRumgjwLrfXjbAs/edit?usp=sharing
**(Code.gs)**
function doGet(request) {
return HtmlService.createTemplateFromFile('Index').evaluate();
}
function include(filename){
return HtmlService.createHtmlOutputFromFile(filename).getContent();
}
function getSelectList() {
try {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Customers");
var values = sheet.getRange(2,1,sheet.getLastRow(), 1).getValues();
return values;
}
catch(err) {
Logger.log(err);
}
}
function processForm(formObject){
var sheet = SpreadsheetApp.getActiveSheet();
sheet.appendRow([formObject.key,
formObject.driver,
formObject.customer,
formObject.phone_Number,
formObject.note,
formObject.fill_Date,
formObject.history,
formObject.latLong,
formObject.color,
formObject.gallons,
formObject.lbs_Gal,
formObject.status,
]);
}
**(Index.html)**
<!DOCTYPE html>
<html>
<head>
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@4.5.3/dist/css/bootstrap.min.css" integrity="sha384-TX8t27EcRE3e/ihU7zmQxVncDAy5uIKz4rEkgIXeMed4M0jlfIDPvg6uqKI2xXr2" crossorigin="anonymous">
<?!= include('JavaScript'); ?>
<body>
<div class="container">
<div class="row">
<div class="col-8" style="background: bisque;">
<div id="output"></div>
<center>
<div>
<h1>-New QLF Order Form-</h1>
<form id="myForm" onsubmit="handleFormSubmit(this)">
<div class="form-row">
</div>
<div class="form-group col-sm-6">
<b><label for="customer">Customer Location(s)</label>
<select id="customer">
</select>
<script>
(function () {
google.script.run.withSuccessHandler(
function (selectList) {
var select = document.getElementById("customer");
for( var i=0; i<selectList.length; i++ ) {
var option = document.createElement("option");
option.text = selectList[i][0];
select.add(option);
}
}
).getSelectList();
}());
</script>
</div>
<div class="form-group col-sm-6">
<b><label for="note">Note</label></b>
<input type="text" autocomplete="off" class="form-control" id="note" name="note" placeholder="">
</div>
<div class="form-group col-sm-6">
<label for="status"></label>
<input type="hidden" class="form-control" id="status" name="status" value="Pending">
</div>
<div class="form-group col-sm-6">
<label for="order_added"></label>
<input type="hidden" class="form-control" id="order_added" name="order_added" value="New Order">
</div>
<button type="submit" class="btn btn-primary btn-block">Submit</button>
</form>
<div id="output"></div>
</div>
</body>
</html>
**(JavaScript)**
<!DOCTYPE html>
<html>
<head>
<base target="_top">
</head>
<body>
<script>
function preventFormSubmit(){
var forms=document.querySelectorAll('form');
for (var i=0;i<forms.length;i++){
forms[i].addEventListener('submit',function(event){
event.preventDefault();
});
}
}
window.addEventListener('load',preventFormSubmit);
function handleFormSubmit(formObject){
google.script.run.processForm(formObject);
document.getElementById("myForm").reset();
}
function setCaretPosition(elemId, caretPos) {
var elem = document.getElementById(elemId);
if(elem != null) {
if(elem.createTextRange) {
var range = elem.createTextRange();
range.move('character', caretPos);
range.select();
}
else {
if(elem.selectionStart) {
elem.focus();
elem.setSelectionRange(caretPos, caretPos);
}
else
elem.focus();
}
}
}
</script>
</body>
</html>
``