A guard tour answers one question: was every checkpoint visited, and when. Dedicated guard tour systems answer it with hardware wands and metal tags. A printed QR tag at each checkpoint and a phone in the guard's pocket answers the same question for the price of lamination, and a Google Sheet is a perfectly good place for the answer to land.
Written by QR to Sheets, so read the product section as vendor material. The free Google Forms route is covered first, and the limits section applies to every QR-based tour, ours included.
The free route: a Google Form per checkpoint
Make one Google Form with a checkpoint field and a guard name field, then use the form's Pre-fill form option to generate a link per checkpoint with the checkpoint already filled in. Turn each pre-filled link into a QR code and put it on the tag. The guard scans the tag with the phone camera, the form opens with the checkpoint set, they pick their name and submit. Each submission is a timestamped row in the linked sheet, and it costs nothing.
Where it breaks on a real round
- Every checkpoint needs signal at the moment of submitting. Basements, car parks, plant rooms and stairwells are the places a patrol exists for, and the places a form submission is most often lost.
- It is slow. Open the form, pick a name, submit, wait for confirmation, at every tag, in the dark, with a torch in the other hand.
- The guard name is self-selected, so the log records whichever name was picked.
- A Google Apps Script web app can remove the form step, but the offline problem, the camera permissions and the upkeep become yours.
Scanning rounds with a scanner link
With QR to Sheets the guard opens a scanner link in the phone browser, allows the camera once, and scans each tag as they pass. It takes a second or two per checkpoint. Each scan becomes a row in your own Google Sheet with the time, the checkpoint code and the name of the link, so a Night round link and a Day round link separate themselves. Guards install nothing and create no account.
When signal drops in a basement, the scan is saved on the phone first and syncs when the guard walks back into coverage, and the phone keeps a CSV backup that can be downloaded from the scanner screen. The round carries on without the guard having to notice.
One behaviour to expect on repeating rounds: the scanner shows an amber Already scanned badge, with the words Saved anyway, when the same code repeats on the same link within 12 hours. On a route walked every hour, every checkpoint after the first round shows it. That is information, not an error; the row is written every time. Tell guards so in the same message as the link, or give each round its own link.
Finding a missed checkpoint
The log shows what was scanned. A missed checkpoint is something that is not in the log, so it only appears when you compare the log against the checkpoint list. With the scan log timestamps in column A and checkpoint codes in column B, and the checkpoint list in its own tab, a shift start in B1 and a shift end in C1:
- Visits per checkpoint in the shift: =COUNTIFS(Log!B:B, A2, Log!A:A, ">="&$B$1, Log!A:A, "<"&$C$1). Zero means missed.
- Last visit per checkpoint: =MAXIFS(Log!A:A, Log!B:B, A2). Conditional formatting that shades anything older than the round interval shows the gap at a glance.
- Longest gap between rounds: sort a copy of the log by checkpoint and then time, and subtract each row's time from the one before it.
- Keep the checkpoint list as the single source of what should be scanned. When a checkpoint is added, add it there first.
What a QR tour proves, and what it does not
A scan proves the checkpoint's code was shown to a camera at a time. It does not prove the guard stood at the checkpoint: a photo of a tag scans as well as the tag, and a determined guard can photograph a route once and scan it from the guard hut. QR to Sheets does not record GPS or any other location, so the tag itself is the only evidence of place.
- Mount tags where a photo is awkward to take in passing, and replace a tag if you suspect it has been photographed.
- Watch for scans of distant checkpoints seconds apart; nobody walks between them that fast.
- Spot-check in person occasionally. The log makes the question answerable; it does not answer it by itself.
- A Google Sheet is editable by anyone with edit access. Share it with clients as view-only, keep edit access narrow, and remember that File, Version history shows what changed.
| Capability | Google Form per checkpoint (free) | QR to Sheets |
|---|---|---|
| Cost | Free | Free for 300 scans, then 7 USD per registered device per month |
| At each checkpoint | Camera opens the form; the guard picks a name and submits | Camera reads the tag and the row is written |
| No signal at the checkpoint | Submission lost | Held on the phone, synced later |
| Separating rounds | A field the guard fills in | One scanner link per round or shift |
| Missed checkpoint report | A formula you write | A formula you write |
| Records GPS location | No | No |
| Where data lives | Your Google Sheet | Your Google Sheet |
Step by step
- 1
List the checkpoints in a tab of their own
One row per checkpoint, with a short code and a plain description: CP-07, East stairwell, level 2. This list is what makes a missed checkpoint visible later.
- 2
Print one tag per checkpoint
Encode the checkpoint code. The free bulk generator makes a printable tag per row; laminate them and fix them at a consistent height, out of direct rain and sun.
- 3
Create one scanner link per route or shift
Night round, Day round, Car park. The link name is written into every row, so rounds separate themselves in the sheet.
- 4
Walk the route once yourself
Scan every tag with the phone a guard will use, in the lighting of the actual shift. A tag that will not read at 2am under a sodium lamp needs moving before the first real round.
- 5
Check the round, not just the rows
A formula comparing scans against the checkpoint list for each shift shows what was missed. Review it daily for the first fortnight, then weekly.
Frequently asked questions
Do guards need an app for a QR guard tour?+
Not with a scanner link. The guard opens a URL in the phone browser, allows the camera once and scans each tag. Nothing is installed and there is no account. A Google Form route also needs no app, but it needs signal at every checkpoint.
Does it record the guard's location?+
No. QR to Sheets does not record GPS or any other location. The checkpoint code in each row is the evidence of place, which is why tags should be fixed where they cannot easily be photographed.
How do I know if a checkpoint was missed?+
Keep a tab listing every checkpoint and count each one's scans within the shift with COUNTIFS. A count of zero is a missed checkpoint. The log alone cannot show a missed checkpoint, because a missed checkpoint is a row that does not exist.
Why does every checkpoint show an amber badge after the first round?+
The scanner flags a code repeated on the same link within 12 hours, with the words Saved anyway. On an hourly route every checkpoint repeats. The scan is still written each time; the badge is information. Give each round its own link if you would rather not see it.
Can a guard fake a round?+
Yes, with photos of the tags, and no QR system can rule that out on its own. Mount tags where a photo is awkward, watch for distant checkpoints scanned seconds apart, and spot-check in person.
What happens in a basement with no signal?+
The scan is saved on the phone and syncs when the guard is back in coverage. The phone also keeps a CSV backup of its scans that can be downloaded from the scanner screen.