Google-sheets – Google Scripts UI show new row as confirmation after form submit

google sheetsgoogle-apps-script

I have tried several things, but decided to finally ask for help with this. I am using a Google Scripts UI form like the one found with the example to insert new rows into a Sheet. After the submit button is pressed each time, I would like a success/confirmation message to show the row data after it has been inserted. I would like for the message to be updated each time a new record is inserted.

Updating the div content is pretty simple, in itself.

<script>
function formSubmit() {
google.script.run.getValuesFromForm(document.forms[0]);
var SuccessMessage = "my new content" ; //a function to pull last inserted row perhaps, here?
document.getElementById('mydiv').innerHTML = SuccessMessage;
}
</script>
<div id="mydiv" class="mydiv">mydiv</div>

This function retrieves the values of the last row. I don't know how to get those values to appear inside the designated div.

  function myFunction() {
  var s = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = s.getSheetByName("DATA");
  var sheet_last_row = sheet.getLastRow();
  var range = sheet.getRange(sheet_last_row, 1, 1, 3);
  values = range.getValues();
  Logger.log(values);
  return values; 
  }

Placing document.getElementById("mydiv").innerHTML = "New Content"; inside the form submit function updates the div contents. I just need to figure out how to get the function to run that retrieves the values of the new row.

ETA: I updated the Example with code found in another post, that displays the last row of the Sheet. What I need to figure out now is how to have that update each time the submit button is pressed and a new row inserted.

Example Custom UI Form inserts rows into Sheet

Best Answer

I found that this works. Change the form submit button of the html file to: <input onclick="formSubmit();go();" type="button" name="mysubmit" id="mysubmit" value="Add Row"/>

In the html file, add this:

<div id="div">waiting...</div>
<script>
function callback(whatToWrite) {
document.getElementById("div").innerHTML=whatToWrite;
}
function go() {
  google.script.run.withSuccessHandler(callback).writeSomething();
}
</script>

In code.gs, add this:

function writeSomething() {
    var ss = SpreadsheetApp.openById("SHEETIDHERE").getSheetByName("DATA")
  var lastRow = ss.getLastRow();
  var range = ss.getRange(lastRow, 1, 1, 2);
  var Names = range.getValues();
  return Names;
}

These changes have the desired result of updating a div with the newest last row each time a new row is inserted. This makes a nice way of showing users that their form submission is successful, and the form remains open to receive more data.

I worked this out with the answer found here: how to use google.script.run as if it was a function. I will update my example Sheet, in case this will be helpful to someone else.

UPDATE: If you are editing existing rows, then you need to move the call of the go() function to inside the formsubmit function. so that the newly updated row is shown in the confirmation div, and not just the last row.: 

function formSubmit() {
            google.script.run.getSireDamfromAutocomplete(document.forms[0]);
            go();
            }
Related Topic