How do I timestamp USB barcode scans in Google Sheets?

A barcode scanner on a cable, or a Bluetooth one paired in keyboard mode, is the cheapest way to get codes into a Google Sheet at a desk: no app, no account and no add-on. The one thing it does not do is record when each code was scanned. This guide covers the free setup end to end, why the obvious NOW() formula fails, the frozen-NOW trick some pages recommend and its risks, and a short Apps Script that does the job properly. Then it covers where a desk scanner stops being the right tool.

How does a USB barcode scanner type into Google Sheets?

Most USB barcode scanners work as a keyboard, often called keyboard-wedge mode. The computer sees a keyboard, so there is no driver or software to install. Click a cell, pull the trigger, and the characters in the barcode are typed into that cell, usually followed by Enter, which moves the cursor to the cell below ready for the next scan. Anything that accepts typing accepts a scan, which is why it works in Google Sheets in any browser.

• Format the code column as plain text first (Format, Number, Plain text). Google Sheets otherwise treats an all-digit code as a number, so 01042 becomes 1042 and no longer matches your product or people list.
• Test the suffix. If the cursor moves right instead of down, the scanner sends Tab; if it stays in the cell, it sends nothing. Scanner manuals print setup barcodes for this, and you scan one to change it.
• Keep one code per row. A lookup such as XLOOKUP against a product or people tab then reads each scan on its own, and the timestamp script below can work row by row.
• Scan one real label into a test row and compare it with the number printed under the barcode. Some codes carry a prefix, a suffix or a check digit, and a lookup only matches an exact copy.

Why NOW() and TODAY() are not timestamps

The first instinct is to put =IF(A2<>"", NOW(), "") in column B. It shows the right time for a moment, then changes. Google's own help on calculation settings lists TODAY and NOW among the volatile functions, "because those values inherently change all the time". Every time the sheet recalculates, every row shows the current time, so by the end of the day the whole column holds one time and the record of when each item was scanned is gone. TODAY() has the same problem a day at a time.

The frozen NOW() trick with iterative calculation

Some pages freeze NOW() with a circular reference. One widely shared article, dated February 17, 2025 on thebricks.com, uses =ARRAYFORMULA(IF(A2:A<>"", IF(C2:C="", NOW(), C2:C), "")) in C2, with File, Settings, Calculation, Iterative calculation turned on and Max. number of iterations set to 1. The formula reads its own column: an empty cell gets the current time, and a cell that already has a time keeps it. It works without any script, which is its appeal.

Know what it rests on before relying on it. Google describes the setting, when it announced it, as one that "allows you to set the maximum number of times a calculation with a circular reference can take place". It is a way to let circular references calculate, not a timestamp feature, and the times exist only as the formula's own last result:

• The setting applies to the whole spreadsheet. A circular reference someone creates by mistake elsewhere calculates quietly instead of showing an error.
• If someone turns the setting off, or copies the tab into a spreadsheet where it is off, the formula has nothing to hold its earlier results, so the stored times can turn into errors.
• The times are not values in cells. Sorting the codes, or deleting or inserting rows, can move a code without moving the time beside it. Copy the column and paste it as values before any of that.

For a quick test it is fine. For a log you will rely on, write the time as a value with a script, below.

The reliable way: an onEdit script that writes the time

Google Apps Script is built into Google Sheets and free. A function named onEdit is a simple trigger, and Google's documentation says "onEdit(e) runs when a user changes a value in a spreadsheet". A scanner typing is, to the browser, a person typing. In the sheet, open Extensions, Apps Script, delete the empty function, paste this, and save. Rename the tab to Scans, or change "Scans" in the script to your tab's name:

• function onEdit(e) { const r = e.range; if (r.getSheet().getName() !== "Scans" || r.getColumn() !== 1 || r.getRow() < 2 || !e.value) return; const t = r.offset(0, 1); if (t.isBlank()) t.setValue(new Date()); }
• It runs on every edit and returns at once unless a single value was typed into column A of the Scans tab, below the header row.
• It writes the date and time into column B of the same row with new Date(). That is a value, not a formula, so it never recalculates, and sorting or filtering moves it with its code.
• It only fills an empty time cell. Correcting a mistyped code later keeps the original scan time; clear column B for that row first if you want a new one.
• Google Sheets stores it as a real date and time, so ordinary date formulas work on it. Format column B as Format, Number, Date time to see both.

Google's triggers documentation sets out the limits, and they matter here. Simple triggers "don't run if a file is opened in read-only (view or comment) mode", so whoever runs the desk needs edit access to the spreadsheet. They "can't run for longer than 30 seconds", which this script is nowhere near. And "script executions and API requests don't cause triggers to run", which is why the script writing column B does not set itself off again.

To count how many times one code was scanned today, put the code in D2 (formatted as plain text, like column A, so a leading zero survives) and use =COUNTIFS(Scans!A:A, D2, Scans!B:B, ">="&TODAY(), Scans!B:B, "<"&TODAY()+1). That reads real dates from the script. The same approach for staff and student cards, with a list of who was late, is in scanning ID cards for attendance.

Does a Bluetooth barcode scanner work the same way?

Yes, when it is paired as a keyboard. Many Bluetooth scanners offer more than one mode, and the keyboard one is usually called HID. The help centre of CartonCloud, a warehouse software company, puts the advice plainly: "If the option is given, please choose for your scanner to operate in HID mode." The manual normally switches modes with a setup barcode, and you then pair the scanner from the computer's Bluetooth settings like any keyboard.

• On a laptop or desktop, a paired scanner behaves exactly like a USB one. The same plain-text column, Enter suffix and onEdit script apply.
• The cable is gone, but the sheet is not: it still has to be open on the paired computer with the cursor in the right cell, and the scanner only reaches as far as its Bluetooth range.
• On an iPhone or iPad, a connected keyboard normally hides the on-screen keyboard. CartonCloud's help centre notes that "double-pressing the scanner trigger may be required for some scanning devices" to bring it back.
• We have not tested a hardware scanner typing into the Google Sheets app on a phone or tablet, or whether the script runs for those edits. Try ten scans there before trusting it with real data.

Where a desk scanner breaks

At one fixed desk with the sheet open, a scanner and the script above are fast and close to free, and people overlook them. They stop fitting when the work moves:

• One desk. The scanner types into one computer, so a second entrance, a second aisle or a stock room needs a second computer with the sheet open.
• The cursor decides where a code goes. Click another cell, switch tab or open an email, and the next scan is typed into whatever has focus.
• Nothing while moving. A cycle count along the aisles, a delivery at the dock or a check at a door means carrying the laptop.
• A team means edit access for everyone at a station, because the script does not run in view-only mode. Each of them can change or delete any row in the spreadsheet.
• A dropped connection. Apps Script runs on Google's servers, so check the times on any rows typed while the computer was offline; the script cannot stamp an edit Google has not received.

For codes typed on a phone through a Google Form, or scanned with a free phone app, the comparison of free routes is in free barcode scanner apps for Google Sheets inventory.

Scanning with a phone camera instead of a hardware scanner

QR to Sheets replaces the scanner and the open sheet with a link. An admin connects a Google Sheet and creates a scanner link; whoever scans opens it in the phone browser, points the camera at a QR code or a common 1D barcode, and each scan becomes a new row with the time, the code and the link name. There is no cell to click, no script to maintain and no hardware to buy, and the person scanning never gets access to the spreadsheet. The full set of ways to get scans into a sheet automatically is in scanning QR codes into Google Sheets automatically, and stock work is on the warehouse page.

• The time is written for you as text in the form YYYY-MM-DD HH:MM:SS in the workspace timezone. It sorts in time order; wrap it in VALUE() in a formula, for example =SUMPRODUCT((Log!B2:B=D2)*(INT(IFERROR(VALUE(Log!A2:A), 0))=TODAY())) to count one code's scans today with the time in column A and the code in column B.
• 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 phones can write to the same sheet at once, each scanning in a different place.
• The camera reads QR codes and common 1D barcodes such as Code 128, Code 39 and EAN, but not Codabar. If your labels are Codabar, a hardware scanner is the better reader.
• Use the scanner link with the phone camera. We do not yet recommend pairing a USB or Bluetooth scanner with it: we have not tested that with real hardware.

What it costs

A scanner and the script cost nothing beyond the hardware. QR to Sheets is free for one scanner link, one Google Sheet and 300 scans in total, not per month, which is enough to prove the setup on real labels. Each phone that scans counts as one device. Free covers one phone; Premium is $7 per phone per month, with unlimited scans and links. The items or people being scanned are never counted.

Step by step

1. 1

   Format the code column as plain text

   Select column A, then Format, Number, Plain text, before the first scan. Otherwise a code such as 01042 is stored as the number 1042 and loses its leading zero.

2. 2

   Check the scanner ends each code with Enter

   Click a cell and scan one code. The cursor should drop to the cell below. If it moves right or stays put, the scanner is sending Tab or nothing; change its suffix with the setup barcodes in its manual.

3. 3

   Add the onEdit script

   Open Extensions, Apps Script, paste the onEdit function from this guide, rename the tab it names to match yours, and save. It writes the date and time beside every code typed into column A.

4. 4

   Format the time column

   Select column B, then Format, Number, Date time, and check File, Settings, Time zone is the zone you work in.

5. 5

   Test with three scans

   Scan three codes, check each has a time beside it, then sign in as the person who will run the desk and confirm they have edit access.

Frequently asked questions