Field trip roll call in Google Sheets: scan students on and off the bus and see who is missing

Every field trip has the same three moments: leaving school, leaving the venue, and arriving back. At each one a teacher counts heads, usually twice, and ticks names on a printed list while children climb on two at a time. The count is what keeps children safe. The list is what tells you, afterwards, who was on which bus and when, and paper is bad at that: ticks without times, three lists for three checkpoints, and nothing anyone can open later.

This guide covers the free Google Sheets route first, where it breaks on a moving trip, then a setup where a chaperone scans each student's card at the bus door, with one formula that lists who has not been scanned since the current checkpoint started.

What is a field trip roll call record for, and what is it not?

Start with the boundary, because a trip is a safeguarding job before it is a spreadsheet job. A roll call record says which students were recorded at a checkpoint and when. It is not a safety system. It does not count heads for you, it does not alert anyone when a child is missing, and it does not know where anyone is.

• Always do the physical headcount your school's trip policy requires, at every checkpoint, whatever the sheet says. The sheet is the written copy.
• Carry the printed roster and the contact details your policy asks for. A phone can run out of battery or signal at the moment you need it.
• A scan proves a code was presented, not who was holding it. A chaperone looking at each child as their card is scanned is what makes the record mean something.
• Permission slips, medical notes and emergency contacts stay wherever your school keeps them. This guide covers the roll call only.

What is the free way to do a field trip roll call in Google Sheets?

A roster with a checkbox column for each checkpoint. On a Roster tab, put the ID in column A, the name in column B and the bus in column C, then head columns D and E Board at school and Board to return, and add more for extra stops. Select the cells under those headings and choose Insert, Checkbox; Google's help on checkboxes lists taking attendance as one of their uses. On the day, open the sheet in the Google Sheets app and tick each child as they board.

• Number not yet ticked for the return, for example in a Summary tab: =COUNTIFS(Roster!A2:A, "<>", Roster!E2:E, FALSE)
• Names not yet ticked for the return: =FILTER(Roster!B2:B, Roster!A2:A<>"", Roster!E2:E=FALSE)
• Both ignore checkbox rows below the last student, because they only count rows with an ID in column A. Change E to the column of the checkpoint you are checking.

Where does a checkbox roster break on a bus?

• Speed. Finding and ticking 40 names on a small screen while children board two at a time means scrolling, and the queue waits for the teacher.
• A tick has no time. Afterwards you can see that a box was ticked, not when, so you cannot tell which bus a child left on if both buses use one list.
• An accidental tap unticks a child while you scroll, and nothing tells you. Version history can show it later; nobody checks version history on a coach.
• Two chaperones ticking the same sheet on a weak signal cannot be sure they are seeing each other's ticks.
• Names that look alike, such as two students called Sam, get ticked on the wrong row.

A Google Form per checkpoint avoids the scrolling but moves the typing to the students, who would each need a phone, signal and a minute at the bus door. For a trip, that is usually worse.

How does scanning each student's card work at the bus door?

This is the part QR to Sheets sells. An administrator signs in with Google once, connects the spreadsheet, and creates a scanner link named Trip that writes to a tab called Log. On the day, the chaperone opens the link in the phone's browser, allows the camera, and scans each student's card as they step on, which takes about a second per student once the line is moving. There is no app and no account for the chaperone, and they never get access to the spreadsheet.

• Cards: the free QR code ID card maker prints a card per student holding only the ID, as lanyard badges at 9 per sheet or as wallet cards at 10 per A4 or 8 per US Letter sheet. It needs no signup and makes the cards in your browser.
• Cards students already have: if their school ID card carries a QR code or a common barcode such as Code 39 or Code 128, scan that instead. Codabar is not read. Scanning student ID cards covers checking a card before the trip.
• Each scan adds a row to the Log tab: the time in column A, the card's ID in column B and link:Trip in column C, then the workspace name, the device type and a scan ID.
• The time is written as text in the form YYYY-MM-DD HH:MM:SS, in the timezone set for your workspace, and Google Sheets stores it as text. The formula below converts it with VALUE().
• If the Log tab is empty before the trip, type the six column names, scanned_at, qr_value, scanner_email, organization, device_type and scan_id, into row 1, so the formulas can start at row 2.
• Open the scanner link at school, where there is signal, and leave that page open for the day. A page reloaded with no signal will not load.
• A lost or forgotten card is not a problem: the scanner has a manual entry field, so the chaperone types the ID and the row is written the same way. Keep a printed list of IDs with the roster.

How do I list who is missing at each checkpoint?

Add a Trip tab. Before the first child boards at a checkpoint, type the date and time it starts into B1, for example 2026-10-14 13:30; Google Sheets recognises that as a date and time. Then put this in A3:

• Missing since B1: =IFERROR(FILTER(Roster!A2:C, Roster!A2:A<>"", ISNA(MATCH(Roster!A2:A, FILTER(Log!B2:B, IFERROR(VALUE(Log!A2:A), 0)>=$B$1), 0))), "Everyone on the list has been scanned")
• It lists the ID, name and bus of every student on the roster with no scan at or after the time in B1. VALUE() turns each text timestamp into a real date and time so it can be compared with B1, and IFERROR skips blank rows.
• Before the first scan of a checkpoint, the list shows everyone, which is correct. As scans arrive, names drop off.
• How many are missing, for a cell such as F1: =SUMPRODUCT((Roster!A2:A<>"")*ISNA(MATCH(Roster!A2:A, FILTER(Log!B2:B, IFERROR(VALUE(Log!A2:A), 0)>=$B$1), 0)))
• Missing from one bus: =IFERROR(FILTER(Roster!A2:C, Roster!A2:A<>"", Roster!C2:C="Bus 1", ISNA(MATCH(Roster!A2:A, FILTER(Log!B2:B, IFERROR(VALUE(Log!A2:A), 0)>=$B$1), 0))), "Everyone on Bus 1 has been scanned")
• Scanned but not on the trip roster, usually a student from another group or a test scan: =IFERROR(UNIQUE(FILTER(Log!B2:B, IFERROR(VALUE(Log!A2:A), 0)>=$B$1, ISNA(MATCH(Log!B2:B, Roster!A2:A, 0)))), "None")

Keep a short list of each checkpoint's start time further down the tab, so you can look back at any leg later by typing its time into B1. The same roster-minus-scans idea, by date rather than by time, is in the guide to finding who is absent, which also has the Google Form versions.

One link for the whole trip, or one per checkpoint?

One link is simplest and works on the free plan. Every checkpoint scans the same cards on the same link, and the formula above tells the legs apart by time. Expect amber from the second checkpoint on: when a card is scanned again on the same link within 12 hours, the phone shows an amber Already scanned notice and saves the row anyway. On a day trip that is nearly every student on the way back, and it means the scan worked. Tell the chaperones before they leave.

One link per checkpoint, such as Board at school and Board to return, keeps the legs apart in column C and avoids the amber notices, but more than one active link needs Premium. The missing formula then matches the link name instead of a time: =IFERROR(FILTER(Roster!A2:C, Roster!A2:A<>"", ISNA(MATCH(Roster!A2:A, FILTER(Log!B2:B, Log!C2:C="link:Board to return"), 0))), "Everyone on the list has been scanned")

Do not switch off Accept duplicate scans for a round trip. Switched off, a link records each code once, ever, so every student's return scan would be refused and no row written, which is the opposite of a roll call. Even on a link used for a single checkpoint, it makes that link single-use for those students, so the next trip would need a new link. Handling duplicate QR scans explains the setting in full.

How many phones do I need, and what does it cost?

Each phone that scans counts as one device. Free covers one phone; Premium is $7 per phone per month. People being scanned never open the link and need nothing.

• One bus with one chaperone phone is one device, which the free plan covers.
• The free plan's 300 scans are in total, not per month. A class of 40 scanned at four checkpoints uses 160, so one day trip fits; a second trip will not.
• Two buses with a phone on each are two devices, which means Premium: 14 USD a month for the two phones. On the free plan, a second phone that tries to scan is refused.
• Open the link in the phone's normal browser. A link opened inside another app or a private tab counts as a new device once it scans. Several phones scanning into one sheet covers sharing a link across phones.

What if there is no signal at the venue?

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. The missing list in the sheet only changes once those scans arrive, so on a coach with no signal it can still show children who are sitting right there. Count heads, keep the printed roster, and read the sheet once the signal is back.

Which fits: a checkbox roster, a trip app, or a scanned sheet?

Attendance apps cover trips too. As of 2026-10-12, OneTap's attendance tracker page lists real-time headcounts for emergency drills, field trips, and campus safety, and its pricing page lists a free plan with up to 20 profiles and a Basic plan at 49.99 USD per month billed yearly with up to 250 profiles.

Where each one wins. A printed list wins when the battery dies, which is why you carry one anyway. A checkbox roster wins for a single small group on one bus. A trip or attendance app wins if you want headcounts and reports without building any formulas. QR to Sheets fits a school that already prints student cards or has ID cards, wants a time on every boarding, and wants the record in its own Google Sheet with no app for anyone.

Student cards and data on a trip

• Put an ID in the QR code, never a name or a phone number. A card dropped at a museum then shows a number and nothing else.
• Keep the spreadsheet in a school-controlled Google account. The chaperone scans without getting access to it, so a parent volunteer can scan without seeing the roster.
• Students need no phone at all; they only show their card. The guide to QR attendance for people without smartphones covers other ways to carry a code.
• Clear the trip's rows when your school's records policy says to.

Step by step

1. 1

   Make a Roster tab for the trip

   One row per student going: an ID in column A, the name in column B and the bus or group in column C. Only students on the trip belong on this tab.

2. 2

   Give every student a card

   Print a QR card per student holding only the ID, as a lanyard badge or a wallet card, or use the barcode on the student ID cards they already carry.

3. 3

   Create one scanner link for the trip

   Point it at a Log tab in the same spreadsheet, open it on the chaperone's phone at school where there is signal, and test it with one card before anyone boards.

4. 4

   Start each checkpoint with a time

   Type the date and time the checkpoint starts into B1 of a Trip tab, then scan each student as they step on. The missing list shrinks as scans arrive.

5. 5

   Count heads before the bus moves

   Do the physical headcount your trip policy requires, every time. Read the missing list as the written copy, and once signal is back if it dropped.

Frequently asked questions