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.

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.

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")
    );
  }
}

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
    if (!email) continue;
    const threads = GmailApp.search("from:" + email + " OR to:" + email, 0, 1);
    if (threads.length > 0) {
      const lastMessage = threads[0].getLastMessageDate();
      sheet.getRange(i + 1, 6).setValue(lastMessage); // Column F
    }
  }
}

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.

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.