How to audit duplicates: Google Sheets & Salesforce guide

Top ways to build a Salesforce duplicate report

Below are three levels of sophistication: manual, no‑code automation, and AI‑agent driven. You can start with the basics and layer automation as your volume grows.

‍

1. Manual and traditional methods (3–7 options)

‍

1.1 Use Salesforce Duplicate Record Set reports

1. In Salesforce Setup, open App Launcher and search for Duplicate Rules and Matching Rules.
2. Ensure matching rules (for Leads, Contacts, Accounts) are active and tuned (e.g., email exact match, fuzzy name match).
3. When duplicates are detected, Salesforce creates Duplicate Record Sets.
4. Go to the Reports tab.
5. Click New Report and search for the type Duplicate Record Sets.
6. Add filters like Record Type = Lead and date filters.
7. Add columns: Duplicate Rule, Record Count, Created Date, Owner.
8. Run the report and export to CSV if you want to review in Google Sheets.

Official docs: https://help.salesforce.com/s/articleView?id=sf.managing_duplicates_overview.htm

Pros: Native, secure, respects existing rules.
Cons: Static, still requires a lot of manual reviewing and merging.

‍

1.2 Classic lead or contact reports with grouped fields

1. Go to Reports → New Report.
2. Choose Leads (or Contacts, Accounts).
3. In the outline, group rows by fields likely to indicate duplicates, like Email, Phone, or Account Name.
4. Add a row‑level summary count of records per group.
5. Filter where RowCount > 1 to surface groups with duplicates (or use a bucket field or custom filter logic to isolate them).
6. Save the report as Potential Lead Duplicates.
7. Export to CSV for deeper analysis in Google Sheets.

Docs (Reports): https://help.salesforce.com/s/articleView?id=sf.reports_builder_edit.htm

Pros: Flexible, no admin access needed.
Cons: Easy to miss fuzzy duplicates and cross‑object duplicates.

‍

1.3 Excel/Sheets style manual dedupe

1. Export any of the above reports to CSV.
2. Open in Google Sheets via File → Import or upload to Drive.
3. In Sheets, use Data → Remove duplicates to quickly remove exact matches on Email or other fields. Docs: https://support.google.com/docs/answer/13971709
4. Use =COUNTIF(A:A, A2) to flag values that appear more than once.
5. Add filters and conditional formatting (e.g., duplicates highlighted in red).
6. Manually decide which record is the “winner” (most complete, most recent activity).
7. Update Salesforce manually by opening each record and merging or editing.

Pros: Transparent and very flexible for one‑off audits.
Cons: Extremely time‑consuming at scale; error‑prone when re‑keying changes.

‍

1.4 Use Salesforce built‑in merge tools

1. From a Lead, Contact, or Account record in Salesforce, click View Duplicates (if enabled by your admin).
2. Salesforce shows potential duplicates based on active matching rules.
3. Select records to merge.
4. For each field, choose the surviving value.
5. Confirm merge.

Docs: https://help.salesforce.com/s/articleView?id=sf.duplicate_rules_merging.htm

Pros: Safe, governed merge process.
Cons: Record‑by‑record; doesn’t give overview of the problem.

‍

2. No‑code automation with Google Sheets and workflow tools

‍

2.1 Live sync Salesforce to Google Sheets

You can avoid constant CSV exports by using a live connector.

Options include:

• Salesforce Data Connector for Google Sheets (from Salesforce Labs)
• Third‑party tools like Zapier, Make, or Coupler.io

Example with Salesforce Connector for Sheets:

1. In Google Sheets, go to Extensions → Add-ons → Get add-ons and search for Salesforce.
2. Install the official Salesforce Connector.
3. Open it via Extensions → Salesforce Connector.
4. Authenticate with your Salesforce org.
5. Build a query selecting Leads/Contacts, including fields like Id, Email, Phone, Company, OwnerId.
6. Schedule automatic refresh (e.g., hourly, daily).

Reference: https://support.google.com/docs/answer/9071123 (using connectors and add-ons)

Now your duplicate‑checking sheet updates automatically without manual exports.

‍

2.2 No‑code dedupe workflows in Sheets

Once the data flows into Sheets, you can create a reusable “dedupe workbook”:

1. Use a dedicated tab Raw_Salesforce for imported data.
2. Create a Dedupe_View tab that references raw data with formulas such as:
   • =UNIQUE(Raw_Salesforce!B:B) to get unique emails.
   • =COUNTIF(Raw_Salesforce!B:B, B2) to count how many times each email appears.
3. Add a helper column Is_Duplicate with a formula like =IF(COUNTIF(Raw_Salesforce!B:B, B2)>1, "Yes", "No").
4. Filter Is_Duplicate = Yes to get a working list.
5. Add a Keep_Record_Id column where your team chooses the winning Salesforce Id.

Pros: Reusable, teams can collaborate in real time.
Cons: Still needs humans to push changes back into Salesforce.

‍

2.3 Push clean data back to Salesforce with no‑code tools

To avoid manual updates:

1. Use Zapier, Make, or another integration platform.
2. Trigger on new or updated rows in a specific Sheets tab (e.g., Approved_Merges).
3. For each row, call Salesforce’s Update Record or Merge actions, using the Keep_Record_Id and Duplicate_Record_Id fields.
4. Log results back into Sheets (status column: Success / Error).

Docs (Salesforce + automation platforms) are typically found under each tool’s help center, e.g., https://help.salesforce.com and https://support.google.com/docs for Sheets.

Pros: Removes repetitive updates, ideal for RevOps and agencies running periodic cleanups.
Cons: Logic is brittle; if your rules change, you must rebuild workflows.

‍

3. Scaled, AI‑agent methods with Simular

Now imagine your “dedupe analyst” is an AI computer agent that can:

• Log into Salesforce and Google Sheets like a human.
• Run reports, export data, and refresh connected Sheets.
• Apply nuanced business rules (e.g., keep record with open Opportunities, preserve custom fields).
• Document every action for audit.

Simular Pro is designed to behave like a power user across your desktop, browser, and cloud apps.

‍

3.1 Agents that run the full duplicate workflow

A Simular AI agent can:

1. Open Salesforce, navigate to Reports, and run your Duplicate Record Sets and Potential Lead Duplicates reports.
2. Export them, open Google Sheets, and paste or import data into the correct tabs.
3. Apply dedupe formulas, filters, and sort logic you’ve standardized.
4. Flag edge cases (e.g., conflicting owners, different account hierarchies) into a Human_Review tab.
5. For all clear‑cut duplicates, open the relevant Salesforce merge pages and complete merges according to rules you’ve described.

Pros: End‑to‑end automation, uses your existing UI and tools, no API development.
Cons: Requires initial design of the workflow and clear written rules.

‍

3.2 Always‑on duplicate monitoring

Instead of a quarterly cleanup, you can:

1. Schedule Simular Pro agents to run nightly.
2. They open Salesforce, apply filters like CreatedDate = YESTERDAY, run duplicate checks for new records only.
3. Update a Google Sheet dashboard summarizing new duplicates found, merged, and pending review.
4. Send summaries via email or Slack.

Because Simular agents are production‑grade and every action is logged, RevOps and agencies can treat them like reliable junior analysts—only faster and tireless.

‍

3.3 Transparent, tweakable logic

Simular emphasizes transparent execution: you see every step the agent takes. When business logic changes (e.g., SDR territories, priority accounts), you update the workflow description once and the agent’s behavior updates without rebuilding brittle no‑code flows.

This is how you go from “cleaning duplicates when it hurts” to a continuous, agent‑driven hygiene system that quietly protects your Salesforce and keeps every Google Sheets dashboard honest.