Most schools and many workplaces have already done the expensive part of a scanning register: every student or employee carries a card with a number printed on it as a barcode. The only missing piece is something that reads that code and writes a row with the time into a Google Sheet. Done well, nobody reprints anything, and a class of 30 is registered in about the time it takes to walk past the door.
This guide covers what is actually on a typical ID card, two free ways to get it into a sheet, where each one breaks, and the formulas that turn a list of scanned IDs into names and a late list.
Written by QR to Sheets, which sells the phone scanning described in the second half, so weigh that accordingly. The free routes come first, and at a single desk the first one is genuinely enough. The barcode formats listed for QR to Sheets were checked against its own scanner on 2026-10-08. Google behaviour, barcode conventions and the CodeREADr facts were read on their own pages the same day.
What is on a typical student or staff ID card?
Turn a card over before planning anything. The number you care about is usually printed in plain digits next to a code, and the code is one of these:
- A 1D barcode in Code 39. It is the long-standing default for student numbers: a barcode-font vendor's guide recommends it because most student ID numbers are no more than 13 characters and Code 39 is simple to encode.
- A 1D barcode in Code 128, which is denser and handles longer IDs that mix letters and digits.
- A library-style barcode. A library supplier names Code 39, Codabar and Follett Interleaved 2 of 5 as the three most common library symbologies, often holding a 14-digit number whose first digit marks a patron rather than a book.
- A QR code, which is more common on newer cards and on cards a school prints itself.
- A chip, a proximity tag or a magnetic stripe. If the card only works by tapping or swiping it on a reader, there is nothing for a camera to read, and none of the scanning routes in this guide will work with it.
Which of these a phone browser can read with QR to Sheets
The QR to Sheets scanner reads QR codes, Data Matrix, PDF417, Code 128, Code 39, Code 93, EAN-13, EAN-8, UPC-A, UPC-E and ITF (interleaved 2 of 5). It does not read Codabar, so a card with a Codabar library barcode will not scan. Scan one real card before a school-wide plan depends on it, especially an older or laminated one.
Check the number the card actually encodes
A scan writes exactly what the code holds, which is not always the number printed under it. Some cards add a prefix, a suffix or a check digit, and a 14-digit library-style number also carries a code for the library. Scan one card into a test row, compare it with your student or staff system, and store IDs on your People tab in the form the card encodes. A lookup only matches an exact copy.
Free route 1: a USB barcode scanner typing into the sheet
A USB barcode scanner behaves like a keyboard. Open the sheet, click the first empty cell in column A, scan a card, and the ID is typed in with Enter, so the cursor moves down ready for the next card. At one fixed desk, such as a school office logging late arrivals, it is fast, cheap and needs no software. Format column A as plain text before the first scan, or an ID such as 01042 is stored as the number 1042 and no longer matches the People tab.
It types the ID and nothing else, so on its own you get no time. A formula such as NOW() beside it is not a timestamp: it recalculates, and every row ends up showing the current time. Add the time with a few lines of Google Apps Script instead. In the sheet, open Extensions, Apps Script, paste this, and save:
- function onEdit(e) { if (e.range.getColumn() === 1 && e.value) e.range.offset(0, 1).setValue(new Date()); }
- It writes the date and time into column B whenever an ID lands in column A. Google's documentation says onEdit runs automatically when a user changes the value of any cell, and a scanner typing counts as a user typing.
- new Date() writes a real date and time, which Google Sheets stores as a date, so the formulas later in this guide work on it.
This is a perfectly good register for one desk. Where it breaks:
- The sheet must be open on that computer with the cursor in the right column. Click somewhere else, and the next ID is typed into whatever cell is selected.
- Google states that simple triggers do not run when a file is opened in view or comment mode, and cannot run for longer than 30 seconds. Whoever runs the desk therefore needs edit access to the whole spreadsheet, names and all.
- A scanner on a cable stays at the desk. It cannot walk to a classroom door, a sports hall, a bus or a field trip.
- One desk is one queue. Two entrances need two computers, each with the sheet open.
Free route 2: a Google Form
A Google Form with one short-answer question, Student ID, linked to a spreadsheet, gives each submission a row with a real timestamp, free. Print a QR code that opens the form, and students submit their own number on their own phones. The full free setup for a school, including the settings that matter, is in QR code attendance for schools.
- A Google Form has no barcode scanner, so the card is not scanned at all. The number is typed, and every typo becomes a row that matches nobody on your list.
- Students need a phone, a signal and a minute each, which turns a 30-second doorway into a queue.
- Anyone with the form link can submit from anywhere, including from home, for a friend.
If the cards already exist, typing their numbers into a form throws away the reason they were printed. That is the gap the next section fills.
How do I scan ID cards with a phone at the door?
QR to Sheets turns a phone browser into the card reader. The admin signs in with Google once, connects the spreadsheet, and creates a scanner link that writes to one tab, here called Log. The teacher or staff member taking attendance opens that link in Safari or Chrome, allows the camera once and scans each card as people come in. There is no app and no account for the person scanning, and they never get access to the spreadsheet.
Each scan becomes a row on the Log tab: the scan time in column A, the card's value in column B, the scanner link it came through in column C (written as link:Main gate), then the workspace name, the device type and a scan ID. The scanner link uses the phone's camera, and the students or staff being scanned never open the link and need nothing.
- Each phone that scans counts as one device. Free covers one phone; Premium is $7 per phone per month. One phone at the gate is one device however many cards it scans.
- A self-scan station works the same way: a tablet on a stand where each person holds their card to the camera is one device. The setup is in a QR check-in kiosk on a shared tablet.
- A worn or forgotten card: type the ID into the scanner's manual entry field, which writes the same kind of row.
- A card scanned twice: by default the second scan is saved anyway, and if the same card was scanned on that link in the last 12 hours the phone shows an amber Saved anyway badge. Keep the link's Accept duplicate scans setting on for daily attendance. Switched off, a link records each card once, ever, and would refuse that card tomorrow.
- If the signal drops, the open scanner keeps saving scans on the phone and sends them when the signal is back, each with its original scan time.
- Several entrances: give each door its own scanner link, named after the door, so column C says where each person came in. The free plan has one link; Premium has unlimited links.
Two honest limits. The phone shows the code it just scanned and a confirmation, not the person's name, because the names live in your sheet rather than in QR to Sheets; showing the name for a scanned ID covers the lookup. And a scan proves a card was presented, not who was holding it. A card lent to a friend scans like any other, so have staff scan each card with its holder in front of them. Whether QR attendance can stop buddy punching goes into that in detail.
Who came in late? Formulas for a card-scan log
Once IDs are arriving, a Present tab can list everyone scanned today with their first scan time and a Late flag. These formulas read a Log tab laid out the way QR to Sheets writes it: time in column A, ID in column B. On the USB scanner sheet above the ID is in column A and the time in column B, so swap Log!$A and Log!$B in each formula. Put headers in row 1 (ID, First scan, Status) and the formulas in row 2:
- ID, in A2: =IFERROR(UNIQUE(FILTER(Log!B2:B, IFERROR(INT(VALUE(Log!A2:A)), 0)=TODAY())), "") lists each card scanned today once, and stays blank before the first scan of the day.
- First scan, in B2, then filled down as far as your longest class or staff list: =IF(A2="", "", MIN(FILTER(IFERROR(MOD(VALUE(Log!$A$2:$A), 1), 0), Log!$B$2:$B=A2, IFERROR(INT(VALUE(Log!$A$2:$A)), 0)=TODAY()))). Format column B as Time.
- Status, in C2, filled down the same way: =IF(A2="", "", IF(TEXT(B2, "hh:mm:ss")>"08:30:00", "Late", "On time")). Change 08:30:00 to your own start time, keeping two digits for the hour. Comparing the times as text, to the second, means a scan at exactly 08:30:00 counts as on time rather than tripping over a rounding error.
- A repeat scan later in the day does not move anyone's first scan time, because B2 takes the earliest scan for that card.
- Add the name in D2 with a lookup against your People tab: =IFERROR(VLOOKUP(A2, People!A:B, 2, FALSE), "Not on the list").
Why VALUE() is there: QR to Sheets writes each time as YYYY-MM-DD HH:MM:SS in the workspace timezone, and Google Sheets stores it as text. VALUE() turns that text into a date and time, INT() keeps the day, and MOD(..., 1) keeps the time of day. VALUE() also accepts a real date, so the formulas work on the USB scanner sheet too, where the script writes a real date, once the two columns are swapped as above. Check your own column with =ISTEXT(Log!A2).
TODAY() follows the spreadsheet's own time zone, set in File, Settings. Set it to the same zone as the scans, or near midnight today will mean the wrong day. A list of who has not scanned at all is a People tab minus today's IDs, which finding who is absent builds step by step.
No barcode on the cards? Print QR ID cards for free
Some schools have no cards, some cards carry only a photo and a name, and some carry a format a phone cannot read. In each case the fix is the same: print a card per person whose QR code holds only their ID. The free QR code ID card maker takes a pasted list of names and IDs, needs no signup, makes the cards in your browser so the list is never uploaded, and prints credit-card size cards at 10 per A4 sheet or 8 per US Letter sheet.
- Put the ID in the code, not the name. A lost card then shows a number and nothing else, and a name change is one cell on the People tab rather than a reprint.
- Use the same IDs as the People tab, so the lookup and the late list work unchanged.
- Print a few spares for new starters and visitors.
Which route fits your school or site?
| Route | Reads the card | Timestamp | Where it works | Access to the spreadsheet | Cost |
|---|---|---|---|---|---|
| USB barcode scanner and Google Sheets | Yes, the barcode, typed in as keystrokes | Only with an Apps Script, which writes a real date | One desk, with the sheet open on that computer | The person at the desk needs edit access | Free apart from the scanner |
| Google Forms | No, the ID is typed | Yes, a real date | Any phone with a signal | Responders only fill in and submit; keep View results summary off | Free |
| CodeREADr | Yes, with the CodeREADr mobile app for Android and iOS | Yes | Phones or a self-service kiosk with the app installed | Scans are copied to Google Sheets through the CodeREADr Connector | Not stated in the article |
| QR to Sheets | Yes, QR codes and common 1D barcodes, not Codabar | Yes, text in the workspace timezone, read with VALUE() | Any phone browser, at a door, a gym or a bus | The person scanning never gets access | Free for one phone and 300 scans in total, then 7 USD per registered device per month |
Where each one wins. A USB scanner is the best value at a single desk where the sheet is open anyway. A Google Form is right when there are no cards and every person has a phone. CodeREADr's own article describes the app checking each scanned ID against a list kept in Google Sheets, and mentions IDs with RFID or NFC tags as well as barcodes, so it fits a site that wants a valid or not valid answer at the door, or reads tap cards. QR to Sheets fits staff who scan existing cards on a phone they already carry, with no app to install and nobody else given access to the spreadsheet.
What it costs
The free plan is one scanner link, one Google Sheet, one registered device and 300 scans in total, not per month. That is about ten school days for one class of 30, enough to prove the setup with real cards. Beyond it, Premium is $7 per phone per month with unlimited scans and links, so a school with one phone at the main gate and one at the side gate pays for two devices, whatever the number of students. The people being scanned are never counted.
Step by step
- 1
Find out what is on the card
Look for a barcode or a QR code on the front or back. Scan one card into a test row and compare the value with the ID number your school or HR system uses, including any prefix or check digit.
- 2
Build a People tab
List every ID exactly as the card encodes it in column A, with the name in column B and the class or team in column C. Format column A as plain text before typing, so leading zeros survive.
- 3
Choose where the scanning happens
A USB scanner on a desk computer suits one fixed spot such as a school office. A staff phone with a scanner link suits a door, a gym, a bus or a field trip.
- 4
Add names and a late list
Look each scanned ID up on the People tab, and add a Present tab that lists everyone scanned today with their first scan time and a Late flag.
- 5
Print cards for anyone without one
If some people have no card, or the cards carry no code, print QR ID cards that hold only the ID number, using the same IDs as the People tab.
Frequently asked questions
Can I scan student ID cards into Google Sheets with a phone?+
Yes, if the card carries a QR code or a common 1D barcode such as Code 39 or Code 128. A scanner link from QR to Sheets opens in the phone browser, reads the card with the camera and writes a timestamped row to your Google Sheet. Cards with only a chip, a proximity tag or a magnetic stripe cannot be read by a camera.
What kind of barcode is on a student ID card?+
Usually Code 39 or Code 128, sometimes a QR code on newer cards. Library-style cards often use Codabar, which the QR to Sheets scanner does not read. Scan one card into a test row and compare it with the ID in your school system before relying on it.
Do students need an app or their own phone?+
No. A teacher or staff member scans each card with one phone, and the students only show their card. They never open the scanner link and need nothing installed.
How do I add a timestamp when a USB barcode scanner types into Google Sheets?+
Use a small onEdit Apps Script that writes new Date() into the next column whenever an ID lands in column A. A NOW() formula does not work as a timestamp, because it recalculates and every row ends up showing the current time.
Our cards only have a magnetic stripe or a tap chip. Can a phone read them?+
Not with the camera. A phone camera reads printed codes only. Either keep the existing reader system, or print QR ID cards for attendance with a free ID card maker, holding the same ID numbers.
Does scanning an ID card prove the student was there?+
No. A scan proves a card was presented, not who was holding it. Have staff scan each card with its holder in front of them, and treat the sheet as a register of cards seen, checked by the person scanning.