Automated certificates + instant reporting for an EdTech startup

Context

Internal ops for a growing course platform: completions, certificates, and follow-up reporting.

• 5–10 person team
• Google Drive + Sheets + Gmail
• Multiple "source sheets" across folders

High-volume output

High-volume runs that needed to be accurate and repeatable.

• ~1,500 certificates generated
• .docx + .pdf outputs per employee
• One-click email delivery from the company mailbox

Before → after

From manual merges and follow-ups to repeatable workflows.

• Hours/days of admin work → minutes per batch
• Fewer missed/incorrect emails
• Consistent formatting and easy review later

Scattered, inconsistent data

Course completion data lived in multiple Sheets across Drive folders. Email addresses and details (title/role, company name variations, module dates) were maintained in separate places.

• Multiple sources of truth (rosters, completions, exports)
• Duplicate names and inconsistent formatting
• High risk of "wrong person, wrong attachment"

Non-negotiables

The system had to work with what the team already used day-to-day.

• Keep Google Drive/Sheets as the source of truth
• Produce branded .docx and .pdf certificates
• One-click sending from the company email address
• Clear logs + safe re-runs (no double-sending)

Certificate generator

Generate personalized certificates from a .docx template using clean, consistent inputs.

• Pulls employee name, module, completion date, role/title, and company details
• Standardizes dates and names (handles accents and capitalization) and checks required fields
• Adds a unique certificate ID so each one can be tracked later

PDF export + mailer

Export certificates to PDF and send each one to the right recipient from the company mailbox.

• Sends in controlled batches and tries again automatically if something fails
• Test run option for review before sending
• Per-recipient log row: status, timestamp, and output file links

Forms → report (Apps Script)

On each submission, generate a personalized report with charts and email it within minutes.

• Starts automatically when someone submits a form (not on a timer)
• Builds visuals (charts/graphs) from submitted data + reference tables
• Exports a branded PDF report and emails it to the submitter automatically

Certificates (one-click batch)

1. Quick checks: find required Sheets and verify access to Drive folders.
2. Import the participant list and completion records; check for missing emails and required details.
3. Resolve template variables and generate .docx files into a working folder.
4. Export PDFs and write a per-employee status log (generated/sent/skipped).
5. Send emails with the right attachment; skip already-sent rows unless explicitly re-sent.

Reporting (runs on submission)

1. On submission, capture the details and check basic inputs.
2. Compute metrics and generate charts (and any required reference lookups).
3. Fill a report template and export to PDF.
4. Email the report to the submitter and write a simple record for support and troubleshooting.

Practical integration

Designed around existing Drive structure and templates rather than requiring a re-organization project.

• Configurable "source map" for Sheets located in different folders
• Template variables documented for easy updates
• Files stored in predictable Drive folders per batch

Operate & support

Clear logs, easy troubleshooting, and a clean path for future changes (new fields, new report variants).

• One-page guide + FAQs for common exceptions
• Simple preview step before sending
• Optional follow-up support as volume increased