Google Worksheet Automation with Alerts

⸻

Project Purpose:

We need a custom Google Sheets solution to help a home care or healthcare-related organization manage and monitor client and employee compliance deadlines. The system must automatically send email alerts when certain due dates approach or are overdue. The workbook will be used to:
• Track client visits and reassessments
• Monitor caregiver documentation and training
• Send automated email alerts twice daily
• Maintain audit readiness and internal compliance

⸻

Scope of Work:
1. Create a Multi-Sheet Google Workbook

Sheet 1: Client Roster

Sheet 2: Employee Compliance Tracker


2. Automate Alerts with Google Apps Script

Script Behavior:
• Script should scan the Client Roster sheet twice daily
• If any client has due/overdue dates (as listed below), send an email

Trigger Configuration:
• Script must run automatically at:
• 6:00 AM CST
• 3:00 PM CST

Logic for Email Alerts:


⸻

Email Format:

A formatted summary listing:
• Client Name
• What’s due (e.g., “Reassessment due in 3 days”)
• The exact due date

⸻

✅ Other Add-Ons:
• Conditional formatting:
• Red = Overdue
• Yellow = Due in 7 days
• Orange = Due in 15 days
• Log Sheet:
A simple sheet that records:
• Date of alert
• Client name
• Type of alert


Ideal Skills and Experience:
- Expertise in Google Workspace, especially Google Sheets
- Experience with setting up automated email alerts (e.g., using Google Apps Script)
- Knowledge of compliance standards (ISO, HIPAA, GDPR)
- Strong attention to detail and organizational skills

Please ensure the worksheet is user-friendly and well-organized.

FULL INSTRUCTIONS PROVIDED IN INSTRUCTIONS