Duplicate Email Detection & RemovalGoogle Sheets + Apps Script

The setup this assumes: a Google Sheet receiving Google Form responses, with the email address in column C. Adjust the column index in one place — there's a table at the bottom.

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: