Uniform and PPE issue log with QR codes in Google Sheets

Uniform and PPE issue is usually recorded on a clipboard by the store-room door, or not at all. That holds until someone asks how many polos a new starter has had this year, why the medium aprons ran out a week after a delivery, or whether the safety boots that a leaver never returned were ever issued to them in the first place. A clipboard can answer none of those without an afternoon of transcription.

The job itself is simple. Every time an item leaves the store, write down who got it, what it was, what size, and when. Done consistently, that one log gives you per-person allowances, stock on hand and a reorder list, all from formulas. The hard part is the consistently, at a busy counter, with a queue of staff waiting to start a shift.

The free route: a Sheet template and a Google Form on the counter

Most uniform issue templates are a single tab with columns for date, employee, item, size, quantity and issued by. Filled in by hand, it is a clipboard in a spreadsheet. The better free version is a Google Form linked to that Sheet, opened on a phone or tablet at the counter.

1. Create a Google Form with a dropdown for the employee, a dropdown for the item, a dropdown for the size and a number field for quantity.
2. Link it to a Google Sheet. Each submission becomes a row with Google's own Timestamp column.
3. Keep the form open on the counter device, and submit once per handover.
4. Build the allowance and stock tabs described below over the response sheet. The formulas are the same whichever route fills the log.

For a team of ten that issues a few items a month, stop there. It costs nothing and it is better than paper. Where it breaks at a busy counter is specific:

• Typing names. A free-text employee field produces Jon, John and J. Smith for the same person, and every count by employee is then wrong. A dropdown fixes the spelling but becomes a list of sixty names that has to be edited every time someone starts or leaves, and picking the neighbour in a long list is an easy slip.
• Misspelt sizes. A text size field collects M, Med, medium and 10/12 for the same garment. Dropdowns fix it, at the cost of four taps per handover.
• Timestamp trust. A typed template date is whatever the person filling it in wrote, often at the end of the shift. A Google Form's Timestamp is the moment of submission, which is trustworthy for when the form was sent, not for when the polo left the shelf if the form was filled in later from memory.
• Signal. A form needs a connection when Submit is pressed. Store rooms at the back of a building are often where the signal is weakest.
• Anyone with the form can record an issue to anyone, which is fine, but then a dropdown choice is the only evidence of who received the item.

Issued is not loaned

Before choosing a layout, sort the items into three kinds. They need different records, and putting all three into one log is how a tracker stops making sense.

• Consumables: disposable gloves, hairnets, earplugs, masks. They are used up and never come back. A per-person record is rarely worth the scan; a count of boxes leaving the store is usually enough.
• Personal issue: polos, aprons, chef jackets, caps, safety boots. The employee keeps them until they wear out. This is what an issue log is for: how many has each person had, and is it within their allowance.
• Returnables: radios, harnesses, keys, a jacket that must be handed back on leaving. Here the question is what is still out, which needs a scan out and a scan back in.

This guide covers the first two. For returnables, use the two-link loan pattern in Chromebook and device loan checkout, which works the same for a radio as for a laptop, and the reconciliation formula in check in and check out with QR codes. A uniform that must be returned on leaving can sit in both: issued here, and checked back in at the exit interview.

Setting up one link per item and size

With QR to Sheets, a scanner link is a web address that opens a camera scanner in the phone browser and writes each scan as a row in your Google Sheet. Nobody on the counter installs anything or signs in. The setup that answers who got what in one row is one link per item and size, with the employee's badge as the thing being scanned.

• Create a link for each item and size you issue: Polo S, Polo M, Polo L, Apron, Cap, Boots 42. Use one naming pattern and stick to it, because the formulas match on these names.
• Point every link at the same Sheet and the same tab, named Issues.
• On the counter phone, save each link as a bookmark under the same name. To issue a medium polo, open Polo M and scan the employee's code.
• Each scan adds one row: the time of the scan in column A, the employee code in column B, and the link name in column C, written as link:Polo M. Columns D to F hold the workspace name, the device type and a scan ID.

Scan once per unit. Three polos is three scans of the same badge on the same link. By default a repeat of the same code on a link within twelve hours is still saved and flagged with an amber already-scanned notice, which here is useful: if the issuer only meant to hand over one, the notice appears while the employee is still at the counter. Keep the link's Accept duplicate scans setting on. Switched off, a link records each code once, ever, which would refuse that employee's replacement polo next year.

If the store room has no signal, scans are kept on the phone and sent when the connection returns, keeping the time they were scanned. The person on the counter never needs access to the spreadsheet itself.

The trade-off: lots of links

Six items in five sizes is thirty links, and switching between them is the cost of this design. It is clearest for items with a handful of sizes. For an item with many sizes, such as safety boots, there are two workable options. Keep one link per size if boots are issued often. Or use one link per item, named Boots, and record each employee's boot size once in a staff register tab, then look the size up by employee code. The second option has a real weakness: the size column then shows the size on file today, not the size handed over, so a change of size quietly rewrites history unless you add a new register row instead of editing the old one.

Why not scan the badge and then the item on one link?

Because each scan is its own row, and nothing joins a badge scan to the item scan after it. You would be pairing rows by position with a formula, and one missed or doubled scan shifts every pair after it onto the wrong person. One scan per row is the only layout where each row can be trusted on its own.

Why not item codes on a single link?

You can label each shelf or bag with a code such as POLO|M and use one link called Counter. Column mapping splits that value into item and size columns, so the row says what left the store and when. What it cannot say is who received it, because the only other thing on the row is the link and the device. That makes it a good fit for consumables, where a stock-out log is all you want, and a poor fit for personal issue.

On price: the free plan is one scanner link, one Sheet, one registered device and 300 scans in total, not per month. That is enough to run one item line from one counter phone, or to trial the whole setup on a single item. A full set of item-and-size links needs the paid plan, at 7 USD per registered device per month with unlimited links and scans. One counter phone is one device, whatever the headcount, so nobody pays per employee.

Printing employee badge codes

The code on the badge should be the employee ID and nothing else. A name changes, gets spelt two ways, and puts personal data on a card that will be lost in a car park. An ID such as E1042 does none of that, and when a badge is lost you reprint the same ID rather than starting a new history.

• Use a letter prefix. A bare number such as 01042 can lose its leading zero in a sheet, or fail to match in a formula when one cell holds a number and the other holds text.
• If staff already carry ID cards with a barcode of their employee number, scan those. The scanner reads common 1D barcodes as well as QR codes.
• Otherwise, paste the list of IDs into the free bulk QR code generator and print them on label sheets for badges or lanyard cards. It needs no signup.
• Keep a staff register tab with the ID, name, role, start date and sizes. The name lives here, once, and every report looks it up from the code.

Allowance per employee with COUNTIFS

Most places set an allowance: three polos and two aprons a year, one pair of boots every twelve months. Build an Allowance tab with item names across row 1 (Polo, Apron, Boots), each item's allowance in row 2, employee codes down column A from row 3, and the start of the allowance period in cell A1, for example =DATE(2026,1,1).

• Issued since the period start, all sizes, in B3 and filled across and down: =SUMPRODUCT((Issues!$B$2:$B=$A3)*(LEFT(Issues!$C$2:$C, LEN("link:"&B$1))="link:"&B$1)*(IFERROR(VALUE(Issues!$A$2:$A), 0)>=$A$1))
• Matching on the start of the link name is what counts every size together, so Polo S, Polo M and Polo L all count towards the Polo allowance. Name links so that one item's name never starts another's.
• Over allowance: a conditional formatting rule on B3 onwards with the custom formula =B3>B$2 turns the cell red when someone has had more than their allowance.
• Names next to codes: =XLOOKUP($A3, Staff!$A:$A, Staff!$B:$B, "Unknown code") pulls the name from the staff register, and Unknown code exposes a badge that was never registered.

The VALUE() part is deliberate. QR to Sheets writes each timestamp as YYYY-MM-DD HH:MM:SS, and VALUE() turns that into a date-time whether the cell holds a date or text. A plain COUNTIFS with a date criterion counts nothing when the column is text, so check with =ISTEXT(Issues!A2) before swapping in a simpler formula.

Stock on hand: received minus issued

Nothing in this setup decrements stock when a scan arrives. Stock on hand is a formula: everything that came in, minus everything that went out. Keep what came in on its own Received tab, typed by hand, because deliveries arrive in bulk and are rare enough that typing them is quicker than scanning thirty identical bags.

• Received tab: date in column A, item and size in column B spelt exactly like the link name (Polo M), quantity in column C, and a note in column D such as the supplier, a laundered return, or a count correction.
• Stock tab: list every link name in column A, then in B2: =SUMIFS(Received!$C:$C, Received!$B:$B, $A2) - COUNTIFS(Issues!$C:$C, "link:"&$A2)
• This match is exact rather than on the start of the name, because stock is counted per size.
• Reorder level in column C, and a flag in D2: =IF(B2<=C2, "Reorder", "")
• Count the shelf monthly. When the count disagrees with the formula, add a correction row to Received with a negative or positive quantity and a note saying why. The gap is your shrinkage, and it stays visible instead of being overwritten.

Laundered uniforms that go back on the shelf belong on the Received tab too, as a returned quantity with a note. Otherwise the formula will show the size running out while the shelf is full.

Sign-off and audit trail

Each row carries the time of the scan, written in the timezone set for the workspace, so the log shows when the item left rather than when someone got round to writing it down. A scanner link only ever adds rows to your sheet; it never edits or deletes one. Make the log hold its shape on your side as well:

• Protect the Issues tab with Data, then Protect sheets and ranges, so only the owner can edit it, and keep the Google account connected to QR to Sheets as an editor or new scans cannot be written. The counter staff do not need the sheet shared with them at all, because the scanner link writes the rows.
• Do all reporting on other tabs. Never sort, filter in place, or tidy the raw log.
• Use version history to see when the sheet changed and restore an earlier version. Google's help notes that the per-cell Show edit history does not show added or deleted rows, so a deleted row is found through version history, not cell history.
• Correct a mistake with a new row, such as a negative entry on the Received tab, never by deleting the original.

Be clear about what the log proves. It shows that a badge code was scanned at the counter at a given time. It is not the employee's signature, and it does not show that the person holding the badge was the person it belongs to. If your policy needs a signed acknowledgement, for example for a uniform deposit or anything deducted from pay, collect that signature separately and check your local rules. The scan log then sits beside it as the record of what was handed over and when.

Step by step

1. 1

   Give every employee an opaque code

   Use an employee ID with a letter prefix, such as E1042, never a name. Print it on a badge or a laminated card, or use the barcode already on a staff ID card if it carries the same ID.

2. 2

   Create one scanner link per item and size

   Name each link exactly as the item and size, such as Polo M or Apron, and point them all at the same tab. Save each link as a bookmark on the counter phone under the same name.

3. 3

   Scan the badge once per unit handed over

   Open the link for the item on the counter, scan the employee's code, and hand the item over. Three polos is three scans; the amber repeat notice on the second and third is expected.

4. 4

   Type deliveries into a Received tab

   Deliveries are rare and arrive in bulk, so type them: date, item and size spelt exactly like the link name, quantity, and a note. Laundered returns and count corrections go here too.

5. 5

   Count allowances and stock with formulas

   COUNTIFS over the issue rows gives each employee's count per item since a start date. Received minus issued gives stock on hand. Neither is maintained for you; both are formulas you own.

6. 6

   Protect the log tab

   Restrict editing of the issue tab to the owner and keep reporting on separate tabs, so the raw log stays exactly as it was written and version history can show any change.

Frequently asked questions