Back to Blog

Google Sheets CRM: Free Template, Formulas and Where It Stops

Tabs, columns, dropdowns and formulas for a spreadsheet CRM that one to three people can run in Google Sheets, a short Apps Script that emails you each morning, and a clean path into a CRM.

Benjamin WagnerBenjamin Wagnerby Benjamin Wagner
Google SheetsCRM TemplateSpreadsheet CRMFree CRM

Yes, Google Sheets works as a CRM for one to three people. Give each kind of record its own tab (contacts, deals, activities), make every fixed value a dropdown, and let a Dashboard tab count and sum the rest with QUERY and COUNTIFS. Google itself does not sell a CRM: its Workspace Marketplace lists third-party ones such as Copper, Pipedrive, Streak and Zoho CRM, and Gmail, Forms and Apps Script are the parts you wire together yourself.

Customermates publishes this guide and is the CRM in the migration example at the end. Everything before that section needs only a Google account.

At a glance

What you build
Five tabs: Lists, Contacts, Deals, Activities and a Dashboard with live follow-up and pipeline tables.
What Google adds
Form responses that land in the sheet by themselves, change alerts by email, and Apps Script for a morning email of past next steps.
Who it suits
One to three people, one sales process, conversations mostly by email.
Where it breaks
Access is per file, not per row. Links between tabs are plain text, and LinkedIn or WhatsApp threads never reach the sheet unless someone types them in.
How to move
Download as .xlsx and import one tab at a time: companies, then contacts, then deals.

Use the contact list template for a small, shared directory with a named owner and next step, before you decide whether to move it into a CRM.

Four setup rules for the sheet

  1. One header row, one row per record. No merged cells, no blank rows, no subtotals between records.
  2. Turn each data tab into a table. Select the range and choose Format › Convert to table. Google's tables guide lets you set a type per column: Drop-down, Date, Currency, People chip and others. A Date column then flags "next Tuesday" with a warning, which matters once formulas compare dates.
  3. Make every fixed value a dropdown. Insert › Drop-down, or Data › Data validation with "Drop-down (from a range)" pointing at the Lists tab. According to Google's dropdown help, invalid entries are rejected by default. Keep it that way: "Proposal" and "proposal sent" are two stages to every formula.
  4. Link by name. A contact's company is the company's exact name, a deal's contact the person's full name. Names are readable, and CRM imports, Customermates included, match links by name.

The template, tab by tab

Lists

RangeValues
A: Stage, B: ProbabilityProspecting 10%, Qualification 20%, Demo 40%, Proposal 60%, Negotiation 80%, Won 100%, Lost 0%
D: Contact typeLead, Customer, Partner, Former customer
E: SourceReferral, Website, LinkedIn, Event, Web form
F: Activity typeCall, Email, Meeting, LinkedIn, WhatsApp
G: TeamFull name of everyone who owns records

These stages and probabilities are an example. Customermates has no preset stages: create the same options on the Stage field of your deals list in Data model (or let AI setup build them in the cloud), and the Stage column moves over later without renaming. Replace the percentages once you have a few months of won and lost deals to count.

Contacts

Columns A to J: First name, Last name, Company, Email, Phone, LinkedIn URL, Contact type, Source, Owner, Notes. Keep first and last name apart; most CRMs need both to create a person. If you sell to companies, add a Companies tab with Company, Website, Industry and City, and point the Company column's dropdown at it.

Deals

ColumnContentExample
A DealTextBrookfield Architects security review
B CompanyDropdownBrookfield Architects
C ContactFull nameSarah Okafor
D ValueNumber18000
E StageDropdown from Lists!AProposal
F ProbabilityFormula60%
G WeightedFormula10800
H OwnerDropdown from Lists!GAnna Berg
I Next stepOne sentenceSend revised scope
J Next step dateDate2025-12-04
K Closed onDate, filled when Won or Lost

Put these in row 2 and fill them down:

F2  =IFERROR(VLOOKUP(E2,Lists!A:B,2,FALSE),0)
G2  =D2*F2

Activities

One row per conversation that mattered: Date, Contact, Deal, Type, Owner, Summary and Next step. It is the only tab that answers "what did we last tell them?", so fill it right after the call or nobody will.

Dashboard

Four numbers:

Open pipeline      =SUMIFS(Deals!D2:D,Deals!E2:E,"<>Won",Deals!E2:E,"<>Lost",Deals!A2:A,"<>")
Weighted pipeline  =SUMIFS(Deals!G2:G,Deals!E2:E,"<>Won",Deals!E2:E,"<>Lost",Deals!A2:A,"<>")
Win rate           =COUNTIF(Deals!E2:E,"Won")/MAX(1,COUNTIF(Deals!E2:E,"Won")+COUNTIF(Deals!E2:E,"Lost"))
Past next steps    =COUNTIFS(Deals!J2:J,"<"&TODAY(),Deals!E2:E,"<>Won",Deals!E2:E,"<>Lost")

Two functions make the Google version better than the Excel one. QUERY builds the pipeline by stage as a table that updates itself:

=QUERY(Deals!A1:K,"select E, count(A), sum(D) where E is not null and E <> 'Won' and E <> 'Lost' group by E label count(A) 'Deals', sum(D) 'Value'",1)

And FILTER gives you a live follow-up list, every open deal whose next step date has passed:

=FILTER(Deals!A2:J,Deals!J2:J<>"",TODAY()>Deals!J2:J,Deals!E2:E<>"Won",Deals!E2:E<>"Lost")

Put each on its own block of the Dashboard and leave room below it, because both spill downwards.

What Google adds around the sheet

Lead capture with Google Forms. Link a form to the spreadsheet under Responses › Select destination for responses, and Google's Forms help says each new response is added automatically. Leave the responses tab as it is and copy qualified leads into Contacts by hand. Editing or reordering its columns is the fastest way to break the form link.

Change alerts. Tools › Notification settings › Edit notifications sends an email when anyone changes the file, right away or as a daily digest. Per Google's notification help, it covers other people's edits only. It tells you that Anna edited Deals. It does not tell you that a next step date passed.

A morning email from Apps Script. For that, open Extensions › Apps Script and paste:

function mailPastNextSteps() {
  const rows = SpreadsheetApp.getActive().getSheetByName('Deals').getDataRange().getValues();
  const today = new Date(); today.setHours(0, 0, 0, 0);
  const due = rows.slice(1).filter(r =>
    r[9] instanceof Date && r[9] < today && r[4] !== 'Won' && r[4] !== 'Lost');
  if (due.length) {
    MailApp.sendEmail(Session.getEffectiveUser().getEmail(),
      due.length + ' past next steps',
      due.map(r => r[0] + ': ' + r[8]).join('\n'));
  }
}

Then add a trigger (clock icon › Add trigger), event source "Time-driven", type "Day timer", 7 to 8 am. Column J is index 9 because the array starts at 0. Google's Apps Script quotas cap a free Gmail account at 100 email recipients and 90 minutes of trigger runtime a day; Workspace accounts get 1,500 recipients and 6 hours. One email a day is far below both.

Filter views instead of filters. A normal filter changes the view for everyone with access. Data › Create filter view saves a filter that changes only your own view, as Google's filter help explains. Give each person a "My open deals" filter view on Owner and Stage.

Where a Google Sheets CRM stops working

Size is rarely the problem. Google allows up to 20 million cells per spreadsheet in its Drive file limits, far more than a small team fills. These limits show up first:

  • Everyone sees everything. Sharing works per file. Protected ranges stop edits, but Google's protection help says plainly that people can still copy and export a protected spreadsheet, and that protection "shouldn't be used as a security measure". A freelancer you add to update two deals can read the whole client list.
  • Links are text. Rename a company and every contact and deal that used the old name points nowhere until you run Find and replace.
  • Conversations stay elsewhere. Gmail threads, LinkedIn messages and WhatsApp chats reach the sheet only when someone summarizes them in Activities.
  • Personal data multiplies. Every download, copy and "make a copy for me" is another place a person's data lives. When someone uses their GDPR rights to see their data (Article 15) or have it erased (Article 17), you have to find all of them.

Stay in Sheets, add a tool, or switch?

If this describes youDo this
One person, a few hundred contacts, mostly emailKeep the template above and the Apps Script email
Two or three people, one shared deal list, everyone may see everythingKeep the sheet, add filter views per person and notification rules
You want CRM features but your team refuses to leave SheetsLook at the CRM add-ons in the Google Workspace Marketplace, which work inside Sheets or Gmail
Several people share deals, follow-ups slip, or deals move in LinkedIn and WhatsApp threadsMove to a CRM, starting with the import below
You run Excel or Microsoft 365 rather than GoogleUse the Excel CRM template, which has the same layout plus a weekly tracker per salesperson

A simple rule of thumb: once two people edit the same deals every week and one follow-up has slipped, move. Before that, the sheet is fine.

Moving a Google Sheets CRM into Customermates

Customermates imports Excel workbooks (.xlsx) and CSV files, one list at a time, so a Google Sheet goes through a download first. There is no live sync with Google Sheets. The import documentation has the full rules, and the Excel template guide maps every column of this layout to a field. A sheet only follows up when someone remembers to open it. In Customermates, each client's conversations sit on the client record (on Customermates Cloud from Pro), Mate or your own AI agent drafts the reply and the next step, and the next-step date feeds your dashboard instead of a filter you have to check.

  1. Clean the tabs

    Use Data › Data cleanup › Remove duplicates on Contacts and Deals, and fix any value that a dropdown flags. Every stage and type should be exactly one of the Lists values.
  2. Create the custom fields

    In Customermates, open the list in Data model and choose Add, then Field. For each dropdown column (Contact type, Source, Industry), add a Single choice field with option labels that match your Lists tab exactly. Use Date fields for Next step date and Closed on. The deal Stage field already exists with the stages above.
  3. Download the workbook

    Choose File › Download › Microsoft Excel (.xlsx). The wizard asks which tab to import, so the same file serves every import.
  4. Import in dependency order

    Companies into Organizations, then Contacts, then Deals. On each list, open More actions, choose Add from file, and match each column to a field, for example email addresses to the contact's Contact details field. Links are matched by name, ignoring case, so a company must exist before a contact can point to it. Owner goes into Assigned, matched by the team member's full name.
  5. Test with ten rows

    The check lists every problem row before anything is added, and you can skip those rows. An import has no undo, so try a small file first. Bring only open next steps from Activities over as tasks; older history can go into a deal's Notes or stay in the archived sheet.

Deal values work differently: in Customermates a deal's value is the sum of its line items, each a service times quantity. The Excel guide's import steps show how to bring your price list in first.

What replaces the Dashboard and the Apps Script

  • QUERY by stage becomes the Deals list as a board grouped by Stage, with total and weighted value per column, shown in the pipeline view.
  • The FILTER follow-up list becomes a saved view of deals filtered on Next step date, sorted oldest first. It can be shared with the team.
  • The morning email can become a routine in Customermates Cloud. Customermates has no reminders or notifications, but a routine set to run daily at 08:00 can follow an instruction like this one, using hosted AI credits:

A routine runs as its owner, and Mate sends messages when told to, so the last sentence matters. Starter includes 1 routine per user and Pro 5; the routines documentation has the run limits.

  • The Activities tab gives way, on Pro and higher, to the unified inbox, where Gmail, Outlook, IMAP, LinkedIn, WhatsApp, Instagram and Telegram threads sit with the contact. Starter has no inbox.

If you still want a reporting copy in Google Sheets, webhooks can send record changes to a separately run n8n instance, which writes them into a sheet.

What it costs, and when Customermates is the wrong step

TeamPlanPer month
3 people, CRM without inboxStarter, €12 per user€36
3 people, with the unified inboxPro, €29 per user€87
Your own serverSelf-hosted AGPL coreNo licence fee, plus hosting; REST, webhooks and MCP, but no inbox and no Mate

Billing is monthly, and the hosted trial runs 14 days on Pro with no credit card: start your trial and import one tab to see your own deals there. Each paid hosted plan includes monthly hosted AI credits per active user; see pricing.

Customermates is the wrong step if your process lives in formulas: it has no formula fields, so custom calculations stay in a spreadsheet through the export. It is also wrong if you need a phone dialer, email campaigns or a native phone app. Customermates has none of those, so look for a CRM that ships them. And if the real requirement is "stay inside Google Sheets", a Marketplace add-on fits better than any separate CRM.

FAQ

Can you use Google Sheets as a CRM?

Yes, for one to three people with one sales process. The five-tab layout above covers contacts, deals, activities and a dashboard, and Apps Script can email you past next steps each morning. It cannot restrict who sees which rows, keep links intact when a name changes, or pull in conversations from Gmail, LinkedIn or WhatsApp.

Does Google have a CRM tool?

No. Google Workspace has Sheets, Gmail, Contacts and Forms, but no CRM of its own. The Sales & CRM category of the Workspace Marketplace lists third-party CRMs that connect to Google, among them Copper, Pipedrive, Streak and Zoho CRM.

Is there a free CRM template for Google Sheets?

The layout on this page is free: copy the headers into a blank spreadsheet, add the dropdowns and paste the formulas. If you download a template elsewhere, check that it keeps one record per row, contacts apart from companies, and no merged cells. A template that fails those checks is hard to import into a CRM later.

How many contacts can a Google Sheets CRM handle?

A spreadsheet holds up to 20 million cells, according to Google's file limits. The practical limit is people, not rows: shared access and slipped follow-ups become a problem long before the file fills up. Customermates currently reads up to 5,000 rows per import file, so split larger tabs.

Can I connect a Google Sheets list to a CRM?

Some CRMs ship a Sheets add-on; the Workspace Marketplace lists Copper CRM for Google Sheets, for example. Customermates does not: it imports Excel and CSV files and exports .xlsx files, one list at a time, with no live sync. You can export a list, edit it in Sheets, download it as .xlsx and import it again as Existing records; its Record ID column updates the existing records instead of creating duplicates.

Can you create a CRM in Excel instead?

Yes. The Excel CRM template uses the same structure with Excel tables, XLOOKUP and a weekly tracker per salesperson. Pick the tool your team already shares files in.

Google details checked on 8 October 2026 against Google's help pages on tables, dropdowns, QUERY, Forms responses, notifications, filter views, protected ranges, Drive file limits, the Apps Script quotas and the Workspace Marketplace.

Start from one description

One description. A workspace your agent built.

This is what setup builds from one sentence. Sample data, the real product.

  1. Describe your business

    One sentence about your team, your clients and how you sell.

  2. Mate builds it

    It asks a few questions, then creates the lists, pipeline, dashboard and playbook.

  3. Work in it

    Connect LinkedIn and email. Click through a finished example below.

Your description to Mate

We're a four-person strategy consultancy. Track clients, engagements and proposals, and help us follow up with prospects on LinkedIn.

Widgets built from the workspace's own lists and fields

Customermates for consultants

Every client conversation from email, LinkedIn and WhatsApp in one place. Mate sets up your workspace and drafts the follow-up, you review and send. Works with Claude, ChatGPT or any agent that speaks MCP.

Customermates

Follow up on every client conversation

Start with one channel and one client on Customermates Cloud, or self-host the open-source core.

✓ No credit card required ✓ First 14 days free