How to Build a Google Sheets CRM with WhatsApp Follow-Ups (Free Template Inside)

If you're running a small business, you've probably tried at least one "real" CRM — HubSpot, Zoho, Salesforce, Pipedrive — and quietly gone back to a spreadsheet within a month.

It's not because you're lazy. It's because most CRMs are built for sales teams of 50, not founders, ops leads, and 3-person teams trying to stop losing leads. The features are bloated. The pricing scales aggressively. And nobody on your team logs into them.

Meanwhile, your leads are dying in WhatsApp chats and Excel files that only one person knows how to read.

In this guide, we'll walk through building a complete CRM in Google Sheets — lead capture, status tracking, auto-reminders, WhatsApp follow-ups, and team assignment — using Apps Script. No paid tools. No code skills required. You can have it running in 10 minutes.

Why Google Sheets Works Better Than You Think

We've set up this exact system for hundreds of small businesses. The pattern is the same every time:

The Apps Script layer is the unlock. It turns a passive spreadsheet into an active system — one that pings you when leads go cold, sends WhatsApp messages when a status changes, and auto-assigns new leads to the right team member.

If you haven't already, our previous post on why most MSMEs fail to convert leads explains the cost of bad follow-up in detail. This post is the practical fix.

What a Good Enough CRM Actually Needs

Forget the 47 features your last CRM tried to sell you. After auditing hundreds of small business setups, here's the minimum a CRM needs to do its job:

1. 1Capture every lead in one place — no more lost WhatsApp chats.
2. 2Track status — new, contacted, qualified, closed, lost.
3. 3Trigger follow-up reminders — automatically, when leads go quiet.
4. 4Send templated messages — so response time is consistent across the team.
5. 5Show pipeline at a glance — conversion rates per source, per stage.

Everything beyond this is nice-to-have. We'll build all five today.

Step 1: Set Up Your Lead Sheet

Create a new Google Sheet. Name it something like "Lead CRM". Rename the first tab to "Leads" and set up these columns in row 1:

• A: Timestamp (auto-filled on capture)
• B: Name
• C: Phone
• D: Email
• E: Source (Meta, Google, Referral, Website, etc.)
• F: Status (New, Contacted, Qualified, Closed, Lost)
• G: Assigned To (team member name)
• H: Last Contacted (date)
• I: Next Follow-Up (date)
• J: Notes

Freeze row 1 (View → Freeze → 1 row). Bold the headers. Use Data → Data validation to make Status and Source dropdown menus — this keeps your data clean and reportable.

Step 2: Auto-Capture Leads From Your Website

Manual data entry kills CRM adoption faster than anything else. The fix: when someone fills your contact or lead form, push them into the sheet automatically using Apps Script as a webhook.

In your Google Sheet: Extensions → Apps Script. Paste this code:

function doPost(e) {
  const sheet = SpreadsheetApp
    .getActiveSpreadsheet()
    .getSheetByName('Leads');

  const data = JSON.parse(e.postData.contents);

  sheet.appendRow([
    new Date(),                  // Timestamp
    data.name || '',             // B
    data.phone || '',            // C
    data.email || '',            // D
    data.source || 'Website',    // E
    'New',                       // F (default status)
    '',                          // G (assigned to)
    '',                          // H (last contacted)
    nextBusinessDay(),           // I (next follow-up = tomorrow)
    data.notes || '',            // J
  ]);

  return ContentService
    .createTextOutput(JSON.stringify({ status: 'ok' }))
    .setMimeType(ContentService.MimeType.JSON);
}

function nextBusinessDay() {
  const d = new Date();
  d.setDate(d.getDate() + 1);
  if (d.getDay() === 0) d.setDate(d.getDate() + 1); // skip Sun
  if (d.getDay() === 6) d.setDate(d.getDate() + 2); // skip Sat
  return d;
}

Save (Ctrl+S). Then Deploy → New deployment → Type: Web app → Execute as: Me → Who has access: Anyone → Deploy. Copy the /exec URL.

On your website, point your form submissions at that URL. Any lead that fills your form lands in your sheet within seconds, pre-populated with timestamp and next follow-up date.

Step 3: Auto-Detect Cold Leads

Leads that nobody has touched in 48 hours are leaking revenue. Apps Script can scan the sheet daily and flag any lead where "Last Contacted" is too old.

function flagColdLeads() {
  const sheet = SpreadsheetApp
    .getActiveSpreadsheet()
    .getSheetByName('Leads');

  const data = sheet.getDataRange().getValues();
  const today = new Date();

  for (let i = 1; i < data.length; i++) {
    const status = data[i][5];        // F: Status
    const lastContacted = data[i][7]; // H: Last Contacted

    if (status === 'Closed' || status === 'Lost') continue;

    const daysSince = lastContacted
      ? Math.floor((today - new Date(lastContacted)) / 86400000)
      : 99;

    if (daysSince > 2) {
      // Highlight the row in red
      sheet.getRange(i + 1, 1, 1, 10)
        .setBackground('#fce8e6');
    }
  }
}

To run this automatically every morning: in Apps Script, click the clock icon (Triggers) in the left sidebar → Add Trigger → Function: flagColdLeads → Event: Time-driven → Day timer → 6am–7am. Save.

Every morning at 6am, your sheet auto-flags every lead that hasn't been touched in 48 hours. Your team walks in to a visual punch list, not a guess.

Step 4: Wire In WhatsApp Follow-Ups

This is where the magic happens. WhatsApp Business has official APIs, but for small teams there's a faster path: pre-built WhatsApp click-to-chat links with templated messages.

Add an extra column "K: WhatsApp Link" with this formula:

=IF(C2="", "", HYPERLINK(
  "https://wa.me/" & SUBSTITUTE(SUBSTITUTE(C2,"+",""), " ","") &
    "?text=" & ENCODEURL(
      "Hi " & B2 & ", this is " & "Your Name" &
      " from " & "Your Company" &
      ". Following up on your inquiry — when's a good time to chat?"
    ),
  "Send WhatsApp"
))

One click on "Send WhatsApp" → opens WhatsApp with the customer's number AND your message pre-typed. The rep just reviews and hits send. Response goes from "I'll get to it later" to ~5 seconds.

You can also vary the message based on lead status. Wrap the formula in an IF that picks different templates for "New" vs "Qualified" leads — first-touch script vs warm follow-up.

Step 5: Auto-Assign Leads to Team Members

If you have a team, round-robin lead assignment prevents the "but I thought *you* were calling them" problem. Apps Script handles this when a new row appears:

const TEAM = ['Priya', 'Raj', 'Anjali']; // your team

function onNewLead(e) {
  const sheet = e.source.getActiveSheet();
  if (sheet.getName() !== 'Leads') return;

  const row = e.range.getRow();
  if (row === 1) return; // skip header

  const assignedCell = sheet.getRange(row, 7); // G: Assigned To
  if (assignedCell.getValue()) return; // already assigned

  // Pick next team member based on row index
  const idx = (row - 2) % TEAM.length;
  assignedCell.setValue(TEAM[idx]);
}

Set this up via Triggers → Add Trigger → Function: onNewLead → Event: From spreadsheet → On change. Every new lead gets auto-assigned in seconds.

Step 6: Pipeline Dashboard

Add a second tab called "Dashboard". Drop these formulas in:

Total Leads:        =COUNTA(Leads!B2:B)
New:                =COUNTIF(Leads!F2:F, "New")
Contacted:          =COUNTIF(Leads!F2:F, "Contacted")
Qualified:          =COUNTIF(Leads!F2:F, "Qualified")
Closed:             =COUNTIF(Leads!F2:F, "Closed")
Conversion Rate:    =COUNTIF(Leads!F2:F,"Closed") / COUNTA(Leads!B2:B)

Leads by Source (use a pivot or):
Meta:               =COUNTIF(Leads!E2:E, "Meta")
Google:             =COUNTIF(Leads!E2:E, "Google")
Referral:           =COUNTIF(Leads!E2:E, "Referral")

For visual reporting: Insert → Chart → Pie chart on the source breakdown. You now have an MIS dashboard that updates in real time.

Common Pitfalls (And How to Avoid Them)

• Apps Script triggers stop firing silently after 6 months if the deployment owner removes their Google account. Use a dedicated business Google account, not a personal one.
• WhatsApp click-to-chat doesn't work if the number isn't in international format. Always store phone numbers with country code (e.g., +14155552671).
• If your sheet exceeds 50,000 rows, formulas slow to a crawl. Archive closed/lost leads to a separate tab quarterly.
• Data validation dropdowns are case-sensitive. "new" and "New" count as different statuses in COUNTIF. Lock down with strict validation.
• Don't share the sheet "anyone with link". Use specific email permissions — leads contain PII.

When You Need Real WhatsApp API (not just click-to-chat)

Everything above uses WhatsApp's click-to-chat URLs — your rep clicks a link, WhatsApp opens with the message pre-filled, they hit send. Free, simple, works perfectly for 1-50 messages a day.

But if you want true automation — AI-generated personalized templates, scheduled sends, inbound replies logged automatically to your sheet, delivery + read tracking, optional AI chatbot for first-touch qualification — you need Meta's official WhatsApp Business Cloud API.

Most teams reach for AiSensy, Wati, Interakt, or DoubleTick at this point. They charge ₹2,000–₹15,000/month forever, and your lead data lives in their cloud.

We built the alternative: WSD WhatsApp CRM. One-time setup, zero platform fee forever, runs entirely inside YOUR Google + Meta + OpenAI accounts. Same architecture as the template you just built, but with real Meta API + AI template generation + inbound webhook + status tracking wired in.