Academic Progress Report Generation and Delivery - n8n Workflow

Educational administrators and school IT staff needing reliable, scheduled reporting.
Users requiring advanced data processing and custom HTML generation within an n8n workflow.
Organizations looking to deploy efficient, customized n8n templates for institutional communications.
Technical users who need to automate complex sequences combining API calls, custom logic, and Gmail automation.

This powerful n8n workflow eliminates the extensive manual effort involved in compiling and distributing weekly student academic progress reports. Designed as a robust solution, this specific set of n8n templates ensures timely, data-driven communication between educational institutions and parents.

The automation leverages the flexibility of the n8n platform to perform several critical functions: it starts with a scheduled n8n trigger, securely accesses a list of students via the Google Sheets n8n node, queries a proprietary Learning Management System (LMS) API for performance data, and uses custom code to calculate key metrics, including overall grades, assignment completion rates, and performance trends. Finally, it uses the Gmail n8n node to deliver a personalized, HTML-formatted report directly to the parent's email, while logging the delivery status to a separate Google Sheet. This comprehensive n8n solution ensures high accuracy and consistency in reporting.

The entire process is initiated by the Weekly Schedule Trigger n8n node, set to run every Monday at 9 AM.

1. Data Retrieval: The first n8n node, 'Get Students List', connects to Google Sheets to retrieve the entire roster of students, including their unique IDs and parent email addresses.


2. Batch Processing: The list is then routed through the 'Split Students for Processing' n8n node, which handles the items in batches of five to prevent overwhelming the downstream API and email services.


3. LMS Integration: For each student, the 'Fetch LMS Academic Data' n8n node sends an authenticated HTTP Request to the external LMS API, pulling the current week's grades, assignments, and attendance data based on the student's ID.


4. Metric Calculation: The 'Process Academic Data' Code n8n node executes complex JavaScript logic. It calculates the overall average grade, assignment completion percentage, and analyzes recent grades to determine if the student's performance trend is 'Improving', 'Declining', or 'Stable'. This customized n8n node aggregates all necessary report data.


5. Report Formatting: The 'Generate HTML Report' Code n8n node dynamically injects the processed data into a detailed, styled HTML template, creating the final email content and setting a personalized subject line. This is a crucial step in this n8n workflow.


6. Distribution: The 'Send Email to Parents' Gmail n8n node sends the fully formatted HTML report to the corresponding parent's email address.


7. Logging: After successful delivery, the 'Log Report Delivery' Google Sheets n8n node appends a record of the transaction (including timestamp and overall grade) to a designated log sheet.


8. Completion Notification: Once all student reports have been processed by the n8n workflow, the final 'Send Admin Summary' Gmail n8n node sends a notification to the administrator, confirming the batch completion.

To deploy this specialized n8n workflow using these n8n templates, follow these steps:

1. Import: Import the provided n8n workflow JSON into your n8n instance.


2. Google Sheets Credentials: Configure or select your Google Sheets (Google API) credentials. Ensure the service account has read/write access to both your Student Database sheet and your Report Logs sheet.


3. Sheet IDs: Update the 'Get Students List' and 'Log Report Delivery' n8n node configurations to replace the placeholder IDs (YOURSTUDENTSHEETID and YOURLOGSHEETID) with your actual Google Sheet document IDs.


4. LMS Credentials: Create a new custom Credential for the 'Fetch LMS Academic Data' n8n node, providing the base URL and an API token for your Learning Management System (LMS).


5. Gmail Credentials: Set up or select your Gmail OAuth2 credentials for the 'Send Email to Parents' and 'Send Admin Summary' n8n node blocks.


6. Activate: Save the n8n workflow and set the main switch to 'Active'. The n8n trigger will handle subsequent executions.

This n8n workflow utilizes several crucial n8n node types to achieve complex automation:

Weekly Schedule Trigger (n8n trigger):
Function: Starts the n8n workflow automatically.
Key Configuration: Runs weekly, specifically every Monday at 9 AM.
Get Students List (Google Sheets n8n node):
Function: Retrieves all student data from the 'Students' worksheet, providing the initial data payload.
Key Configuration: Operation set to 'Read', using service account authentication.
Split Students for Processing (Split In Batches n8n node):
Function: Ensures the subsequent LMS API calls and emails are managed efficiently.
Key Configuration: Batch Size set to 5.
Fetch LMS Academic Data (HTTP Request n8n node):
Function: Retrieves the specific academic performance data for each student from the external LMS.
Key Configuration: Dynamically constructs the URL using expressions like api/students/{{ $json.studentid }}/grades. Requires Authorization header with a Bearer Token.
Process Academic Data (Code n8n node):
Function: Performs the core data processing, calculating averages, completion rates, and identifying performance trends. This custom n8n node centralizes the business logic.
Key Configuration: Custom JavaScript logic calculates fields like overallgrade, performancetrend, and subjectsneedingattention.
Generate HTML Report (Code n8n node):
Function: Creates a visually appealing, professional HTML body for the email using standard HTML/CSS templates, utilizing the calculated data.
Key Configuration: Injects dynamic data points into a styled HTML template.
Send Email to Parents (Gmail n8n node):
Function: Delivers the formatted HTML report to the parent's email address.
Key Configuration: Uses the email address from the input data ({{ $json.parentemail }}) and sends the HTML content as the message body.
Log Report Delivery (Google Sheets n8n node):
Function: Logs the outcome of the reporting process for auditing and compliance.
Key Configuration: Operation set to 'Append'. Maps fields like timestamp, studentname, and overallgrade to the 'Delivery_Log' sheet.