Google-sheets – Script does not trigger on FormSubmit

google sheetsgoogle-apps-script

I am trying to remove older duplicate form responses based on a column using the following code.

The credit for the code goes to:
http://www.jacorre.com/tutorial/remove-duplicate-rows-google-spreadsheets/

The code in my script is:

 function removeDuplicates() {
 var ss = SpreadsheetApp.getActiveSpreadsheet(),
     responses = ss.getSheetByName('Name of Source Sheet'),
     range = responses.getDataRange(),
     numRows = range.getNumRows()-1,
     data = range.getValues(),
     columnHeadings = [data[0]],
     newData = [];
 for (var i=numRows; i>0; i--) {
  var row = data[i],
      duplicate = false;
  for (var j in newData) {
   if (row[4] == newData[j][4]) {
    duplicate = true;
   }
  }
  if (!duplicate) {
   newData.push(row);
  }
 }
 var final = ss.getSheetByName('Name of Destination Sheet');
 if (!final) {
  var final = ss.insertSheet('Name of Destination Sheet');
 } 
 final.clearContents();
 final.getRange(1,1,1,columnHeadings[0].length).setFontWeight('bold').setValues(columnHeadings);
 final.getRange(2, 1, newData.length, newData[0].length).setValues(newData);
}

This has been set to trigger on Form Submit. It works well on new form submissions.

However, when an existing response is edited using 'Form Edit URL' from:
Show URL used to edit responses from a Google Form in a Google Spreadsheet by using a script
the values are not updated into the new sheet.

But if the function is run manually the updated row is updated to the new sheet.

How can I sort this problem? Any help will be appreciated. Thank you.

Best Answer

I just did a test and found that the on form submit event it's not being triggered when a response is edited.

I'm not sure if the on form submit trigger is working as intended, if the above is due to a bug or to a glitch. To be sure, post an issue to the Google Apps Script Issue Tracker.

As a workaround, instead of using the on form submit event, use another way to run your script, like a time-drive trigger.

References

Related Solutions

Google-sheets – Google forms script to delete responses counter

Each form is a distinct object, separate from the spreadsheet itself. The spreadsheet is merely the destination for the form responses, for the purpose of reading and using them, and responses sent to the spreadsheet are no longer connected to the form itself. The actual responses that are connected to the form are stored with the form.

If you want to clear the form responses, you can do so by going to the form editor and selecting

Responses > Delete all responses

To do so in a script you need to do something like this:

var form = FormApp.openById(id);
form.deleteAllResponses();

Note: The warning given when deleting manually states that 'responses already sent to a spreadsheet will not be deleted'. It should read '...will not be deleted from that spreadsheet'

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

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

Best Answer

References

Related Solutions

Google-sheets – Google forms script to delete responses counter

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

Related Topic