Send weekly parent progress reports with Google Sheets, GPT-4o mini and Gmail

Quick overview This workflow runs every Friday at 18:00 UTC to read student performance data from Google Sheets, generate a personalised parent narrative with OpenAI, render it as a PDF via PDFShift, and send updates through Gmail and Telegram, while logging sends back to Google Sheets and alerting errors in Slack.

How it works Runs on a weekly schedule (Friday 18:00 UTC) and pulls all student rows from a Google Sheets “Students” worksheet. Calculates attendance, average test score, and homework completion percentages and flags students that fall below the configured thresholds. Uses OpenAI (gpt-4o-mini) to write a warm, parent-friendly narrative report using each student’s metrics and any red flags. Injects the narrative into an HTML template and converts it into a PDF attachment using the PDFShift API. Sends the PDF report to each parent via Gmail and posts a brief performance summary to the parent via Telegram. If a student meets the urgent criteria, sends an additional high-priority Telegram alert listing the specific concerns. Appends an audit log row to a Google Sheets “Send Logs” worksheet for each student and sends a consolidated Telegram summary of urgent students to the teacher. If the workflow errors, posts an alert message to a Slack channel via the workflow error trigger.

Setup Connect Google Sheets OAuth credentials and set your spreadsheet ID and worksheet names for the “Students” read and “Send Logs” append actions. Add an OpenAI API key credential (or OpenAI connection) for generating the parent narrative. Provide a PDFShift API key via an n8n variable/credential and ensure the HTTP request Authorization header references it. Connect a Gmail OAuth2 credential and confirm the parent email field in your sheet maps to the recipient address. Add a Telegram Bot credential and ensure your sheet contains the correct parent and teacher Telegram chat IDs. Connect Slack OAuth2 credentials and select the channel that should receive workflow error alerts.