By Raj
Build a Custom CRM in Google Sheets with Apps Script
Not every business needs Salesforce. If you have fewer than 1,000 contacts and want to track leads, deals, and follow-ups without paying $50/user/month, Google Sheets plus Apps Script is a surprisingly powerful CRM. You get a single source of truth in a spreadsheet, custom automations (reminders, logging, forms), and no new SaaS subscription, just Google Workspace and a bit of code.
This post outlines a simple sheet structure (Contacts, Deals, Activities), then shows three automations: a time-driven script that emails you follow-up reminders, using Gmail search to update "Last Contact Date," and a Web App form so your team can add leads without touching the sheet directly.
Why build a CRM in Google Sheets?
Most small businesses start tracking leads in a simple spreadsheet. As the business grows, this approach breaks down, data becomes scattered, follow-ups get missed, and there's no systematic way to track deal progress. But jumping to enterprise CRM software like Salesforce or HubSpot ($50-150/user/month) doesn't make sense when you're just starting out.
A custom CRM built in Google Sheets with Apps Script gives you the best of both worlds: the familiarity and flexibility of a spreadsheet, plus automation capabilities that rival paid CRM tools. You can track leads, manage pipelines, automate follow-ups, and even capture leads from web forms, all without leaving Google Sheets.
The real cost comparison: A basic Salesforce plan costs $25/user/month. For a 5-person team, that's $1,500/year. A Google Sheets CRM costs nothing extra if you already have Google Workspace (which most small businesses do).
Sheet structure
Create tabs for Contacts, Deals, and Activities. The Contacts sheet has columns: Name, Email, Company, Phone, Status (Lead / Contacted / Won / Lost), Last Contact Date, Notes.
Recommended column structure for Contacts tab:
• Column A: Name (Full name of the contact)
• Column B: Email (Primary email address)
• Column C: Company (Company or organization name)
• Column D: Phone (Phone number)
• Column E: Status (Lead / Contacted / Qualified / Proposal / Won / Lost)
• Column F: Last Contact Date (Auto-updated via Gmail integration)
• Column G: Next Follow-up Date (Manual entry for scheduled follow-ups)
• Column H: Notes (Free-form text for call notes, meeting summaries)
• Column I: Source (Where the lead came from: Website, Referral, LinkedIn, etc.)
• Column J: Deal Value (Expected deal value in dollars)
Deals tab structure:
• Deal Name, Contact (linked to Contacts), Amount, Stage (Qualification / Proposal / Negotiation / Closed Won / Closed Lost), Expected Close Date, Owner
Activities tab structure:
• Date, Type (Call / Email / Meeting / Note), Contact, Deal, Description, Duration
Automation 1: follow-up reminders
A time-driven trigger checks every morning: if "Last Contact Date" is more than 7 days ago and Status is "Contacted", send a reminder email to yourself:
function checkFollowUps() {
const sheet = SpreadsheetApp
.getActiveSpreadsheet().getSheetByName("Contacts");
const data = sheet.getDataRange().getValues();
const now = new Date();
const reminders = [];
for (let i = 1; i < data.length; i++) {
const status = data[i][4]; // Status column
const lastContact = new Date(data[i][5]);
const daysSince = (now - lastContact)
/ (1000 * 60 * 60 * 24);
if (status === "Contacted" && daysSince > 7) {
reminders.push(data[i][0] + " (" + data[i][1] + ")");
}
}
if (reminders.length > 0) {
GmailApp.sendEmail(
"you@example.com",
"CRM: " + reminders.length + " follow-ups due",
"Follow up with:\n" + reminders.join("\n")
);
}
}How to set this up:
1. Open your Google Sheets CRM file
2. Go to Extensions → Apps Script
3. Paste the code above into the editor
4. Click the clock icon (Triggers) on the left sidebar
5. Add a new trigger: checkFollowUps, Time-driven, Daily, Around 8am
6. Save and authorize the script
This automation ensures you never miss a follow-up again. Every morning, you'll get an email listing all contacts that need attention based on your criteria.
Automation 2: log emails automatically
Use GmailApp.search() to find threads with a contact's email and log the latest date into the "Last Contact Date" column. Example: for each contact email in the sheet, search from:contact@example.com OR to:contact@example.com, get the most recent thread date, and write it to the Last Contact column. Run this on a schedule (e.g. daily) so the CRM updates itself.
function updateLastContactDates() {
const sheet = SpreadsheetApp.getActiveSpreadsheet()
.getSheetByName("Contacts");
const data = sheet.getDataRange().getValues();
for (let i = 1; i < data.length; i++) {
const email = data[i][1]; // Column B - Email
if (!email) continue;
try {
// Search Gmail for emails with this contact
const query = "from:" + email + " OR to:" + email;
const threads = GmailApp.search(query, 0, 1);
if (threads.length > 0) {
const lastMessage = threads[0].getLastMessageDate();
// Write to Column F (Last Contact Date)
sheet.getRange(i + 1, 6).setValue(lastMessage);
Logger.log("Updated " + data[i][0] + " - Last contact: " + lastMessage);
}
} catch (error) {
Logger.log("Error for " + email + ": " + error.message);
}
}
}Important considerations:
• Gmail API has rate limits (about 100 searches per 100 seconds)
• Add Utilities.sleep(100) between iterations to avoid hitting limits
• This script requires the "gmail.readonly" scope in manifest
• Consider running this only once daily to minimize API calls
Automation 3: Web App form for new leads
Build a simple Web App (using HtmlService) that your team uses to add leads. The form writes to the Contacts sheet and sends a confirmation. No separate CRM tool needed.
// Web App - doGet function serves the HTML form
function doGet() {
return HtmlService.createTemplateFromString("LeadForm")
.evaluate()
.setTitle("Add New Lead")
.setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
}
// HTML form template
const leadFormHtml = `
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<link href="https://fonts.googleapis.com/css2?family=Inter:wght@400;500;600&display=swap" rel="stylesheet">
<style>
body { font-family: "Inter", sans-serif; max-width: 500px; margin: 40px auto; padding: 20px; }
h2 { color: #202124; margin-bottom: 20px; }
label { display: block; margin-bottom: 5px; font-weight: 500; color: #5F6368; }
input, select, textarea { width: 100%; padding: 10px; margin-bottom: 15px; border: 1px solid #DADCE0; border-radius: 4px; box-sizing: border-box; }
button { background: #1A73E8; color: white; padding: 12px 24px; border: none; border-radius: 4px; cursor: pointer; font-weight: 500; }
button:hover { background: #1557B0; }
.success { background: #E6F4EA; padding: 15px; border-radius: 4px; color: #137333; margin-bottom: 20px; }
</style>
</head>
<body>
<h2>Add New Lead</h2>
<form id="leadForm" onsubmit="handleSubmit(event)">
<label>Full Name *</label>
<input type="text" name="name" required>
<label>Email *</label>
<input type="email" name="email" required>
<label>Company</label>
<input type="text" name="company">
<label>Phone</label>
<input type="tel" name="phone">
<label>Source</label>
<select name="source">
<option value="Website">Website</option>
<option value="Referral">Referral</option>
<option value="LinkedIn">LinkedIn</option>
<option value="Cold Call">Cold Call</option>
<option value="Trade Show">Trade Show</option>
<option value="Other">Other</option>
</select>
<label>Notes</label>
<textarea name="notes" rows="3"></textarea>
<button type="submit">Submit Lead</button>
</form>
<div id="message"></div>
<script>
function handleSubmit(event) {
event.preventDefault();
const form = event.target;
const formData = {};
form.querySelectorAll("input, select, textarea").forEach(el => {
formData[el.name] = el.value;
});
google.script.run.addLeadToSheet(formData);
}
function onSuccess() {
document.getElementById("leadForm").style.display = "none";
document.getElementById("message").innerHTML = "<div class=\"success\">Lead added successfully! <a href=\"/\">Add another</a></div>";
}
</script>
</body>
</html>
`;
// Server-side function to add lead
function addLeadToSheet(formData) {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Contacts");
const lastRow = sheet.getLastRow();
sheet.getRange(lastRow + 1, 1, 1, 10).setValues([[
formData.name,
formData.email,
formData.company || "",
formData.phone || "",
"Lead", // Default status
new Date(), // Last Contact Date
"", // Next Follow-up Date
formData.notes || "",
formData.source || "Website",
"" // Deal Value
]]);
// Send confirmation email
GmailApp.sendEmail(
formData.email,
"Thank you for your interest!",
"Hi " + formData.name + ",\n\nThank you for reaching out. We'll be in touch soon!"
);
return "Success";
}To deploy the Web App:
1. In Apps Script editor, click Deploy → New deployment
2. Select Web app as the type
3. Set Execute as: "Me" (your account)
4. Set Who has access: "Anyone" (or "Anyone within my organization")
5. Click Deploy and copy the URL
6. Share this URL with your team or embed in your website
Now your team can add leads from anywhere, even from their mobile phones, without needing access to the spreadsheet directly.
Additional CRM features you can add
Once you have the basics working, here are more advanced features to consider:
1. Pipeline visualization: Create a summary sheet that shows deal counts and values by stage using COUNTIF and SUMIF formulas.
2. Email templates: Store common email templates in a separate sheet and use Apps Script to merge them with contact data before sending via GmailApp.
3. Task reminders: Similar to follow-up reminders, create tasks that appear on specific dates based on the "Next Follow-up Date" column.
4. Activity logging: Automatically log when you send an email or make a call using GmailApp and CalendarApp.
5. Duplicate detection: Check before adding new contacts if an email already exists to prevent duplicates.
Best practices for your Google Sheets CRM
Consistent data entry: Establish conventions for how you enter data. For example, always use the same status values (Lead, Contacted, Qualified, Proposal, Won, Lost).
Regular cleanup: Set a monthly reminder to review stale leads (no activity in 60+ days) and either update them or mark as lost.
Backup your data: Create a time-triggered script that copies your CRM to a backup spreadsheet weekly.
Use data validation: Add dropdown menus for columns like Status and Source to ensure consistent data entry across your team.
When to level up
As your team and data grow, you might need: role-based access, pipeline visualizations, multi-user locking, or integration with Stripe/Shopify. I can build all of that inside Google Workspace, no new SaaS subscription. Get a quote.
FAQ
Can I really build a CRM in Google Sheets?
Yes, Google Sheets combined with Apps Script can handle most basic CRM functions: contact management, deal tracking, follow-up reminders, email logging, and lead capture via web forms. It's ideal for small teams with up to 1,000 contacts.
Is Google Sheets CRM free?
If you already have Google Workspace (which includes Google Sheets), there's no additional cost. Apps Script is free for personal and business use. The only costs would be if you need advanced Google Workspace features or add-ons.
How do I automate follow-ups in Google Sheets CRM?
Use Apps Script with a time-driven trigger. The script checks each contact's "Last Contact Date" and "Status" column, then sends you an email reminder when contacts haven't been reached in a specified number of days (e.g., 7 days).
Can multiple team members use the same Google Sheets CRM?
Yes, but with limitations. Anyone with edit access to the spreadsheet can use it. For better control, use the Web App approach where team members submit leads through a form without directly accessing the sheet. For advanced multi-user features, consider upgrading to a proper CRM.
How do I capture leads from my website to Google Sheets?
Deploy an Apps Script Web App and embed the form URL on your website. When someone submits the form, the data automatically writes to your Contacts sheet. You can also use Google Forms and link it to your CRM sheet.
What's the difference between a Google Sheets CRM and a real CRM?
Enterprise CRMs like Salesforce offer advanced features: complex automation workflows, sales forecasting, reporting dashboards, mobile apps, and integrations with hundreds of tools. A Google Sheets CRM works well for small teams but may become limiting as you scale beyond 1,000 contacts or need advanced reporting.
Can I integrate my Google Sheets CRM with other tools?
Yes, Apps Script can connect to many APIs. Common integrations include: sending data to Mailchimp for email marketing, syncing with Google Calendar for meetings, pushing deals to Stripe for invoicing, or connecting to Shopify for order data.
How secure is data in a Google Sheets CRM?
Google Sheets offers enterprise-level security with encryption at rest and in transit. You can control access through Google Workspace admin controls, use row-level protection for sensitive data, and enable 2-step verification for your account. For highly sensitive data, consider Google's additional security features.
Can I track email opens and clicks in my Google Sheets CRM?
Basic tracking requires third-party tools like Mailchimp or HubSpot. However, you can track when contacts open your emails by using a tracking pixel (requires additional setup) or by logging when contacts respond to your emails using the Gmail search automation described above.