DEV Community

Stackscale
Stackscale

Posted on

Remove Duplicate Leads in Google Sheets with Make and OpenAI

Duplicate leads are polluting your Google Sheet. Learn how to remove duplicate leads in google sheets — Make watches your sheet, OpenAI spots the copies, even the near ones, and every duplicate archives itself so one clean row stays per lead.

This is the companion tutorial for the StackScale video above. No code, no live sheet, and no real lead data — the whole flow runs on two fictional demo lead pairs so you can test it safely.

What You Will Build

A Make scenario that checks every new lead against the ones already in your sheet and archives the copies: exact matches instantly, and near matches too — different case, a missing "Ltd", a typo. The first row stays, the copy moves to a Duplicates Archive tab, and low-confidence pairs go to review instead of being archived.

Prerequisites / Tools

  • A Make account (free plan is enough to start)
  • An OpenAI account
  • A free Google Sheet with your leads
  • Two dummy duplicate pairs to test with — Acme Studio against Acme Studios Ltd, and Northwind Bakery twice, both fictional demo names

Disclosure: Some links below are affiliate links, meaning StackScale may earn a commission at no extra cost to you.

The Workflow

Make watches your leads sheet for new rows, then sends each new lead to OpenAI with the leads already in the sheet. OpenAI returns a match verdict with a confidence score as JSON. The dedupe rule then decides: keep the earliest row, archive the copy, or send a low-confidence pair to review.

Step 1: Build the Leads Sheet

One row per lead, with columns for name, email, company, source, and first seen. Then add a tab called Duplicates Archive — copies move there, never deleted.

Step 2: Trigger on New Rows

In Make, set the scenario to run when a new row appears, or every morning at nine for a daily sweep. It reads the new row and every lead already in the sheet. Only new rows are checked, so the flow stays fast.

Step 3: The OpenAI Matching Prompt

Send the new lead and the existing leads to OpenAI with this prompt (copy as-is):

Compare the new lead against the leads already in the sheet. Compare name, email, and company. Same email means same lead, even if the name is spelled differently. Return JSON only: {"match": true or false, "confidence": 0 to 1, "original": "the existing lead name", "reason": "one sentence"}.
Enter fullscreen mode Exit fullscreen mode

Step 4: Apply the Dedupe Rule

Copy this field mapping into the sheet module:

{
  "Name": "lead name",
  "Email": "lead email",
  "Company": "company name",
  "Source": "form / import",
  "First seen": "first seen date",
  "Status": "kept / duplicate / review"
}
Enter fullscreen mode Exit fullscreen mode

If OpenAI says match, keep the earliest row and move the copy to the Duplicates Archive tab. If it says no match, the new row stays. If confidence is low, send the pair to a review tab and let a human decide — never archive a lead the flow is unsure about.

Step 5: Test with Dummy Pairs

Add "Acme Studios Ltd" next to "Acme Studio" and watch the copy archive itself. Add a second Northwind Bakery row and watch it go too. Two duplicates in, two archived.

The Finished Result

One row per lead, every copy archived, and a review tab for the uncertain ones. Open the sheet each morning and the counts are right.

One Honest Limit

Google Sheets already has a built-in tool — Data, then Data cleanup, then Remove duplicates — free, and two clicks. But it only catches exact matches. It will never see that "Acme Studio" and "Acme Studios Ltd" are the same lead, and it never runs by itself. This Make and OpenAI build is for fuzzy matches and automatic daily cleaning, not for a one-off exact cleanup. OpenAI can also over-match two different companies with similar names — that is why low-confidence pairs go to review, and nothing is ever deleted outright.

Watch the Full Tutorial

The video at the top walks through the same five steps with the synthetic mockups. All mockups in the video are synthetic, created for this tutorial, with dummy data only — no real lead is shown, and no real email address appears anywhere.

New automation tutorials every day on StackScale — subscribe on YouTube at https://www.youtube.com/@StackScaleHQ.

Top comments (0)