Google Sheets to Gmail, personalized lead drafts ready
Leads land in a spreadsheet… and then sit there. Not because you don’t care, but because rewriting “quick intro” emails all day is exhausting, and the context switching guarantees mistakes.
This Sheets Gmail drafts automation hits sales reps first. But founders doing their own outreach and marketing ops cleaning up lead handoffs feel the same drag. You get personalized Gmail drafts automatically, so follow-ups go out faster and still sound like a human wrote them.
Below is what the workflow does, why it works, and how you can adapt it to your lead sources and brand voice.
How This Automation Works
The full n8n workflow, from trigger to final output:
n8n Workflow Template: Google Sheets to Gmail, personalized lead drafts ready
flowchart LR
subgraph sg0["On form submission Flow"]
direction LR
n0@{ icon: "mdi:database", form: "rounded", label: "Get row(s) in sheet", pos: "b", h: 48 }
n1@{ icon: "mdi:robot", form: "rounded", label: "Introductory email ", pos: "b", h: 48 }
n2@{ icon: "mdi:message-outline", form: "rounded", label: "Create a draft", pos: "b", h: 48 }
n3@{ icon: "mdi:database", form: "rounded", label: "Append or update row in sheet", pos: "b", h: 48 }
n4["<div style='background:#f5f5f5;padding:10px;border-radius:8px;display:inline-block;border:1px solid #e0e0e0'><img src='https://flowpast.com/wp-content/uploads/n8n-workflow-icons/form.svg' width='40' height='40' /></div><br/>On form submission"]
n5@{ icon: "mdi:database", form: "rounded", label: "Append row in sheet", pos: "b", h: 48 }
n6@{ icon: "mdi:robot", form: "rounded", label: "AI Agent", pos: "b", h: 48 }
n7@{ icon: "mdi:brain", form: "rounded", label: "Google Gemini Chat Model", pos: "b", h: 48 }
n8@{ icon: "mdi:web", form: "rounded", label: "HTTP Request", pos: "b", h: 48 }
n9["<div style='background:#f5f5f5;padding:10px;border-radius:8px;display:inline-block;border:1px solid #e0e0e0'><img src='https://flowpast.com/wp-content/uploads/n8n-workflow-icons/code.svg' width='40' height='40' /></div><br/>Code"]
n10@{ icon: "mdi:database", form: "rounded", label: "Update row in sheet", pos: "b", h: 48 }
n9 --> n10
n6 --> n9
n8 -.-> n6
n2 --> n3
n4 --> n5
n5 --> n6
n0 --> n1
n1 --> n2
n10 --> n0
n7 -.-> n6
end
%% Styling
classDef trigger fill:#e8f5e9,stroke:#388e3c,stroke-width:2px
classDef ai fill:#e3f2fd,stroke:#1976d2,stroke-width:2px
classDef aiModel fill:#e8eaf6,stroke:#3f51b5,stroke-width:2px
classDef decision fill:#fff8e1,stroke:#f9a825,stroke-width:2px
classDef database fill:#fce4ec,stroke:#c2185b,stroke-width:2px
classDef api fill:#fff3e0,stroke:#e65100,stroke-width:2px
classDef code fill:#f3e5f5,stroke:#7b1fa2,stroke-width:2px
classDef disabled stroke-dasharray: 5 5,opacity: 0.5
class n4 trigger
class n1,n6 ai
class n7 aiModel
class n0,n3,n5,n10 database
class n8 api
class n9 code
classDef customIcon fill:none,stroke:none
class n4,n9 customIcon
The Problem: Personalized outreach doesn’t scale manually
Most teams start “organized.” Leads go into Google Sheets, someone checks the sheet, then someone opens Gmail and writes an intro. That’s fine at five leads a week. At 20 or 50, it becomes a quiet mess. You forget who asked what. You send the right email to the wrong person. Or you send something so generic it might as well be spam. The worst part is the mental load: you’re constantly re-reading notes, skimming company sites, and trying to sound personal when you’re already behind.
It adds up fast. Here’s where it usually breaks down.
- Leads don’t get contacted quickly because the sheet isn’t someone’s “home base” all day.
- Company research gets skipped, so your first email reads like a template.
- Copy-pasting fields (name, company, message) creates little errors that kill trust.
- No consistent tracking exists, so you can’t tell what was drafted, sent, or ignored.
The Solution: Auto-enrich leads and generate Gmail drafts
This workflow turns a basic lead capture into ready-to-send Gmail drafts that still feel specific to the person. It starts by capturing a lead (via an embedded form trigger, or any source you swap in), then logs the details to Google Sheets. Next, an AI Agent researches the company online using the company name, pulling back a short description and a location category you define (Delhi/NCR, Bangalore, Mumbai, or Other by default). After that enrichment is written back into the same sheet, n8n pulls every lead marked “To Send” and asks ChatGPT-4o to draft a personalized introductory email using your template rules. Finally, the message is saved as a draft inside Gmail and the sheet is updated so your team can see it’s ready.
The workflow begins when a new lead is captured and stored in Google Sheets. Then it enriches the record with company info and location so the email has real context. Once a lead is eligible for outreach, it generates a subject + body and creates the Gmail draft automatically.
What You Get: Automation vs. Results
| What This Workflow Automates | Results You’ll Get |
|---|---|
|
|
Example: What This Looks Like
Say you get 15 inbound leads a week and you currently spend about 10 minutes per lead: open the sheet, Google the company, write an email, paste details, and log the date. That’s about 2.5 hours weekly. With this workflow, adding the lead happens instantly, research and drafting run in the background, and your “work” becomes reviewing and hitting send (maybe 2 minutes per lead). You get roughly 2 hours back every week, and replies usually improve because the emails contain real context.
What You’ll Need
- n8n instance (try n8n Cloud free)
- Self-hosting option if you prefer (Hostinger works well)
- Google Sheets to store leads and statuses.
- Gmail to create drafts for your sales inbox.
- OpenAI API Key (get it from the OpenAI API dashboard).
- Google Gemini API Key (get it from Google AI Studio).
Skill level: Intermediate. You’ll connect accounts, map sheet columns, and adjust prompts to match your offer.
Don’t want to set this up yourself? Talk to an automation expert (free 15-minute consultation).
How It Works
A new lead comes in. The workflow starts with a form submission trigger (you can swap this for a webhook, a CRM push, or a sheet trigger later). The lead’s name, email, company, designation, and message are captured right away.
The lead is logged and marked for outreach. n8n appends a new row in Google Sheets and sets a Status value like “To Send,” which becomes your queue. This is also where you keep the fields your team actually needs: reminder columns, dates, and notes.
Company info is enriched automatically. An AI Agent uses the company name to research a simple description and a location category through Gemini + an external HTTP request tool. A short code step then splits that output into clean fields and updates the same row in Sheets.
Drafts get created in Gmail. The workflow pulls all rows with Status “To Send” (it even handles “To send” if someone typed it differently), generates a tailored intro email with ChatGPT-4o, and saves it as a Gmail draft. After that, the row is updated to “Drafted” and the Intro email Date is logged.
You can easily modify the status rules to fit your pipeline (for example, “Qualified” instead of “To Send”) based on your needs. See the full implementation guide below for customization options.
Step-by-Step Implementation Guide
Step 1: Configure the Form Trigger
Set up the lead capture form that starts the workflow and collects the data used downstream.
- Add and open Form Submission Trigger.
- Set Form Title to
Let's Talk AI marketing automationand Form Description toLead Capture Form. - Configure the form fields exactly as listed: First name, Last Name, Company name, Designation, Email Id (email), and Message (textarea).
- Optional: Set the Button Label to
Let's talkin Options.
First name vs First Name).Step 2: Connect Google Sheets
Capture form submissions and update lead records as the workflow progresses.
- Open Add Sheet Entry and set Operation to
append. - Choose your Document and Sheet Name (currently
[YOUR_ID]andForm Filled). - Map the columns exactly as configured, including Status set to
To Sendand these expressions:{{$json.Message}},{{$json["Email Id"]}},{{$json["Last Name"]}},{{$json["First name"]}},{{$json.Designation}},{{$json['Company name']}}. - Credential Required: Connect your googleSheetsOAuth2Api credentials in Add Sheet Entry.
- Open Modify Sheet Row and set Operation to
updatewith Matching Columns set toEmail ID. - Map Email ID to
{{$('Form Submission Trigger').item.json['Email Id']}}, Location to{{$json.companyLocation}}, and Company Information to{{$json.companyDescription}}. - Credential Required: Connect your googleSheetsOAuth2Api credentials in Modify Sheet Row.
- Open Retrieve Sheet Rows, set Combine Filters to
OR, and configure filters for Status with values=To sendandTo Send. - Credential Required: Connect your googleSheetsOAuth2Api credentials in Retrieve Sheet Rows.
- Open Upsert Sheet Row, set Operation to
appendOrUpdate, and Matching Columns toEmail ID. - Map Status to
Drafted, Email ID to{{$('Draft Intro Email').item.json.message.content.Emailid}}, and Intro email Date to{{$now}}. - Credential Required: Connect your googleSheetsOAuth2Api credentials in Upsert Sheet Row.
Email ID and Company Information), or updates may fail.Step 3: Set Up the AI Research and Transformation
Enrich each lead with company information and normalize the AI output for sheet updates.
- Open AI Research Agent and set the prompt Text to the exact template provided, including the expression
{{ $json['Company Name'] }}. - Confirm Gemini Chat Engine is connected as the language model for AI Research Agent. Credential Required: Connect your googlePalmApi credentials in Gemini Chat Engine.
- External API Request is attached as an AI tool to AI Research Agent with URL set to
{{ /*n8n-auto-generated-fromAI-override*/ $fromAI('URL', ``, 'string') }}. - If the external API needs authentication, add credentials in the AI Research Agent tool configuration (not in External API Request directly).
- Open Transform Script and paste the provided JavaScript to parse Company Description and Company Location from the AI output.
Step 4: Configure Email Drafting and Delivery
Generate a personalized email draft and store the status in Google Sheets.
- Open Draft Intro Email and set Model to
chatgpt-4o-latest. - Ensure JSON Output is enabled and the message template includes the required expressions like
{{ $json["First Name"] }},{{ $json["Company Name"] }},{{ $json["Company Information"] }},{{ $json.Message }}, and{{ $json["Email ID"] }}. - Credential Required: Connect your openAiApi credentials in Draft Intro Email.
- Open Generate Email Draft and set Resource to
draft. - Set Subject to
{{ $json.message.content.EmailSubject }}and Message to{{ $json.message.content.EmailContent }}. - Under Options, set Send To to
{{ $json.message.content.Emailid }}. - Credential Required: Connect your gmailOAuth2 credentials in Generate Email Draft.
EmailSubject and EmailContent may be missing—keep the prompt consistent.Step 5: Test and Activate Your Workflow
Run a full test to confirm data capture, enrichment, drafting, and sheet updates are all working.
- Click Execute Workflow and submit a test entry through Form Submission Trigger.
- Verify the new row is appended in Add Sheet Entry with Status set to
To Send. - Confirm AI Research Agent produces company data and Transform Script outputs
companyDescriptionandcompanyLocation. - Check that Modify Sheet Row updates Company Information and Location, and Retrieve Sheet Rows pulls rows with
To Sendstatus. - Ensure Draft Intro Email returns JSON with
EmailSubject,EmailContent, andEmailid, and that Generate Email Draft creates a Gmail draft. - Confirm Upsert Sheet Row updates Status to
Draftedand writes Intro email Date using{{$now}}. - When everything looks correct, switch the workflow to Active for production use.
Common Gotchas
- Google Sheets credentials can expire or the sheet can lose permissions. If things break, check the n8n credential connection and confirm the sheet ID is still accessible.
- If you’re using Wait nodes or external rendering, processing times vary. Bump up the wait duration if downstream nodes fail on empty responses.
- Default prompts in AI nodes are generic. Add your brand voice early or you’ll be editing outputs forever.
Frequently Asked Questions
About an hour if your sheet columns are already in place.
No. You will mostly connect accounts and map fields from your sheet into the draft email.
Yes. n8n has a free self-hosted option and a free trial on n8n Cloud. Cloud plans start at $20/month for higher volume. You’ll also need to factor in OpenAI and Gemini API usage costs, which depend on how many leads you process and how long your prompts are.
Two options: n8n Cloud (managed, easiest setup) or self-hosting on a VPS. For self-hosting, Hostinger VPS is affordable and handles n8n well. Self-hosting gives you unlimited executions but requires basic server management.
Yes, but you’ll change the trigger. Replace the Form Submission Trigger with a Webhook or your preferred app trigger, then map the incoming fields to the same Google Sheets columns (First name, Last name, Email ID, Company Name, Message). Many teams also tweak the location categories inside the AI research prompt and adjust the Status values so the “Retrieve Sheet Rows” filter matches their pipeline.
Usually it’s an expired OAuth connection or the Gmail account doesn’t allow the permissions n8n requested. Reconnect the Gmail credential in n8n, then confirm you’re drafting into the same mailbox your team expects. If it works for a few leads and then stops, check for Google security prompts and account-level restrictions.
If you self-host, it’s mostly limited by your server and your Google/OpenAI/Gemini rate limits.
It depends on how “smart” you want the workflow to be. Zapier and Make can absolutely create Gmail drafts from a sheet, but the moment you add enrichment, branching logic (like Status handling), and structured AI output parsing, costs and complexity climb. n8n is comfortable with multi-step logic, code-based cleanup when needed, and running in batches so you can process a queue of leads in one go. Frankly, the biggest win is control: you can self-host and avoid paying per-step for every draft. Talk to an automation expert if you want a quick recommendation based on your lead volume.
Once this is in place, your sheet stops being a graveyard and becomes a real outreach queue. The workflow handles the repetitive parts so you can focus on the conversations that actually close deals.
Need Help Setting This Up?
Our automation experts can build and customize this workflow for your specific needs. Free 15-minute consultation—no commitment required.
