CRMData QualityHow-to

CRM Data Health Dashboard: Automate Quality Checks with Formulas & Scripts

sspreadsheet

2026-02-05

9 min read

Build a Google Sheets CRM data health dashboard that finds missing fields, invalid emails, and duplicates — plus automated daily reports.

Stop wasting time on messy CRM exports — build a live Data Health Dashboard in Google Sheets

Hook: If your sales pipeline reports are full of blank emails, invalid addresses, and hidden duplicates, you’re not alone — and you don’t need a BI team to fix it. In 2026, with AI-generated contacts and multi-source integrations multiplying dirty rows, a lightweight, automated Google Sheets-based CRM Data Health Dashboard gives small teams fast control.

Why this matters in 2026

Recent industry research (Salesforce and independent studies in late 2025) shows that poor data hygiene remains one of the top barriers to scaling analytics and AI. Duplicate records, missing fields, and invalid contact info create low trust in dashboards and waste SDR time. Building quality checks directly in Sheets — combined with Apps Script automation — is a pragmatic, high-ROI approach for operations teams and SMBs.

Overview: What you’ll build

By the end of this guide you’ll have a Google Sheets-based CRM Data Health Dashboard that:

Detects missing required fields (email, phone, company)
Flags invalid emails and phone numbers with robust formulas
Finds exact and fuzzy duplicates using formulas and Apps Script
Summarizes problems in a dashboard with pivot tables and charts
Sends an automated daily report (email or Slack) using serverless functions or Apps Script

Step 1 — Prepare the source sheet

Start with a clean import of your CRM export or connector feed into a sheet called RawData. Use one column per field. Example columns:

ID (CRM ID)
First Name
Last Name
Email
Phone
Company
Lead Source
Created Date

Keep RawData as the single source of truth. All checks and helper columns will live in a separate sheet (Checks) that references RawData by range names or direct ranges.

Step 2 — Add formula-based quality checks

In a new sheet Checks, add helper columns that evaluate each row. Short formulas are fast to compute and easy to audit.

2.1 Normalize fields (best practice)

Create normalized keys used for duplicates and fuzzy checks. Normalization trims spaces, collapses whitespace, and lowercases:

=LOWER(TRIM(REGEXREPLACE(C2,"\s+"," ")))

Use this on names, companies and emails. Example headers and formula placements assume RawData rows start at row 2.

2.2 Detect missing critical fields

Flag missing email, phone, or company quickly:

=IF(TRIM(RawData!D2)="","Missing Email", "OK")

Or combine checks in one column:

=IF(OR(TRIM(RawData!D2)="",TRIM(RawData!E2)=""),"Missing Critical","OK")

2.3 Validate emails with REGEXMATCH

A practical, widely used regex (covers most valid patterns):

=IF(RawData!D2="","Missing",
IF(REGEXMATCH(TRIM(RawData!D2),"^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$"),"OK","Invalid"))

Notes: regex won’t catch disposable domains or mailbox quality. For domain checks, cross-reference a domain blocklist or use Apps Script with a domain verification API.

2.4 Validate phone numbers

Phone number formats vary — here’s a conservative check that confirms digits and length (7–15 digits):

=IF(RawData!E2="","Missing",
IF(REGEXMATCH(REGEXREPLACE(RawData!E2,"[^0-9]",""),"^\d{7,15}$"),"OK","Invalid"))

2.5 Exact duplicate detection (fast)

Create a composite key to detect exact duplicates — combine normalized email + name + company:

=LOWER(TRIM(RawData!D2)) & "|" & LOWER(TRIM(RawData!B2 & " " & RawData!C2)) & "|" & LOWER(TRIM(RawData!F2))

Then count occurrences:

=COUNTIF($K$2:$K,$K2)

Flag rows where count>1 as duplicates.

2.6 Flag potential duplicates using MATCH + index

A simple fuzzy indication: check if the same email appears but company differs:

=IF(AND(RawData!D2<>&"",COUNTIF(RawData!D:D,RawData!D2)>1),"Email Duplicate","")

2.7 Fuzzy duplicate detection (Apps Script)

Exact matching misses typos. Use a lightweight Apps Script Levenshtein function to score name similarity, then mark pairs above a threshold (e.g., 0.85).

/**
 * Returns pairs of rows with high similarity for Name+Company
 */
function findFuzzyDuplicates() {
  const ss = SpreadsheetApp.getActive();
  const raw = ss.getSheetByName('RawData');
  const out = ss.getSheetByName('FuzzyResults') || ss.insertSheet('FuzzyResults');
  out.clear();
  const data = raw.getRange(2,1,raw.getLastRow()-1,8).getValues();
  const normalize = s => (s||'').toString().trim().toLowerCase().replace(/\s+/g,' ');
  const names = data.map(r => normalize(r[1]+' '+r[2]) + '|' + normalize(r[5]));
  const results = [];
  for (let i=0;i0.85) results.push([i+2,j+2,score.toFixed(2),data[i][3],data[j][3]]); // rows,score,email1,email2
    }
  }
  out.getRange(1,1,1,5).setValues([['Row A','Row B','Score','Email A','Email B']]);
  if(results.length) out.getRange(2,1,results.length,5).setValues(results);
}

// Levenshtein-based similarity
function similarity(s1,s2){
  if(!s1||!s2) return 0;
  const lev = levenshtein(s1,s2);
  const maxLen = Math.max(s1.length,s2.length);
  return 1 - lev/maxLen;
}

function levenshtein(a,b){
  if(a===b) return 0;
  const matrix=[];
  let i,j;
  for(i=0;i<=b.length;i++) matrix[i]=[i];
  for(j=0;j<=a.length;j++) matrix[0][j]=j;
  for(i=1;i<=b.length;i++){
    for(j=1;j<=a.length;j++){
      if(b.charAt(i-1)===a.charAt(j-1)) matrix[i][j]=matrix[i-1][j-1];
      else matrix[i][j]=Math.min(matrix[i-1][j-1]+1, matrix[i][j-1]+1, matrix[i-1][j]+1);
    }
  }
  return matrix[b.length][a.length];
}

This script writes pairs into a sheet so an analyst can review and merge duplicates in the CRM. For large datasets, limit comparisons by buckets (same first initial, same domain) to reduce runtime.

Step 3 — Build the Data Health Dashboard

Create a sheet called Dashboard and surface KPIs with pivot tables, charts, and big-number formulas.

Key metrics to display

Total records
Records with missing critical fields
Invalid emails / phones
Exact duplicates
Fuzzy duplicate candidates
Problem rate by Lead Source or Owner

Quick formulas for big numbers

TotalRecords: =COUNTA(RawData!A2:A)
MissingCritical: =COUNTIF(Checks!C2:C,"Missing Critical")
InvalidEmails: =COUNTIF(Checks!D2:D,"Invalid")
ExactDuplicates: =COUNTIF(Checks!K2:K,">1")

Problem rate by Lead Source (Pivot or QUERY)

Use QUERY for a compact summary:

=QUERY({RawData!G2:G,Checks!C2:C},"select Col1, count(Col1), sum(Col2='Missing Critical') where Col1 is not null group by Col1",1)

Pivot table tips

Rows: Lead Source
Values: Count of Total Records, Count of Missing Critical
Filters: Date range (Created Date) so you can slice by week/month

Step 4 — Automate reports with Apps Script

Automation turns checks from passive to proactive. Use Apps Script to run checks nightly and email a summary, post to Slack, or create weekly cleanup tasks in a task tracker.

4.1 Simple daily email summary

Use a time-driven trigger to run this function once per night. It reads dashboard KPIs and sends a compact email.

function sendDailyDataHealthSummary(){
  const ss = SpreadsheetApp.getActive();
  const dash = ss.getSheetByName('Dashboard');
  // Adjust cell addresses to match your dashboard layout
  const total = dash.getRange('B2').getValue();
  const missing = dash.getRange('B3').getValue();
  const invalid = dash.getRange('B4').getValue();
  const dupes = dash.getRange('B5').getValue();

  const body = `CRM Data Health Summary — ${new Date().toLocaleDateString()}`+
    `Total records: ${total}
Missing critical fields: ${missing}`+
    `Invalid emails/phones: ${invalid}
Exact duplicates: ${dupes}`;

  MailApp.sendEmail({
    to: 'ops-team@yourcompany.com',
    subject: 'Daily CRM Data Health Snapshot',
    htmlBody: body
  });
}

4.2 Post to Slack (optional)

Post the same summary to a Slack channel using an incoming webhook for fast visibility. Protect your webhook URL using the Script Properties store — treat it like any secret and rotate it regularly.

4.3 Create a cleanup task list

Write a script that exports flagged rows to a sheet or creates tasks (Trello, Asana) via API so your team can review and merge duplicates without manually scanning reports.

Step 5 — Integration best practices (2026 trends)

In 2026, CRMs are more interconnected — multiple channels, AI-generated leads, and third-party integrations. These practices reduce dirty data at source:

Validate at capture: use front-end validation and email verification APIs to reject bad emails before they land in the CRM.
Source tagging: add a Lead Source ID for every integration so you can report problem rates per source.
Rate-limit AI imports: AI and enrichment tools generate volume fast — set quality gates (email & domain checks) before auto-creation.
Use webhooks, not bulk imports: incremental syncs are easier to validate and roll back.

Troubleshooting & performance tips

Large Sheets can be slow. Use these tips:

Limit volatile formulas. Prefer ARRAYFORMULA or single-range formulas over row-by-row heavy regex when possible.
Bucket data for fuzzy comparisons (same email domain or same first letter) to reduce pairwise checks.
Move heavy computations to Apps Script and write results to a separate sheet to avoid recalculation delays.
Use cached ranges and batch writes in Apps Script to minimize API calls.

Real-world example (case study)

Small SaaS vendor “CloudOpsCo” had 55K CRM rows with a 12% invalid contact rate and 8% exact duplicates. They implemented this dashboard and:

Cut SDR time spent cleaning lists by 60%
Improved email deliverability 9% in three months after blocking disposable domains
Recovered $120K ARR from lost renewal notices (merged duplicate accounts)

Key enablers: nightly Apps Script report to Sales Ops, automated flagging of AI-imported leads, and a weekly cleanup workflow that used the FuzzyResults sheet.

Advanced strategies

Use external APIs for domain and mailbox verification

Link to services like Kickbox/Abstract (2026 vendors updated) via Apps Script to get deeper validation (role accounts, disposable detection, MX checks). Store scores and prioritize high-value records for manual review.

Integrate with Google Cloud Functions or AWS Lambda

For enterprise volumes, offload heavy fuzzy matching and enrichment to serverless functions and write back cleaned keys to Sheets or the CRM via API.

Versioning and audit trail

Keep an Audit sheet that records each automatic action (merge candidate flagged, email validated). This improves trust and supports rollbacks when automations misfire.

Checklist: What to deploy this week

Import last 90 days of CRM rows to RawData.
Add normalization and the five helper checks (missing, invalid email, invalid phone, exact duplicate, composite key).
Create Dashboard KPIs and a pivot by Lead Source.
Install Apps Script for fuzzy dupe detection and a nightly email trigger.
Test and iterate: review fuzzy results, adjust threshold, then operationalize merging strategy in your CRM.

Common gotchas

Regex false negatives: some valid but uncommon emails fail strict patterns — always include a review step.
International phones: adapt regex for country formats or use libphonenumber via Apps Script to validate.
Performance: scripts that compare n^2 pairs will hit quota for large sets — bucket first.

“Cleaning data is never one-and-done. Make checks part of your pipeline.” — Ops best practice, 2026

Actionable takeaways

Start with formula checks — they’re transparent and easy to explain to stakeholders.
Use Apps Script for complex checks and automation (emailing, Slack, export tasks).
Prioritize high-value records for enrichment and manual review.
Monitor problem rates by source and measure improvement monthly.

Next steps & resources

Download our customizable CRM Data Health Template (Sheets) for the full setup: helper columns, Dashboard layout, Apps Script examples, and a one-click install guide (available in our templates store).

Want to go deeper? We also offer a premium pack with:

Pre-built Apps Script for domain verification and Slack integration
Scalable fuzzy-matching pipeline (bucketed + serverless example)
Custom onboarding support to connect HubSpot, Salesforce, or Pipedrive

Final thoughts (2026 outlook)

With AI and multiple integration points increasing the volume of CRM records in 2026, data quality becomes the foundation of customer-facing operations. Lightweight Sheets-based dashboards, combined with automation, let small teams implement enterprise-grade checks without heavy tooling. Start with formulas, add scripts, and bake validation into capture points — your analytics and AI will thank you.

spreadsheet

Contributor

Senior editor and content strategist. Writing about technology, design, and the future of digital media. Follow along for deep dives into the industry's moving parts.