Duplicate Email Detection & RemovalGoogle Sheets + Apps Script

This assumes a Google Sheet taking Google Form responses with the email address in column C. If yours is somewhere else you only have to change the column index in one place, and there's a table at the bottom for it.

Download this guide as Markdown

Conditional formatting

• Apply to range — C2:C for the email cell alone, or A2:Z to light up the whole row
• Format rule — Custom formula is
• Formula — =COUNTIF($C$2:$C, $C2) > 1
• Formatting — red fill

New form responses are checked as they come in, with nothing to re-run.

Removing the duplicates

Go to Extensions → Apps Script, paste the script below, save, and run it.

function removeDuplicates() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var data = sheet.getDataRange().getValues();
  var seen = {};
  var rowsToDelete = [];

  // Start at row 1 to skip the header (row 0)
  for (var i = 1; i < data.length; i++) {
    var email = data[i][2].toString().toLowerCase().trim()
      .replace(/[^\x20-\x7E]/g, '').replace(/\s+/g, '');

    if (seen[email]) {
      rowsToDelete.push(i + 1);
    } else {
      seen[email] = true;
    }
  }

  // Delete from bottom to top so row numbers don't shift
  for (var j = rowsToDelete.length - 1; j >= 0; j--) {
    sheet.deleteRow(rowsToDelete[j]);
  }

  SpreadsheetApp.getUi().alert(rowsToDelete.length + ' duplicate rows removed.');
}

Why each piece is there

• data[i][2] targets column C — the array is zero-indexed, so A=0, B=1, C=2.
• .toLowerCase() collapses case differences, so John@gmail.com and john@gmail.com count as one address.
• .replace(/[^\x20-\x7E]/g, '') strips the non-printable characters Google Forms sometimes carries in. Two addresses that look identical on screen aren't equal without this.
• .replace(/\s+/g, '') removes hidden whitespace, including the trailing space a paste often brings.
• Rows are deleted bottom-to-top. Deleting top-down shifts every row number below the one you removed, so the second deletion would hit the wrong row.

Targeting a different column

Replace data[i][2] with the index of the column holding the address: