n8n Automation: CTD Dossier Folders + Gemini Email Drafts

Automate CTD dossier folder creation and Gemini-assisted email drafting—fully inside n8n

This n8n automation watches your Google Sheets CTD dossier intake, builds a structured CTD folder set in Google Drive, uses Google Gemini to draft module assignment emails for human review in Gmail, and sends scheduled due-date reminders until tasks are completed.

What this workflow does

• Triggers on new intake rows: When a new row is added to your Google Sheets intake form, the workflow starts the CTD dossier processing.
• Prevents duplicates and invalid requests: It reads the existing registrations, identifies rows matching the same product and dosage form (or missing required fields), and deletes duplicate/invalid entries from Google Sheets.
• Creates Google Drive folder structure: It makes a CTD dossier root folder in Google Drive and prepares a module list with their checklist filenames.
• Publishes module checklists: For each module, it creates a subfolder, downloads the module checklist via HTTP, uploads the checklist file to the module folder, and sends an error alert via Gmail if the upload fails.
• Drafts assignment emails with Google Gemini: Using Google Gemini (with a Google Sheets lookup tool), it finds the module owner and drafts an HTML assignment email including folder links and a due date.
• Human approval workflow: The drafted email is sent to a reviewer via Gmail for approval, then the module owner receives the checklist attachment, and a tracking row is appended to a Google Sheets tracking sheet.
• Scheduled reminders: On a schedule, it checks tracking entries, calculates days remaining to each due date, and sends reminder emails via Gmail for modules that are due soon or overdue.

Use cases

• Centralizing CTD intake from Google Sheets and automatically generating per-module Drive folders.
• Reducing manual effort for assigning module owners and distributing checklists with correct due dates.
• Ensuring follow-ups happen automatically as deadlines approach via Gmail reminder emails.

Technical details

• Integrations: Google Sheets, Google Drive, Gmail, Google Gemini, HTTP download
• Workflow logic: if, set, code, filter, sticky note
• Core automation behavior: row validation + Drive folder creation + Gemini email drafting + approvals + tracking + scheduled reminders