Audit student grades and generate PDF report cards with Google Sheets and Slack

Quick overview

Youtube Video: https://youtu.be/Gk9IuNJBgnc

This workflow runs every 6 hours to audit student marks from Google Sheets against historical records in PostgreSQL, flag anomalies to Slack, and generate per-student PDF report cards via pdfendpoint.com before saving them to Google Drive and posting a generation summary to Slack.

How it works

1. Runs every 6 hours on a schedule.
2. Reads the latest teacher-submitted marks from a Google Sheets worksheet and normalizes fields like student ID, term, marks, max marks, and attendance.
3. Loads student mark history from PostgreSQL and merges it with the current submissions to detect missing marks or absences.
4. Calculates percentages and grades, checks for invalid mark ranges and sharp drops versus historical averages, and logs incomplete/absent records as anomalies in PostgreSQL.
5. For records with invalid mark totals, posts a Slack alert to the admin with the student, subject, and mismatch reason.
6. For valid records, builds an HTML report card per student/term, converts it to PDF using pdfendpoint.com, and uploads the PDF to a Google Drive folder in batches.
7. Posts a Slack digest summarizing all newly generated report cards with Google Drive links.

Setup

1. Create and connect Google credentials (Service Account) with access to the source Google Sheets file, and confirm the document ID and “Teacher Submissions” sheet selection are correct.
2. Create and connect a PostgreSQL credential, and ensure the tables student_mark_history and mark_audit_anomalies exist with columns used in the workflow.
3. Add a Slack OAuth credential and set the target channel (for both audit alerts and the generation summary).
4. Add an HTTP Bearer credential for pdfendpoint.com and ensure the convert endpoint is reachable from your n8n instance.
5. Connect Google Drive OAuth credentials and select the destination folder where generated PDFs should be uploaded.