Build a Simple CRM in Google Sheets With Apps Script (Free Template Included)

2026-04-06 · Daniel Diaz

How-to Guides build crm google sheets apps script google sheets crm template with automation simple crm in google sheets tutorial free crm google sheets apps script google apps script crm

Build a Simple CRM in Google Sheets With Apps Script (Free Template Included)

You don't need Salesforce to track 50 leads. You probably don't even need HubSpot's free tier, with its 47 onboarding screens and "upgrade now" banners. If your sales pipeline fits in your head but you keep losing track of follow-ups, a Google Sheet with some Apps Script glue is the right tool.

I've built CRM systems for clients ranging from solo consultants to 15-person sales teams. The pattern is always the same: they signed up for a real CRM, used 10% of it, and eventually went back to a spreadsheet. So let's skip that detour and build a CRM in Google Sheets with Apps Script that actually fits how small teams work.

Why Build a CRM in Google Sheets Instead of Paying for One

The honest answer? Because you'll actually use it.

Paid CRMs are built for enterprise sales orgs. They have pipeline stages, forecasting models, territory assignments, and reporting dashboards designed for VP-level rollups. If you're a freelancer tracking 20 clients or a small agency managing inbound leads, that's a sledgehammer for a thumbtack.

Google Sheets gives you a few things paid CRMs don't:

Zero cost. Not "free tier with limits." Actually free.
Full control over your data structure. Add a column when you need one. Rename a stage. No admin panel, no permission requests.
Your team already knows the interface. There's no training period. Everyone has used a spreadsheet.
Apps Script turns it into a real app. Custom menus, automated emails, triggered workflows. You get 80% of CRM automation with code you control.

The tradeoff is real though. Once you're past about 5,000 contacts or need role-based access control, you'll outgrow this. More on that later.

What Our Google Sheets CRM Will Do

By the end of this tutorial, you'll have a spreadsheet that handles:

Contact management with company info, deal value, and interaction history
A deals pipeline with stages you define, expected close dates, and weighted revenue
Automatic activity logging that timestamps every stage change
Email reminders for overdue follow-ups (sent automatically, no clicking required)
A dashboard showing pipeline value by stage and overdue tasks
A custom menu so your team doesn't need to touch the script editor

I've put together a free template you can copy at the end. But I'd recommend building it step by step first so you understand what each piece does.

Setting Up the CRM Spreadsheet Structure

Create a new Google Sheet. Name it something you'll actually find later ("CRM" works, "Untitled spreadsheet" does not). You need four tabs.

Contacts Sheet

This is your source of truth for every person and company you're tracking. Here are the columns you need:

Column A: Contact ID -- Auto-generated (we'll script this)
Column B: First Name
Column C: Last Name
Column D: Email
Column E: Phone
Column F: Company
Column G: Role/Title
Column H: Lead Source -- Where they came from (referral, website, cold outreach)
Column I: Status -- Active, Inactive, or Lost
Column J: Date Added -- Auto-populated by script
Column K: Last Contacted -- Updated automatically from the activity log
Column L: Notes -- Free text for context

Set up Data Validation on column I (Status) and column H (Lead Source) using dropdown lists. Go to Data > Data validation, select the range, and add your options. This prevents the "is it Active or active or ACTIVE" problem that kills every CRM spreadsheet that doesn't use dropdowns.

Deals Pipeline Sheet

This is where money lives. Each row is an opportunity tied to a contact.

Column A: Deal ID -- Auto-generated
Column B: Contact ID -- Links to the Contacts sheet
Column C: Deal Name -- Short description ("Website redesign for Acme")
Column D: Company -- Auto-pulled from Contact ID
Column E: Stage -- Dropdown: Lead, Qualified, Proposal Sent, Negotiation, Won, Lost
Column F: Deal Value -- Dollar amount
Column G: Probability -- Auto-set based on stage
Column H: Weighted Value -- =F2*G2
Column I: Expected Close -- Date
Column J: Next Follow-Up -- Date
Column K: Owner -- Who on your team owns this deal
Column L: Notes
Column M: Created Date -- Auto-populated
Column N: Last Updated -- Auto-populated on edit

For the Probability column, use a formula that maps stages to percentages:

=IFS(E2="Lead",10%,E2="Qualified",25%,E2="Proposal Sent",50%,E2="Negotiation",75%,E2="Won",100%,E2="Lost",0%)

Copy that down the column. Weighted Value (H) then becomes =F2*G2, giving you a realistic pipeline forecast instead of the "add up every deal and pretend they'll all close" number.

Activity Log Sheet

Every interaction gets recorded here. This is what separates a CRM from a contact list.

Column A: Log ID -- Auto-generated
Column B: Contact ID -- Links to the Contacts sheet
Column C: Deal ID -- Optional, links to the Deals sheet
Column D: Date -- Auto-populated
Column E: Type -- Dropdown: Email, Call, Meeting, Note, Stage Change
Column F: Description -- What happened
Column G: Logged By -- Who recorded this

Dashboard Sheet

We'll build this out with formulas and charts in a later section. For now, create the tab and leave it empty.

Adding Apps Script Automation

Here's where the spreadsheet becomes a CRM. Open the script editor: Extensions > Apps Script. Delete whatever's in the default Code.gs file and replace it with the functions below.

I'm breaking this into logical chunks. You can put them all in one file or split them across multiple .gs files in the project.

Auto-Logging Contact Interactions

This function watches for stage changes on the Deals sheet and automatically creates an entry in the Activity Log:

function onEdit(e) {
  var sheet = e.range.getSheet();
  var sheetName = sheet.getName();

  if (sheetName === 'Deals Pipeline' && e.range.getColumn() === 5) {
    logStageChange(e);
  }

  if (sheetName === 'Deals Pipeline' || sheetName === 'Contacts') {
    updateLastModified(e);
  }
}

function logStageChange(e) {
  var row = e.range.getRow();
  var sheet = e.range.getSheet();
  var contactId = sheet.getRange(row, 2).getValue();
  var dealId = sheet.getRange(row, 1).getValue();
  var oldValue = e.oldValue || '(new)';
  var newValue = e.value;

  var log = SpreadsheetApp.getActiveSpreadsheet()
    .getSheetByName('Activity Log');
  var logId = 'LOG-' + new Date().getTime();

  log.appendRow([
    logId,
    contactId,
    dealId,
    new Date(),
    'Stage Change',
    'Stage changed from ' + oldValue + ' to ' + newValue,
    Session.getActiveUser().getEmail()
  ]);
}

function updateLastModified(e) {
  var sheet = e.range.getSheet();
  var row = e.range.getRow();
  if (row === 1) return;

  if (sheet.getName() === 'Deals Pipeline') {
    sheet.getRange(row, 14).setValue(new Date());
  }
}

One thing to watch: onEdit is a simple trigger, so it runs automatically but has limited permissions. It can't send emails or access services that require authorization. That's why we separate the email logic into a different function with an installable trigger.

Deal Stage Change Notifications

Want a Slack-style notification when a deal moves to "Won" or "Lost"? This function sends an email to whoever you specify:

function notifyOnBigStageChanges() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet()
    .getSheetByName('Deals Pipeline');
  var data = sheet.getDataRange().getValues();
  var notifyEmail = 'you@yourcompany.com';

  for (var i = 1; i < data.length; i++) {
    var stage = data[i][4];
    var dealName = data[i][2];
    var value = data[i][5];
    var lastUpdated = data[i][13];

    if (!lastUpdated) continue;

    var updatedDate = new Date(lastUpdated);
    var fiveMinAgo = new Date(new Date().getTime() - 5 * 60000);

    if (updatedDate > fiveMinAgo) {
      if (stage === 'Won' || stage === 'Lost') {
        MailApp.sendEmail(
          notifyEmail,
          'Deal ' + stage + ': ' + dealName,
          'Deal "' + dealName + '" (' + formatCurrency(value) +
          ') just moved to ' + stage + '.'
        );
      }
    }
  }
}

function formatCurrency(amount) {
  return '$' + Number(amount).toLocaleString('en-US',
    {minimumFractionDigits: 0, maximumFractionDigits: 0});
}

Set this up as a time-driven trigger that runs every 5 minutes: In the Apps Script editor, click the clock icon (Triggers), add a new trigger for notifyOnBigStageChanges, and set it to run every 5 minutes. This way it catches recent stage changes without hammering the sheet on every edit.

Follow-Up Reminder Emails

This is the automation that actually saves deals. It checks the "Next Follow-Up" column and emails the deal owner when something is overdue:

function sendFollowUpReminders() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet()
    .getSheetByName('Deals Pipeline');
  var data = sheet.getDataRange().getValues();
  var today = new Date();
  today.setHours(0, 0, 0, 0);

  var remindersSent = 0;

  for (var i = 1; i < data.length; i++) {
    var followUpDate = data[i][9];
    var stage = data[i][4];
    var owner = data[i][10];
    var dealName = data[i][2];
    var company = data[i][3];

    if (!followUpDate || stage === 'Won' || stage === 'Lost') continue;

    var followUp = new Date(followUpDate);
    followUp.setHours(0, 0, 0, 0);

    if (followUp <= today && owner) {
      MailApp.sendEmail(
        owner,
        'Overdue follow-up: ' + dealName,
        'Your follow-up for "' + dealName + '" (' + company +
        ') was due ' + followUp.toLocaleDateString() +
        '. Stage: ' + stage +
        '\n\nOpen the CRM: ' +
        SpreadsheetApp.getActiveSpreadsheet().getUrl()
      );
      remindersSent++;
    }
  }

  Logger.log('Sent ' + remindersSent + ' follow-up reminders');
}

Set this one to run daily at 8 AM (or whenever your team starts their day). Under Triggers, choose time-driven > Day timer > 8am to 9am.

💡

Quick tip: Google limits free accounts to 100 emails per day through MailApp. If you're sending a lot of reminders, batch them into a single digest email per person instead of one per deal.

Auto-Populating Company Data

When someone enters a Contact ID on the Deals sheet, this pulls the company name automatically so you don't have to type it twice:

function autoPopulateCompany() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var deals = ss.getSheetByName('Deals Pipeline');
  var contacts = ss.getSheetByName('Contacts');

  var contactData = contacts.getDataRange().getValues();
  var contactMap = {};

  for (var i = 1; i < contactData.length; i++) {
    contactMap[contactData[i][0]] = contactData[i][5];
  }

  var dealsData = deals.getDataRange().getValues();
  for (var j = 1; j < dealsData.length; j++) {
    var contactId = dealsData[j][1];
    if (contactId && contactMap[contactId]) {
      deals.getRange(j + 1, 4).setValue(contactMap[contactId]);
    }
  }
}

You can call this from the custom menu (coming up next) or attach it to an onEdit trigger that fires when column B of the Deals sheet changes.

Building the CRM Dashboard

Switch to your Dashboard tab. This is where formulas do the heavy lifting. No Apps Script needed here.

Pipeline Value by Stage

In cell A1, type "Pipeline Summary". Then build this table starting at A3:

A3: Stage          B3: Deal Count    C3: Total Value    D3: Weighted Value
A4: Lead           B4: =COUNTIF('Deals Pipeline'!E:E,"Lead")
                   C4: =SUMIF('Deals Pipeline'!E:E,"Lead",'Deals Pipeline'!F:F)
                   D4: =SUMIF('Deals Pipeline'!E:E,"Lead",'Deals Pipeline'!H:H)

Repeat for each stage. Or use a single QUERY formula to build the whole thing dynamically:

=QUERY('Deals Pipeline'!A:N, "SELECT E, COUNT(A), SUM(F), SUM(H) WHERE E IS NOT NULL GROUP BY E LABEL E 'Stage', COUNT(A) 'Deals', SUM(F) 'Total Value', SUM(H) 'Weighted Value'", 1)

That one formula replaces about 20 individual SUMIF/COUNTIF calls. Select the output range and insert a bar chart (Insert > Chart) for a visual pipeline breakdown.

Activity Summary Charts

Add another section below for recent activity. This formula counts activities by type for the last 30 days:

=QUERY('Activity Log'!A:G, "SELECT E, COUNT(A) WHERE D >= date '"&TEXT(TODAY()-30,"yyyy-MM-dd")&"' GROUP BY E LABEL E 'Activity Type', COUNT(A) 'Count'", 1)

Overdue Follow-Ups Alert

This one surfaces deals that need attention right now:

=QUERY('Deals Pipeline'!A:N, "SELECT C, D, E, F, J, K WHERE J < date '"&TEXT(TODAY(),"yyyy-MM-dd")&"' AND E <> 'Won' AND E <> 'Lost' ORDER BY J ASC LABEL C 'Deal', D 'Company', E 'Stage', F 'Value', J 'Follow-Up Due', K 'Owner'", 1)

Put a bold red header above this table. "OVERDUE FOLLOW-UPS" in 14pt font. Make it impossible to ignore.

This is the finishing touch that makes it feel like a real application. Add this to your Apps Script:

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('CRM Tools')
    .addItem('Add New Contact', 'showNewContactForm')
    .addItem('Add New Deal', 'showNewDealForm')
    .addSeparator()
    .addItem('Refresh Company Data', 'autoPopulateCompany')
    .addItem('Send Follow-Up Reminders Now', 'sendFollowUpReminders')
    .addSeparator()
    .addItem('Generate Pipeline Report', 'generateReport')
    .addToUi();
}

function showNewContactForm() {
  var html = HtmlService.createHtmlOutput(
    '<form id="contactForm">' +
    '<label>First Name</label><br><input name="firstName" required><br>' +
    '<label>Last Name</label><br><input name="lastName" required><br>' +
    '<label>Email</label><br><input name="email" type="email" required><br>' +
    '<label>Phone</label><br><input name="phone"><br>' +
    '<label>Company</label><br><input name="company" required><br>' +
    '<label>Role</label><br><input name="role"><br>' +
    '<label>Lead Source</label><br>' +
    '<select name="source">' +
    '<option>Referral</option><option>Website</option>' +
    '<option>Cold Outreach</option><option>Event</option>' +
    '</select><br><br>' +
    '<input type="button" value="Save Contact" ' +
    'onclick="google.script.run.withSuccessHandler(function(){' +
    'google.script.host.close()}).saveContact(this.parentNode)">' +
    '</form>')
    .setWidth(350)
    .setHeight(400);
  SpreadsheetApp.getUi().showModalDialog(html, 'New Contact');
}

function saveContact(form) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet()
    .getSheetByName('Contacts');
  var id = 'CON-' + new Date().getTime();

  sheet.appendRow([
    id,
    form.firstName,
    form.lastName,
    form.email,
    form.phone,
    form.company,
    form.role,
    form.source,
    'Active',
    new Date(),
    '',
    ''
  ]);

  SpreadsheetApp.getActiveSpreadsheet().toast(
    'Contact added: ' + form.firstName + ' ' + form.lastName,
    'Success', 3
  );
}

function showNewDealForm() {
  var html = HtmlService.createHtmlOutput(
    '<form id="dealForm">' +
    '<label>Contact ID</label><br><input name="contactId" required><br>' +
    '<label>Deal Name</label><br><input name="dealName" required><br>' +
    '<label>Deal Value ($)</label><br><input name="dealValue" type="number" required><br>' +
    '<label>Stage</label><br>' +
    '<select name="stage">' +
    '<option>Lead</option><option>Qualified</option>' +
    '<option>Proposal Sent</option><option>Negotiation</option>' +
    '</select><br>' +
    '<label>Expected Close Date</label><br><input name="closeDate" type="date"><br>' +
    '<label>Owner Email</label><br><input name="owner" type="email"><br><br>' +
    '<input type="button" value="Save Deal" ' +
    'onclick="google.script.run.withSuccessHandler(function(){' +
    'google.script.host.close()}).saveNewDeal(this.parentNode)">' +
    '</form>')
    .setWidth(350)
    .setHeight(400);
  SpreadsheetApp.getUi().showModalDialog(html, 'New Deal');
}

function saveNewDeal(form) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet()
    .getSheetByName('Deals Pipeline');
  var id = 'DEAL-' + new Date().getTime();

  sheet.appendRow([
    id,
    form.contactId,
    form.dealName,
    '',
    form.stage,
    Number(form.dealValue),
    '',
    '',
    form.closeDate ? new Date(form.closeDate) : '',
    '',
    form.owner,
    '',
    new Date(),
    new Date()
  ]);

  var newRow = sheet.getLastRow();
  sheet.getRange(newRow, 7).setFormula(
    '=IFS(E' + newRow + '="Lead",10%,E' + newRow + '="Qualified",25%,E' + newRow + '="Proposal Sent",50%,E' + newRow + '="Negotiation",75%,E' + newRow + '="Won",100%,E' + newRow + '="Lost",0%)'
  );
  sheet.getRange(newRow, 8).setFormula('=F' + newRow + '*G' + newRow);

  autoPopulateCompany();

  SpreadsheetApp.getActiveSpreadsheet().toast(
    'Deal added: ' + form.dealName, 'Success', 3
  );
}

function generateReport() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var dashboard = ss.getSheetByName('Dashboard');
  ss.setActiveSheet(dashboard);
  SpreadsheetApp.getActiveSpreadsheet().toast(
    'Dashboard refreshed. Formulas are up to date.', 'Report', 3
  );
}

Now when anyone opens the spreadsheet, they see "CRM Tools" in the menu bar right next to Help. No fumbling through sheets to find the right tab. No accidental column deletions.

For more on building interactive dashboards in Google Sheets, check out the step-by-step dashboard guide. And if you want to go deeper on the email automation side, the Gmail Apps Script guide covers triggers, templates, and batch sending in detail.

When to Upgrade to a Real CRM

This setup works great until it doesn't. Here are the signs you've outgrown it:

You need role-based permissions. Google Sheets sharing is all-or-nothing. If your sales reps shouldn't see each other's pipeline numbers, you need a real CRM. You can hack around this with IMPORTRANGE and separate sheets per user, but it gets ugly fast.

Your contact list passes 5,000 rows. Apps Script starts to slow down noticeably around this point. QUERY formulas get sluggish. The sheet takes longer to open. It still works, but the experience degrades.

You need a mobile app. Google Sheets on mobile is functional but painful for data entry. If your team logs calls from the field, they'll want something purpose-built.

You need integrations beyond Google Workspace. Apps Script plays nicely with Gmail, Calendar, and Drive. But if you need Slack integration, Stripe payment tracking, or LinkedIn enrichment, you're writing a lot of custom code. At that point, something like Pipedrive or even the HubSpot free tier will save you time.

Multiple people edit deals simultaneously. Google Sheets handles concurrent editing for simple text cells, but formula recalculation and script triggers can collide. If two reps update stages at the same moment, you might lose an activity log entry.

So is this CRM a stepping stone? For most small teams, yes. And that's fine. You'll get months (or years) of value out of it before you hit those limits.

Download the Free Template

I've put together a Google Sheet template with everything from this tutorial pre-built. All four tabs, the Apps Script automation, the dashboard formulas, and the custom menu. You can copy it and start adding contacts in about 30 seconds.

💡

To get the template: Make a copy of the CRM template from the Diazovate resources page. Go to File > Make a copy to get your own editable version. The Apps Script code copies over with the sheet automatically.

After you copy it:

Open Extensions > Apps Script and review the code. Update the notification email address in notifyOnBigStageChanges to your own.
Run any function once manually (click the play button) so Google prompts you for permissions. You'll need to authorize the script to send emails and edit the spreadsheet.
Set up your time-driven triggers: daily for follow-up reminders, every 5 minutes for stage change notifications.
Customize the dropdown values on the Contacts and Deals sheets to match your sales process.

That's it. You've got a free CRM in Google Sheets with Apps Script automation that handles contacts, deals, activity logging, reminders, and reporting. No monthly fee. No vendor lock-in. And when you eventually outgrow it, your data is already in a format you can export anywhere.

Why Build a CRM in Google Sheets Instead of Paying for One

The honest answer? Because you'll actually use it.

Google Sheets gives you a few things paid CRMs don't:

Zero cost. Not "free tier with limits." Actually free.
Full control over your data structure. Add a column when you need one. Rename a stage. No admin panel, no permission requests.
Your team already knows the interface. There's no training period. Everyone has used a spreadsheet.
Apps Script turns it into a real app. Custom menus, automated emails, triggered workflows. You get 80% of CRM automation with code you control.

The tradeoff is real though. Once you're past about 5,000 contacts or need role-based access control, you'll outgrow this. More on that later.

What Our Google Sheets CRM Will Do

By the end of this tutorial, you'll have a spreadsheet that handles:

Contact management with company info, deal value, and interaction history
A deals pipeline with stages you define, expected close dates, and weighted revenue
Automatic activity logging that timestamps every stage change
Email reminders for overdue follow-ups (sent automatically, no clicking required)
A dashboard showing pipeline value by stage and overdue tasks
A custom menu so your team doesn't need to touch the script editor

I've put together a free template you can copy at the end. But I'd recommend building it step by step first so you understand what each piece does.

Setting Up the CRM Spreadsheet Structure

Create a new Google Sheet. Name it something you'll actually find later ("CRM" works, "Untitled spreadsheet" does not). You need four tabs.

Contacts Sheet

This is your source of truth for every person and company you're tracking. Here are the columns you need:

Column A: Contact ID -- Auto-generated (we'll script this)
Column B: First Name
Column C: Last Name
Column D: Email
Column E: Phone
Column F: Company
Column G: Role/Title
Column H: Lead Source -- Where they came from (referral, website, cold outreach)
Column I: Status -- Active, Inactive, or Lost
Column J: Date Added -- Auto-populated by script
Column K: Last Contacted -- Updated automatically from the activity log
Column L: Notes -- Free text for context

Deals Pipeline Sheet

This is where money lives. Each row is an opportunity tied to a contact.

Column A: Deal ID -- Auto-generated
Column B: Contact ID -- Links to the Contacts sheet
Column C: Deal Name -- Short description ("Website redesign for Acme")
Column D: Company -- Auto-pulled from Contact ID
Column E: Stage -- Dropdown: Lead, Qualified, Proposal Sent, Negotiation, Won, Lost
Column F: Deal Value -- Dollar amount
Column G: Probability -- Auto-set based on stage
Column H: Weighted Value -- =F2*G2
Column I: Expected Close -- Date
Column J: Next Follow-Up -- Date
Column K: Owner -- Who on your team owns this deal
Column L: Notes
Column M: Created Date -- Auto-populated
Column N: Last Updated -- Auto-populated on edit

For the Probability column, use a formula that maps stages to percentages:

=IFS(E2="Lead",10%,E2="Qualified",25%,E2="Proposal Sent",50%,E2="Negotiation",75%,E2="Won",100%,E2="Lost",0%)

Copy that down the column. Weighted Value (H) then becomes =F2*G2, giving you a realistic pipeline forecast instead of the "add up every deal and pretend they'll all close" number.

Activity Log Sheet

Every interaction gets recorded here. This is what separates a CRM from a contact list.

Column A: Log ID -- Auto-generated
Column B: Contact ID -- Links to the Contacts sheet
Column C: Deal ID -- Optional, links to the Deals sheet
Column D: Date -- Auto-populated
Column E: Type -- Dropdown: Email, Call, Meeting, Note, Stage Change
Column F: Description -- What happened
Column G: Logged By -- Who recorded this

Dashboard Sheet

We'll build this out with formulas and charts in a later section. For now, create the tab and leave it empty.

Adding Apps Script Automation

Here's where the spreadsheet becomes a CRM. Open the script editor: Extensions > Apps Script. Delete whatever's in the default Code.gs file and replace it with the functions below.

I'm breaking this into logical chunks. You can put them all in one file or split them across multiple .gs files in the project.

Auto-Logging Contact Interactions

This function watches for stage changes on the Deals sheet and automatically creates an entry in the Activity Log:

function onEdit(e) {
  var sheet = e.range.getSheet();
  var sheetName = sheet.getName();

  if (sheetName === 'Deals Pipeline' && e.range.getColumn() === 5) {
    logStageChange(e);
  }

  if (sheetName === 'Deals Pipeline' || sheetName === 'Contacts') {
    updateLastModified(e);
  }
}

function logStageChange(e) {
  var row = e.range.getRow();
  var sheet = e.range.getSheet();
  var contactId = sheet.getRange(row, 2).getValue();
  var dealId = sheet.getRange(row, 1).getValue();
  var oldValue = e.oldValue || '(new)';
  var newValue = e.value;

  var log = SpreadsheetApp.getActiveSpreadsheet()
    .getSheetByName('Activity Log');
  var logId = 'LOG-' + new Date().getTime();

  log.appendRow([
    logId,
    contactId,
    dealId,
    new Date(),
    'Stage Change',
    'Stage changed from ' + oldValue + ' to ' + newValue,
    Session.getActiveUser().getEmail()
  ]);
}

function updateLastModified(e) {
  var sheet = e.range.getSheet();
  var row = e.range.getRow();
  if (row === 1) return;

  if (sheet.getName() === 'Deals Pipeline') {
    sheet.getRange(row, 14).setValue(new Date());
  }
}

Deal Stage Change Notifications

Want a Slack-style notification when a deal moves to "Won" or "Lost"? This function sends an email to whoever you specify:

function notifyOnBigStageChanges() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet()
    .getSheetByName('Deals Pipeline');
  var data = sheet.getDataRange().getValues();
  var notifyEmail = 'you@yourcompany.com';

  for (var i = 1; i < data.length; i++) {
    var stage = data[i][4];
    var dealName = data[i][2];
    var value = data[i][5];
    var lastUpdated = data[i][13];

    if (!lastUpdated) continue;

    var updatedDate = new Date(lastUpdated);
    var fiveMinAgo = new Date(new Date().getTime() - 5 * 60000);

    if (updatedDate > fiveMinAgo) {
      if (stage === 'Won' || stage === 'Lost') {
        MailApp.sendEmail(
          notifyEmail,
          'Deal ' + stage + ': ' + dealName,
          'Deal "' + dealName + '" (' + formatCurrency(value) +
          ') just moved to ' + stage + '.'
        );
      }
    }
  }
}

function formatCurrency(amount) {
  return '$' + Number(amount).toLocaleString('en-US',
    {minimumFractionDigits: 0, maximumFractionDigits: 0});
}

Follow-Up Reminder Emails

This is the automation that actually saves deals. It checks the "Next Follow-Up" column and emails the deal owner when something is overdue:

function sendFollowUpReminders() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet()
    .getSheetByName('Deals Pipeline');
  var data = sheet.getDataRange().getValues();
  var today = new Date();
  today.setHours(0, 0, 0, 0);

  var remindersSent = 0;

  for (var i = 1; i < data.length; i++) {
    var followUpDate = data[i][9];
    var stage = data[i][4];
    var owner = data[i][10];
    var dealName = data[i][2];
    var company = data[i][3];

    if (!followUpDate || stage === 'Won' || stage === 'Lost') continue;

    var followUp = new Date(followUpDate);
    followUp.setHours(0, 0, 0, 0);

    if (followUp <= today && owner) {
      MailApp.sendEmail(
        owner,
        'Overdue follow-up: ' + dealName,
        'Your follow-up for "' + dealName + '" (' + company +
        ') was due ' + followUp.toLocaleDateString() +
        '. Stage: ' + stage +
        '\n\nOpen the CRM: ' +
        SpreadsheetApp.getActiveSpreadsheet().getUrl()
      );
      remindersSent++;
    }
  }

  Logger.log('Sent ' + remindersSent + ' follow-up reminders');
}

Set this one to run daily at 8 AM (or whenever your team starts their day). Under Triggers, choose time-driven > Day timer > 8am to 9am.

💡

Quick tip: Google limits free accounts to 100 emails per day through MailApp. If you're sending a lot of reminders, batch them into a single digest email per person instead of one per deal.

Auto-Populating Company Data

When someone enters a Contact ID on the Deals sheet, this pulls the company name automatically so you don't have to type it twice:

function autoPopulateCompany() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var deals = ss.getSheetByName('Deals Pipeline');
  var contacts = ss.getSheetByName('Contacts');

  var contactData = contacts.getDataRange().getValues();
  var contactMap = {};

  for (var i = 1; i < contactData.length; i++) {
    contactMap[contactData[i][0]] = contactData[i][5];
  }

  var dealsData = deals.getDataRange().getValues();
  for (var j = 1; j < dealsData.length; j++) {
    var contactId = dealsData[j][1];
    if (contactId && contactMap[contactId]) {
      deals.getRange(j + 1, 4).setValue(contactMap[contactId]);
    }
  }
}

You can call this from the custom menu (coming up next) or attach it to an onEdit trigger that fires when column B of the Deals sheet changes.

Building the CRM Dashboard

Switch to your Dashboard tab. This is where formulas do the heavy lifting. No Apps Script needed here.

Pipeline Value by Stage

In cell A1, type "Pipeline Summary". Then build this table starting at A3:

A3: Stage          B3: Deal Count    C3: Total Value    D3: Weighted Value
A4: Lead           B4: =COUNTIF('Deals Pipeline'!E:E,"Lead")
                   C4: =SUMIF('Deals Pipeline'!E:E,"Lead",'Deals Pipeline'!F:F)
                   D4: =SUMIF('Deals Pipeline'!E:E,"Lead",'Deals Pipeline'!H:H)

Repeat for each stage. Or use a single QUERY formula to build the whole thing dynamically:

=QUERY('Deals Pipeline'!A:N, "SELECT E, COUNT(A), SUM(F), SUM(H) WHERE E IS NOT NULL GROUP BY E LABEL E 'Stage', COUNT(A) 'Deals', SUM(F) 'Total Value', SUM(H) 'Weighted Value'", 1)

That one formula replaces about 20 individual SUMIF/COUNTIF calls. Select the output range and insert a bar chart (Insert > Chart) for a visual pipeline breakdown.

Activity Summary Charts

Add another section below for recent activity. This formula counts activities by type for the last 30 days:

=QUERY('Activity Log'!A:G, "SELECT E, COUNT(A) WHERE D >= date '"&TEXT(TODAY()-30,"yyyy-MM-dd")&"' GROUP BY E LABEL E 'Activity Type', COUNT(A) 'Count'", 1)

Overdue Follow-Ups Alert

This one surfaces deals that need attention right now:

=QUERY('Deals Pipeline'!A:N, "SELECT C, D, E, F, J, K WHERE J < date '"&TEXT(TODAY(),"yyyy-MM-dd")&"' AND E <> 'Won' AND E <> 'Lost' ORDER BY J ASC LABEL C 'Deal', D 'Company', E 'Stage', F 'Value', J 'Follow-Up Due', K 'Owner'", 1)

Put a bold red header above this table. "OVERDUE FOLLOW-UPS" in 14pt font. Make it impossible to ignore.

This is the finishing touch that makes it feel like a real application. Add this to your Apps Script:

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('CRM Tools')
    .addItem('Add New Contact', 'showNewContactForm')
    .addItem('Add New Deal', 'showNewDealForm')
    .addSeparator()
    .addItem('Refresh Company Data', 'autoPopulateCompany')
    .addItem('Send Follow-Up Reminders Now', 'sendFollowUpReminders')
    .addSeparator()
    .addItem('Generate Pipeline Report', 'generateReport')
    .addToUi();
}

function showNewContactForm() {
  var html = HtmlService.createHtmlOutput(
    '<form id="contactForm">' +
    '<label>First Name</label><br><input name="firstName" required><br>' +
    '<label>Last Name</label><br><input name="lastName" required><br>' +
    '<label>Email</label><br><input name="email" type="email" required><br>' +
    '<label>Phone</label><br><input name="phone"><br>' +
    '<label>Company</label><br><input name="company" required><br>' +
    '<label>Role</label><br><input name="role"><br>' +
    '<label>Lead Source</label><br>' +
    '<select name="source">' +
    '<option>Referral</option><option>Website</option>' +
    '<option>Cold Outreach</option><option>Event</option>' +
    '</select><br><br>' +
    '<input type="button" value="Save Contact" ' +
    'onclick="google.script.run.withSuccessHandler(function(){' +
    'google.script.host.close()}).saveContact(this.parentNode)">' +
    '</form>')
    .setWidth(350)
    .setHeight(400);
  SpreadsheetApp.getUi().showModalDialog(html, 'New Contact');
}

function saveContact(form) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet()
    .getSheetByName('Contacts');
  var id = 'CON-' + new Date().getTime();

  sheet.appendRow([
    id,
    form.firstName,
    form.lastName,
    form.email,
    form.phone,
    form.company,
    form.role,
    form.source,
    'Active',
    new Date(),
    '',
    ''
  ]);

  SpreadsheetApp.getActiveSpreadsheet().toast(
    'Contact added: ' + form.firstName + ' ' + form.lastName,
    'Success', 3
  );
}

function showNewDealForm() {
  var html = HtmlService.createHtmlOutput(
    '<form id="dealForm">' +
    '<label>Contact ID</label><br><input name="contactId" required><br>' +
    '<label>Deal Name</label><br><input name="dealName" required><br>' +
    '<label>Deal Value ($)</label><br><input name="dealValue" type="number" required><br>' +
    '<label>Stage</label><br>' +
    '<select name="stage">' +
    '<option>Lead</option><option>Qualified</option>' +
    '<option>Proposal Sent</option><option>Negotiation</option>' +
    '</select><br>' +
    '<label>Expected Close Date</label><br><input name="closeDate" type="date"><br>' +
    '<label>Owner Email</label><br><input name="owner" type="email"><br><br>' +
    '<input type="button" value="Save Deal" ' +
    'onclick="google.script.run.withSuccessHandler(function(){' +
    'google.script.host.close()}).saveNewDeal(this.parentNode)">' +
    '</form>')
    .setWidth(350)
    .setHeight(400);
  SpreadsheetApp.getUi().showModalDialog(html, 'New Deal');
}

function saveNewDeal(form) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet()
    .getSheetByName('Deals Pipeline');
  var id = 'DEAL-' + new Date().getTime();

  sheet.appendRow([
    id,
    form.contactId,
    form.dealName,
    '',
    form.stage,
    Number(form.dealValue),
    '',
    '',
    form.closeDate ? new Date(form.closeDate) : '',
    '',
    form.owner,
    '',
    new Date(),
    new Date()
  ]);

  var newRow = sheet.getLastRow();
  sheet.getRange(newRow, 7).setFormula(
    '=IFS(E' + newRow + '="Lead",10%,E' + newRow + '="Qualified",25%,E' + newRow + '="Proposal Sent",50%,E' + newRow + '="Negotiation",75%,E' + newRow + '="Won",100%,E' + newRow + '="Lost",0%)'
  );
  sheet.getRange(newRow, 8).setFormula('=F' + newRow + '*G' + newRow);

  autoPopulateCompany();

  SpreadsheetApp.getActiveSpreadsheet().toast(
    'Deal added: ' + form.dealName, 'Success', 3
  );
}

function generateReport() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var dashboard = ss.getSheetByName('Dashboard');
  ss.setActiveSheet(dashboard);
  SpreadsheetApp.getActiveSpreadsheet().toast(
    'Dashboard refreshed. Formulas are up to date.', 'Report', 3
  );
}

Now when anyone opens the spreadsheet, they see "CRM Tools" in the menu bar right next to Help. No fumbling through sheets to find the right tab. No accidental column deletions.

When to Upgrade to a Real CRM

This setup works great until it doesn't. Here are the signs you've outgrown it:

You need a mobile app. Google Sheets on mobile is functional but painful for data entry. If your team logs calls from the field, they'll want something purpose-built.

So is this CRM a stepping stone? For most small teams, yes. And that's fine. You'll get months (or years) of value out of it before you hit those limits.

Download the Free Template

💡

After you copy it:

Open Extensions > Apps Script and review the code. Update the notification email address in notifyOnBigStageChanges to your own.
Run any function once manually (click the play button) so Google prompts you for permissions. You'll need to authorize the script to send emails and edit the spreadsheet.
Set up your time-driven triggers: daily for follow-up reminders, every 5 minutes for stage change notifications.
Customize the dropdown values on the Contacts and Deals sheets to match your sales process.

← Back to Blog

Build a Simple CRM in Google Sheets With Apps Script (Free Template Included)

Why Build a CRM in Google Sheets Instead of Paying for One

What Our Google Sheets CRM Will Do

Setting Up the CRM Spreadsheet Structure

Contacts Sheet

Deals Pipeline Sheet

Activity Log Sheet

Dashboard Sheet

Adding Apps Script Automation

Auto-Logging Contact Interactions

Deal Stage Change Notifications

Follow-Up Reminder Emails

Auto-Populating Company Data

Building the CRM Dashboard

Pipeline Value by Stage

Activity Summary Charts

Overdue Follow-Ups Alert

Adding a Custom Menu for Easy Access

When to Upgrade to a Real CRM

Download the Free Template

Why Build a CRM in Google Sheets Instead of Paying for One

What Our Google Sheets CRM Will Do

Setting Up the CRM Spreadsheet Structure

Contacts Sheet

Deals Pipeline Sheet

Activity Log Sheet

Dashboard Sheet

Adding Apps Script Automation

Auto-Logging Contact Interactions

Deal Stage Change Notifications

Follow-Up Reminder Emails

Auto-Populating Company Data

Building the CRM Dashboard

Pipeline Value by Stage

Activity Summary Charts

Overdue Follow-Ups Alert

Adding a Custom Menu for Easy Access

When to Upgrade to a Real CRM

Download the Free Template