Route saved email attachments to project folders

Northwind’s inbox is the front door for a lot of project work. Clients send contracts, suppliers send invoices, photographers send proofs — and every one of those attachments needs to land in the right Drive folder, not in the “download then forget” pile. Doing it by hand is the kind of small chore that slips on a Friday and becomes a search-the-inbox archaeology dig on Monday.

This script reads a Routes sheet that maps subject keywords to folder IDs, scans the last day of mail for attachments, drops each attachment into the matching folder, and labels the thread so it never gets routed twice. It runs quietly on a clock trigger, so the filing happens whether anyone is paying attention or not.

What you’ll need

• A Google Sheet called Routes with two columns: keyword and folderId. One row per project — for example acme | 1abcAcmeFolderId.
• The Drive folders themselves, with the script account holding edit access.
• Nothing extra to install. The script creates the attachments/routed Gmail label the first time it runs.

The script

// The sheet that maps subject keywords to Drive folder IDs.
const ROUTES_SHEET_ID = '1abcRoutesId';

// Gmail search window. Keep it small — the trigger runs often, and a
// shorter window means fewer threads to scan each pass.
const SEARCH_QUERY = 'has:attachment newer_than:1d -label:attachments/routed';

// Label applied to every thread we file, so it never gets routed twice.
const ROUTED_LABEL = 'attachments/routed';

/**
 * Scans recent mail for attachments, files them into the folder whose
 * keyword matches the subject, and labels the thread as routed.
 */
function routeAttachments() {
  const routes = readSheet(ROUTES_SHEET_ID);
  if (!routes.length) {
    Logger.log('Routes sheet is empty — nothing to match against.');
    return;
  }

  const threads = GmailApp.search(SEARCH_QUERY);
  if (!threads.length) {
    Logger.log('No new threads with attachments.');
    return;
  }

  // Create the label on first run, then reuse it on every pass.
  const routed = GmailApp.getUserLabelByName(ROUTED_LABEL)
    || GmailApp.createLabel(ROUTED_LABEL);

  let filed = 0;
  for (const t of threads) {
    const subject = t.getFirstMessageSubject().toLowerCase();

    // First matching route wins — keep the most specific keywords at the
    // top of the sheet (e.g. "acme-contract" above "acme").
    const route = routes.find((r) => r.keyword
      && subject.includes(String(r.keyword).toLowerCase()));
    if (!route) continue;

    const folder = DriveApp.getFolderById(route.folderId);
    for (const msg of t.getMessages()) {
      for (const att of msg.getAttachments()) {
        // Skip Gmail's inline image attachments — they are usually
        // signatures, not real files.
        if (att.getSize() < 1024 && /^image\//.test(att.getContentType())) continue;
        folder.createFile(att);
        filed++;
      }
    }
    t.addLabel(routed);
  }
  Logger.log('Filed ' + filed + ' attachment(s) across ' + threads.length + ' thread(s).');
}

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

How it works

1. routeAttachments reads the Routes sheet into a list of { keyword, folderId } objects. If the sheet is empty there is nothing to match against, so the script logs and stops.
2. It searches Gmail for threads from the last day that have an attachment and are not already labelled attachments/routed. The negative label clause is what stops the script re-filing the same message every hour.
3. It looks up — or creates on first run — the attachments/routed label.
4. For each thread, it lower-cases the subject and finds the first route whose keyword appears in the subject. The order of the sheet matters: more specific keywords should sit above broader ones.
5. For each message in the matched thread, it copies every attachment into the route’s folder using folder.createFile(att). Tiny inline images are skipped so signature logos do not pile up in the project folder.
6. Once all attachments are filed, the thread gets the attachments/routed label and falls out of the next search.

Example run

Suppose the Routes sheet looks like this:

keyword	folderId
acme-contract	1abcAcmeContractsId
acme	1abcAcmeId
smith wedding	1abcSmithWeddingId

Two new threads arrive overnight:

• Subject: “Acme contract — signed copy” with contract.pdf attached.
• Subject: “Smith wedding — final proofs” with proofs.zip attached.

After the next trigger fires, contract.pdf lands in the Acme contracts folder (the more specific keyword wins), proofs.zip lands in the Smith wedding folder, both threads get the attachments/routed label, and the log reads Filed 2 attachment(s) across 2 thread(s).

Trigger it

This is a background job — set it on a clock trigger so the filing happens without anyone thinking about it.

1. In the Apps Script editor, open Triggers (the clock icon).
2. Add Trigger for routeAttachments, event source Time-driven, type Hour timer, interval Every hour.
3. Approve the Gmail and Drive scopes the first time it runs.

Every hour matches the newer_than:1d window with comfortable overlap — even if a run fails, the next one will pick up the same threads because they still will not carry the routed label.

Watch out for

• Keyword order matters. Array.find returns the first match, so put narrower keywords (acme-contract) above broader ones (acme) in the sheet.
• The newer_than:1d window is a safety net, not a guarantee. If the trigger is paused for more than a day, older threads slip past. Widen the window to newer_than:7d when you re-enable it, then narrow it again.
• Large attachments count against Gmail and Drive quotas. A single run that files dozens of multi-megabyte files will eat into the daily UrlFetch and Drive write budgets — keep the search window tight.
• Subjects are not unique. If two clients have similar project names, a single keyword may match both. Use more specific keywords (acme-2025-contract) rather than relying on the sender, which this script intentionally ignores.
• The script does not de-duplicate files. If the same attachment arrives twice in separate threads, you will get two copies in the folder. Add a name check against folder.getFilesByName(att.getName()) if that matters.