Asset tracking fails for organisational reasons far more often than technical ones. The spreadsheet is rarely the problem. The problem is that someone borrowed a laptop in March, nobody recorded it, and now the register says it is in the cupboard.
A QR label plus a phone scan makes recording a movement fast enough that people actually do it. That is the whole mechanism.
What to encode on the label
Encode the asset ID and nothing else. It is tempting to encode a description or a URL, but both go stale.
- IDs are stable. Descriptions change when equipment is repurposed.
- A short ID scans faster and more reliably than a long string.
- A found label reveals nothing sensitive about what the item is or who has it.
- If you later move to a different system, IDs port cleanly.
Two tables, not one
The most common mistake is a single sheet where scanning overwrites a status column. It looks simpler and it destroys your history.
- Movement log: append-only, one row per scan. Timestamp, asset ID, direction, who scanned. Never edited.
- Asset register: one row per asset, with current status derived from the latest movement using a lookup.
With that separation you can answer questions that matter later: who had this item in June, which items have been out longest, which are overdue, and how often each is actually used. A single overwritten status column can answer none of these.
Keeping the log append-only also means a mistaken scan is corrected by adding a correcting entry rather than editing history. That is what makes the record trustworthy in a dispute.
Check-out and check-in without a complex app
The simplest workable model is two scanner destinations: one that records an outbound movement and one that records a return. Staff pick the right one and scan. No dropdowns, no typing.
With QR to Sheets, that is two scanner links pointing at the same Sheet, or at two tabs. Whoever is handing out equipment opens the check-out link; whoever receives returns opens the check-in link. Neither needs an account. Check in and check out with QR codes has the formula that shows what is still out, and the warehouse scanning page shows the scanner side.
Common cases worth planning for
- Damaged labels. Keep a manual entry fallback and a way to reprint a label for an existing ID.
- Bulk items. Consumables that are not individually tracked need a count field, not a per-item label.
- Assets that never come back. Decide the point at which an item is written off rather than left permanently outstanding.
- Multiple locations. Record location as its own column rather than inferring it from who scanned.
- Shared assets used many times a day. Consider whether every movement needs recording, or only movements between people.
Reporting
Because everything is in a Sheet, reporting is ordinary spreadsheet work: currently-out items via a lookup on the latest movement per asset, overdue items by comparing check-out date against an expected return window, utilisation by counting movements per asset, and loss rate by tracking items with no return scan after a threshold.
Step by step
- 1
Give every asset a permanent identifier
Assign an asset ID that never changes, even if the item is repaired, reassigned, or renamed. Everything else in the system hangs off this identifier.
- 2
Label the assets
Print QR labels encoding the asset ID and fix them where they will survive handling. A label that falls off after a month makes the whole system unreliable.
- 3
Build the asset register
One row per asset in a Sheet: ID, description, category, purchase date, current status, current holder. This is the reference table, not the movement log.
- 4
Keep a separate movement log
One row per scan: timestamp, asset ID, direction, who scanned. Never edit this log. The register is derived from it, not the other way round.
- 5
Scan on every movement
Check-out when an item leaves, check-in when it returns. The discipline of scanning both directions is what makes the data worth anything.
Frequently asked questions
Should the QR code contain the asset name or the asset ID?+
The ID. Names change when equipment is repurposed, IDs do not, and an ID keeps a lost label from revealing what the item is. Everything else about the asset lives in the register, keyed on that ID.
Do I need one sheet or two for asset tracking?+
Two. An append-only movement log with one row per scan, and an asset register with one row per asset whose current status is derived from the log. Overwriting a status column in a single sheet destroys the history you will eventually need.
How do I handle an item that is scanned out twice without being returned?+
Treat it as a data question rather than an error. The log shows two consecutive outbound movements, which usually means a return went unrecorded. Add a correcting entry rather than editing the original rows.
Can this work for school device loans?+
Yes, it is the same pattern. Label the devices, scan out when a student takes one, scan in on return, and derive the current holder from the latest movement. Encode the device ID and the student ID separately rather than combining them.
What about assets that are used many times a day?+
Decide whether every movement genuinely needs recording. For high-frequency shared equipment, tracking only transfers between people, or only overnight movements, often gives you the accountability you need without scan fatigue.