Automate Deduplication in Google Sheets

Why Automate Deduplication?

The Business Case for Automation

Duplicate records silently erode trust in your data and drain valuable team time. Whilst manual cleanup is often error-prone and difficult to scale, automation transforms deduplication into a repeatable, auditable and schedulable process. Consider the hidden costs of manual deduplication:

• Time Waste: A team member manually reviewing 1000 records for duplicates can consume 20-40 hours of labour.
• Inconsistency: Different reviewers apply different criteria, leading to missed duplicates and false positives.
• No Audit Trail: Manual deletions leave no record of what changed, why or who approved the action.
• Scalability Ceiling: As your data grows, manual processes become exponentially slower and more error-prone.

Automated deduplication delivers measurable value: 30-50% reduction in operational time, consistent application of matching rules, complete auditability and the ability to process larger datasets reliably.

Quick Checklist

Sample Dataset and Goals

This workflow uses a contacts sheet with name, email and phone columns. Our objective is to identify likely duplicates, assign them a confidence score and either merge or flag them for review.

Apps Script Workflow for Copy and Paste

Add this script to Extensions -> Apps Script in your sheet. The code below uses Flookup's custom spreadsheet functions to identify duplicates and write match confidence scores into adjacent columns.

Key strategies:

• Use Flookup's =FLOOKUP() formula to compare each record against a master list and return similarity scores.
• Write suggestions to a dedicated review sheet to avoid destructive, irreversible edits.
• Process records in batches to stay within Apps Script quota limits.

function runDedupe() {
  var ss = SpreadsheetApp.getActive();
  var sh = ss.getSheetByName('Contacts');
  var lastRow = sh.getLastRow();
  if (lastRow < 2) return;
  // Apply Flookup's FLOOKUP formula to compare each name against the full list
  var formulaRange = sh.getRange(2, 6, lastRow - 1, 1);
  var formula = '=FLOOKUP(A2, $A$2:$A$' + lastRow + ', 1, 2, 0.8, "score")';
  formulaRange.setFormula(formula);
  // Freeze values to preserve results and prevent recalculation drift
  formulaRange.copyTo(formulaRange, { contentsOnly: true });
}

Integrating with Flookup

Flookup provides higher-quality similarity scores and contextual matching than simple key comparisons. Use the add-on's menu functions for quick, UI-driven deduplication or its custom formulas for repeatable, formula-based workflows.

Spreadsheet Formula Example:

=FLOOKUP(A2, 'Master List'!A:B, 2, FALSE, 0.85)

For recurring automation, use Flookup's Schedule Functions feature (Extensions > Flookup Data Wrangler > Miscellaneous > Schedule functions) to run deduplication on an hourly or daily schedule without writing any code.

Choosing Between Manual vs Apps Script vs Flookup Schedule Functions

Three common strategies exist for handling deduplication in Google Sheets. Each offers distinct trade-offs between ease of use, control and accuracy.

Scheduling Automated Runs

Add a time-based trigger to run the dedupe regularly. Best practice involves running a conservative scoring pass daily that writes suggestions to a Review sheet, then running a weekly manual approval that applies merges. You can also email a short summary when the number of high-confidence matches exceeds a threshold.

function scheduleDedupe(){
ScriptApp.newTrigger('runDedupe').timeBased().everyDays(1).atHour(2).create();
}

For notifications, generate a CSV summary and email it via MailApp.sendEmail() or integrate with Slack using an incoming webhook.

Testing, Validation and Rollback Strategy

Pre-Implementation Testing

Always run the script on a copy of your data first. Create a test dataset with 100-200 known duplicates and verify that your script identifies them correctly. Document your test results and establish a baseline confidence threshold before deploying to production.

Implementation Best Practices

Store suggested edits in a separate Review sheet with a proposed_action column. Keep a change log (timestamp, user, rows affected, action taken) and provide a one-click rollback mechanism that replays the log to revert changes if needed.

Never apply destructive merges directly to the master data. Always require manual approval from an authorised user before consolidating records.

Validation Checklist

Before finalising any deduplication run, verify the following:

• Confidence scores for all suggested matches are above your established threshold (typically 0.85+).
• A sample of 5-10 high-confidence matches has been manually reviewed for accuracy.
• False positive rate is below 5% based on random sampling.
• All changes are logged with timestamp, user and action details.
• A backup copy of the original dataset exists before any merges are applied.
• Rollback instructions and procedures are documented and accessible to the team.

Key Metrics to Track

Monitor these metrics to assess deduplication quality and refine your approach over time:

• Matches Found: Total number of duplicate pairs identified in each run.
• Merges Applied: Number of merges actually executed after manual review.
• False Positive Rate: Percentage of suggested matches that were incorrect, calculated from manual review feedback.
• Data Recovery Time: How quickly you could revert changes if an error was discovered.
• Confidence Score Distribution: Track the distribution of match scores; high variance may indicate threshold tuning opportunities.

The Future of Automated Data Hygiene

Automating deduplication saves time and reduces risk, but start slow. Use suggested matches in a review workflow, monitor false positives and tune thresholds. When ready, automate the merge step for high-confidence matches only.