Log and report construction punch list items with OpenAI, Google Sheets, Gmail and Telegram

Quick Overview This workflow accepts construction punch list messages via a webhook, uses OpenAI plus a Google Sheets trade roster to structure and route each item, logs it to Google Sheets, emails the responsible subcontractor via Gmail, and sends confirmations and weekly status updates through Telegram.

How it works Receives a POST request via a webhook containing a punch list message, sender, and Telegram chat ID. Validates the message text and, if missing, sends usage instructions back to the sender via Telegram. Uses OpenAI to extract location, trade, severity, and description, and looks up the responsible company and email from a Google Sheets TradeRoster. Validates the parsed result and, if key fields are missing, replies in Telegram asking for a clearer message and a roster-mapped trade. Appends the punch item to a Google Sheets PunchLog, emails the assigned subcontractor via Gmail, and confirms logging back to the sender on Telegram. If the severity is Critical or High, sends an alert to the project manager via Telegram. Every Monday at 09:00, pulls open items from PunchLog, generates a concise weekly report with OpenAI, and sends it via Gmail and Telegram.

Setup Create a Google Sheets spreadsheet with two tabs named PunchLog and TradeRoster, and populate TradeRoster with columns for trade, company name, and contact email. Add credentials for OpenAI, Google Sheets (OAuth2), Gmail (OAuth2), and Telegram Bot API in n8n. Update the Google Sheets document ID and sheet names if your spreadsheet differs from PunchLog/TradeRoster, and ensure PunchLog has columns matching the fields the workflow appends (punch_id, received_at, sender, location, trade, severity, description, assigned_company, status). Copy the webhook URL for the punch intake endpoint and configure your message source (for example, a Telegram bot relay) to POST the punch text, chat_id, and sender to it. Set the project manager Telegram chat ID for high-severity alerts and the weekly report email recipient address to your preferred targets.