Generate overdue lead follow-up Gmail drafts with Google Sheets and Gemini

Automatically identifies overdue sales leads and generates personalized follow-up emails using AI.

How it works

1. Runs every weekday
2. Reads leads from Google Sheets
3. Filters leads with no contact for 5+ days
4. Downloads last meeting transcript from Google Drive
5. Uses Google Gemini to write a personalized email
6. Saves the email as a Gmail Draft for review
7. Updates lead status in the Sheet

Setup

• Google Sheets with columns: Current Status, Last Contact, Name, Company, Email, Meeting Transcript, Sales Rep
• Google Drive access for transcript files
• Google Gemini API key (free from Google AI Studio)
• Gmail account
• Slack workspace

Supported file types

• PDF
• TXT

Notes

• Emails are saved as drafts only — not sent automatically
• Supported lead statuses: New
• Skips leads with status: Draft Created, Sent, Closed

Inspiration

This workflow was built to solve a real problem at an educational institute.
The sales team needed someone to remind them about follow-ups and draft personalized emails — but hiring a personal assistant wasn't financially feasible.

Based on that, this system acts as that assistant: it tracks overdue leads, reads meeting transcripts, and writes personalized follow-up emails automatically.

I kept this stack free where possible — using Google Gemini's free tier for AI.
For small sales teams, I believe the daily token limits are more than sufficient, making this a zero-cost automation.