Automate school notice distribution via WhatsApp, email and Excel

This automated n8n workflow distributes school notices to stakeholders (students, parents, and staff) via WhatsApp, email, and other channels. It streamlines the process of scheduling, validating, and sending notices while updating distribution status.

System Architecture

- Notice Distribution Pipeline: - Daily Notice Check - 9 AM: Triggers the workflow daily at 9 AM via Cron. - Read Notices getAll worksheet: Retrieves notice data from a spreadsheet. - Validation Flow: - Validate Notice Data: Validates and formats notice data. - Distribution Flow: - Process Notice Distribution: Prepares notices for multiple channels. - Prepare Email Content: Generates personalized email content. - Send Email Notice: Delivers emails to recipients. - Prepare WhatsApp Content: Formats notices for WhatsApp. - Send WhatsApp Notice: Sends notices via WhatsApp Business API. - Status Update: - Update Notice Status: Updates the distribution status in the spreadsheet.

Implementation Guide

- Import Workflow: Import the JSON file into n8n. - Configure Cron Node: Set to trigger daily at 9 AM (e.g., 0 9 ). - Set Up Credentials: Configure SMTP and WhatsApp Business API credentials. - Prepare Spreadsheet: Create a Google Sheet with noticeid, recipientname, email, phone, noticetext, distributiondate, and status columns. - Test Workflow: Run manually to verify notice distribution and status updates. - Adjust Thresholds: Modify validation rules or content formatting as needed.

Technical Dependencies

- Cron Service: For scheduling the workflow. - Google Sheets API: For reading and updating notice data. - SMTP Service: For email notifications (e.g., Gmail, Outlook). - WhatsApp Business API: For sending WhatsApp messages. - n8n: For workflow automation and integration.

Database & Sheet Structure