How can I add multiple inputs from an HTML UI to a Google Spreadsheet?

Question

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.

Mogsdad · Accepted Answer · 2015-12-17T03:25:20.060

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:

addItem.gs

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;
}

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="button" value="Submit"
        onclick="google.script.run
            .withSuccessHandler(google.script.host.close)
            .itemAdd(this.parentNode)" />
    </form>
</html>

I can't thank you enough man! Not sure if comments are the appropriate place to express gratitude here, but thanks for the help with solving this (and properly formatting my question!). — Cameron, Dec 17 '15 at 04:37
Glad to help! All we ask for are upvotes and accepts... we're weird that way. Welcome to SO! — Mogsdad, Dec 17 '15 at 04:38
Sorry to bug you more, but I had a quick question about creating a UID for that first column that I left blank, and didn't want to start a new thread for it if it was a simple fix. I'm using a code from [another stack exchange post](http://webapps.stackexchange.com/a/15452) and I'm trying to figure out if I can set it to activate once the itemAdd function adds a new row with all the data. Any ideas on how I might do this? It works when I manually trigger it, I just can't figure out how to do it automatically. — Cameron, Dec 17 '15 at 05:22
I recommend changing itemAdd(this.parentNode) to .itemAdd(document.forms[0]). Why? When formatting, for example with tables, it became quite fiddly with parentNode.parentNode.parentNode.. and so on. — Doctor Rudolf, Jan 08 '18 at 21:58

How can I add multiple inputs from an HTML UI to a Google Spreadsheet?

1 Answers1

addItem.gs

Index.html

Linked