// Sheets · Apps Script

Highlight duplicate values in Google Sheets.

Use Apps Script to count occurrences in a map, then flush all highlights in a single setBackgrounds call — the batch approach that does not freeze on large sheets.

“I want to automatically highlight every cell that contains a duplicate value in a column or range, without the script timing out on a sheet with thousands of rows.”

The script

copy · paste · trigger
highlightDuplicates.gs
Apps Script
// highlightDuplicates.gs
// Highlight duplicate values in column A, rows 2–1000.
// Change RANGE_ADDRESS and SHEET_NAME to match your sheet.
const SHEET_NAME = 'Sheet1';
const RANGE_ADDRESS = 'A2:A1000';
const DUPE_COLOR = '#FFD966'; // amber
const CLEAR_COLOR = '#FFFFFF'; // white

function highlightDuplicates() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SHEET_NAME);
  const range = sheet.getRange(RANGE_ADDRESS);
  const values = range.getValues();
  const counts = {};
  for (const row of values) {
    const v = String(row[0]).trim();
    if (v !== '') counts[v] = (counts[v] || 0) + 1;
  }
  const colors = values.map(function(row) {
    const v = String(row[0]).trim();
    return [counts[v] > 1 ? DUPE_COLOR : CLEAR_COLOR];
  });
  range.setBackgrounds(colors);
}
Generate yours

Need a variant? Gnaw writes a custom version from one sentence — fields, triggers, edge cases handled.

Walkthrough

Why per-cell loops break on large sheets

The first time I watched this bite someone, they had 800 rows and a script that called setBackground inside a nested loop. It took 40 seconds and eventually hit Apps Script's 6-minute execution limit. The problem is that every Sheets API call, no matter how small, crosses a process boundary — Apps Script runs in Google's V8 sandbox and each call to the spreadsheet service is an HTTP round-trip under the hood. Eight hundred calls is 800 round-trips.

The fix is to make exactly two service calls: one getValues to pull everything into a plain JavaScript array, and one setBackgrounds to push all the colors back. Everything between those two calls is local JavaScript — a frequency map built with a for-of loop, then a map() pass to assign colors. On a 1000-row column, the whole function runs in under 2 seconds.

Building the frequency map and color grid

getValues() returns a 2D array: each element is a row, each row is an array of cell values. For a single-column range like A2:A1000, every row array has exactly one element, so the value is always row[0].

The counts object is a plain key-value map: iterate once, increment. String(row[0]).trim() handles the two most common edge cases — numeric values that Sheets returns as JavaScript numbers, and cells with accidental leading or trailing spaces that would otherwise count as distinct from their neighbors.

The colors array mirrors the shape of values exactly: one sub-array per row, one color string per column. setBackgrounds requires this shape to match the range dimensions, or it throws a "The number of rows in the data does not match the number of rows in the range" error. Using values.map() to build colors guarantees the shapes stay in sync even if you change RANGE_ADDRESS later.

Running it automatically on edits

To re-highlight whenever the sheet changes, open Triggers (clock icon in the Apps Script editor), add a new trigger for highlightDuplicates, set the event source to "From spreadsheet," and choose "On edit." The on-edit trigger fires on every cell change, so for sheets that receive frequent edits consider an onChange trigger instead, which fires once per edit session rather than once per keystroke.

If you need to scope duplicates across multiple columns, extend RANGE_ADDRESS to cover them (for example, 'A2:C1000') and change row[0] to a concatenated key like row[0] + '|' + row[1] to treat the full row as the duplicate unit. The setBackgrounds shape scales automatically because the map() still mirrors values.

Want a custom version?

Describe your sheet and the rule you want. Gnaw writes the Apps Script — fields, triggers, edge cases — in one shot.

Generate yours Browse BullPack

FAQ

4 questions
Does this work if the duplicate values are numbers, not text?
Yes. String(row[0]).trim() converts whatever type Sheets returns — number, boolean, Date — into a string before it hits the frequency map, so numeric duplicates like 42 are counted the same as the string '42'. One edge case: dates are serialized as their locale string, which may vary by sheet locale. If you are deduplicating dates, compare row[0].valueOf() instead.
Why does setBackgrounds throw 'The number of rows in the data does not match'?
The colors array you pass must have exactly as many rows as the range has rows, and each inner array must have exactly as many entries as the range has columns. The safest way to guarantee this is to build colors with values.map() so its length is always derived from getValues() output, not hard-coded.
Can I highlight duplicates across two separate columns, treating each column independently?
Call the function twice with two different RANGE_ADDRESS values, or restructure the map to key on column index. The simplest approach: duplicate the function body into a helper that accepts a range, and call it once for each column. Sharing one frequency map across columns would conflate values that happen to be equal but live in logically separate fields.
The script runs but clears highlights I set manually. How do I preserve existing formatting?
Call range.getBackgrounds() before building the counts map, store the result, and in the map() pass only replace the color when counts[v] > 1 — otherwise fall back to the existing background from the stored array. This adds one more service call but keeps manual highlights on cells that are not duplicates.

Related scripts