Fix and Update n8n AI Automation for the COMMS department

Core Workflow Requirements
1. Lead Intake & Email Automation

When Comms receives a new lead via email:

The system should:

• Extract subject and brief details
• Identify department owner and team members mentioned
• Pull email addresses automatically from Google Directory
• Upload any attachments to Google Drive
• Generate a shareable Drive link
• Send email to all mentioned members
• CC Comms on all outgoing emails
• Set a Google Calendar reminder with delivery date & time

The process must work dynamically (no manual copy-paste).

2. Google Sheet Automation (Master Comms Tracker)

The Comms master Google Sheet must automatically update with:

• Project name
• Owner
• Assigned team members
• Activity description
• Date & time
• Delivery deadline
• Status
• Any additional required fields

If required fields are missing, the system should:
• Prompt the user (via form/chat/email)
• Collect missing details
• Update the sheet dynamically

This must be structured and scalable.

3. Reporting & Analytics

Comms should be able to:

• Generate reports from the Google Sheet
• Filter by date range / owner / status
• See open vs closed projects
• Export reports if needed
• Optional: AI-generated summary reports


4. Job Updates & Lifecycle Management

The system must allow updates to existing projects:

• Change owner
• Reassign team
• Mark as Lost
• Mark as Closed
• Mark as Postponed
• Edit deadlines
• Add activity logs

All updates must automatically reflect in:
• Google Sheet
• Email notifications (if needed)
• Activity tracking

AI Component Expectations

We want intelligent automation, not just triggers.

Examples:

• Provide deliverables from the upload RFP (pdf, word, ppt)
• Extract structured data from email body
• Understand names and match to directory emails
• Validate missing information
• Suggest delivery timelines
• Generate structured email responses
• Provide summary reports

OpenAI or similar LLM integration is expected.

Technical Expectations

• Clean, modular architecture
• Documented workflow
• Scalable design
• Error handling
• Logging
• Secure authentication
• Future extensibility

We prefer someone experienced with:
• Google Workspace API
• n8n or similar workflow engines
• OpenAI API
• Automation architecture

Ideal Freelancer Profile

• Proven experience in AI automation
• Experience with Google Workspace automation
• Experience building business workflow systems
• Strong understanding of scalable architecture
• Ability to suggest improvements