Imported leads often repeat the same email. Deduping in Apps Script is faster than manual filters for recurring loads.
deduplicateRowsByEmail scans column B, lowercases/trims emails, and keeps the first row for each address.
Blank emails are preserved (not treated as duplicates of each other) so incomplete rows are not collapsed.
The sheet is rewritten with clearContents + setValues — back up first.
Need this built? Hire a Google Apps Script developer →
Sheet / project setup
| Sheet | Column | Purpose |
|---|---|---|
| Leads | A Name | Lead name |
| Leads | B Email | Dedupe key |
| Leads | C Source | Attribution |
| EMAIL_COL | 2 | 1-based column index |
What this script does
First-wins dedupe by normalized email with a full sheet rewrite.
Prerequisites
Leads sheet; backup copy; email in column B.
- Decide first vs last wins
- Normalize Gmail dots only if required
- Width stable across rows
Walkthrough
Insert two rows with same email different case; run; confirm one remains.
Edge cases
clearContents removes formatting — use clear({contentsOnly:true}) if you need formats kept.
- Order preserved for first occurrences
- Empty emails kept
How to test
Count unique emails with a Set before/after.
Hardening for production
Write duplicates to Leads_Dupes instead of dropping; soft-delete with a flag column.
Variations
Dedupe on compound keys like email+campaign.
Full code: deduplicateRowsByEmail()
Backup the spreadsheet, set EMAIL_COL, then run deduplicateRowsByEmail().
/**
* Deduplicate rows by email, keeping the first occurrence.
*/
const SHEET = "Leads";
const EMAIL_COL = 2; // B
function deduplicateRowsByEmail() {
const sheet = SpreadsheetApp.getActive().getSheetByName(SHEET);
const values = sheet.getDataRange().getValues();
const seen = {};
const kept = [values[0]];
let removed = 0;
for (let i = 1; i < values.length; i++) {
const email = String(values[i][EMAIL_COL - 1] || "").trim().toLowerCase();
if (!email) {
kept.push(values[i]);
continue;
}
if (seen[email]) {
removed++;
continue;
}
seen[email] = true;
kept.push(values[i]);
}
sheet.clearContents();
sheet.getRange(1, 1, kept.length, kept[0].length).setValues(kept);
Logger.log("Removed %s duplicate email rows; kept %s", removed, kept.length - 1);
}- Line 5: 1-based email column index.
- Line 15: Normalizes case before comparison.
- Line 20: Tracks emails already kept.
- Line 16: Blank emails are not deduped against each other.
- Line 28: Prepares the sheet for rewrite.
- Line 29: Writes the deduped matrix.
Deploy this example
- 01
Open Apps Script
In the bound spreadsheet: Extensions → Apps Script. For standalone projects, create one at script.google.com and link your Sheet by ID.
- 02
Paste and save
Add a .gs file, paste the code below, rename constants at the top (sheet names, column letters, API property keys), then save.
- 03
Authorize once
Run the main function from the editor. Accept OAuth scopes when prompted — triggers cannot run until authorization succeeds once.
- 04
Add the trigger
Triggers → Add trigger → choose the handler function and event (time-driven, on edit, or on form submit). Delete test triggers before production.
Before you run: dedupe
- 1Backup or duplicate the file
- 2Confirm email column index
- 3Agree first-wins policy with stakeholders
- 4Check blank email policy
- 5Run on a copy with known duplicates first
- 6Re-apply filters/frozen rows if needed after rewrite
Frequently asked questions
Iterate from the bottom or overwrite seen[email] with the latest row index, then rebuild.
Normalize by stripping +tag before @ if your business treats them as one person.
getValues snapshots calculated values; formulas are not restored. Use getFormulas if needed.
Works until memory pressure — process in chunks writing to a new sheet.
Lowercasing collapses those. Accented characters still differ.
Build a seen map from the master sheet, then filter the secondary sheet.
Only via version history or your backup — treat as destructive.