Originally written for bulldo.gs — republished here with the canonical link pointing home.
I have a Google Sheet with duplicate rows and I want to remove them programmatically, either on demand or on a schedule, without destroying the rest of my data.
// removeDuplicates.gs — dedup active sheet, keep first occurrence
// Run from Extensions > Apps Script, or bind to a trigger.
function removeDuplicateRows() {
var sheet = SpreadsheetApp.getActiveSheet();
var data = sheet.getDataRange().getValues();
var seen = new Set();
var unique = [];
for (var i = 0; i < data.length; i++) {
var key = data[i].join('|');
if (!seen.has(key)) {
seen.add(key);
unique.push(data[i]);
}
}
sheet.clearContents();
sheet.getRange(1, 1, unique.length, unique[0].length).setValues(unique);
}
Why rewrite instead of delete
The instinct when deduplicating is to loop through the sheet and call deleteRow on each duplicate. That works, but it has a sharp edge: every call to deleteRow shifts all rows below it up by one. If you delete row 3, what was row 4 is now row 3, and your loop index is already pointing at the new row 4. The safe workaround people reach for is iterating bottom-to-top, which works but means holding the full duplicate set in memory anyway, making one API call per deleted row.
The approach here sidesteps the problem entirely. Read everything once with getDataRange().getValues() — a single API call that returns a 2D array. Build the deduplicated array in JavaScript using a Set to track which row fingerprints you have already seen. Then clear the sheet and write the result back with one setValues call. Two API calls total, regardless of how many duplicates you had. For a 10,000-row sheet, this is the difference between a script that finishes in two seconds and one that times out at the six-minute Apps Script execution limit.
The row key is built with data[i].join('|'). The pipe character works as a separator in practice; if your data actually contains pipe characters in every column, use a multi-character sentinel like '||~||' to eliminate false collisions. I have watched this bite people on CRM exports where one column was a phone number formatted as +1|555|0100 — the joined key collides across rows that are genuinely distinct.
Wiring it to a trigger or a menu item
Running removeDuplicateRows manually from the Apps Script editor is fine for one-off cleanup. For anything recurring — a sheet fed by a form, a nightly import, a webhook dump — you want a trigger.
In the Apps Script editor, open the clock icon (Triggers), add a new trigger, point it at removeDuplicateRows, and set it to time-driven on whatever cadence matches your ingest rate. The script runs as you, under your quota, so it counts against your daily Apps Script runtime allowance (6 minutes per execution, 90 minutes per day on the free tier).
If you want a button in the sheet UI instead, add this alongside the existing function: function onOpen() { SpreadsheetApp.getActiveSpreadsheet().addMenu('Tools', [{name: 'Remove duplicates', functionName: 'removeDuplicateRows'}]); }. The onOpen trigger fires automatically each time the spreadsheet opens and puts a menu entry under a custom Tools header. The first time I set this up for a client, I forgot that onOpen only runs for the user who opens the file — shared sheets where a non-owner triggers it still work, because the menu calls the bound script, which runs as the script owner.
Deduping on a subset of columns, not the whole row
Sometimes two rows are duplicates for your purposes even if one column differs — a timestamp column, an auto-incremented ID, a status field that got updated. In that case, building the key from the entire row gives you false negatives (rows that should be collapsed but aren't, because column G differs by a second).
To key on specific columns only, replace the join line. If columns A, B, and C (indexes 0, 1, 2) define uniqueness: var key = [data[i][0], data[i][1], data[i][2]].join('|'). Everything else in the script stays the same. The unique array still keeps the full row — you are only changing what counts as a duplicate, not what gets written back.
One practical note: this keeps the first occurrence. If the column you excluded (say, a timestamp) is meaningful and you want the most recent occurrence rather than the earliest, sort the data descending by that column before the dedup loop. getValues returns rows in sheet order, so pre-sort in the sheet or add a JavaScript sort step before the Set iteration.
FAQ
Will this delete my header row?
No, as long as your header is row 1 and is unique (which it almost always is — column names do not repeat). The Set sees it first and keeps it. If you have a pathological case where two header rows are identical, add an if (i === 0) { unique.push(data[i]); continue; } guard at the top of the loop.
The script runs but then the sheet is blank — what happened?
Almost certainly clearContents fired and then setValues failed silently because unique was empty or unique[0] was undefined. This happens when getDataRange returns a sheet with no data (e.g., a freshly created tab) or when all rows were considered duplicates of each other — check that your separator character does not appear in every cell of a column. Add a guard: if (unique.length === 0) return; before the clearContents call.
Can I run this on a specific named sheet instead of the active one?
Replace SpreadsheetApp.getActiveSheet() with SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Your Sheet Name'). getSheetByName returns null if the name does not match exactly (case-sensitive, leading/trailing spaces count), so add a null check if you are running this from a trigger where the active sheet might not be the one you intend.
How do I dedup across multiple sheets at once?
Wrap the body of removeDuplicateRows in a helper that accepts a sheet argument, then iterate: var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets(); for (var s = 0; s < sheets.length; s++) { dedupSheet(sheets[s]); }. Each sheet gets its own isolated Set, so duplicates are evaluated per-sheet, not across sheets.
Want the plain-English version? Describe the automation at bulldo.gs and get working Apps Script back — free, no login.
