3

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.

  • This question was crossposted to Stack Overflow. See stackoverflow.com/questions/36296705/… (at this time it doesn't have any answer). – Rubén Mar 29 '16 at 23:33
  • When a form is submitted a new row is added to the submission sheet. When a submission is edited, the row that already exists is updated. This is an assumption, but I'd guess that the form submit trigger isn't meant to pick up on edits and instead looks for the insert event. – LoganGoesPlaces Mar 31 '16 at 14:27
  • I have quite a few processes that rely upon the form submit trigger and on occasion the trigger doesn't go off (related to the issue linked in the answer below.) As a work around, every time I process a form submission I add a record to a tracking sheet and then I have a nightly trigger that goes through and compares submissions to the tracking sheet. If the record is missing from the tracking sheet I process it. You could probably set up a nightly trigger to check for the duplicates. – LoganGoesPlaces Mar 31 '16 at 14:29
1

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

  • Thanks for the reply Ruben. The duplication script has to compare form responses so its essential that a formsubmit trigger script be used. Will update this post if I find a solution. – J Doe Mar 30 '16 at 5:34
  • @JDoe: I updated my answer. – Rubén Mar 30 '16 at 9:00
  • I went on "Google Apps Script Issue Tracker" and it seems that the "on form submit triggger" seems to be a common bug (faced by many users) probably due to something on Google's side. Will provide any updates here as they come up. – J Doe Mar 31 '16 at 2:45
  • Looks that the related issue report was posted yesterday: code.google.com/p/google-apps-script-issues/issues/…. The other issues about of form submit are not about the same thing, I think. – Rubén Mar 31 '16 at 12:19
  • 1
    I posted that yesterday after you suggested I do. It is my post. – J Doe Apr 1 '16 at 18:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.