Inbound imports often get an idempotency story ("re-run without duplicating"). Outbound pushes are the opposite footgun: two sheet rows with the same email and your API creates two contacts. Flag duplicates on the sheet before UrlFetch, then sync only clean rows.
TL;DR
- Pick a key column (email, external id, SKU).
- Scan once; first win stays keep; later hits get Dup (or similar) in a Status column.
- Push loop skips anything marked Dup / empty key.
- Complements "re-run import without duplicating" — that protects inbound; this protects outbound.
- Use a document lock if humans edit while the scanner runs.
Using the Apps Script editor a lot? NitroGAS drops free themes & snippets right into script.google.com — optional Co-Pilot when you want a boost.
What does a simple scanner look like?
function flagDuplicateKeys(sheet, keyHeader, statusHeader, options) {
options = options || {};
var headerRow = options.headerRow || 1;
var keepLabel = options.keepLabel || 'keep';
var dupLabel = options.dupLabel || 'Dup';
var lastCol = sheet.getLastColumn();
var lastRow = sheet.getLastRow();
if (lastRow <= headerRow) return { kept: 0, dups: 0 };
var headers = sheet.getRange(headerRow, 1, 1, lastCol).getValues()[0];
var keyCol = headers.indexOf(keyHeader);
var statusCol = headers.indexOf(statusHeader);
if (keyCol === -1) throw new Error('Missing key header: ' + keyHeader);
if (statusCol === -1) throw new Error('Missing status header: ' + statusHeader);
var numRows = lastRow - headerRow;
var values = sheet.getRange(headerRow + 1, 1, numRows, lastCol).getValues();
var seen = {};
var kept = 0;
var dups = 0;
for (var i = 0; i < values.length; i++) {
var key = String(values[i][keyCol] == null ? '').trim().toLowerCase();
if (!key) {
values[i][statusCol] = values[i][statusCol] || '';
continue;
}
if (seen[key]) {
values[i][statusCol] = dupLabel;
dups++;
} else {
seen[key] = true;
// don't overwrite a richer status unless empty / previous dup mark
var cur = String(values[i][statusCol] == null ? '').trim();
if (!cur || cur === dupLabel) values[i][statusCol] = keepLabel;
kept++;
}
}
sheet.getRange(headerRow + 1, 1, numRows, lastCol).setValues(values);
return { kept: kept, dups: dups };
}
Normalize keys the same way your API will (normalizeEmail for emails).
How do you push only clean rows?
function pushCleanRows(sheet, keyHeader, statusHeader) {
flagDuplicateKeys(sheet, keyHeader, statusHeader);
var data = sheet.getDataRange().getValues();
var headers = data[0];
var keyCol = headers.indexOf(keyHeader);
var statusCol = headers.indexOf(statusHeader);
for (var r = 1; r < data.length; r++) {
if (String(data[r][statusCol]).toLowerCase() === 'dup') continue;
var key = String(data[r][keyCol] || '').trim();
if (!key) continue;
// UrlFetchApp.fetch(...) with muteHttpExceptions + backoff as needed
}
}
Keep-first vs keep-latest
Default keep-first is safest for "first spreadsheet row wins." If you need keep-latest, scan bottom-up or sort by Updated At before flagging — document the rule on the sheet so humans don't fight the bot.
Wrap-up
Duplicates are a sheet problem until they become an API problem. Flag early, push late.
NitroGAS snippets for locks, header maps, and UrlFetch backoff fit around this; Co-Pilot is optional when the upstream payload shape is messy. Happy Coding.
