How to Build a Simple CRM in Google Sheets

What a spreadsheet CRM needs

A simple CRM answers three questions: who are our leads, what stage is each one at, and who should do what next. Google Sheets handles this well for a small team if the table is built with discipline from day one.

The minimal set:

1. A Leads sheet: one row per lead, fixed columns.
2. A Lists sheet: statuses, sources, managers for dropdowns.
3. A Dashboard sheet: numbers calculated by formulas.
4. Reminders: highlighting of due follow-ups and, optionally, a daily email.

Lead table structure

Agree on columns before entering the first lead and do not add free-form ones later.

Rules that save time later:

• One row, one lead. Do not merge cells and do not put two contacts in one row.
• Freeze the header row (View → Freeze) and turn on a filter.
• Write history elsewhere. A cell comment or a separate "Activities" sheet with lead ID, date and note keeps the main table readable.

Status dropdowns

Select column G, open Data → Data validation, choose "Dropdown (from a range)" and point to the list of statuses on the Lists sheet. A typical funnel:

• New
• Contacted
• Proposal sent
• Negotiation
• Won
• Lost

Set the rule to reject invalid input so nobody types "in progress" by hand. Do the same for Source and Manager. Without dropdowns, the dashboard formulas will miss rows with misspelled values.

Follow-up reminders

The simplest reminder is conditional formatting. Select A2:K, choose Format → Conditional formatting → Custom formula:

=AND($I2<>"", $I2<=TODAY(), $G2<>"Won", $G2<>"Lost")

Rows with an overdue or today's follow-up are highlighted. Add a filter view "My follow-ups" (Data → Filter views) for each manager: filtered by their name and sorted by the next contact date.

If you need an email every morning, a short Apps Script with a time-driven trigger does the job:

function remindFollowUps() {
  const sheet = SpreadsheetApp.getActive().getSheetByName('Leads');
  const rows = sheet.getDataRange().getValues().slice(1);
  const today = new Date();
  today.setHours(23, 59, 59, 999);
  const due = rows.filter(r => r[8] instanceof Date && r[8] <= today && !['Won', 'Lost'].includes(r[6]));
  if (due.length) {
    MailApp.sendEmail('sales@example.com', 'Follow-ups for today',
      due.map(r => `${r[2]} (${r[3]}), ${r[4]}, ${r[7]}`).join('\n'));
  }
}

Add it via Extensions → Apps Script and set a daily time-driven trigger.

Summary dashboard

On the Dashboard sheet, calculate the key numbers with formulas so they update by themselves:

Leads by status:      =COUNTIF(Leads!G:G, "New")
Won amount:           =SUMIFS(Leads!K:K, Leads!G:G, "Won")
Manager's open leads: =COUNTIFS(Leads!H:H, "Aziz", Leads!G:G, "<>Won", Leads!G:G, "<>Lost")
Overdue follow-ups:   =COUNTIFS(Leads!I:I, "<"&TODAY(), Leads!G:G, "<>Won", Leads!G:G, "<>Lost")
Conversion:           won / (won + lost), using the cells above

For a table by manager and status at once, QUERY or a pivot table works well: =QUERY(Leads!A:K, "select H, G, count(A) where A is not null group by H, G", 1).

Access rights

• Share by email, not by link. "Anyone with the link" access is the most common way client data leaks.
• Editors only for the sales team, viewers for management.
• Protect the Lists and Dashboard sheets and the header row (Data → Protect sheets and ranges).
• Version history (File → Version history) lets you restore data after an accidental deletion.

Keep in mind: Google Sheets cannot hide individual rows from an editor. If managers must not see each other's clients, the spreadsheet has reached its limit.

Signs it is time for a real CRM

• Several managers edit the table at once and overwrite each other.
• You need calls, messengers and email linked to the lead automatically.
• The history of interactions no longer fits into comments.
• Access must be restricted by client, team or branch.
• Tasks, reminders and reports take more time to maintain than to sell.