Menu
I've got a spreadsheet with a button that links to a function in my Google Apps Script, openInputDialog. My desired outcome is that pushing the button opens an HTML UI where a user can input text to five fields, and the text is taken from that input and appended to a new row at the bottom of the spreadsheet. I'm experiencing an issue where when clicking the submit button nothing happens; the dialog does not close, and more importantly, there is not a new row appended with the values that are input in it.
The code is as follows:
addItem.gs:
function openInputDialog() {
var html = HtmlService.createHtmlOutputFromFile('Index')
return HtmlService.createHtmlOutputFromFile('Index')
.setSandboxMode(HtmlService.SandboxMode.IFRAME);
SpreadsheetApp.getUi()
.showModalDialog(html, 'Add Item');
}
function itemAdd() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];
sheet.appendRow([" ", 'category', 'item', 'manupub', 'details', 'quantity']);
}
Index.html:
<!DOCTYPE html>
<html>
<head>
<base target="_top">
</head>
<br>
<form>
Category:<br>
<input type="text" name="category">
<br>
Item:<br>
<input type="text" name="item">
<br>
Manufacturer or Publisher:<br>
<input type="text" name="manupub">
<br>
Details:<br>
<input type="text" name="details">
<br>
Quantity:<br>
<input type="text" name="quantity">
<br><br>
<input type="submit" value="Add Item">
</form>
<script>
google.script.run.addItem();
</script>
</html>
I'm pretty sure that the answer to my issue lies with some simple problem or misuse of some part of this script, but my programming knowledge is currently not good enough to properly understand the Google Apps Script documentation that I've been reading.
806
To add a form that's linked to your Sheet, go to the Insert > Form menu option to add your first form. Go to Insert > Form to add your first form.
Your script is currently calling addItem with no parameters, as soon as the page loads:
<script>
google.script.run.addItem();
</script>
Instead, you need to call this function when the Submit button is clicked. While we use HTML forms in Google Apps Script, we can't use the normal submit action; instead, we set up an input button, and use a click handler to collect the form content and transfer it to the server function.
Your Submit button could be something like this:
<input type="button" value="Submit"
onclick="google.script.run
.withSuccessHandler(google.script.host.close)
.addItem(this.parentNode)" />
The success handler will be invoked when a response is returned from the runner, addItem(). To just close the dialog, use google.script.host.close. You could also have a failure handler; it would be invoked if the runner threw an exception.
(Note: you had itemAdd in your gs, but addItem in your JavaScript - that would never have worked.)
Your openInputDialog() function is odd; it has an unnecessary return in it that would stop the dialog from showing up, probably left over from some debugging attempt.
When the runner function, itemAdd(), gets called, it should be passed the content of the HTML form. Since the submit button is a part of that form, the fields of the form appear as properties of its parent node in the DOM; so the click handler passes this.parentNode as a parameter to the runner.
On the server side, itemAdd() receives the form object, so we need a parameter to facilitate operations on it. The named form fields are then referenced like this:
sheet.appendRow([" ", form.category, form.item, form.manupub, form.details, form.quantity]);
Anyway, this works now:
function openInputDialog() {
var html = HtmlService.createHtmlOutputFromFile('Index').setSandboxMode(HtmlService.SandboxMode.IFRAME);
SpreadsheetApp.getUi()
.showModalDialog(html, 'Add Item');
}
function itemAdd(form) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];
sheet.appendRow([" ", form.category, form.item, form.manupub, form.details, form.quantity]);
return true;
}
<!DOCTYPE html>
<html>
<head>
<base target="_top">
</head>
<br>
<form>
Category:<br>
<input type="text" name="category">
<br>
Item:<br>
<input type="text" name="item">
<br>
Manufacturer or Publisher:<br>
<input type="text" name="manupub">
<br>
Details:<br>
<input type="text" name="details">
<br>
Quantity:<br>
<input type="text" name="quantity">
<br><br>
<input type="button" value="Submit"
onclick="google.script.run
.withSuccessHandler(google.script.host.close)
.itemAdd(this.parentNode)" />
</form>
</html>
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
