Build breakdown

From spreadsheet row to emailed PDF: a document pipeline

Summary: A pipeline that turns each ready row of a spreadsheet into a filled Word template, converts it to PDF, files it with a predictable name, and emails it, built to stop a bad row from becoming a document and to avoid sending the same one twice.

A build I made for a posted project brief. I built this as a working sample on made-up data. It is not a delivered engagement, and nothing here is a result from live use.

The problem, as a generic brief

Someone keeps client details in a Google Sheet. They have a Word template with marked spots. They want each new row to produce a finished PDF, saved in a folder, and emailed to the right person, with no copy and paste.

The parts that usually go wrong are blank or malformed cells, long addresses that break the layout, and a trigger that fires twice and sends the same document again.

What I built

A working pipeline that runs end to end on a sample sheet:

  • A 12 row sample sheet (a CSV export stands in for the sheet) with deliberate edge cases: blank optional fields, a comma in a name, currency typed with $ and commas, mixed date formats, a very long address, an accented name, a duplicate row, a row edited after it was sent, a row with no line items, and a row with 30 line items.
  • A one page Word template with a header, a client block, a repeating line item table, a total, optional notes, and a footer.
  • A Python script that validates each row, fills the template, converts it to PDF, files it, writes an email message with the PDF attached, and records everything in a run log.
  • A small test suite for the parsing, validation and duplicate rules.

For the sample, the emails are written out as message files rather than sent, so nothing leaves the machine.

How it works

  1. Read a row and map the column headers to named fields once, so a renamed column is caught early.
  2. Normalize it: trim spaces, parse dates in several formats, strip currency symbols, split the line items.
  3. Validate it: required fields, email shape, ZIP format, amounts. A bad row is marked "needs fix" with the reason, and the rest of the batch carries on.
  4. Check for duplicates with a key built from the client name, document number and date, plus a content hash. Same key and same hash is skipped. Same key with different content is flagged "changed after send" and is not resent without approval.
  5. Fill the Word template, including the repeating table rows and the optional notes block.
  6. Convert to PDF, then check the PDF opens, has pages, and contains the key values.
  7. File it with a name like date_client_document-number.pdf, without overwriting a different document.
  8. Email it with the PDF attached, and only mark the row sent after the message is built.
  9. Log it in an append-only run log. A second run of the same sheet sends nothing new.

Failures are reported with the row number and the reason instead of failing silently.

Stack

Python, docxtpl (Word templates), python-docx, pypdf, Microsoft Word for the PDF conversion in the sample run, Google Sheets as the intended source. For a production build the same stages can swap in the Google Sheets API, a headless office converter on a small server, and the Gmail API. For a Google-only setup, Apps Script is the simpler option.

What I'd do next

  • Connect the real sheet and send test emails to a single inbox until the template and wording are signed off.
  • Add a "Ready" column so only rows marked ready are processed.
  • Add an owner alert email for any failed row.
  • Move conversion to a headless converter so it runs unattended on a server.
  • Test the template with a few real rows before go live.
Figure placeholder

FREE CHECKLIST

Get the 10-minute lead leak check.

Seven quick tests you can run from your phone to see where calls, forms, and reviews are slipping away. Choose your business type and we will also send short tips that fit.