All guides
Data quality

How to handle duplicate QR scans in Google Sheets

Record every scan, flag the repeat, dedupe in the sheet: UNIQUE, COUNTIFS and QUERY formulas for a clean tab, and why refusing a repeat scan loses data.

By the QR to Sheets team 8 min readUpdated

Short answer

In Google Sheets, a duplicate QR scan is usually best recorded and flagged rather than refused, because a scan that was thrown away cannot be told apart from one that never happened.

Keep the raw scan log untouched and build a deduplicated tab with UNIQUE, COUNTIFS or QUERY that keeps each code's first scan per day.

In QR to Sheets, each scanner link's Accept duplicate scans setting has three choices: Always, the default, saves every scan and flags a repeat within 12 hours; Once a day records each code once per day; Once ever records each code only once.

Duplicate scans come from three different places, and only one of them is a mistake. A code sitting in front of the camera gets read again as a phone passes over it. A person is not sure the first scan registered and scans again. And sometimes a person or an item genuinely comes back: a second entry after lunch, a tool going out twice in one day, two units of the same product on a shelf.

Those three need three different answers, and at the moment of scanning nothing can reliably tell them apart. That is why the useful question is not how to stop duplicates, but where to decide what they mean. The answer this guide argues for: record everything, flag the repeat at the time, and make the decision in the spreadsheet.

Written by QR to Sheets, so read the product section as vendor material. The formulas below work on any sheet of scan rows, whichever tool wrote them, including a Google Form's response sheet.

Why refusing a repeat scan is the wrong default

A system that silently rejects the second scan feels tidy, and it destroys information. Afterwards you cannot tell a person who scanned once from a person whose second scan was thrown away, and you cannot recover the genuine second trip that the rule happened to swallow. If a record is ever disputed, a complete log is evidence; a log with rejected rows missing is an argument.

Recording and flagging keeps the log complete and moves the judgement to the one place it can be changed later without losing anything. If you decide next month that a second scan within ten minutes should count as the same visit, you change a formula. If the scanner had refused it, that data would simply not exist.

How do I handle duplicate scans for free with a Google Form?

A Google Form behind a printed QR code writes every submission to a linked Google Sheet, duplicates included. That is the right behaviour for the log, and it costs nothing. The form's Limit to 1 response setting is not a duplicate filter for this job: Google's own help states that it requires respondents to sign in to their Google Account, so it limits accounts rather than codes and adds a sign-in step to every scan.

Formulas that build a deduplicated tab

These assume the timestamp is in column A and the scanned value in column B, which is the default QR to Sheets row and close to most form layouts. Adjust the letters to your own sheet. QR to Sheets writes the timestamp as YYYY-MM-DD HH:MM:SS, which Google Sheets stores as text, so the formulas below convert it with VALUE(); that also works on a column that already holds real dates, such as a Google Form's.

  • Each distinct code once: =UNIQUE(B2:B). Fast, but it drops the timestamp, so it answers who and never when.
  • The date of each scan, in a helper column G: =IF(A2="", "", INT(VALUE(A2))), formatted as a date.
  • A first-scan flag per code per day, in column H: =COUNTIFS($B$2:B2, B2, $G$2:G2, G2)=1. It is TRUE on the first scan of that code on that date and FALSE on every repeat, because rows arrive in time order.
  • The deduplicated register on its own tab: =FILTER(Log!A2:H, Log!H2:H=TRUE).
  • The earliest scan of each code on each day, without helper columns: =QUERY({ARRAYFORMULA(IFERROR(INT(VALUE(Log!A2:A)), "")), Log!B2:B, ARRAYFORMULA(IFERROR(VALUE(Log!A2:A), ""))}, "select Col2, Col1, min(Col3) where Col2 is not null group by Col2, Col1", 0). Format the result's date column as a date and its last column as a date-time.
  • A visual check on the raw log: a conditional formatting rule with the custom formula =COUNTIF($B:$B, $B2)>1 shades every value that appears more than once.

Google Sheets also has Data, Data cleanup, Remove duplicates. It deletes the repeated rows from the range you select, so it belongs on a copy of the data, never on the tab your scans land in.

Where the free route breaks

  • Nothing warns the person at the door. A repeat is discovered at the weekly reconciliation, when it can no longer be asked about.
  • Typed values create near-duplicates. A-102, a102 and A-102 with a trailing space are three different values to a formula; wrap the column in UPPER(TRIM()) before you compare, or scan instead of typing.
  • A submission attempted with no signal is lost, and people who are unsure whether it went through submit again. The weak connection manufactures real duplicates.
  • A Google Form has no camera scanner of its own, so the code usually has to be read by one app and pasted into another, which invites the double submit.

What QR to Sheets does with a repeat scan

This is the product we make, so treat it as one design rather than a claim about every tool. It separates the accidental repeat from the deliberate one. A code held in front of the camera is accepted once; it has to leave the frame before the scanner will take it again, so a phone resting over a badge does not write a row every second.

By default, a code scanned again on purpose is always written. When the same value repeats on the same scanner link within 12 hours, the screen turns amber, says Already scanned with the number of times on this link, and adds Saved anyway. The scan is not blocked, and if the repeat check is unavailable for any reason the scanner simply shows no badge and carries on. The window is per link, so a code scanned on a Check out link and then on a Check in link does not warn.

Some jobs really do want the scanner to refuse, so each scanner link has a setting called Accept duplicate scans with three choices, free on every plan. Always, the default, records every scan as described above. Once a day records each code once per calendar day, which suits a daily register (next section). Once ever records each code once, ever, which suits one ticket, one entry or one prize per voucher. On either of the last two, a repeat shows Already scanned with the time it was first scanned, and nothing is added to the Sheet, even when two phones scan the same code at the same moment. It is a per-link choice, so keep Always for check in and check out, where the same code is meant to be scanned twice, and for a field trip roll call with a return leg, and remember the trade-off above: a refused scan leaves no row to look at later. That holds across phones: when several phones scan into one sheet, a code refused on one is refused on all of them.

Two quieter behaviours matter for duplicates too. A scan resent after a dropped connection is not written twice, so a flaky signal does not double your rows. And each scan is saved on the phone first, so the worker does not rescan out of doubt when the network is slow.

Google Forms + formulas (free) compared with QR to Sheets
CapabilityGoogle Forms + formulas (free)QR to Sheets
CostFreeFree for 300 scans, then 7 USD per phone per month
Warning when a code repeatsNoneAmber Already scanned badge with the count, and Saved anyway
Repeat windowNot applicable12 hours, counted per scanner link
Code left in front of the cameraNot applicable, no camera scannerAccepted once; it must leave the frame first
Resent after a dropped connectionSubmission lost, often retypedNot written twice
Is the repeat row writtenYesYes on Always, the default
Refuse a repeat at the scannerNo built-in optionPer-link setting: Once a day or Once ever, repeats shown as Already scanned
Deduplicated registerFormulas you writeFormulas you write, or a link set to Once a day

How do I record each person once a day?

For a daily register, a daily breakfast or lunch, or any job where each person should count once per day, set the scanner link's Accept duplicate scans to Once a day. The link then records each code once per calendar day: a second scan the same day shows Already scanned with the time of the first scan, and nothing is added to the Sheet. The next day the same code is recorded again, so one link serves the whole term. The day is counted in your workspace's time zone, which you set in Settings, so check it before the first morning.

Do not use Once a day where the same code is meant to be scanned again the same day. Check in and check out, after-school sign in and sign out, a field trip's return leg, volunteer shifts, stock counts and equipment loans all stay on Always, because Once a day would refuse the second scan. And the trade-off above still applies: a refused repeat leaves no row, so the Sheet cannot show that someone scanned twice. If you want that evidence, stay on Always and use the first-scan flag formulas above instead.

Which scan should count?

  • Attendance and sign-in: the first scan per person per day is the arrival. Keep later scans; they are the answer when someone asks what time a person left. If you only ever want the arrival, a link set to Once a day records just that.
  • Check in and check out: do not deduplicate across directions. Use two scanner links and compare the counts per code; a repeat within one direction is the thing to investigate.
  • Stock counts: a repeat is often a real second unit. Never deduplicate a count blindly; count scans per code instead, and treat a sudden run of repeats as a phone held over one label.
  • Event entry: a repeat usually means re-entry or a shared code. Flag it for the door rather than hiding it from the report. A multi-session conference, where a one-off session link may reasonably refuse repeats, is covered in session attendance for conferences and training.

Step by step

  1. 1

    Decide what a repeat means in your workflow

    For attendance, a second scan the same day is usually noise. For check-out, it may be a genuine second trip. For a stock count, it may be a second unit of the same product. Write the rule down before you write a formula.

  2. 2

    Leave the raw log alone

    Never delete rows from the tab your scans land in. Every report, including the deduplicated one, is a view built on top of it, so a wrong formula can always be fixed and a disputed record can always be checked.

  3. 3

    Add a date column and a first-scan flag

    A helper column holding the date of each scan, and a running COUNTIFS that marks the first occurrence of each code on each date, gives you a TRUE/FALSE you can filter, pivot and count on.

  4. 4

    Build the clean tab from the flag

    A FILTER or QUERY over the log that keeps only first scans is the deduplicated register. It updates itself as new rows arrive, so nobody has to clean the data by hand.

  5. 5

    Tell the people scanning what a warning means

    If your scanner shows a repeat warning, say in the same message as the link that the scan was still saved and that nobody needs to rescan. Most real-world duplicates come from people who were not sure the first scan worked.

Frequently asked questions

How do I remove duplicate QR scans in Google Sheets?+

Do not remove them from the log. Add a date column and a first-scan flag with COUNTIFS, then build a separate tab with FILTER or QUERY that keeps only first scans. The raw log stays complete and the clean tab updates itself as new scans arrive.

Does QR to Sheets block a duplicate scan?+

Not by default. Every deliberate scan is saved, and a repeat of the same value on the same link within 12 hours shows an amber badge with the repeat count and the words Saved anyway. If you want a link to refuse repeats, set its Accept duplicate scans setting to Once a day, which records each code once per day, or Once ever, which records each code once; a refused repeat shows Already scanned and adds no row. A code held in front of the camera is only accepted once until it leaves the frame either way.

How do I record each person once a day?+

Set the scanner link's Accept duplicate scans to Once a day. Each code is then recorded once per calendar day in your workspace's time zone; a second scan the same day shows Already scanned and is not added, and the next day it records again. Keep Always for check in and check out, where the same code scans twice in a day.

Will scanning someone in and out trigger the repeat warning?+

Not if you use two scanner links, one for each direction, which is the recommended setup anyway. The repeat window is counted per link, so the same badge scanned on a Check in link and then a Check out link does not warn.

Can a Google Form stop someone submitting twice?+

Only by account. The Limit to 1 response setting requires respondents to sign in to their Google Account, so it limits each account to one response. It cannot recognise the same code submitted twice, and it adds a sign-in step that most scanning workflows cannot afford.

Why do I see the same code twice a few seconds apart?+

Usually because the person scanning was not sure the first scan worked, or the code was typed and pasted twice. Clear success feedback on the scanner and a note in the instructions that a scan does not need repeating remove most of them. Treat pairs seconds apart as one event in your formulas.

What formula keeps only the first scan per person per day?+

With timestamps in column A, codes in column B and the date of each scan in column G, put =COUNTIFS($B$2:B2, B2, $G$2:G2, G2)=1 in the first data row of a new column and fill it down. It is TRUE for the first scan of each code on each date. Filter on TRUE. On a QR to Sheets link set to Once a day the repeat is never written, so the log already holds one row per code per day.

Try it on your own Google Sheet

Connect Google, create a scanner link, and open it on the phone that will scan. Have that phone at hand: setup takes a few minutes and ends with your first row in the Sheet. Free covers 300 scans on one phone, and the people you scan never install anything.

Start for free

No credit card required

A second door, booth or classroom is a second phone

Free covers one phone and one scanner link. Premium lets several phones scan at the same time, one per door, booth or classroom, into the same Sheet: $7 per phone per month. Running several classes, events or lists from one phone? Premium on that one phone is $7 a month, with a separate scanner link for each. Paid yearly, it is $70 per phone per year (2 months free).

How many phones will scan at the same time?

$7 x 2 = $14 a month

Sign in with Google and, on the free plan, the billing page opens with this choice. Nothing is charged until you confirm the card checkout, and you can change the number of phones later.

Keep reading