Detect duplicate rows on a composite key

Northwind’s Invoices sheet is filled from a few directions at once — some rows come from an export, some are typed in by hand, some are copied from last month’s tab as a starting point. Sooner or later the same invoice ends up on the sheet twice. An invoice number on its own is not enough to spot that, because two different clients can both have an invoice number 001.

This automation flags genuine duplicates by matching on a composite key — the pair (client, invoiceNumber) together. Any two rows that share both values are coloured pink, so a duplicate stands out at a glance without anyone scanning the sheet line by line.

What you’ll need

• An Invoices sheet with a header row. Two columns must be headed exactly client and invoiceNumber — together they form the key.
• The spreadsheet ID, dropped into the config block in place of the placeholder.
• A daily trigger, set up once — see Trigger it.

The script

// The spreadsheet that holds the Invoices sheet.
const INVOICES_SHEET_ID = '1abcInvoicesSheetId';

// The two header names whose values, together, form the duplicate key.
const KEY_HEADERS = ['client', 'invoiceNumber'];

// The pink fill used to mark a duplicate row.
const FLAG_COLOUR = '#fde2e1';

/**
 * Scans the Invoices sheet and colours every row that shares its
 * (client, invoiceNumber) pair with another row.
 */
function flagComposites() {
  const sheet = SpreadsheetApp.openById(INVOICES_SHEET_ID).getSheets()[0];

  // 1. Read the whole sheet and split off the header row.
  const values = sheet.getDataRange().getValues();
  const [header, ...rows] = values;
  if (!rows.length) {
    Logger.log('No invoice rows — nothing to check.');
    return;
  }

  // 2. Map each header name to its column index for easy lookup.
  const col = Object.fromEntries(header.map((h, i) => [h, i]));

  // 3. Group sheet row numbers by their composite key.
  const seen = new Map();
  rows.forEach((r, i) => {
    const key = KEY_HEADERS.map((h) => r[col[h]]).join('|');
    if (!seen.has(key)) seen.set(key, []);
    seen.get(key).push(i + 2); // +2: skip the header, switch to 1-based rows
  });

  // 4. Clear any colouring from a previous run.
  sheet.getRange(2, 1, rows.length, header.length).setBackground(null);

  // 5. Colour every key that appears on more than one row.
  let flagged = 0;
  for (const ids of seen.values()) {
    if (ids.length < 2) continue;
    for (const r of ids) {
      sheet.getRange(r, 1, 1, header.length).setBackground(FLAG_COLOUR);
      flagged++;
    }
  }
  Logger.log(`Flagged ${flagged} duplicate rows.`);
}

How it works

1. flagComposites opens the invoices spreadsheet and reads every row in one call, then splits the header off the data.
2. If there are no data rows it logs a message and stops — no point checking an empty sheet.
3. It builds a small lookup, col, that maps each header name to its column index, so the script never depends on columns sitting in a fixed order.
4. It walks the rows, builds a key for each one by joining the client and invoiceNumber values with a |, and records the sheet row number under that key in a Map.
5. It clears any pink fill left by an earlier run, so a row that is no longer a duplicate goes back to plain.
6. It looks at each group of row numbers. Any key with two or more rows is a duplicate, and every row in that group gets the pink fill.

Example run

Suppose the Invoices sheet contains these rows:

client	invoiceNumber	amount
Acme Ltd	001	1,200
Beta Co	001	480
Acme Ltd	002	950
Acme Ltd	001	1,200	← pink
Acme Ltd	001	1,200	← pink

Rows 2 and 3 share invoice number 001 but belong to different clients, so they are left alone. Rows 2, 5 and 6 all carry the pair (Acme Ltd, 001) — the script colours rows 5 and 6, and row 2 as well, since all three are part of the same duplicate group.

Trigger it

Run the check once a day so duplicates are caught the morning after they are entered:

1. In the Apps Script editor, open Triggers (the clock icon).
2. Click Add trigger.
3. Choose the flagComposites function, event source Time-driven, type Day timer, and pick an early-morning hour.
4. Save and approve the authorisation prompt.

You can also run flagComposites by hand from the editor whenever you have just pasted in a batch of rows.

Watch out for

• The match is exact. Acme Ltd and Acme Ltd (with a trailing space) count as different clients and will not be flagged as duplicates. Trim your data, or add .toString().trim() when building the key.
• Case matters too — acme ltd and Acme Ltd are treated as separate clients. Lower-case both values in the key if your data is inconsistent.
• It flags every row in a duplicate group, including the original. The pink fill tells you a row has a twin; deciding which copy to keep is still a human call.
• The script only colours rows — it never deletes anything. That is deliberate: it is safer to review duplicates than to have a script remove them.
• A blank client or invoiceNumber produces a key like |001. Several empty rows will all match each other and get flagged — filter out blank rows first if that is noisy.