Imports fail halfway. APIs page slowly. Someone clicks the menu twice. If "run again" means double every row, you don't have an importer — you have a liability. Here's the bootstrapper pattern: treat every import as re-runnable. Upsert by key when you can, watermark when you must, and never wipe blindly.
TL;DR
- Prefer upsert by a stable key (email, order ID, SKU) over blind append.
- Persist a cursor / watermark for paged APIs so retries resume, not restart.
- On full refresh jobs, clear below headers — don't delete the header row.
- Lock the critical section so two runs don't interleave.
- Log start + finish with counts so you can prove the re-run was clean.
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.
Why do duplicate rows happen?
Usually one of these:
- Append-only importer with no key check — every successful (or partial) run adds again
- No watermark — page 1–N re-fetched after a timeout, written twice
- Two overlapping runs (trigger + manual) writing the same payload
- "Clear the sheet" that also nuked headers, then a half-write left garbage
Clients notice duplicates weeks later during a reconcile. Fix the shape once.
What does a re-runnable import look like?
Three modes. Pick one on purpose:
| Mode | When | Shape |
|---|---|---|
| Upsert | Rows have a stable unique key | Find-or-update; append only if new |
| Watermark / cursor | API is paged or time-ordered | Store last success; continue from there |
| Full refresh | Snapshot replace is OK | Clear data rows, rewrite under headers |
Don't mix "append forever" with "I thought this was a sync."
How do you upsert instead of append?
Header-aware upsert: scan the key column, update the matching row, or append if missing.
function upsertRowByKey(sheet, keyHeader, keyValue, valuesByHeader) {
var data = sheet.getDataRange().getValues();
var headers = data[0];
var keyCol = headers.indexOf(keyHeader);
if (keyCol === -1) throw new Error('Missing header: ' + keyHeader);
var rowIndex = -1;
for (var r = 1; r < data.length; r++) {
if (String(data[r][keyCol]) === String(keyValue)) {
rowIndex = r;
break;
}
}
var row = headers.map(function (h) {
if (valuesByHeader.hasOwnProperty(h)) return valuesByHeader[h];
return rowIndex === -1 ? '' : data[rowIndex][headers.indexOf(h)];
});
if (rowIndex === -1) {
sheet.appendRow(row);
return sheet.getLastRow();
}
sheet.getRange(rowIndex + 1, 1, 1, row.length).setValues([row]);
return rowIndex + 1;
}
Call it inside your import loop:
function importContacts(contacts) {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Contacts');
ensureHeaders(sheet, ['email', 'name', 'status', 'updatedAt']);
contacts.forEach(function (c) {
upsertRowByKey(sheet, 'email', c.email, {
email: c.email,
name: c.name,
status: c.status || 'active',
updatedAt: new Date()
});
});
}
Re-run the same payload: same emails update in place. No twins.
How do you resume a paged API without rewriting page 1?
Store the cursor in Script Properties (or a Config sheet). Advance it only after a successful write for that page.
function getCursor_() {
return PropertiesService.getScriptProperties().getProperty('importCursor') || '';
}
function setCursor_(value) {
PropertiesService.getScriptProperties().setProperty('importCursor', String(value));
}
function importNextPage() {
return withScriptLock(function () {
var cursor = getCursor_();
var page = fetchPage_(cursor); // your UrlFetch + parse
if (!page.rows.length) {
Logger.log('Nothing left to import');
return 0;
}
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Import');
ensureHeaders(sheet, page.headers);
page.rows.forEach(function (rowObj) {
upsertRowByKey(sheet, page.keyHeader, rowObj[page.keyHeader], rowObj);
});
if (page.nextCursor) setCursor_(page.nextCursor);
else setCursor_('DONE');
return page.rows.length;
});
}
If the run dies mid-page, either:
- Make the page write idempotent (upsert), or
- Don't advance the cursor until the whole page lands
Advancing the cursor before the write is how you skip data forever. Don't.
When is a full refresh safer than upsert?
Daily snapshots, small datasets, or APIs that don't give reliable keys. Then:
- Ensure headers exist
- Clear content below the header row
- Write the fresh snapshot
- Optionally archive the previous snapshot tab first
function clearSheetKeepHeaders(sheet, headerRows) {
headerRows = headerRows || 1;
var lastRow = sheet.getLastRow();
var lastCol = sheet.getLastColumn();
if (lastRow <= headerRows || lastCol < 1) return;
sheet.getRange(headerRows + 1, 1, lastRow - headerRows, lastCol).clearContent();
}
function refreshSnapshot(rows) {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Daily');
ensureHeaders(sheet, ['id', 'name', 'amount']);
clearSheetKeepHeaders(sheet, 1);
if (!rows.length) return;
var values = rows.map(function (r) { return [r.id, r.name, r.amount]; });
sheet.getRange(2, 1, values.length, 3).setValues(values);
}
Still wrap with a lock if triggers can overlap — two refreshes clearing each other is a special kind of pain.
How do you stop double-clicks and overlapping triggers?
Serialize the import with a lock. Script Lock for standalone / property cursors; Document Lock when the bound spreadsheet is the shared resource.
function runImportMenu() {
try {
var n = withDocumentLock(function () {
return importNextPage();
}, 10000);
SpreadsheetApp.getActive().toast('Imported ' + n + ' rows', 'Import', 5);
} catch (err) {
SpreadsheetApp.getActive().toast(String(err), 'Import busy/failed', 8);
throw err;
}
}
Fail loud when the lock isn't acquired. A silent no-op looks like success in the Executions list.
Minimal test plan
- Run twice with the same fixture payload → row count unchanged on second run (upsert) or cursor at DONE
- Kill mid-run (throw after N rows) → re-run does not create duplicates
- Overlap two manual runs → one waits or fails clearly; sheet not interleaved
- Headers survive a refresh path
- Log or toast shows inserted vs updated counts if you track them
Soft Co-Pilot note
If you're wiring this into yet another client workbook, NitroGAS keeps helpers like upsertRowByKey, ensureHeaders, and the lock wrappers as snippets — Co-Pilot helps adapt key columns when the client's schema is weird. Free extension; Co-Pilot optional. The patterns above are copy-paste complete either way.
Closing checklist
- Chosen mode: upsert, watermark, or full refresh (documented in a comment)
- Stable key or cursor stored outside the data rows
- Headers ensured before write; clear-keep-headers on refresh
- Lock around the critical section
- Re-run tested with the same payload
- No "just append again" path left in the menu
Happy Coding!
