A governed email-automation pipeline: from spreadsheet to sending

Most email automation starts life as a spreadsheet and a prayer: a column of names, a column of bodies, and someone pressing a button hoping nothing goes wrong twice. It usually works, right up until the sheet is re-run after a crash and half the list gets a second copy of the same email. The fix is not to abandon the spreadsheet; for a small operations team it is often the right interface. The fix is to wrap it in a pipeline that treats sending as a governed process rather than a one-off script.

This guide describes that pipeline: reading contacts and content from a controlled sheet, generating or selecting the message, sending through an authenticated channel, marking status back immediately, and tracking delivery so failures are visible rather than silent. Every piece exists to answer one question with certainty: did this specific person receive this specific email, once.

Use the spreadsheet as a queue, not a log

The mental model that prevents most bugs is to treat the sheet as a work queue rather than a historical record. A queue has a clear notion of pending versus done, and the pipeline’s only job on each pass is to find the pending rows, process them, and flip them to done. Read access needs only the columns required to build the message; write access is a single status column, kept as narrow and predictable as possible.

from google.oauth2.service_account import Credentials
from googleapiclient.discovery import build

SCOPES = ["https://www.googleapis.com/auth/spreadsheets"]

def sheets_client(creds_file):
    creds = Credentials.from_service_account_file(creds_file, scopes=SCOPES)
    return build("sheets", "v4", credentials=creds).spreadsheets()

def read_pending(svc, spreadsheet_id, sheet_range):
    rows = svc.values().get(
        spreadsheetId=spreadsheet_id, range=sheet_range
    ).execute().get("values", [])
    header, body = rows[0], rows[1:]
    idx = {name: i for i, name in enumerate(header)}
    pending = []
    for i, row in enumerate(body, start=2):        # sheet rows are 1-indexed + header
        status = row[idx["status"]] if len(row) > idx["status"] else ""
        if status != "done" and row[idx.get("email", 0)]:
            pending.append({"row": i, "email": row[idx["email"]],
                            "firstname": row[idx.get("firstname", 0)]})
    return pending

Reading the header row into an index map, rather than hard-coding column letters, means the sheet can be reorganised without breaking the script. That small habit saves a surprising number of production incidents.

Validate before you ever build a message

Every row that reaches the send step should have already passed a validation gate. Reject malformed emails outright, and treat an empty required field as a skip rather than a guess. A pipeline that silently invents a greeting for a missing first name, or sends to a string that is not really an email address, erodes trust in the whole system the first time someone notices.

import re

EMAIL_RE = re.compile(r"^[^@\s]+@[^@\s]+\.[^@\s]+$")

def valid(row):
    if not EMAIL_RE.match(row["email"]):
        return False, "invalid email"
    if not row.get("firstname"):
        return False, "missing firstname"
    return True, ""

Log the rejection reason alongside the row rather than discarding it silently. A weekly glance at the rejection log is often how you discover that a whole batch of imports has a systematic formatting problem, long before it becomes a support ticket.

Send through one authenticated function, always

Every message, regardless of what generated its content, should pass through a single send function. Centralising this is what makes rate limiting, retries and authentication consistent, and it is the one place you need to audit when a deliverability problem appears.

import time

def send_email(mailer, to, subject, html_body, max_retries=3):
    for attempt in range(max_retries):
        try:
            return mailer.send(to=to, subject=subject, html=html_body)
        except mailer.TransientError:
            time.sleep(2 ** attempt)
        except mailer.PermanentError as e:
            return {"error": str(e), "permanent": True}
    return {"error": "max retries exceeded", "permanent": False}

Distinguishing transient from permanent failures matters. A transient error, such as a momentary provider timeout, deserves a retry. A permanent error, such as a hard bounce on a malformed address, should stop retrying immediately and be recorded so the address is never attempted again.

Mark the sheet the instant a send resolves

The single habit that prevents duplicate sends is writing the status back to the sheet immediately after each send attempt resolves, not in a batch at the end of the run. If the process crashes midway, everything already marked stays marked, and a re-run only touches what genuinely remains pending.

def mark_status(svc, spreadsheet_id, sheet_name, row_number, status):
    svc.values().update(
        spreadsheetId=spreadsheet_id,
        range=f"{sheet_name}!F{row_number}",     # status column
        valueInputOption="RAW",
        body={"values": [[status]]},
    ).execute()

def run(svc, mailer, spreadsheet_id, sheet_range, sheet_name, render):
    for row in read_pending(svc, spreadsheet_id, sheet_range):
        ok, reason = valid(row)
        if not ok:
            mark_status(svc, spreadsheet_id, sheet_name, row["row"], f"skip:{reason}")
            continue
        subject, html = render(row)
        result = send_email(mailer, row["email"], subject, html)
        status = "done" if "error" not in result else f"failed:{result['error'][:40]}"
        mark_status(svc, spreadsheet_id, sheet_name, row["row"], status)

Writing one cell per row is a small amount of API traffic for a large amount of safety. The alternative, batching status writes until the end, is exactly the pattern that produces duplicate sends when a run does not finish cleanly.

Track delivery, not just dispatch

Sending is not the same as delivering, and a pipeline that only measures whether the API call succeeded is blind to bounces, spam placement and complaints that arrive later through webhooks or provider reports. Ingest those signals into the same status column vocabulary, so a single glance at the sheet tells the full story of a send: attempted, delivered, bounced or complained. This is also where a suppression list is built and enforced going forward, so an address that bounced hard is never retried by a future campaign.

def apply_delivery_event(svc, spreadsheet_id, sheet_name, email_to_row, event):
    row = email_to_row.get(event["email"])
    if not row:
        return
    kind = event["type"]                 # delivered | bounced | complained
    mark_status(svc, spreadsheet_id, sheet_name, row, kind)
    if kind in ("bounced", "complained"):
        add_to_suppression(event["email"])

Govern the pipeline, not just the send

Governance is what separates a pipeline you can defend from one that quietly becomes a liability. Keep a per-run cap so a bug cannot exhaust the whole list in one pass. Record who triggered each run and when, so any question about a specific send has an answer. Respect an explicit consent or legal-basis field on every row, and treat its absence as a reason to skip rather than a detail to assume. None of this is exciting engineering, and all of it is what makes the difference between an automation your legal team is comfortable with and one that becomes a problem the first time someone asks where a message came from.

Why a queue beats a script that “just sends”

It is worth being explicit about why this extra structure earns its keep, because on a small list it can feel like overhead. The moment a list grows past a few dozen rows, or a run starts taking long enough that a network blip or a laptop sleeping mid-send becomes a real possibility, a script that fires sequentially with no state tracking turns every interruption into a guessing game. Did row forty-three send before the crash? Nobody knows, and the two safe options are both bad: re-run everything and risk duplicates, or manually inspect the provider’s sent log and reconstruct what happened by hand.

A queue with per-row status removes the guessing entirely. The next run simply asks the sheet what remains pending, and the answer is always correct because it was written at the moment truth was known, not reconstructed afterward from a separate log. This is the same principle that makes database transactions trustworthy: write the state change as close as possible to the event it represents, and recovery from any failure becomes mechanical rather than forensic.

Handling content that needs more than a template

Some campaigns need more than variable substitution into a fixed template; a genuinely personalised body, generated per recipient from a small set of facts about them. The render function slots into the same pipeline without disturbing anything else, because the queue, validation, sending and status-marking logic are all indifferent to how the subject and body were produced.

def render_from_facts(model, row):
    prompt = (
        "Write a two-sentence email opener for a business contact, "
        "professional tone, referencing their company by name.\n"
        f"Name: {row['firstname']}\nCompany: {row.get('company', '')}"
    )
    opener = model.complete(prompt).strip()
    subject = f"A quick note for {row.get('company', 'your team')}"
    body = BASE_TEMPLATE.format(opener=opener, firstname=row["firstname"])
    return subject, body

Keeping generation as a small, swappable function is what lets the same governed pipeline serve a static newsletter one week and an AI-personalised outreach campaign the next, without touching any of the parts that keep sending safe.

Where responsibility actually sits

It is worth naming, plainly, who owns what once a pipeline like this exists, because automation has a way of diffusing accountability until nobody quite remembers who approved a given send. The person who edits the sheet owns the content and the audience; they decide what gets said and to whom, and that decision should be visible and reviewable before a run begins, not discovered afterward in an inbox complaint. The pipeline owns mechanics: it guarantees that whatever content and audience were approved get delivered once, tracked honestly, and never duplicated regardless of crashes, retries or overlapping runs. Keeping that boundary clear is what lets a marketing or operations team trust an engineering-built system without needing to understand its internals, and it is what lets engineering build confidently without being asked to make judgement calls about messaging that were never theirs to make. A pipeline that blurs this line, by embedding content decisions in code or by letting a script silently override what a human approved, is where trust in automation usually breaks down, often long after the original author has moved on and nobody left can explain why a particular message was sent to a particular list.

Testing the pipeline before it ever touches a real list

The safest way to gain confidence in a pipeline like this is to run it, in full, against a small list of addresses you control, before it ever touches a real audience. Seed a test sheet with a handful of your own inboxes, some deliberately malformed to confirm validation rejects them correctly, and run the entire flow end to end: read, validate, send, mark, and simulate a mid-run crash by killing the process partway through, then run it again to confirm nothing duplicates and nothing pending is skipped. That single rehearsal, repeated whenever the pipeline changes meaningfully, catches the overwhelming majority of the failure modes that would otherwise surface for the first time against a real audience, which is precisely the moment nobody wants to discover them.

Key takeaways

1. Treat the spreadsheet as a queue: pending versus done, nothing else.
2. Validate every row before it reaches the send step, and log rejections with a reason.
3. Route every message through one authenticated send function with retry logic that distinguishes transient from permanent failures.
4. Mark status immediately per row, never in an end-of-run batch, so a crash never causes a duplicate.
5. Ingest delivery events, not just dispatch results, and build a suppression list from bounces and complaints.
6. Cap volume per run, log who triggered it, and require an explicit consent field on every row.

A spreadsheet is a perfectly good interface for a small operations team. What makes it dangerous is treating a send as fire-and-forget. Wrap it in a pipeline that queues, validates, marks and tracks, and the same simple sheet becomes something you can trust at scale.