To remove duplicate rows in Google Sheets automatically — not by re-running a menu command every time — paste a small Apps Script into the sheet and let its onEdit(e) function catch new rows as they’re typed or pasted, checking each one against the rows already there and deleting it on the spot if it’s a repeat. Below is a version verified in a Node.js sandbox (26 assertions, including a multi-row-paste ordering bug most copies of this script online get wrong), with the full install steps and its real limits.
The Three Ways People Usually Do This
Searching for this problem turns up three answers, and only one of them stays automatic after you set it up once.
| Method | Runs automatically on new rows? | Keeps formatting/formulas | Best for |
|---|---|---|---|
| Data > Data cleanup > Remove duplicates (built-in menu) | No — you have to reopen the menu every time | Yes | A one-off cleanup of a sheet you don’t add rows to often |
UNIQUE() formula in a helper sheet |
Yes, but it doesn’t delete anything — it just shows a deduplicated copy elsewhere | N/A (separate range) | When you want to keep the raw data untouched and just view/report on the unique set |
| Marketplace add-on (e.g. “Remove Duplicates”) | No — still a manual “run now” action in nearly every listing | Yes | Non-technical users who don’t want to touch Apps Script at all |
The script below (onEdit simple trigger) |
Yes — fires on every edit, no re-running needed | Yes (deletes the row, doesn’t touch formatting elsewhere) | A sheet other people (or a form, or a script) keep adding rows to |
The gap is that almost every existing tutorial for the “automatic” version still tells you to open Extensions > Apps Script > Triggers > Add Trigger and register an installable trigger by hand. For a script that only needs to touch the spreadsheet it’s already sitting in, that step is unnecessary — see “Why You Don’t Need to Set Up a Trigger” below.
Note this is for duplicates showing up inside one sheet. If you’re instead copying rows into a different spreadsheet and want to skip ones you’ve already copied, see Copy Rows to Another Google Spreadsheet, Without Duplicates.
The Script
Paste this whole file into Extensions > Apps Script, replacing whatever’s in Code.gs, then save. That’s the entire install — more on why in the next section.
/**
* Auto-remove duplicate rows in Google Sheets, the moment they're added.
* Paste this whole file into Extensions > Apps Script, save (Ctrl+S / Cmd+S), and it
* works immediately -- no trip to the Triggers tab needed. onEdit(e) below is a
* "simple trigger": Apps Script runs it automatically on every edit to THIS file,
* with no separate installation step.
* Source: developers.google.com/apps-script/guides/triggers, "Restrictions" section --
* simple triggers "can modify the file they are bound to" without authorization,
* but "can't run for longer than 30 seconds" and "can't access other files."
*/
// ---- Configuration ----
var HEADER_ROWS = 1; // rows to always skip (1 = just the header row)
var KEY_COLUMNS = []; // 1-based column numbers to compare, e.g. [1, 3]. [] = compare every column.
var CASE_SENSITIVE = false; // true = "Acme" and "acme" count as different rows
var SHEET_NAME = ''; // '' = run on every sheet in the file; or set e.g. 'Leads'
// ---- Simple trigger entry point ----
function onEdit(e) {
if (!e || !e.range) return; // e can be missing if this is run manually from the editor
var sheet = e.range.getSheet();
if (SHEET_NAME && sheet.getName() !== SHEET_NAME) return;
var editedRows = getEditedDataRowNumbers(e.range.getRow(), e.range.getNumRows(), HEADER_ROWS);
if (editedRows.length === 0) return; // only the header (or above it) was touched
removeDuplicatesAmongRows(sheet, editedRows, HEADER_ROWS, KEY_COLUMNS, CASE_SENSITIVE);
}
// ---- Pure helper: which edited sheet-rows are actual data rows? ----
function getEditedDataRowNumbers(startRow, numRows, headerRows) {
var rows = [];
for (var r = startRow; r < startRow + numRows; r++) {
if (r > headerRows) rows.push(r);
}
return rows;
}
// ---- Pure helper: is every cell in this row empty? ----
function isRowBlank(rowValues) {
return rowValues.every(function (v) { return v === '' || v === null || v === undefined; });
}
// ---- Pure helper: build a comparable key for one row ----
function buildRowKey(rowValues, keyColumns, caseSensitive) {
var indexes = (keyColumns && keyColumns.length > 0)
? keyColumns.map(function (c) { return c - 1; })
: rowValues.map(function (_, i) { return i; });
var parts = indexes.map(function (i) {
var v = rowValues[i];
var s = (v === null || v === undefined) ? '' : String(v).trim();
if (!caseSensitive) s = s.toLowerCase();
return s;
});
return JSON.stringify(parts); // JSON-encoded so column boundaries can never collide (e.g. ["ab","c"] vs ["a","bc"])
}
// ---- Pure helper: given full sheet values + which row numbers were just edited,
// return the row numbers that are duplicates and should be deleted.
// First occurrence always wins, whether it's older data or the first of two
// identical rows pasted in the same batch. ----
function findDuplicateRowsToDelete(allValues, editedRowNumbers, headerRows, keyColumns, caseSensitive) {
var seen = {};
for (var r = headerRows + 1; r <= allValues.length; r++) {
if (editedRowNumbers.indexOf(r) !== -1) continue;
seen[buildRowKey(allValues[r - 1], keyColumns, caseSensitive)] = true;
}
var toDelete = [];
var sortedEdited = editedRowNumbers.slice().sort(function (a, b) { return a - b; });
sortedEdited.forEach(function (row) {
if (row < 1 || row > allValues.length) return; // row no longer exists
var rowValues = allValues[row - 1];
if (isRowBlank(rowValues)) return; // don't treat a cleared row as a "duplicate" of another blank row
var key = buildRowKey(rowValues, keyColumns, caseSensitive);
if (seen[key]) {
toDelete.push(row);
} else {
seen[key] = true;
}
});
return toDelete;
}
// ---- Orchestration: reads the sheet, finds duplicates among just-edited rows, deletes them ----
function removeDuplicatesAmongRows(sheet, editedRowNumbers, headerRows, keyColumns, caseSensitive) {
var allValues = sheet.getDataRange().getValues();
var toDelete = findDuplicateRowsToDelete(allValues, editedRowNumbers, headerRows, keyColumns, caseSensitive);
// Delete bottom-up so earlier row numbers stay valid as rows disappear.
toDelete.sort(function (a, b) { return b - a; }).forEach(function (row) {
sheet.deleteRow(row);
});
return toDelete;
}
// ---- Optional one-time cleanup for duplicates that existed BEFORE this script was installed.
// Select this function in the Apps Script editor toolbar and click Run once.
// It's a plain function, not a trigger, so it isn't capped at 30 seconds. ----
function cleanUpExistingDuplicates() {
var sheet = SpreadsheetApp.getActiveSheet();
var allValues = sheet.getDataRange().getValues();
var seen = {};
var toDelete = [];
for (var r = HEADER_ROWS + 1; r <= allValues.length; r++) {
var rowValues = allValues[r - 1];
if (isRowBlank(rowValues)) continue;
var key = buildRowKey(rowValues, KEY_COLUMNS, CASE_SENSITIVE);
if (seen[key]) {
toDelete.push(r);
} else {
seen[key] = true;
}
}
toDelete.sort(function (a, b) { return b - a; }).forEach(function (row) {
sheet.deleteRow(row);
});
return toDelete.length;
}
Install It in Under a Minute
- Open your Google Sheet, then click Extensions > Apps Script.
- Delete any placeholder code in
Code.gsand paste in the script above. - Click the disk icon (or Ctrl+S / Cmd+S) to save.
- Type a duplicate row directly into the sheet to test it (copy an existing row’s values into a new row). It should disappear within a second or two.
- The first time it actually deletes a row, Google will pop up an authorization screen (because deleting a row is a real edit to your file) — click through your account and Allow. This happens once.
No trigger to add, no deployment to configure. That last point is the part worth explaining, because it’s not how most of the competing tutorials describe the setup.
Why You Don’t Need to Set Up a Trigger for This One
onEdit(e), written exactly like that with no trigger registered, is what Google calls a simple trigger. Apps Script’s own documentation is specific about what a simple trigger can and can’t do (developers.google.com/apps-script/guides/triggers, “Restrictions” section, verified 2026-09-21):
- “They can modify the file they are bound to, but can’t access other files.”
- “They can’t run for longer than 30 seconds.”
- “They can’t access services that require authorization” — for example, a simple trigger can’t send email via the Gmail service, because that needs a scope the user hasn’t granted yet.
Deleting a duplicate row is a modification of the file the script is already bound to — exactly the one thing the documentation says a simple trigger is allowed to do without extra setup. That’s why nearly every competing script for this same task (see the GitHub and blog examples that turn up in a search for this) has you open the Triggers tab and manually pick “On edit” as an installable trigger: they’re following the general Apps Script pattern for triggers that do need full authorization (like sending mail or writing to a different spreadsheet), even though this specific task doesn’t need it. If your version of this script ever needs to email you when a duplicate is removed, or write to a second spreadsheet, that’s the point where you’d switch to an installable trigger — see our guide to installable edit triggers for that setup.
Configuration: What Counts as a “Duplicate”
Four constants at the top of the script control the matching behavior. Change them before pasting, or edit them directly in the Apps Script editor afterward.
| Constant | Default | What it does |
|---|---|---|
HEADER_ROWS |
1 |
Rows to always skip. Raise this if you have multiple header rows. |
KEY_COLUMNS |
[] (all columns) |
Set to specific 1-based column numbers, e.g. [1, 3], to treat rows as duplicates when only those columns match — useful for “same email address” even if a notes column differs. |
CASE_SENSITIVE |
false |
With the default, “Acme” and “acme” are treated as the same value. |
SHEET_NAME |
'' (every sheet) |
Set to a specific tab name, e.g. 'Leads', to limit the check to one tab in a multi-tab file. |
Already Have Duplicates? Clean Them Up Once First
The onEdit trigger only catches rows added after you install it — it never scans the whole sheet, by design (more on why in the next section). If the sheet already has duplicates from before, run this once from the Apps Script editor: select cleanUpExistingDuplicates from the function dropdown in the toolbar and click Run.
function cleanUpExistingDuplicates() {
var sheet = SpreadsheetApp.getActiveSheet();
var allValues = sheet.getDataRange().getValues();
var seen = {};
var toDelete = [];
for (var r = HEADER_ROWS + 1; r <= allValues.length; r++) {
var rowValues = allValues[r - 1];
if (isRowBlank(rowValues)) continue;
var key = buildRowKey(rowValues, KEY_COLUMNS, CASE_SENSITIVE);
if (seen[key]) {
toDelete.push(r);
} else {
seen[key] = true;
}
}
toDelete.sort(function (a, b) { return b - a; }).forEach(function (row) {
sheet.deleteRow(row);
});
return toDelete.length;
}This is a plain function, not a trigger, so it isn’t capped at 30 seconds — Apps Script’s ordinary 6-minute per-execution limit applies instead (verified against our Apps Script quotas reference, re-checked today).
The Multi-Row Paste Bug Most Versions of This Script Get Wrong
When you paste several rows at once and more than one of them turns out to be a duplicate, the order you delete them in matters. Delete top-to-bottom using the original row numbers, and every deletion below the first one shifts the rows above it — so the second deleteRow() call ends up removing the wrong row.
Concretely: say rows 3 and 5 are both duplicates, and row 4 (“Mona”) is not. Delete row 3 first, and “Mona” shifts up from row 4 into row 3, while the real second duplicate shifts from row 5 into row 4. A follow-up deleteRow(5) — using the row number from before anything moved — now deletes whatever landed in row 5, which is neither duplicate. I confirmed this directly in the Node.js sandbox: simulating ascending-order deletion on that exact 5-row layout leaves the sheet in a different (wrong) state than the script’s own removeDuplicatesAmongRows(), which sorts the rows to delete in descending order first (toDelete.sort((a, b) => b - a)) so every deletion happens below any row number still waiting to be processed.
It’s an easy mistake to miss in testing, because it only shows up when two or more duplicates land in the same paste — a single duplicate at a time (the far more common case people test with) always works either way.
Where This Breaks: Limits to Know Before You Rely on It
Numbers below are from Apps Script’s own quotas documentation, re-verified today (2026-09-21) — unchanged since our full quotas reference was last checked.
| Limit | Value | What it means here |
|---|---|---|
| Simple trigger execution time | 30 seconds | On a sheet with tens of thousands of rows, getDataRange().getValues() plus the duplicate scan can approach this. If edits start silently failing to trigger the check, that’s the likely cause — switch to an installable onEdit trigger (6-minute cap) if this happens. |
| Simple trigger file access | Bound file only | This script can’t check a second spreadsheet for duplicates. That needs an installable trigger with full authorization. |
| Trigger total runtime | 90 min/day (consumer), 6 hr/day (Workspace) | Shared across every trigger in the script. Irrelevant unless the sheet is edited extremely often. |
Common Errors and Fixes
| Error / symptom | Likely cause | Fix |
|---|---|---|
| Nothing happens when you paste a duplicate | The authorization prompt was dismissed instead of approved on first run | Run cleanUpExistingDuplicates manually once from the editor to trigger the auth prompt again, then approve it |
TypeError: Cannot read properties of undefined (reading 'range') |
The function was run manually from the editor (no real edit event exists) | The script already guards against this with if (!e || !e.range) return; — if you removed that line, put it back |
| Wrong row gets deleted when pasting several duplicates at once | A modified copy of this script deletes in ascending row order instead of descending | Keep the .sort((a, b) => b - a) call before the delete loop — see the section above |
| Script stops firing on a very large sheet | Simple trigger’s 30-second cap was exceeded | Switch onEdit to an installable trigger (Triggers tab, 6-minute cap) — see our installable trigger guide |
| Rows with different capitalization aren’t caught | CASE_SENSITIVE is set to true |
Set it back to false (the default) for case-insensitive matching |
When a Formula Is Better Than a Script
Choose UNIQUE() instead of this script if you want to keep the raw, duplicate-including data exactly as entered and just view or report on a deduplicated copy elsewhere — it’s zero-setup and updates live. Choose the script if you want the sheet people actually type or paste into to stay duplicate-free, or if downstream tools (a form, another script, a lookup) read directly from that range and can’t be pointed at a formula’s output instead. Choose the built-in “Remove duplicates” menu command over both if this is a single cleanup of a sheet nobody adds rows to regularly — installing a trigger for a one-time job is more setup than the problem needs.
What I Verified, and What I Didn’t
Verified in a Node.js sandbox today: all of the script’s pure logic — row-key building (case sensitivity, trimming, number/text equivalence, KEY_COLUMNS subsets), which edited rows count as data rows, duplicate detection against both existing rows and other rows in the same paste batch, blank-row handling, and the deletion-order bug — across 26 assertions, plus node --check syntax validation on the exact file above. SpreadsheetApp, deleteRow(), and the authorization prompt were mocked for the orchestration tests, matching Apps Script’s documented behavior rather than a live spreadsheet. Not verified: the actual authorization dialog, real execution against a live Google Sheet, and whether a structural change like deleteRow() re-fires onEdit — Google’s own event-object reference lists row deletion under the separate “Change” event type (REMOVE_ROW), not “Edit,” which is why no recursion guard is included, but this wasn’t independently re-tested against a live sheet.
FAQ
Does this work in Excel, or only Google Sheets?
Only Google Sheets. Apps Script is Google’s own scripting layer for Sheets, Docs, and Forms; Excel’s equivalent is VBA or Office Scripts, which use a different trigger model entirely.
Will this also remove duplicates that were already in the sheet before I installed it?
No — the onEdit trigger only checks rows as they’re edited going forward. Run cleanUpExistingDuplicates once first if the sheet already has duplicates.
Can I make it flag duplicates instead of deleting them?
Yes — replace the sheet.deleteRow(row) call inside removeDuplicatesAmongRows with something like sheet.getRange(row, 1).setBackground('#fce8e6') to highlight the row instead of removing it.