Project milestone workflow that watches Google Sheets milestone rows, calculates completion percentage from task counts, enriches records, routes Concours and standard projects through separate HTML email builders, merges branches, sends Gmail project update emails to customers, posts live progress summaries to the lead mechanic's Slack channel, syncs public milestone data to Customer Portal Airtable, logs successful executions to an audit sheet, and sends Gmail failure alerts to the Project Manager or workflow error alerts on pipeline failures.
n8n · workflow canvasproduction build
Context
A service workflow needed to keep customers updated on project milestones with tier-appropriate email tone, give the lead mechanic real-time progress summaries, maintain a public Customer Portal record, and preserve an audit trail of all milestone updates.
Approach
Built a two-part n8n workflow system with a Google Sheets milestone watch trigger and a global workflow error catch. The main flow calculates project completion percentage and enriches the record, branches on Concours vs standard to build tailored HTML emails, merges the branches, sends Gmail project update emails, and on success posts a Slack shop floor summary, syncs milestone data to Airtable Customer Portal, and logs the execution; on Gmail failure it notifies the Project Manager, and the global error trigger sends Gmail workflow error alerts.
Architecture
Workflow 1
Google Sheets: Watch Project Milestones
Code: Calculate % Complete & Enrich
If: Concours or Standard?
Concours: Code: Build Concours Restoration Email HTML
Standard
Code: Build Standard Email HTML
Merge Email Branches
Gmail: Send Project Update Email
Success: Slack: Shop Floor Summary + Airtable: Customer Portal Record + Google Sheets: Log Successful Execution
