Detect bounced emails and clean your list

Every time Northwind sends its newsletter, a handful of addresses bounce — mailboxes that have been closed, domains that no longer exist, typos that were never caught. The bounce notices land quietly in the inbox and nobody reads them. So the same dead addresses get mailed again next month, the bounce rate creeps up, and eventually the sending reputation takes a hit.

This script closes that loop. It reads the delivery-failure notices that mailer-daemon and postmaster send back, pulls out the address that failed, and marks the matching row on the Subscribers sheet as bounced. The next send simply skips anything flagged that way, so the list cleans itself without anyone trawling the inbox by hand.

What you’ll need

• A Google Sheet with a subscribers list. Row 1 must be a header, and the script expects an email column and an unsubscribed column — the latter is where it writes the bounced flag.
• A Gmail account that receives the bounce notices — that is, the account the newsletter is sent from.
• Nothing else. The script only reads bounce mail and writes one cell per dead address.

The script

// The spreadsheet that holds your subscribers list.
const SUBS_SHEET_ID = '1abcSubsSheetId';

// Subject/body markers that identify a genuine bounce notice. Plenty of
// other mail comes from mailer-daemon, so we confirm against these.
const BOUNCE_PATTERNS = [
  /Mail Delivery Subsystem/i,
  /Delivery Status Notification/i,
  /Undelivered Mail Returned/i,
];

// Only look at recent bounces — older notices have already been handled.
const SEARCH_WINDOW = 'newer_than:7d';

/**
 * Reads recent bounce notices, then marks each failed address as
 * "bounced" in the unsubscribed column of the Subscribers sheet.
 */
function flagBouncedAddresses() {
  const sheet = SpreadsheetApp.openById(SUBS_SHEET_ID).getSheets()[0];

  // 1. Read the whole sheet and split the header from the data rows.
  const values = sheet.getDataRange().getValues();
  const [header, ...rows] = values;
  if (!rows.length) {
    Logger.log('Subscribers sheet is empty — nothing to do.');
    return;
  }

  // 2. Map header names to column indexes so the code is order-independent.
  const col = Object.fromEntries(header.map((h, i) => [h, i]));

  // 3. Gather every address that bounced in the search window.
  const bounced = collectBouncedAddresses();
  if (bounced.size === 0) {
    Logger.log('No new bounces found.');
    return;
  }

  // 4. Flag each matching row. We mutate the in-memory values array and
  //    write it back once, rather than touching the sheet per row.
  let flagged = 0;
  rows.forEach((r, i) => {
    if (bounced.has(r[col.email])) {
      values[i + 1][col.unsubscribed] = 'bounced';
      flagged++;
    }
  });

  // 5. Push the updated values back to the sheet in a single write.
  sheet.getDataRange().setValues(values);
  Logger.log('Flagged ' + flagged + ' bounced address(es).');
}

/**
 * Searches the inbox for delivery-failure notices and returns a Set of
 * the email addresses that failed, lower-cased for reliable matching.
 */
function collectBouncedAddresses() {
  const out = new Set();

  // Bounces come back from mailer-daemon or postmaster.
  const threads = GmailApp.search(
    'from:mailer-daemon OR from:postmaster ' + SEARCH_WINDOW
  );

  for (const thread of threads) {
    const body = thread.getMessages()[0].getPlainBody();

    // Confirm this is a real bounce, not just any automated mail.
    if (!BOUNCE_PATTERNS.some((re) => re.test(body))) continue;

    // Pull the failed address out of the "Final-Recipient" or "To:" line.
    const m = body.match(
      /(?:final recipient|to)[:\s]+([\w.+-]+@[\w.-]+\.\w+)/i
    );
    if (m) out.add(m[1].toLowerCase());
  }
  return out;
}

How it works

1. flagBouncedAddresses opens the subscribers spreadsheet and reads the whole range in one call, splitting the header row from the data rows.
2. If there are no data rows it logs a message and stops — there is nothing to clean.
3. It builds a col lookup from the header so it works whether email is in column A or column F. As long as the header names match, the column order does not matter.
4. collectBouncedAddresses searches Gmail for mail from mailer-daemon or postmaster in the last seven days, confirms each one is a genuine bounce against BOUNCE_PATTERNS, and extracts the failed address with a regex. Addresses are lower-cased and stored in a Set so duplicates collapse.
5. Back in the main function, every row whose email is in that set has its unsubscribed cell set to bounced. The changes are made in memory.
6. The updated array is written back to the sheet in a single setValues call, which is far faster than editing one cell at a time.

Example run

Say three sends bounced overnight. The inbox holds notices like:

From: Mail Delivery Subsystem <[email protected]>
Subject: Delivery Status Notification (Failure)

  Final-Recipient: rfc822; [email protected]
  Action: failed

Before the run, the Subscribers sheet looks like this:

email	name	unsubscribed
[email protected]	Jordan Lee
[email protected]	Sam Cole
[email protected]	Pat Ng

After the run, the bounced rows are flagged and the next send skips them:

email	name	unsubscribed
[email protected]	Jordan Lee	bounced
[email protected]	Sam Cole
[email protected]	Pat Ng	bounced

Trigger it

Run this on a daily time-driven trigger, scheduled to fire after your newsletter sends finish so the bounces have had time to arrive:

1. In the Apps Script editor, open Triggers (the clock icon).
2. Click Add trigger.
3. Choose function flagBouncedAddresses, event source Time-driven, and a Day timer for an hour after your usual send time.

Watch out for

• Bounce formats vary. Most providers include a Final-Recipient or To: line, but some do not. If addresses are slipping through, log the raw body of a missed notice and widen the regex to match its layout.
• Soft bounces are not permanent. A full mailbox or a temporary server problem can trigger a bounce notice even though the address still works. If that matters, only flag after the same address bounces twice.
• The search window is seven days. If the script does not run for over a week, older bounces will be missed — widen SEARCH_WINDOW or keep the trigger reliable.
• It overwrites the unsubscribed cell. If a subscriber has genuinely unsubscribed and later bounces, the value flips to bounced. Use a separate column if you need to keep the two states apart.
• setValues rewrites the whole sheet. On a very large list that is still one call, but any formulas or formatting outside the data range are untouched — only cell values are written.