Automated MVA Enrichment Certificate Bookkeeping and Invoicing

Description and use

The automation eliminates manual data entry for certificates and invoice generation. It reduces errors by using OCR and programmatic logic for FIFO matching. It provides a single source of truth in Google Sheets and allows the tutor to focus on reviewing drafts rather than creating them.

Problem

Lamp Post Tutoring manually processes Mission Vista Academy (MVA) enrichment certificates by reading PDFs from emails and typing data into Google Sheets, leading to slow workflows, typos in dates and amounts, inconsistent invoice numbering, and manual invoice generation.

Outcome

The automation eliminates manual data entry for certificates and invoice generation. It reduces errors by using OCR and programmatic logic for FIFO matching. It provides a single source of truth in Google Sheets and allows the tutor to focus on reviewing drafts rather than creating them.

Workflow

• Poll Gmail for emails from MVA containing certificate PDFs after a configured go-live date.
• Extract PDF attachments and convert them to text using Google Drive's OCR capabilities.
• Parse the OCR text to extract Student ID, Certificate Number, Service Date, and Total Amount.
• Validate the extracted data; flag unreadable amounts or unknown Student IDs for manual review.
• Write validated certificate data as 'Certificate' rows to the corresponding Student Ledger tab in Google Sheets.
• Recompute the Session Pool for each student by summing 'Session' rows (positive) and 'Certificate' rows (negative).
• Apply strict FIFO logic to match available session credits against uninvoiced certificates.
• Generate an invoice PDF from a Google Doc template for each batch of covered certificates.
• Save the invoice PDF to a designated Drive folder.
• Create a Gmail draft addressed to the client with the invoice attached.
• Write an 'Invoice' row to the Student Ledger with status 'Draft'.
• Send a combined digest email to the tutor summarizing certificates entered and invoices drafted.
• Tutor reviews Gmail drafts, sends invoices, and manually updates invoice status to 'Sent' or 'Paid' in the Sheet.

Evidence

The tutor manually reads each one and types it into a Google Sheet... typo'd dates, blank dates, inconsistent invoice numbers

Source

A Google Apps Script... polls Gmail for MVA certificate emails, OCRs each attached Certificate PDF... generates an invoice PDF from a Doc template

Source