How to Build a Simple CRM in Google Sheets (and Automate It)

Running a growing business means you’re constantly juggling leads, contacts, and follow‑up tasks. A full‑featured CRM can be powerful, but many teams start with something familiar—Google Sheets. Paired with a no‑code automation platform such as Automate Anything, a spreadsheet can become a live, collaborative CRM that tracks interactions, sends reminders, and even updates other tools without writing a single line of code.

Below is a step‑by‑step guide to create a functional CRM in Google Sheets, add useful automation, and keep the system tidy as your data expands.

1. Set Up the Core Spreadsheet

1.1 Create the master sheet

1. Open a new Google Sheet and rename the tab to Leads.
2. In row 1, add column headings that cover the information you need to capture. A practical starter set includes:

1.2 Add data validation

• Status – Highlight column G, then Data → Data validation → Criteria: List of items and type New,Contacted,Qualified,Won,Lost. This standardises the status values.
• Owner – Use the same approach for column H, listing the names of your sales or ops team members.
• Follow‑up Date – Set column K to Date format so Google Sheets recognises it as a date value.

1.3 Auto‑generate Lead IDs

In cell A2, paste the formula:

=IF(B2="","", "L-" & TEXT(ROW()-1,"0000"))

Drag the formula down a few rows. The ID will populate once you enter a company name, giving each record a consistent reference.

2. Make the CRM Collaborative

Google Sheets is built for real‑time collaboration, but a few tweaks prevent accidental overwrites:

• Protect the header row – Right‑click row 1 → Protect range → set permissions to “Only you”.
• Limit editing rights – If you share the sheet with a larger group, give them Comment access and create a separate “Data Entry” sheet where they can submit new leads via a simple form (see next section).

3. Capture New Leads with Google Forms

A Google Form provides a clean front‑end for anyone to submit lead information without seeing the whole spreadsheet.

1. In the Sheet, click Tools → Create a form.
2. Replicate the fields you need (Company, Contact Name, Email, Phone, Source).
3. In the form’s Responses tab, choose Select response destination → Existing spreadsheet and pick your CRM sheet.
4. The form will automatically add a new row to Leads; the Lead ID and validation rules will apply instantly.

4. Add Automation with Automate Anything

Now that the data lives in a sheet, you can connect it to other tools and set up reminders—without touching code.

4.1 Trigger a follow‑up email

1. In Automate Anything, start a new workflow and choose Google Sheets – New Row as the trigger. Point it to the Leads sheet and filter on Status = "New".
2. Add an action Gmail – Send Email (or your preferred email service). Map the fields:
   • To: {{Email}}
   • Subject: “Thanks for your interest, {{Contact Name}}”
   • Body: Include a brief intro and a link to schedule a call.
3. Save and turn on the workflow. Every time a lead is captured, the system sends a personalized acknowledgment automatically.

4.2 Create calendar reminders for follow‑ups

1. Add a second trigger: Google Sheets – Updated Row when Follow‑up Date changes.
2. Use a Date/Time filter to ensure the date is today or in the future.
3. Add an action Google Calendar – Create Event:
   • Title: Follow‑up: {{Company}} – {{Next Action}}
   • Start time: {{Follow‑up Date}} at a time you prefer.
   • Attendees: {{Owner}} (use the email address of the owner).
4. Turn the workflow on. The assigned team member gets a calendar entry the moment a follow‑up date is set.

4.3 Sync qualified leads to a marketing platform

If you use a marketing automation tool that supports webhooks, you can push leads that reach the “Qualified” stage.

1. Trigger: Google Sheets – Updated Row where Status = "Qualified".
2. Action: Webhook – POST to the marketing platform’s endpoint, passing JSON payload:

   {
     "lead_id": "{{Lead ID}}",
     "company": "{{Company}}",
     "email": "{{Email}}",
     "owner": "{{Owner}}"
   }
   

3. The lead now appears in your email nurture flow automatically.

5. Keep the Data Clean Over Time

A simple sheet can become unwieldy if old rows linger. Here are two low‑effort practices:

• Archive quarterly – Use Automate Anything to copy rows where Status is “Won” or “Lost” to a separate sheet named Archive, then delete them from the active Leads tab.
• Conditional formatting – Highlight rows where Follow‑up Date is past due. Select the range, choose Format → Conditional formatting, set the rule to =K2<TODAY() and pick a soft red fill. This visual cue helps the team spot overdue tasks instantly.

6. Reporting Basics

Even a lightweight CRM should give you visibility into pipeline health.

1. Insert a new sheet called Dashboard.
2. Use COUNTIF formulas to calculate totals:

=COUNTIF(Leads!G:G, "New")          // New leads
=COUNTIF(Leads!G:G, "Qualified")   // Qualified leads
=COUNTIF(Leads!G:G, "Won")          // Closed‑won

3. For a funnel view, create a small bar chart that references those counts. Place the chart on the Dashboard sheet for at‑a‑glance updates during stand‑ups.

7. Scaling Tips When the Spreadsheet Grows

• Split data by year – Create a separate sheet for each fiscal year and adjust your automations to reference the current year’s tab.
• Use unique IDs – If you ever migrate to a dedicated CRM, a consistent Lead ID makes the import painless.
• Lock columns you don’t want edited – As more people touch the sheet, protect columns like Lead ID and Owner to avoid accidental changes.

8. When to Move Beyond the Sheet

A Google Sheet CRM works well for early‑stage teams, pilot projects, or small sales operations. Consider transitioning to a purpose‑built system when:

• You need advanced reporting (forecasting, win‑rate analysis).
• Multiple departments require distinct permission levels.
• Integration volume exceeds what a no‑code platform can handle comfortably.

Even then, the workflows you built with Automate Anything can often be reused with a new data source, preserving the automation investment you’ve already made.

Quick Recap

By following these steps, you turn a familiar spreadsheet into a living CRM that nudges your team, logs every interaction, and feeds data to the rest of your tech stack—all without writing code.

Ready to automate the next piece of your workflow? Build your first automation at https://automateanythingsoftware.com.