Check in / check out with QR codes and Google Sheets

Most tracking guides assume one scan per item. A check in / check out log is different in kind: the same code is scanned at least twice, and the answer you want — who is on site, what is still out, how long it was gone — lives in the gap between the two rows rather than in either row on its own.

That distinction sounds pedantic until the first reconciliation. A single-event log can be messy and still useful. A two-event log with a missing second scan does not just lose one row; it leaves an item permanently marked as out, and after a fortnight of that the sheet is telling you nothing you trust.

People or objects: the same scan, different failure

The mechanics are identical and the failure modes are not. Decide which one you are building before you choose a method.

People: shift sign in and sign out

• The forgotten scan is the going-home scan, because nobody is waiting on it.
• An open shift at midnight is usually a missed sign-out, not someone still working.
• Staff will not install anything, and temporary crews change weekly.
• Attendance data has a payroll and duty-of-care use, so gaps are expensive.

Objects: equipment out and back

• The forgotten scan is the return, because the item is already back on the shelf.
• An item marked out for a month is usually a missed check-in, not a theft.
• The code lives on the item, so it is stable and can be printed once.
• You often want a condition note on return, which a bare scan does not capture.

The free route: Google Forms

This works, costs nothing, and is the right answer for a small stable group. Build two Google Forms — one Check out, one Check in — each with a short-answer field for the code and a dropdown for the person. Take the QR code for each form from the three-dot menu, print both, and tape them where the movement happens.

1. Create two forms, one per direction, each writing to the same spreadsheet as its own tab.
2. Add a required short-answer field for the item code or staff name.
3. Generate each form's QR code and label the printouts clearly: OUT here, IN there.
4. In a third tab, use COUNTIF across both response tabs to show what is currently out.

Where it breaks is specific and worth knowing before you commit. A Google Form has no camera scanner of its own, so the phone camera reads the poster's QR to open the form, and the item's own barcode still has to be typed. A submission attempted with no signal is lost, not queued, which matters at a store-room door or a site gate. And there is nothing stopping the same person scanning out twice, which quietly corrupts the count.

The middle route: Apps Script

A Google Apps Script web app can host a real camera scanner and append rows directly, which removes the typing. You own the code and pay nothing. In exchange you own the failures: authorisation scopes when the sheet owner changes, execution quotas, a re-deploy for every fix, and the offline problem is still yours to solve.

This is a good fit if someone on the team genuinely maintains it. It is a poor fit if it will be written once by whoever is technical and then inherited by someone who is not.

The hosted route

A hosted scanner link is a URL that opens a camera scanner in the browser and appends a timestamped row to your Google Sheet. Make two links, name them Check out and Check in, point both at the same spreadsheet, and print the QR for each. Workers open a link and scan; there is no app, no account and no sign-up.

The reconciliation formula

This is the part people skip, and it is the whole point. With one tab of check-outs and one of check-ins, a per-code comparison of the two counts tells you what is still out. Anything where outs exceed ins is unreturned; anything where ins exceed outs means a scan happened in the wrong direction and needs correcting at the source, not in the formula.

• Count outs and ins per code, then subtract. A positive number means it is still out.
• Sort by the oldest unmatched check-out — that is your chase list.
• Investigate negatives immediately; they mean the two stations are being confused.
• Review weekly. A reconciliation nobody reads is the same as no reconciliation.

Which to choose

• A dozen items and one trusted person: two Google Forms. Do not overbuild it.
• Codes already printed on the items and several people scanning: a hosted scanner link earns its cost in avoided typing alone.
• Poor signal at the door: anything that submits over the network at the moment of scanning will fail. Test in the actual doorway.
• Reservations, overdue reminders and per-item history: buy dedicated asset software.

What happens if the same code is scanned twice at the same station?

In a two-event log this is worse than a nuisance. A duplicate check-out makes an item look like it left twice, so the reconciliation formula shows it as still out even after it has been returned and scanned back in. One accidental double tap at the store-room door can put a laptop on your chase list for a fortnight.

It happens for ordinary reasons. Somebody is not sure the scan registered and tries again. Two people scan the same trolley of kit within a minute of each other. A phone is held over a shelf of labelled tools and reads the nearest one repeatedly.

The design question is whether the system should refuse the repeat or record it and warn. We record and warn, and for check in and check out that is a deliberate choice rather than a limitation. When the same value is scanned again within a twelve-hour window, an amber badge shows the repeat count and states that the row was saved anyway. The scan is never blocked, the row is always written, and if the check cannot run it fails open rather than refusing a legitimate scan. Whoever is at the station sees the warning at the moment it can still be acted on, which is worth far more than a silent correction discovered at the weekly reconciliation.

It also preserves the case that a suppressing system cannot handle: a tool genuinely going out twice in one day. If the second scan had been thrown away, that trip would not exist in your data and no formula could recover it. The cost of this choice is that your reconciliation tab has to tolerate duplicates, grouping by code and day or filtering on the repeat before subtracting, which is spreadsheet work you own either way since the Google Forms route offers no duplicate signal at all.

Can one person check out equipment using someone else's code?

Yes, and it is worth being direct about it because chain of custody is often the reason people build these logs in the first place. A scan records that a code was presented to a camera. It does not record who was holding it. A staff badge can be lent, a code on a phone screen can be forwarded, and at an unstaffed station nothing observes the transaction at all.

That has a specific consequence for equipment logs: if you scan only the item, you know what left and when, but not who took it. If you scan a person code and then the item code, you know who claimed it, which is a much better record and still not proof. Both are enormously better than a signature on a clipboard, and neither is a chain-of-custody audit trail in the legal sense. If you need that, this is the point where dedicated asset software with per-item history stops being overkill.

• Put the station where somebody can see it: a staffed counter or a doorway in view, not a back corridor.
• Print codes on the items themselves. An item code cannot be lent independently of the item.
• Scan the person and then the item as a pair, so an unmatched item scan is visibly incomplete.
• Review the log for outs that cluster within a few seconds; that pattern usually means one person scanned several badges.
• Accept the limit and design the process around it rather than assuming the technology closes it.

Can two people share one scanning device?

Yes, and for most check in and check out setups a shared station is the right shape. A scanner link is a public URL with an eight-character token, so it can be open on a tablet at the store-room door all day and used by everybody who walks past. There is no login to switch, no session to sign out of, and no worker account to provision when the crew changes on Monday.

The billing follows the same shape, which is what makes shared stations practical. On the paid plan you pay 7 USD per registered device per month. One link serves unlimited phones and the device limit belongs to the workspace, not to the link, so a store room with one tablet on the In link and one on the Out link is two devices regardless of how many people use them. Nobody is licensed per user, which matters when the people doing the scanning are temporary crew.

The trade-off is that a shared station has no identity. Every row from that tablet says the same thing about who scanned it. If you need to know which staff member checked a drill out, the identity has to be in the data itself: scan a person code before the item, or split the flow so that the person's own phone opens the link rather than the shared tablet.

How do I get this data into our HR, payroll or asset system?

Because both routes write into a normal Google Sheet that you own, this is a spreadsheet export problem rather than an integration project, which is the main reason people choose a sheet over a closed system. There is no vendor database to request an export from and no API to negotiate.

The practical work is in shaping the data, not moving it. Payroll and HR systems want one row per person per day with a total, not a stream of raw events, so build a reporting tab that reconciles the two event logs into whatever shape the receiving system imports and export that tab rather than the raw log. Keep the raw log untouched underneath as the record of what actually happened.

• Export the reporting tab as CSV, which is what most HR, payroll and student information systems accept.
• Match the receiving system's identifier exactly. An employee number or asset tag in the scanned code imports cleanly; a person's name does not.
• Consider encoding a delimited value such as BADGE-001|John Smith|Contractor and splitting it into columns on the way in, so the import file needs less rework. Note that tracking columns come first, so your first custom column is spreadsheet column D.
• Keep the timestamp column a real date. This is where an ISO string stored as text hurts most: the export looks fine and the receiving system rejects or misreads every row.
• Never delete rows from the raw log to tidy an export. Copy to a reporting tab instead, since our product never deletes a row from your sheet by design and you should hold yourself to the same rule.

Step by step

1. 1

   Decide what one scan means before you print anything

   Check in / check out is the one workflow where an ambiguous row is worthless. Write down whether a scan means the item left, the item returned, the person arrived or the person went home. Every later problem traces back to skipping this.

2. 2

   Choose a direction model

   Two scanner links (one In, one Out) is the model that survives real use. One link that alternates direction per code is tidier on paper, but a missed scan inverts every state after it and nobody notices until the report is wrong.

3. 3

   Label the thing, not the person, where you can

   A code on a laptop, a toolbox or a radio is permanent. A code on a lanyard walks out of the building. For staff, a printed sheet by the door usually beats per-person badges for the first month.

4. 4

   Put the two stations where the movement actually happens

   The Out station belongs at the exit and the In station at the return point. A single desk in the middle guarantees people scan when convenient rather than when true.

5. 5

   Reconcile with a formula, not by eye

   The value is in the difference between the two logs, not in either one alone. A COUNTIF per code comparing outs to ins tells you what is still out; reading two tabs side by side does not.

Frequently asked questions