Google Sheets with Apps Script cleaning duplicate records

How to Remove Duplicates in Google Sheets Automatically (and Keep the Right Row)

ByMohammad Farhad

Duplicates in a Google Sheet are rarely just untidy. In a product list they mean the same item rings up at two prices. In a CRM sheet they mean two salespeople call the same lead. Removing them by hand works once; then they come back.

We built the approach below for a retailer with 3,259 product records spread over two 10,000-row sheets and 1,013 duplicate barcodes. Cleaning it took two people a full day. The script now does it in minutes and only asks a person to look at the genuine conflicts.

Why the built-in tool isn’t enough

Sheets has Data → Data cleanup → Remove duplicates, which deletes rows that match exactly. Real duplicates usually don’t match exactly: the same barcode has a different price, or the same email has a different phone number. You need to decide which row wins, and you want a record of what was different.

The rule we use

  1. Group rows by a key: barcode, SKU, or lowercase email.
  2. Keep the most recently updated row for each key.
  3. If the duplicates disagree on something that matters, such as price, log them to a Review tab for a human decision.
  4. Write the clean list to a separate Clean tab, so the original data is never destroyed.

The script

Open Extensions → Apps Script in your sheet and paste this. It assumes a Products tab with Barcode, Price and Updated columns; change the names to match yours.

function resolveDuplicates() {
  const ss = SpreadsheetApp.getActive();
  const rows = ss.getSheetByName('Products').getDataRange().getValues();
  const head = rows.shift();
  const iKey = head.indexOf('Barcode');
  const iPrice = head.indexOf('Price');
  const iDate = head.indexOf('Updated');

  const latest = new Map();          // key -> newest row
  const conflicts = [];

  rows.forEach((r) => {
    const key = String(r[iKey]).trim();
    if (!key) return;
    const seen = latest.get(key);
    if (!seen) { latest.set(key, r); return; }
    if (seen[iPrice] !== r[iPrice]) conflicts.push([key, seen[iPrice], r[iPrice]]);
    if (new Date(r[iDate]) > new Date(seen[iDate])) latest.set(key, r);
  });

  writeTab_(ss, 'Clean', [head, ...latest.values()]);
  writeTab_(ss, 'Review', [['Barcode', 'Price A', 'Price B'], ...conflicts]);
}

function writeTab_(ss, name, data) {
  const sh = ss.getSheetByName(name) || ss.insertSheet(name);
  sh.clearContents();
  if (data.length) sh.getRange(1, 1, data.length, data[0].length).setValues(data);
}

Because it reads and writes the whole range in one go, this handles tens of thousands of rows comfortably inside Apps Script’s 6-minute limit.

Making it stick

  • Run it on a schedule. Add a nightly time-driven trigger so duplicates never build up again.
  • Normalise keys first. Trim spaces, lowercase emails and strip leading zeros from barcodes if your systems do that inconsistently. Most “missed” duplicates are formatting.
  • Merge sheets the same way. To combine two sources, read both ranges into one array before the loop. The newest row still wins.
  • Keep an audit trail. Add a timestamp column to the Review tab so you can see when each conflict appeared.

Beyond barcodes

The same pattern de-duplicates CRM contacts by email, merges supplier price lists, and cleans enriched lead lists before they reach a sales team. On one lead project we enriched 13,616 businesses; de-duplicating first saved paying for the same lookup twice.

Got a sheet that keeps filling with duplicates? Book a free Workflow Audit and we’ll show you what a permanent fix looks like.

Similar Posts