How can I edit a google sheets HTML UI element after submitting form?

Question

From How can I add multiple inputs from an HTML UI to a Google Spreadsheet? I have copied the following script from the top answer, and made 2 additions which I've marked with comments in the scripts below.

addItem.gs

Addition #2 has been made to the file below

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];
  //Addition #2
  document.getElementById("status_value").innerHTML = "Submitted!"; 
  sheet.appendRow(["  ", form.category, form.item, form.manupub, form.details, form.quantity]);
  return true;
}

Index.html

Addition #1 has been made to the file below

<!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>
    <!-- Addition #1 -->
    <p>Status: </p><p id="status_value"></p>
</html>

Problem:

The script above fails when it gets to Addition #2. It seems it cannot find the id=status_value element in the html.

Note:

I want the html to be updated from the .gs script file because, I have use cases where the script may take a while, I want to change the status message as the script gets to certain milestones in the script.

Chris · Accepted Answer · 2019-02-16T06:14:03.273

1

The ".gs" files run serverside and the html runs clientside. You can not pass data back and forth as if they are running together, hence the 'google.script.run' functionality.

If you want to send data back from the serverside script after a function is called from the client side using that functionality, just return it.

You can then use the returned data to make a change clientside:

Html

<input type="button" value="Submit" onclick="google.script.run .withSuccessHandler(function (data) { document.getElementById("status_value").innerHTML = data; google.script.host.close() ; } ) .itemAdd()" />

.gs

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

edited Feb 16 '19 at 06:14

answered Feb 16 '19 at 05:53

Chris

2,057
1
15
25

I actually wrote this from my phone (bored on a long road trip) so there may be some errors but the gist is there! – Chris Feb 16 '19 at 05:59
Thank you very much. This looks interesting, and I may be able to achieve something similar to what I am trying to do. How would I access the "Submitted" value in the HTML? If I have one example, I can modify for my purposes. – Wronski Feb 16 '19 at 06:05
1

The '. onSuccessHandler()' takes a function with a parameter. When you return a value from the server used you can just access that parameter see the 'data' parameter in my example above. – Chris Feb 16 '19 at 06:10
I was not aware of that. Very helpful to know. Thank you. – Wronski Feb 16 '19 at 06:24

score 1 · Answer 2 · answered Feb 16 '19 at 05:54

Please note that

... browser-based features like DOM manipulation or the Window API are not available.

The above means that getElementById can't be used on server-side code (.gs).

From my answer to Google Apps Script Javascript Standard Support

From https://developers.google.com/apps-script/guides/services/#basic_javascript_features (January 27, 2019)

Basic JavaScript features

Apps Script is based on JavaScript 1.6, plus a few features from 1.7 and 1.8. Many basic JavaScript features are thus available in addition to the built-in and advanced Google services: you can use common objects like Array, Date, RegExp, and so forth, as well as the Math and Object global objects. However, because Apps Script code runs on Google's servers (not client-side, except for HTML-service pages), browser-based features like DOM manipulation or the Window API are not available.

How can I edit a google sheets HTML UI element after submitting form?

2 Answers2