Auto-categorize emails by project keyword

Northwind tags client emails by project so each account’s correspondence stays together. Gmail’s own filters can do this, but editing them by hand stops scaling past about five projects — every new client means clicking through the filter UI, and there is no single place to see all the rules.

This automation moves the rules into a Sheet. Each row pairs a keyword with a Gmail label; the script reads the sheet, searches recent mail for each keyword, and applies the matching label. Adding a project becomes one new row instead of a trip through Gmail’s settings.

What you’ll need

• A Filters sheet with two columns and a header row: keyword and label. An example row: acme rebrand in keyword, clients/acme/rebrand in label.
• The Gmail labels named in the sheet must already exist — the script applies labels, it does not create them.
• The Filters sheet’s file ID, dropped into the config constant at the top of the script.

The script

// The sheet holding the keyword-to-label rules.
const FILTERS_SHEET = '1abcFiltersSheetId';

// How far back to search — keeps each run fast and within quota.
const SEARCH_WINDOW = 'newer_than:1d';

/**
 * Reads the keyword/label rules from the Filters sheet and applies each
 * label to recent threads matching its keyword.
 */
function applyKeywordLabels() {
  // 1. Read the Filters sheet and build a column-name lookup.
  const [header, ...rows] = SpreadsheetApp.openById(FILTERS_SHEET)
    .getSheets()[0].getDataRange().getValues();
  const col = Object.fromEntries(header.map((h, i) => [h, i]));

  if (!rows.length) {
    Logger.log('No filter rules defined — nothing to do.');
    return;
  }

  for (const row of rows) {
    const keyword = row[col.keyword];
    const labelName = row[col.label];

    // 2. Skip incomplete rows.
    if (!keyword || !labelName) continue;

    // 3. Skip rows whose label does not exist in Gmail.
    const label = GmailApp.getUserLabelByName(labelName);
    if (!label) {
      Logger.log('Label not found, skipping: ' + labelName);
      continue;
    }

    // 4. Search recent mail for the keyword, excluding already-labelled
    //    threads so we do not relabel the same thread every run.
    const query = '"' + keyword + '" ' + SEARCH_WINDOW +
      ' -label:' + labelName;
    const threads = GmailApp.search(query);

    // 5. Apply the label to every matching thread.
    threads.forEach((t) => t.addLabel(label));
  }
}

How it works

1. The script reads the whole Filters sheet, splits off the header, and builds a col lookup so columns can be addressed by name.
2. If there are no rules, it logs a message and stops.
3. For each rule it reads the keyword and label, skipping any row missing either value.
4. It looks the label up in Gmail with getUserLabelByName. If the label does not exist, it logs the name and moves on rather than crashing.
5. It builds a Gmail search query — the quoted keyword, a recency filter, and -label: so threads that already carry the label are excluded.
6. It runs the search and adds the label to every thread that comes back.

Example run

The Filters sheet:

keyword	label
acme rebrand	clients/acme/rebrand
northwind summit	events/summit

On a run, an email from yesterday with the subject “Acme rebrand — final artwork” matches the first rule and gets the clients/acme/rebrand label. A thread about “Northwind Summit logistics” gets events/summit. A thread already carrying clients/acme/rebrand is skipped because of the -label: clause, so it is never relabelled.

Trigger it

This should run continuously so labels appear soon after mail arrives:

1. In the Apps Script editor, open Triggers (the clock icon).
2. Click Add Trigger.
3. Choose applyKeywordLabels, event source Time-driven, type Minutes timer, and pick Every 15 minutes.

Watch out for

• Single-word keywords with double meanings (for example Acme on its own) catch unrelated threads. Use multi-word phrases, or add a from: clause in the keyword column to scope the match.
• The search only covers the last day (SEARCH_WINDOW). If a run is missed for longer than that, older mail will not be picked up — widen the window or accept the gap.
• Labels are never created. A typo in the label column means that rule is silently skipped; check the execution log if a project’s mail is not being tagged.
• Labels are added, never removed. If you change a rule, previously labelled threads keep the old label until you clear it by hand.
• Gmail search counts toward daily quota. Many rules running every 15 minutes add up — keep the rule list lean, or run less often if you hit limits.