Tech With Galvan
Back to Articles
AutomationMar 8, 2029 • 5 min read

How to Build a Simple CRM in Google Sheets With Automation

Build a simple CRM in Google Sheets — lead tracking, pipeline, follow-ups, and automation. Free, no-code, and ready in 30 minutes.

Galvan
Galvan

Founder & Creator

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:

  1. Select column F (Status) → Data → Data validation
  2. Criteria: List of items → New,Contacted,Qualified,Proposal,Won,Lost
  3. 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)
  1. Select column F → Format → Conditional formatting
  2. 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

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.