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 is unique in practice, and it never changes when rows move around:

function submissionId_(timestampIso, email, name, need) {
  var parts = timestampIso
    ? [timestampIso, email.toLowerCase(), name.toLowerCase()]
    : ['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.

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. Use the content instead (email + name + message). Two identical pasted rows will count as one submission, which is usually what you want.

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
  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.

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.