---
aliases: 
tags: 
dailynote: "[[2026-02-12]]"
---
# Google Sheets – Duplicate Email Detection & Removal

## Setup Overview

- **Sheet:** Google Form responses linked to a Google Sheet
- **Email Column:** Column C

---

## 1. Conditional Formatting (Highlight Duplicates Red)

- **Apply to range:** `C2:C` (email cell only) or `A2:Z` (entire row)
- **Format rule:** Custom formula is
- **Formula:**

	```
    =COUNTIF($C$2:$C, $C2) > 1
    ```
	
- **Formatting:** Red fill
- New form responses are automatically checked as they come in.

---

## 2. Apps Script (Remove Duplicates)

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

This keeps the **first occurrence** of each email and deletes all later duplicates.

```javascript
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, ''); // Column C (index 2)

    if (seen[email]) {
      rowsToDelete.push(i + 1); // +1 because sheet rows are 1-indexed
    } 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.');
}
```

### Key Details

- `data[i][2]` targets **Column C** (zero-indexed: A=0, B=1, C=2)
- `.toLowerCase()` handles case differences (e.g., `John@gmail.com` vs `john@gmail.com`)
- `.replace(/[^\x20-\x7E]/g, '')` strips invisible/non-printable characters that Google Forms sometimes adds
- `.replace(/\s+/g, '')` removes all hidden whitespace
- Rows are deleted bottom-to-top so row numbers don't shift during deletion

### To Change the Target Column

Replace `data[i][2]` with the correct index:

|Column|Index|
|---|---|
|A|0|
|B|1|
|C|2|
|D|3|
|E|4|

### Optional: Automate on a Schedule

In Apps Script, go to **Triggers** (clock icon) → **Add Trigger** → set `removeDuplicates` to run on a time-based schedule (e.g., every hour or daily).