Detect and flag duplicate customer emails

The same customer rarely uses just one email address. They raise a question from their work account, chase it from a personal one, and reply from their phone on a third — and to your inbox those look like three separate strangers. The history of one conversation ends up scattered, and whoever picks it up has to piece the customer together by hand.

At Northwind, the fix is a Contacts sheet that records each client’s known addresses. This script reads that sheet and, for every client, makes sure all their threads — whichever address they came from — land under one shared clients/{name} label. Open the label and you see the whole relationship in one place, no matter how many inboxes the customer emails from.

What you’ll need

• A Google Sheet whose first tab is a contacts list, with a header row containing exactly these columns: name, primaryEmail, aliases.
• The aliases column holds a client’s other addresses as a comma-separated list — for example [email protected], [email protected]. Leave it blank for clients with only one address.
• The Sheet’s ID, set as CONTACTS_SHEET in the config below.
• Nothing else — the script creates each clients/{name} label itself.

The script

// The Sheet whose first tab lists clients and their known email addresses.
const CONTACTS_SHEET = '1abcContactsSheetId';

// Only group threads from the recent past, so each run stays fast.
const WINDOW = 'newer_than:7d';

/**
 * Reads the Contacts sheet and, for each client, applies a shared
 * "clients/{name}" label to recent threads from any of their addresses.
 */
function groupContactAliases() {
  const contacts = readSheet(CONTACTS_SHEET);

  // 1. Bail out early if the contacts sheet has no rows.
  if (!contacts.length) {
    Logger.log('No contacts in the sheet — nothing to do.');
    return;
  }

  let labelled = 0;
  for (const contact of contacts) {
    // 2. Split the comma-separated aliases into a clean list.
    const aliases = (contact.aliases || '')
      .split(',')
      .map((s) => s.trim())
      .filter(Boolean);
    if (aliases.length === 0) continue; // no aliases to group

    // 3. Get (or create) the shared label for this client.
    const labelName = 'clients/' + slug(contact.name);
    const label = GmailApp.getUserLabelByName(labelName)
      || GmailApp.createLabel(labelName);

    // 4. For each alias, find recent threads not yet on the label and tag them.
    for (const alias of aliases) {
      const threads = GmailApp.search(
        'from:' + alias + ' ' + WINDOW + ' -label:' + labelName,
      );
      threads.forEach((t) => {
        t.addLabel(label);
        labelled++;
      });
    }
  }
  Logger.log('Applied client labels to ' + labelled + ' thread(s).');
}

/**
 * Turns a client name into a label-safe slug: lower case, with runs of
 * non-alphanumeric characters collapsed to single hyphens.
 */
function slug(s) {
  return String(s)
    .toLowerCase()
    .replace(/[^a-z0-9]+/g, '-')
    .replace(/^-|-$/g, '');
}

/**
 * Reads the first tab of a sheet into an array of objects keyed by the
 * header row.
 */
function readSheet(id) {
  const [header, ...rows] = SpreadsheetApp.openById(id)
    .getSheets()[0]
    .getDataRange()
    .getValues();
  return rows.map((r) =>
    Object.fromEntries(header.map((k, i) => [k, r[i]])));
}

How it works

1. groupContactAliases reads the contacts sheet into a list of objects via readSheet. If the sheet has no rows, it logs a message and stops.
2. For each client it splits the aliases cell on commas, trims each entry, and drops any blanks. A client with no aliases is skipped — there is nothing to group.
3. It builds a label name from the client’s name with slug, then fetches that label or creates it if it does not exist yet.
4. For every alias it runs a Gmail search for recent mail from: that address that is not already on the label — the -label: clause means a thread is only ever tagged once. Each matching thread picks up the shared label.
5. slug keeps label names tidy and valid by lower-casing the client name and collapsing anything that is not a letter or digit into single hyphens.
6. readSheet is a small helper that turns the sheet grid into objects keyed by the header row, so the rest of the code reads contact.aliases rather than chasing column numbers.

Example run

Say the Contacts sheet holds one row:

name	primaryEmail	aliases
Priya Shah	[email protected]	[email protected], [email protected]

During the last seven days, two threads arrived — one from [email protected], one from [email protected]. After a run, both threads carry the label clients/priya-shah, sitting alongside the threads from her primary [email protected] address. Opening that one label now shows the full conversation, however many addresses Priya used.

Trigger it

This should run quietly in the background so threads are grouped soon after they arrive:

1. In the Apps Script editor, open Triggers (the clock icon).
2. Click Add Trigger.
3. Choose groupContactAliases, event source Time-driven, type Hour timer, interval Every hour.
4. Save and approve the authorisation prompt.

The newer_than:7d window gives a comfortable margin — even if the trigger misses a few hours, a thread will still be caught on a later run.

Watch out for

• The script labels threads from a client’s aliases. Threads from their primaryEmail are assumed to already be labelled by a normal Gmail filter — this automation only stitches in the extra addresses.
• It matches on the sender address only. If a client emails from a brand-new address that is not yet in the aliases cell, that thread will not be grouped until you add it.
• An alias shared by more than one person — a generic info@ or support@ address — would pull unrelated threads under a client’s label. Keep the aliases column to addresses that genuinely belong to one individual.
• A thread is only labelled once thanks to the -label: clause, but a client who replies on an old thread after the seven-day window will not have that thread re-checked. Widen WINDOW if late replies are common.
• Label names come straight from slug(name). Two clients whose names slug to the same string would share a label — give them distinguishing names in the sheet if that ever happens.