How to run a school store or student bank with ID cards and Google Sheets

A student-run store selling pencils at break, a classroom economy where students earn and spend points, a school bank that teaches saving, a reward store for good behaviour: they all have the same shape. There is a list of students, a list of deposits and purchases, and a balance per student. At classroom scale, a Google Sheet does that well, and it keeps the record in the school's own account.

What can a spreadsheet do for a school store, and what can it not?

Start with the limits, because they decide whether this guide is for you. A spreadsheet records transactions and adds them up. It does not take cash or card payments, it does not stop a purchase at the counter, and it does not know who is holding a card. A scan proves a card was presented, not who presented it. If the store takes real money from parents or sells enough stock to need a till, a point-of-sale system is the right tool, and the comparison near the end names one.

• What you need for any route: a roster with one ID per student, a log of every deposit and purchase with the student's ID and the amount, and one balance per student.
• What you decide as a school: the actions and their amounts, who is allowed to record them, what happens when a balance goes below zero, and who can see the balances.

Free route: a Google Form for deposits and purchases

Create a Google Form with three questions: Student ID, Type with the choices Deposit and Spend, and Amount. Open it on the store's phone or laptop, and the clerk submits one response per transaction. Each response becomes a row in the linked sheet: A Timestamp, B Student ID, C Type, D Amount.

Google Forms adds each response as a new row, so a formula filled down beside the responses does not reach the rows that arrive later. Put the formula in the header cell of an empty column instead:

• Signed amount, in E1 of the response tab: ={"Signed amount"; ARRAYFORMULA(IF(B2:B="", "", IF(C2:C="Spend", -D2:D, D2:D)))}. Deposits stay positive and purchases turn negative.
• On a Balances tab with IDs in column A and names in column B, the balance in C2, filled down: =SUMIFS('Form Responses 1'!E2:E, 'Form Responses 1'!B2:B, A2)
• A flag in D2, filled down: =IF(C2<0, "Overspent", "")

If your student IDs are plain numbers, format the ID column on the Balances tab as Format, Number, Plain text before typing them, so 001234 keeps its leading zeros and matches what the form recorded. IDs with a letter in front, such as S1001, avoid the question.

Free route two: a USB barcode scanner into a sheet

If students already carry barcoded ID cards, a USB barcode scanner plugged into a laptop types the card's number into whichever cell is selected, as if it were typed on the keyboard. The clerk scans into the Student ID column and types the amount next to it, and the same SUMIFS gives the balances. Getting a timestamp into each row is the tricky part, and using a USB barcode scanner with Google Sheets covers it. We have not tested a USB scanner with the QR to Sheets scanner link, so treat this as a laptop and a sheet, not a way into the app.

Where the free routes break

• Typing at the counter is slow. A queue at break time leaves a clerk typing IDs and amounts on a phone keyboard.
• A typo goes to the wrong student or to nobody. S1010 instead of S1001 moves a purchase to another account, and the formula has no way to know.
• The USB route ties the store to one laptop, and the clerk typing into the sheet can see, and change, every student's balance.
• Neither can stop a purchase. The balance updates after the row is written, so a student can spend below zero before anyone sees it.

How does the scanned version work?

In QR to Sheets, an admin signs in with Google once, connects the store's spreadsheet and creates scanner links. The clerk opens a link in Safari or Chrome on a phone, allows the camera once and scans student ID cards, with no app and no account. A scanner link writes the same kind of row whatever it scans: the scan time, the scanned code and the link's name. There is no amount column. So the amount comes from which link was used: one link per action, named Deposit 1, Deposit 5, Spend 1 and Spend 2, all writing to a tab called Log.

• Each scan adds one row: the scan time in column A, the card's ID in column B, and the link in column C. Column C is the scanner_email column, and for a scanner link it holds link: followed by the link's name, so the rows read link:Deposit 5 or link:Spend 1. Columns D, E and F hold 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. It sorts correctly; formulas that do arithmetic on it wrap it in VALUE().
• If the Log tab was empty when scanning started, type scanned_at, qr_value, scanner_email, organization, device_type and scan_id into row 1 first, so every formula can start at row 2.
• The link's name is shown at the top of the scanner, so the clerk can check they are on Spend 2 before scanning.
• On a Prices tab, list each link name in column A exactly as you named it, without the link: part, and its amount in column B: Deposit 1 is 1, Spend 2 is -2. Renaming a link changes the name on new rows only, so keep the old name on the Prices tab too, or its rows stop counting.

For the cards, many existing student ID barcodes scan as they are; scanning student ID cards lists which formats a phone reads, and Codabar, used on some older library cards, is not one of them. Test one card before you plan around it. If students have no cards, the free QR code ID card maker turns a pasted list of names and IDs into printable cards, with no signup. Put only the ID in the code, never a name.

How do I see each student's balance?

On the Balances tab, with IDs in column A and names in column B, put this in C2 and fill it down:

• Balance: =SUMPRODUCT(COUNTIFS(Log!$B$2:$B, $A2, Log!$C$2:$C, "link:"&Prices!$A$2:$A$20)*Prices!$B$2:$B$20)
• How it works: COUNTIFS counts the student's scans on each link listed on the Prices tab, the multiplication turns each count into an amount, and SUMPRODUCT adds them. A student scanned twice on Deposit 5 and once on Spend 2 has 5 + 5 - 2 = 8.
• Overspent flag, in D2, filled down: =IF(C2<0, "Overspent", "")
• A row from a link that is not on the Prices tab counts as zero, so add every link name you create, including old names.
• Cards scanned that are not on the roster, anywhere on a Report tab: =IFERROR(UNIQUE(FILTER(Log!B2:B, Log!B2:B<>"", COUNTIF(Balances!A2:A, Log!B2:B)=0)), "None")

A statement for one student: type their ID in B1 of a Statement tab and put =FILTER(Log!A2:C, Log!B2:B=B1) in A3. It lists every deposit and purchase with its time and link. For today's totals per action, put the link names in column A of a Daily tab, then in B2: =SUMPRODUCT((Log!$C$2:$C="link:"&A2)*(IFERROR(INT(VALUE(Log!$A$2:$A)), 0)=TODAY())), and in C2: =B2*VLOOKUP(A2, Prices!$A$2:$B$20, 2, FALSE). VALUE() turns each text timestamp into a date-time, and INT() keeps the day, so a scan at 23:59:59 counts today and one at midnight counts tomorrow.

Can it stop a student spending more than they have?

No. The scanner does not see the balance, and a Spend scan is recorded whatever the balance is. The Overspent flag appears once the row reaches the Sheet, which is after the student has the pencil. Schools handle that with a rule rather than software:

• Keep the Balances tab open on a second screen at the counter, and check it before a large purchase.
• Decide what a negative balance means: a debt cleared by the next deposit, or no purchases until it is back to zero.
• Correct a mistake with a Refund 1 link whose amount on the Prices tab is 1, rather than by editing the Log. The raw log stays a true record of what was scanned.

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. Until then, those rows are not in the Sheet and the balances do not include them.

Repeat scans, wrong links and forgotten cards

• Two items at the same price are two scans on the same link. The second scan within 12 hours shows an amber Already scanned notice with the count and the words Saved anyway: that is expected, and both rows count.
• Keep each link's Accept duplicate scans setting on, which is the default. Switched off, a link records each code once, ever, so every student could buy on it only once.
• A scan on the wrong link is corrected with the opposite action, such as a Refund link, never by deleting a row.
• A forgotten or damaged card is not a problem: the scanner has a manual entry field, so the clerk types the ID and the row is written the same way.

Which phones, 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 store phone with all its links open in the same browser is one device; a link opened in another app or a private tab counts as a new device once it scans.

The free plan has one scanner link and 300 scans in total, not per month, so it can run a single action, such as a Deposit 1 link, for a short trial. A store needs at least one deposit and one spend link, which means Premium, with unlimited links and scans: one store phone is $7 a month.

Which tool fits: a sheet, a classroom bank app or a till?

Two kinds of product do this job with more built in. ClassBank describes itself on its own page as "a free digital classroom economy platform for K-12 teachers and schools", with bonuses, fines, jobs, bills, a store and student checking and savings accounts on the free plan, and says it "is free for individual teachers"; Pro is $100 a year. Its page says students "can access their classroom bank account, store, and savings goals from any web browser on any device", and does not mention scanning ID cards. Square is a point-of-sale system for taking real payments; its pricing page says "Your complete business toolkit, starting at $0" and "Only pay when you take a payment."

Where each wins: ClassBank wins for a classroom economy where students log in to see their own accounts, and it costs nothing for one teacher. A till such as Square wins the moment real money changes hands. A spreadsheet wins when the cards already exist, the clerk needs to move a queue quickly, and the school wants the record in its own Google Sheet.

Students' data in a store spreadsheet

• Put an ID in the card's code, never a name. Any phone camera can read a QR code, so a lost card should reveal nothing.
• Keep the spreadsheet in the school's own Google account, shared only with the staff who run the store. Student clerks scanning through the link add rows without seeing anyone's balance.
• Points and balances about children are still records about children. Follow your school's data protection policy on who sees them and how long they are kept; this guide is not legal advice.

Step by step

1. 1

   Give every student an ID

   List each student once on a Balances tab, with an ID such as S1001 in column A and the name in column B. Use the number on the ID cards students already carry, or print cards that hold only the ID.

2. 2

   Decide the actions and their amounts

   Write each action and its amount on a Prices tab: Deposit 1 is 1, Deposit 5 is 5, Spend 1 is -1, Spend 2 is -2. Keep the list short; every action becomes a scanner link.

3. 3

   Create one scanner link per action

   Name each link exactly as on the Prices tab and point them all at the same Log tab. The link's name is written on every row it records, which is how the Sheet knows the amount.

4. 4

   Scan the student's card on the matching link

   The clerk opens the link for the action, checks its name at the top of the scanner, and scans the card. One scan per unit: two items at 2 points each are two scans on Spend 2.

5. 5

   Read balances and flags on the Balances tab

   One formula per student adds up their scans on each link times its amount. A second column flags any balance below zero, and a report lists cards that are not on the roster.

Frequently asked questions