I am trying to lauch an html form from a google spread sheet using the HTMLService and return data to the script from a select input. I am collecting the data with this line: -
But I am not sure how to get it back to the script file: I have tried various iterations of: - city= form.Projects_list.text; - city= form.Projects_list[0]; - city= form.Projects_list.[0][0];
but none of these seem to be the right handle to the select. The other variables are coming back from the form as desired.
How can I grab that last variable?
HTML File
<b>Add Row To Spreadsheet</b><br />
<form>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>jQuery UI Datepicker - Default functionality</title>
<link rel="stylesheet" href="//code.jquery.com/ui/1.12.1/themes/base/jquery-ui.css">
<link rel="stylesheet" href="/resources/demos/style.css">
<script src="https://code.jquery.com/jquery-1.12.4.js"></script>
<script src="https://code.jquery.com/ui/1.12.1/jquery-ui.js"></script>
<script>
$( function() {
$( "#datepicker" ).datepicker();
} );
$( function() {
$( ".widget input[type=submit]" ).button();
$( "button, input, a" ).click( function( event ) {
event.preventDefault();
} );
} );
</script>
<form id = "dateform">
<p>Date: <input type="text" id="datepicker" name ="datepicker"></p><p></p>
Reason for Delay: <input id="reason" name="reason" type="text" />
Last name: <input id="lastName" name="lastName" type="text" />
<input onclick="formSubmit()" type="button" value="Add Row" />
<input onclick="google.script.host.close()" type="button" value="Exit" />
<hr>
<div id = 'pList'>
<table>
<tr>
<td>Select A City</td><td><select id="Projects_list"name ="Projects_list"></select></td>
</tr>
</table>
</div>
</form>
<script type="text/javascript">
function formSubmit() {
google.script.run.getValuesFromForm(document.forms[0]);
google.script.host.close();
}
</script>
<script type="text/javascript">
// Client-side JavaScript that uses the list returned by
// GAS function "getValuesForRngName()" to populate the dropdown.
// This is standard client-side JavaScript programming that uses
// DOM manipulation to get page elements and manipulate them.
function onSuccess(values) {
var opt,
dropDown;
for(i = 0;i < values.length; i +=1){
dropDown = document.getElementById("Projects_list");
opt = document.createElement("option");
dropDown.options.add(opt);
// Remember that GAS Range method "GetValues()" returns
// an array of arrays, hence two array indexes "[i][0]" here.
opt.text = values[i][0];
opt.value = values[i][0];
}
}
function populate(){
google.script.run.withSuccessHandler(onSuccess).getValuesForRngName('Projects20');
}
</script>
<script>
// Using the "load" event to execute the function "populate"
window.addEventListener('load', populate);
</script>
<script>
// Client-side JavaScript that uses the list returned by
// GAS function "getValuesForRngName()" to populate the dropdown.
// This is standard client-side JavaScript programming that uses
// DOM manipulation to get page elements and manipulate them.
function onSuccessx(values) {
var opt,
dropDown;
for(i = 0;i < values.length; i +=1){
dropDown = document.getElementById("Projects_list");
opt = document.createElement("option");
dropDown.options.add(opt);
// Remember that GAS Range method "GetValues()" returns
// an array of arrays, hence two array indexes "[i][0]" here.
opt.text = values[i][0];
opt.value = values[i][0];
}
dropDown = dropDown.options.sort()
}
function populatex(){
google.script.run.withSuccessHandler(onSuccess).getValuesForRngName('Projects20');
}
</script>
<script>
// Using the "load" event to execute the function "populate"
window.addEventListener('loadx', populate);
</script>
App Script File
function demoHtmlServices() {
var ss = SpreadsheetApp.getActiveSpreadsheet(),
html = HtmlService.createHtmlOutputFromFile('changeDateHTML');
setRngName();
ss.show(html);
}
//getValuesFromForm
function getValuesFromForm(form){
var firstName = form.firstName,
lastName = form.lastName,
reason = form.reason,
newDate = form.datepicker,
city= form.Projects_list.text;
sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
SpreadsheetApp.getUi().alert('Test');
sheet.appendRow([lastName, reason, newDate, city]);
var ui = SpreadsheetApp.getUi() ;
alert('Test');
ui.alert('form.Projects_list(0)')
google.script.host.close();
}
// Display the GUI
// Execute this function from the script editor ro run the application.
// Note the call to "setRngName()". This ensures that all newly added
// values are included in the dropdown list when the GUI is displayed
function displayGUI() {
var ss,
html;
setRngName();
ss = SpreadsheetApp.getActiveSpreadsheet();
html = HtmlService.createHtmlOutputFromFile('index').setSandboxMode(HtmlService.SandboxMode.IFRAME);
ss.show(html);
}
// Called by Client-side JavaScript in the index.html.
// Uses the range name argument to extract the values.
// The values are then sorted and returned as an array
// of arrays.
function getValuesForRngName(rngName) {
var rngValues = SpreadsheetApp.getActiveSpreadsheet().getRangeByName(rngName).getValues();
return rngValues.sort();
}
//Expand the range defined by the name as rows are added
function setRngName() {
var ss = SpreadsheetApp.getActiveSpreadsheet(),
sh = ss.getSheetByName('DropdownValues'),
firstCellAddr = 'A2',
dataRngRowCount = sh.getDataRange().getLastRow(),
listRngAddr = (firstCellAddr + ':A' + dataRngRowCount),
listRng = sh.getRange(listRngAddr);
ss.setNamedRange('20Projects', listRng);
//Logger.log(ss.getRangeByName('Cities').getValues());
}
// For debugging
function test_setRngName() {
setRngName();
}
