Create CTD dossier folders and AI-drafted assignment emails with Google Workspace and Gemini

Quick overview

This workflow watches a Google Sheets intake form for new CTD dossier requests, creates a structured folder set in Google Drive, uses Google Gemini to draft module assignment emails for human approval in Gmail, logs assignments to a tracking sheet, and sends scheduled reminder emails as due dates approach.

How it works

1. Triggers when a new row is added to a Google Sheets intake spreadsheet.
2. Reads all registrations, identifies rows with the same product and dosage form (or missing required fields), and deletes the duplicate/invalid rows from Google Sheets.
3. Creates a CTD dossier root folder in Google Drive and builds a list of CTD modules with their checklist filenames.
4. For each module, creates a subfolder in Google Drive, downloads the module checklist via HTTP, uploads it to the module folder, and emails an error alert via Gmail if the upload fails.
5. Uses Google Gemini (with a Google Sheets lookup tool) to find the module owner and draft an HTML assignment email with folder links and a due date.
6. Sends the drafted email to a reviewer via Gmail for approval, then emails the module owner with the checklist attached and appends a tracking row to a Google Sheets tracking spreadsheet.
7. On a schedule, reads the tracking sheet, calculates days remaining to each due date, and sends reminder emails via Gmail for incomplete modules that are due soon or overdue.

Setup

1. Configure Google credentials for Google Sheets, Google Drive, and Gmail, plus a Google Gemini (Google AI) connection for the LangChain agent.
2. Select the Google Sheets documents and sheet tabs for the intake sheet, the module-owners sheet (Module, Owner, Email), and the tracking sheet, and ensure the intake sheet includes “Nombre del Producto” and “Forma Farmacéutica” columns.
3. Update the workflow settings values such as reviewer email, parent Google Drive folder ID, checklist base URL, deadline days, initial/completed status labels, and reminder threshold days.
4. Host the module checklist .xlsx files at the configured base URL with filenames that match the module list, and ensure the workflow’s module names exactly match the “Module” values in the owners sheet.