How to stop duplicate Google Form entries in Sheets

Updated 2026-11-06T00:00:00.000Z · 6 min read · Examples use sample data

You add a script that copies Google Form responses into a cleaner tab. It works. Then you re-run it after a change — and every lead appears twice. This guide explains why that happens and how to make re-runs harmless.

Why duplicates happen

A “copy new responses” script usually does one of these:

Processes every row each time — fine the first time, duplicates every row the second time.
Remembers the last row number — breaks as soon as someone sorts, deletes or inserts rows.
Runs from an on-submit trigger and a manual “process all” button — the same response gets handled twice.

The fix isn’t to run less. It’s to make running twice produce the same result as running once — what engineers call idempotent.

The fix: a stable submission ID

Give each response a fingerprint that only depends on the response itself. Before adding a row, check whether that fingerprint is already in the destination tab.

Google Forms writes a Timestamp for every response, accurate to the second. Timestamp + email + name + message is unique in practice, and it never changes when rows move around:

function submissionId_(timestampIso, email, name, need) {
  var parts = [timestampIso || 'no-ts', email.toLowerCase(), name.toLowerCase(), (need || '').toLowerCase()];
  var s = parts.join('|');
  var h = 0x811c9dc5;                       // FNV-1a, 32-bit
  for (var i = 0; i < s.length; i++) {
    h ^= s.charCodeAt(i);
    h = (h + ((h << 1) + (h << 4) + (h << 7) + (h << 8) + (h << 24))) >>> 0;
  }
  return 'L-' + ('0000000' + h.toString(16)).slice(-8);
}

L-3f9a1c2e is short enough to show in a column and to search for.

Why the message is part of it: someone can send two different requests within the same second (a double tab, a form embedded twice). With only timestamp + email + name both get the same ID and the second one is silently skipped. We shipped exactly that bug in Lead Capture Mini 1.1 and fixed it in 1.2.

Rows without a timestamp

People paste rows into the responses tab by hand, or import a CSV. Those rows may have no timestamp. Don’t fall back to “now” — then the ID changes on every run and you’re back to duplicates. The 'no-ts' branch uses the content instead. Two identical pasted rows will count as one submission, which is usually what you want.

A 32-bit hash can collide

With FNV-1a 32-bit, two different submissions share a hash with a chance of roughly n² / 8.6 billion — about 0.01 % at 1,000 leads. Rare, but cheap to guard against: when an ID already exists, also compare email, name and message. If they differ, it’s a different request — give it L-3f9a1c2e-2 instead of skipping it. The suffix is deterministic, so the next run finds it again.

Check before you write

Read the IDs that already exist once, then skip any response whose ID is in that set:

var existing = leads.getRange(2, 1, leads.getLastRow() - 1, 1).getValues();
var seen = {};
existing.forEach(function (r) { seen[r[0]] = true; });

rows.forEach(function (row) {
  var id = submissionId_(/* … */);
  if (seen[id]) return;        // already processed — skip (compare content too, see above)
  seen[id] = true;             // also catches duplicates inside this batch
  out.push(buildRow(row, id));
});
leads.getRange(leads.getLastRow() + 1, 1, out.length, out[0].length).setValues(out);

Writing all new rows with one setValues call is also much faster than appendRow in a loop.

Don’t confuse “duplicate submission” with “repeat customer”

The same person may send two genuinely different requests. Those should both be kept. A useful middle ground: add the second one, but flag it — for example Needs review: email already sent a request — so a person decides whether to merge them.

Test it

Make 7 sample responses, including one person who writes twice.
Run your script. Count the rows.
Run it again. The count must not change — in Lead Capture Mini the toast reads Added 0 lead(s), skipped 7 already processed.
Paste one row without a timestamp, run twice, and check it was added once.
Add two rows with the same timestamp, email and name but different messages — both must be added.

We run exactly these checks as automated tests on Lead Capture Mini before every release, and its guide includes the same 7 sample inquiries so you can repeat them yourself.