Introduction
A CRM doesn’t have to be Salesforce or HubSpot. For most freelancers and small businesses, a Google Sheet is enough — it’s free, flexible, and your team already knows how to use it.
This post builds a simple but powerful CRM in Google Sheets with automation.
The CRM structure
Create a Google Sheet with these tabs:
Tab 1: Leads
| A: Name | B: Email | C: Phone | D: Company | E: Source | F: Status | G: Score | H: Assigned To | I: Notes | J: Created | K: Last Contact | L: Next Action |
|---|
Tab 2: Pipeline
| A: Lead Name | B: Stage | C: Value (₹) | D: Probability | E: Expected Close | F: Notes |
|---|
Tab 3: Activity Log
| A: Date | B: Lead | C: Action | D: Notes | E: By |
|---|
Tab 4: Settings
| A: Setting | B: Value |
|---|---|
| Lead statuses | New, Contacted, Qualified, Proposal, Won, Lost |
| Sources | Website, Referral, LinkedIn, Cold Email, Upwork |
| Team members | Alice, Bob, Carol |
Step 1: Set up data validation
Make the sheet user-friendly with dropdowns:
- Select column F (Status) → Data → Data validation
- Criteria: List of items →
New,Contacted,Qualified,Proposal,Won,Lost - Repeat for Source, Assigned To, etc.
This prevents typos and ensures consistent data.
Step 2: Add conditional formatting
Color-code by status:
- New → Light blue
- Contacted → Yellow
- Qualified → Orange
- Proposal → Purple
- Won → Green
- Lost → Red (strikethrough)
- Select column F → Format → Conditional formatting
- Set rules for each status
Step 3: Add formulas
Lead count by status
=COUNTIF(F:F, "New")
=COUNTIF(F:F, "Won")
Total pipeline value
=SUMIF(Pipeline!B:B, "Proposal", Pipeline!C:C)
Conversion rate
=COUNTIF(F:F, "Won") / COUNTA(A:A) * 100
Days since last contact
=TODAY() - K2
(Conditional format: red if > 7 days)
Step 4: Automate with Apps Script
Auto-score leads
function scoreLeads() {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Leads");
const data = sheet.getDataRange().getValues();
for (let i = 1; i < data.length; i++) {
const row = data[i];
const source = row[4]; // Source column
const message = row[3]; // Message column
let score = 0;
// Score by source
if (source === "Referral") score += 30;
if (source === "LinkedIn") score += 20;
if (source === "Website") score += 15;
if (source === "Cold Email") score += 10;
// Score by message quality
if (message && message.length > 50) score += 20;
if (message && message.toLowerCase().includes("budget")) score += 15;
if (message && message.toLowerCase().includes("urgent")) score += 10;
sheet.getRange(i + 1, 7).setValue(score); // Score column
}
}
Auto-notify on new lead
function onNewLead(e) {
const sheet = e.source.getActiveSheet();
if (sheet.getName() !== "Leads") return;
const row = e.range.getRow();
const name = sheet.getRange(row, 1).getValue();
const email = sheet.getRange(row, 2).getValue();
const phone = sheet.getRange(row, 3).getValue();
MailApp.sendEmail("[email protected]",
`New lead: ${name}`,
`Name: ${name}\nEmail: ${email}\nPhone: ${phone}\n\nCheck the CRM for details.`);
}
Set up a trigger: On edit → onNewLead
Auto-follow-up reminder
function checkFollowUps() {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Leads");
const data = sheet.getDataRange().getValues();
const today = new Date();
for (let i = 1; i < data.length; i++) {
const lastContact = new Date(data[i][10]); // Last Contact column
const daysSince = Math.floor((today - lastContact) / (1000 * 60 * 60 * 24));
const status = data[i][5]; // Status column
if (status !== "Won" && status !== "Lost" && daysSince > 3) {
// Send reminder
MailApp.sendEmail("[email protected]",
`Follow up: ${data[i][0]}`,
`${data[i][0]} hasn't been contacted in ${daysSince} days. Status: ${status}`);
}
}
}
Set up a daily trigger.
Step 5: Build a dashboard
Summary metrics
Total leads: =COUNTA(Leads!A:A) - 1
New (this month): =COUNTIFS(Leads!J:J, ">="&DATE(2026,3,1), Leads!J:J, "<"&DATE(2026,4,1))
Won: =COUNTIF(Leads!F:F, "Won")
Conversion rate: =COUNTIF(Leads!F:F, "Won") / COUNTA(Leads!A:A)
Pipeline value: =SUMIF(Pipeline!B:B, "Proposal", Pipeline!C:C)
Charts
- Pie chart: Leads by status
- Bar chart: Leads by source
- Line chart: Leads over time
CRM vs dedicated tools
| Google Sheets CRM | HubSpot (Free) | Pipedrive | |
|---|---|---|---|
| Cost | Free | Free (limited) | $15/mo |
| Setup | 30 min | 1 hour | 1 hour |
| Features | Basic | Advanced | Advanced |
| Automation | Apps Script | Built-in | Built-in |
| Scalability | 100-500 leads | Unlimited | Unlimited |
| Best for | Solo freelancers, small teams | Growing businesses | Sales teams |
Rule: Start with Sheets. Move to HubSpot/Pipedrive when you have 3+ team members or 500+ leads.
Common mistakes
- Too many columns — 20 columns is overwhelming. Start with 8-10.
- No data validation — typos in status/source fields break formulas
- No automation — manual follow-up reminders are forgotten. Automate.
- Not updating — a CRM is only useful if it’s current. Update daily.
- No backup — export to CSV weekly. Google Sheets can be accidentally deleted.
Key Concepts
- CRM = Customer Relationship Management
- Pipeline = stages a lead goes through before becoming a customer
- Lead scoring = assigning a numeric value to lead quality
- Conversion rate = % of leads that become customers
- Follow-up = contacting a lead after initial contact
- Data validation = dropdown lists to ensure consistent data
What to Try Next
- Capture leads into Google Sheets for the full pipeline
- Get instant WhatsApp alerts for new leads
- Automate lead follow-up emails for consistent outreach
- Organise client projects with a system
FAQ
Google Sheets CRM vs HubSpot — which to use?
Sheets for solo freelancers and small teams (under 5 people, under 500 leads). HubSpot for growing businesses (3+ team members, 500+ leads, need automation). Start with Sheets; migrate to HubSpot when you outgrow it.
Can I use this CRM for multiple team members?
Yes — share the Google Sheet with your team. Use data validation and conditional formatting to keep it organized. For more than 3-4 team members, consider a dedicated CRM (HubSpot, Pipedrive).
How do I prevent data loss?
Export to CSV weekly. Use Google Sheets’ version history (File → Version history). Don’t store sensitive data (passwords, payment info) in the CRM. See Store Client Passwords Safely.