Google Apps Script: Check, Copy, Email and Delete Duplicate row from Google Form Submission

Need your help on this, i am totally new to the Google Apps Script and struggling with the small function of following:-

  1. Check for the duplicate on specific field (only one field) value submitted from Google Form
  2. If no duplicate found, do nothing.
  3. If duplicate found, script will copy the entire duplicate row found to the another sheet in the same spreadsheet.
  4. Script will then send an email to the responder automatically.
  5. Script will then delete the duplicate row and wait for the next submission.

I’ve a snippet of the modified script getting from the online resources, and play around it but unfortunately looks like it went so much wrong and not works as per expected.

What i have:-

  1. In my Google Form, I’ve only capture one field value as Short Answer text field named Serial
  2. In my Form Responses sheet only contains three column (timestamp, Serial & Email address collected from those responded into the form)
  3. Duplicate Response sheet, contains the same header as in Form Responses (will use to copy the entire duplicate records row here before actually delete it from the Form Responses Sheet).
  4. My code is as below:-
function removeDuplicates()
{
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Form Responses 1");
  var targetsheet= SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Duplicate Responses")
  var targetNewRow = targetsheet.getLastRow() + 1;
  var dataRange = sheet.getDataRange();
  var data = dataRange.getValues();
  var indexOfStatusColumn = 1;
  var newData = [];
  data.forEach(function (row, rowI){
    var isDuplicate = false
    if (newData.length)
    newData.forEach(function (dup, dupI)
    {
      if (dup[3] == row[3])
      {
        if (row[indexOfStatusColumn] == dup[indexOfStatusColumn])
        {
          targetsheet.appendRow(row);
          sendEmail(row[2],row[1]);
          isDuplicate = true;
 
        }
        return (false);
      }
    })
    if (!isDuplicate)
      newData.push(row);
  });
  dataRange = sheet.getRange(2, 1, dataRange.getLastRow() - 1, dataRange.getLastColumn());
  sheet.deleteRow(dataRange.getLastRow() - 1);
  //dataRange.clearContent();
  sheet.getRange(1, 1, newData.length, newData[0].length).setValues(newData);
}

function sendEmail(emailaddr, sn) {}

Issue:-

  1. The initial code was written originally with clearContent() function, but apparently i found once the contents is cleared and new submissions is made it will actually appended only next after the empty row which caused it left the duplicate row empty and not reusable.

  2. The duplicate row copied to the another sheets “Duplicate responses” is not as expected, it will also copied non-duplicate records intermittently when mass submission is made.

Appreciate your help on this please!

Go to Source
Author: Fariz Azmi