Reading and writing sheets Updated November 2026

Remove duplicates

range.removeDuplicates() deletes rows that repeat an earlier row in every column; range.removeDuplicates([2]) treats rows as duplicates when only column B matches (sheet column numbers). In JavaScript, filter with a Set on a key: const seen = new Set(); rows.filter(r => !seen.has(r[1]) && seen.add(r[1])).

Syntax

range.removeDuplicates() | range.removeDuplicates(columnsToCompare)

Part	What it is
`columnsToCompare`	Array of absolute column indexes to compare; omit for all columns

Examples

const sheet = SpreadsheetApp.getActiveSheet();
const range = sheet.getRange(1, 1, sheet.getLastRow(), sheet.getLastColumn());
const result = range.removeDuplicates();
Logger.log(result.getNumRows());

Result: Repeated rows deleted; logs the number of rows left, for example 88

range.removeDuplicates([2]);

Result: Keeps the first row for each email in column B, removes the rest

const rows = sheet.getRange(2, 1, sheet.getLastRow() - 1, 3).getValues();
const seen = new Set();
const unique = rows.filter(r => {
  const key = String(r[1]).trim().toLowerCase();
  if (seen.has(key)) return false;
  seen.add(key);
  return true;
});
sheet.getRange(2, 1, rows.length, 3).clearContent();
sheet.getRange(2, 1, unique.length, 3).setValues(unique);

Result: Case-insensitive dedupe on column B with whitespace ignored

Common mistakes

removeDuplicates keeps rows that look identical: they differ by trailing spaces or case; normalize with trim and toLowerCase in the JavaScript version.
The header row was removed as a duplicate of another header: include it anyway, headers are unique, but if you have two header rows start at the second.
Old rows remain under the deduped block: clear the original range before writing the shorter unique array.
Column index off: removeDuplicates uses sheet column numbers; for a range starting at C, column C is 3.

Practice Remove duplicates

These drills check your answer on live data, free, no account needed.

Remove duplicates by a fieldReturn the items with duplicates removed, where two items are duplicates when `item[key]` is the same value. Keep the first item for each value, in the original order. Values are compared the way a `Set` compares them, so the number `1` and the string `"1"` are different.
Count by a fieldReturn an object that maps each value of `item[key]` to how many items have it. Skip items where the field is missing or `undefined`.

Questions

Which row is kept?

The first occurrence, reading top to bottom, in both removeDuplicates and the Set filter.

How do I count duplicates instead of removing them?

Build a count object: rows.forEach(r => counts[r[1]] = (counts[r[1]] || 0) + 1) and list keys with counts above 1.

Delete rowsDelete rows in Google Sheets with Apps Script: deleteRow and deleteRows by position, delete rows that match a condition by looping from the bottom, blanks.
Sort a rangeSort data in Google Sheets with Apps Script: range.sort by column number, descending order, multiple sort keys, sorting without the header, sorting an array.
map, filter and reduceUse map, filter and reduce on Google Sheets data in Apps Script: transform rows, keep rows that match a condition, sum a column, chain them, write back.
Loop through rowsLoop through rows in Google Sheets with Apps Script: read the data once with getValues, loop the 2D array with for, for...of or forEach, then write back.