Google Sheets barcode scan input with Apps Script 6
Introduction — Why your hands are still busy even with a barcode scanner
People searching for how to build Google Sheets barcode scan input usually already own a handheld scanner, but they’re frustrated that for every inbound or outbound operation they have to keep switching between the sheet and the scanner. After scanning a barcode, they move the cell with the keyboard, type the quantity, switch sheets, and in that process their hands and eyes are constantly splitting focus. The ideal flow is simple: scan barcode with scanner → Enter → auto-save in one go.
In this post, using keyboard-emulation (HID) barcode scanners or phone apps as the baseline, we’ll implement a Google Sheets Apps Script sidebar that accepts barcodes and, with a single Enter, automatically records to inbound/outbound sheets. At scan time we’ll also validate format and prevent duplicate scans within a set time window, so the result is not just a toy example but a minimal, production-ready unit.
This is part 6 in a series on building a warehouse inventory system in Google Sheets. Earlier, we separated inbound and outbound sheets, and in Google Sheets outbound automation: Apps Script 2 and Google Sheets auto inventory aggregation | Apps Script 3 we built automatic inventory aggregation and email reports. In this installment we’ll add Google Sheets barcode scanner integration, completing a scan-input layer that lets people work without directly editing sheets.
Core concepts of barcode scan input
To pipe barcodes straight into Google Sheets, it’s important to understand how your scanner sends input. This article assumes a keyboard-emulation (HID) scanner or app. Devices in this category read the barcode, then send the sequence of characters as if typed on a keyboard, followed by a final Enter (newline). If your device types characters directly into a browser input field without special drivers, it almost certainly works this way.
Leveraging this behavior, you can automate work with Apps Script sidebar scan input alone, without complex API integrations. The flow is:
- Show a sidebar on the right side of Google Sheets
- In the sidebar, place a barcode text box, inbound/outbound selector, quantity field, and save button
- When the scanner reads a code, it fills the text box
- When Enter is pressed (either manually or automatically by the scanner), a save function runs
- Apps Script receives the code and performs Google Sheets inbound/outbound barcode auto-entry
In real warehouse environments, multiple users work at once, and barcodes sometimes get misread. So instead of scanning straight into cells, it’s safer to have a script layer up front that handles format validation (regex) and duplicate scan prevention. The code in this post includes these minimum safety checks and is structured so you can easily extend it later.
Creating the sidebar menu and base screen
First, we’ll let users open the sidebar from the Google Sheets menu. Here we’ll define configuration constants, create a menu item, and add a function that shows the sidebar. Once this skeleton is in place, you can reuse the same pattern for other features.
Step 1 — Configuration constants, menu, and opening the sidebar
This code defines the sheet names for inbound, outbound, and scan logs, the duplicate-scan time window, and the barcode format as a regular expression. It also adds an “Open Scan Input” item to the shared “Warehouse Tools” menu at the top of Google Sheets.
Where to paste: In Google Sheets → Extensions → Apps Script → Code.gs, add this at the very top.
After pasting: Save, then in the script editor run onOpen once and approve permissions.
// Configuration — change only this block to match your sheet
const SCAN_CONFIG = { // → configuration bundle
SHEET_INBOUND: 'Inbound', // → inbound sheet name
SHEET_OUTBOUND: 'Outbound', // → outbound sheet name
SCAN_LOG_SHEET: 'Scan Log', // → scan log sheet
CACHE_PREFIX: 'scan:', // → per-barcode cache key prefix
VALID_TYPES: ['INBOUND', 'OUTBOUND'], // → accepted movement types
DUP_SECONDS: 5, // → duplicate window (sec)
BARCODE_REGEX: /^([A-Z0-9]{4})-([A-Z0-9]{4})$/, // → example barcode format
TIMEZONE: 'America/New_York' // → set your warehouse time zone
};
// Add the shared 'Warehouse Tools' menu when the sheet opens
function onOpen() { // → runs when sheet opens
var menu = SpreadsheetApp.getUi() // → get UI object
.createMenu('Warehouse Tools'); // → build the menu once
addScanMenu_(menu); // → attach this part's items
menu.addToUi(); // → display menu
}
function addScanMenu_(menu) { // → when merging, call only this
menu.addItem('Open Scan Input', 'showScanSidebar'); // → add menu item
}
// Show the sidebar
function showScanSidebar() { // → open sidebar function
var html = HtmlService.createHtmlOutputFromFile('ScanSidebar') // → load HTML
.setTitle('Barcode Scan Input'); // → sidebar title
SpreadsheetApp.getUi().showSidebar(html); // → show sidebar
}How to check it: After refreshing the Google Sheet, you should see a “Warehouse Tools” menu at the top. Clicking “Open Scan Input” should open an empty sidebar. If that works, the basic wiring is correct.
Merging several parts into one project: if you paste this next to an earlier part, onOpen() is declared twice — only the last one survives and the earlier menu disappears. That is why every part of this series uses the same menu name, Warehouse Tools, and keeps its own entries in a helper such as addScanMenu_(menu). To merge, delete this part's onOpen() and add a single line — addScanMenu_(menu); — inside the onOpen() from Part 1. Everything then lives under one Warehouse Tools menu: inbound, outbound, stock, rack layout, barcode scan and Load summary. Do the same with constants that repeat across parts (SHEET_INBOUND and friends): keep one copy and delete the rest, because declaring the same const twice in one project is an error by itself.
Building the sidebar HTML UI
Now let’s build the actual screen that receives barcode input. This part is standard HTML plus a bit of JavaScript, and includes a barcode field, inbound/outbound selector, quantity input, save button, and status message. An HID scanner will automatically fill the barcode field when it has focus.
Step 2 — Build the sidebar HTML screen
This code is the HTML shown in the sidebar. It includes the scanner input field, type selector, quantity field, save button, and a status display area.
Where to paste: In the Apps Script editor, click + → HTML, name the file ScanSidebar, and replace its contents entirely with the code below.
After pasting: Just save.
<!-- ScanSidebar.html — barcode scan input panel -->
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<style>
body { font-family: Arial, sans-serif; font-size: 13px; }
label { display: block; margin-top: 8px; }
input, select, button { width: 100%; box-sizing: border-box; }
#status { margin-top: 8px; font-size: 12px; }
.ok { color: green; }
.err { color: red; }
</style>
</head>
<body>
<label>Barcode</label>
<input type="text" id="barcode" autofocus>
<label>Type</label>
<select id="type">
<option value="INBOUND">Inbound</option>
<option value="OUTBOUND">Outbound</option>
</select>
<label>Qty</label>
<input type="number" id="qty" value="1" min="1">
<button id="saveBtn" style="margin-top:10px;">Save (Enter)</button>
<div id="status"></div>
<script>
const barcodeInput = document.getElementById('barcode');
const typeSelect = document.getElementById('type');
const qtyInput = document.getElementById('qty');
const saveBtn = document.getElementById('saveBtn');
const statusDiv = document.getElementById('status');
function showStatus(msg, ok) {
statusDiv.textContent = msg;
statusDiv.className = ok ? 'ok' : 'err';
}
function clearForm() {
barcodeInput.value = '';
qtyInput.value = 1;
barcodeInput.focus();
}
function doSave() {
const code = barcodeInput.value.trim();
const type = typeSelect.value;
const qty = parseInt(qtyInput.value, 10);
if (!code) {
showStatus('Please scan the barcode.', false);
barcodeInput.focus();
return;
}
if (!qty || qty <= 0) {
showStatus('Quantity must be a number greater than or equal to 1.', false);
qtyInput.focus();
return;
}
saveBtn.disabled = true;
showStatus('Saving...', true);
google.script.run
.withSuccessHandler(function(res) {
if (res.ok) {
showStatus('Saved:' + res.msg, true);
clearForm();
} else {
showStatus('Error:' + res.msg, false);
}
saveBtn.disabled = false;
})
.withFailureHandler(function(err) {
showStatus('Script error:' + err.message, false);
saveBtn.disabled = false;
})
.onScanSubmit(code, type, qty);
}
// Save on button click
saveBtn.addEventListener('click', function() {
doSave();
});
// Save with the Enter key
barcodeInput.addEventListener('keydown', function(e) {
if (e.key === 'Enter') {
e.preventDefault();
doSave();
}
});
</script>
</body>
</html>How to check it: In Google Sheets, click “Scan Barcode → Open Scan Input.” If you see a barcode field, type selector, quantity field, save button, and status area on the right, your UI is ready.
Handling validation, duplicates, and sheet writes in Apps Script
Now we’ll build the Apps Script handler that receives scan data from the sidebar. At this stage we’ll implement barcode format validation, duplicate-scan prevention with regex-based format checks, automatic inbound/outbound writes, and scan log recording in one go.
Step 3 — Barcode format validation and parsing function
First, we’ll create a parser that validates the barcode against a predefined rule and splits it into pieces if needed. In this example we use the AAAA-BBBB format (4 alphanumeric chars, a dash, 4 alphanumeric chars).
Where to paste: In Code.gs, add this below the showScanSidebar function.
After pasting: Save, then run testParseBarcode and check the log.
// Validate the barcode format and split it into container / model
function parseBarcode_(code) {
var regex = SCAN_CONFIG.BARCODE_REGEX;
var m = code.match(regex);
if (!m) {
throw new Error('Invalid barcode format. Example: ABCD-1234');
}
return {
container: m[1],
model: m[2]
};
}
// Example function for testing
function testParseBarcode() {
var r = parseBarcode_('AB12-CD34');
Logger.log(r);
}How to check it: In the Apps Script editor, choose testParseBarcode and run it. If the execution log shows something like {container=AB12, model=CD34}, format validation and parsing are working.
Step 4 — Duplicate-scan prevention and writing inbound/outbound rows
Next, we’ll write the main function called by the sidebar, which handles duplicate prevention and writing to sheets. We’ll use LockService to block concurrent runs, and CacheService.getScriptCache() to reject a repeated barcode within SCAN_CONFIG.DUP_SECONDS. Then we append one row each to the inbound/outbound sheet and the scan log sheet.
Two details matter here. First, remembering only the last barcode is not enough. Scan A → B → A within three seconds and the single remembered value has already been overwritten by B, so the second A sails through. The code below therefore keys the cache per barcode (scan:<barcode>) and lets the cache expiry (DUP_SECONDS) clear it. Second, mark the barcode only after the rows are written. If the format check or the sheet lookup fails and the duplicate marker is already in place, the operator's immediate retry is rejected as a duplicate while nothing was ever saved. So cache.put sits at the very end, after both appends.
The type value is checked against a whitelist too. Without that check every value other than OUTBOUND — including typos and empty strings — is quietly treated as inbound, and outbound scans pile up on the inbound sheet.
Where to paste: Add this code below parseBarcode_ in Code.gs.
After pasting: Create sheets named Inbound, Outbound, and Scan Log in advance, then run testOnScanSubmit to add test data.
// Final handler for a scan submission
function onScanSubmit(code, type, qty) {
var lock = LockService.getScriptLock();
lock.waitLock(10000); // wait up to 10 seconds
try {
code = String(code || '').trim();
qty = Number(qty);
if (!code) {
return { ok: false, msg: 'Barcode is empty.' };
}
if (!qty || qty <= 0) {
return { ok: false, msg: 'Quantity must be a number greater than or equal to 1.' };
}
if (SCAN_CONFIG.VALID_TYPES.indexOf(type) === -1) { // → whitelist check on the type
return { ok: false, msg: 'Invalid movement type: ' + type };
}
// Guard against duplicate scans — remembered per barcode
var cache = CacheService.getScriptCache(); // → cache shared by every operator
var cacheKey = SCAN_CONFIG.CACHE_PREFIX + code; // → key for this barcode only
var now = new Date();
if (cache.get(cacheKey)) { // → still cached = scanned recently
return { ok: false, msg: 'Ignored as a duplicate scan.' };
}
// Parse the barcode
var parsed = parseBarcode_(code);
var container = parsed.container;
var model = parsed.model;
// Shared values
var tz = SCAN_CONFIG.TIMEZONE;
var timeStr = Utilities.formatDate(now, tz, 'yyyy-MM-dd HH:mm:ss');
var user = Session.getActiveUser().getEmail() || 'unknown';
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheetName = (type === 'OUTBOUND') ? SCAN_CONFIG.SHEET_OUTBOUND : SCAN_CONFIG.SHEET_INBOUND;
var sheet = ss.getSheetByName(sheetName);
if (!sheet) {
throw new Error('Sheet not found:' + sheetName);
}
// Append one row to the inbound/outbound sheet (time, barcode, container, model, qty, user)
sheet.appendRow([
timeStr,
code,
container,
model,
qty,
user
]);
// Also record it in the scan log sheet
var logSheet = ss.getSheetByName(SCAN_CONFIG.SCAN_LOG_SHEET);
if (logSheet) {
logSheet.appendRow([
timeStr,
code,
type,
qty,
user
]);
}
// We only get here once the rows are written — mark the barcode now, not earlier
cache.put(cacheKey, String(now.getTime()), SCAN_CONFIG.DUP_SECONDS); // → expires by itself
var msg = (type === 'OUTBOUND' ? 'Outbound' : 'Inbound') + ' ' + qty + ' pcs';
return { ok: true, msg: msg };
} catch (e) {
return { ok: false, msg: e.message };
} finally {
lock.releaseLock();
}
}
// For testing: run a save with a sample barcode
function testOnScanSubmit() {
var res = onScanSubmit('AB12-CD34', 'INBOUND', 3);
Logger.log(res);
}How to check it: With Inbound, Outbound, and Scan Log sheets created, run testOnScanSubmit. If a new row appears at the bottom of the Inbound and Scan Log sheets, the full processing pipeline is working. To check duplicate handling, run it twice with the same barcode within five seconds — the second run should log {ok=false, msg=Ignored as a duplicate scan.} and add no row. Scanning AB12-CD34 → CD34-AB12 → AB12-CD34 quickly should block the third one as well. Then you can move on to testing with your actual scanner via the sidebar.
Practical tips for real warehouse use
With just the code above, you already have a minimal warehouse-management Google Sheets barcode setup for automatic inbound/outbound input. But for long-term production use, it’s worth considering a few more points.
First, clearly document your barcode rules internally. The example uses a simple two-part container-model structure, but in reality you might have three or more segments like location-item-lot. In that case, adjust SCAN_CONFIG.BARCODE_REGEX to your pattern and update parseBarcode_ to return the segments you actually need. Having a fixed format greatly reduces errors when aggregating inventory later.
Second, check your scanner or app settings for an option to send Enter after scan. Many HID barcode scanners include configuration barcodes in the manual to toggle an appended Enter (carriage return). Once configured, operators can essentially work as scan → auto-save, with no extra keyboard action. Some mobile barcode scanner apps offer the same option.
Third, in environments with multiple people scanning at once, the combination of LockService and cache-based duplicate checks from this article already reduces basic conflicts. Still, it’s best to separate the scan-input sheets from your complex calculation sheets. For example, keep inbound, outbound, and scan log sheets as raw data only, then handle inventory aggregation and reporting in separate sheets or even a separate file. This structure ties in naturally with Google Sheets auto inventory aggregation | Apps Script 3.
Fourth, one of the most frequent real-world questions is about recovery from incorrect input. The dedicated scan log sheet exists precisely for this. You can filter the problematic time window, identify the bad inbound/outbound rows, and either fix them manually or later add correction scripts that “replay” from the log. The first priority is simply logging enough detail.
Post-install checks and common errors
Here are the issues that most often appear during setup and what to check. Running through this list once before going live will save time.
First, authorization issues. If clicking Save in the sidebar shows 'Script error:Authorization is required' or similar, you haven’t granted Apps Script permissions yet. Open the script editor, run onOpen or testOnScanSubmit manually, then in the permission prompt choose your account and click “Advanced → Continue (project name)” to approve.
Second, file-name and sheet-name mismatches. If the HTML file name referenced in showScanSidebar (ScanSidebar) doesn’t match the actual HTML file name, or if the names in SCAN_CONFIG.SHEET_INBOUND etc. don’t match the tabs in the spreadsheet, you’ll get errors. If you see an error mentioning Sheet not found:, re-check the constants and the actual tab names.
Third, regex not matching real barcodes. If you constantly see 'Invalid barcode format. Example: ABCD-1234', your real barcodes don’t fit the sample regex. In that case, copy one actual barcode string and adjust SCAN_CONFIG.BARCODE_REGEX and parseBarcode_ to match it. For patterns like LOC-ITEM-LOT, you’d define three capture groups in the regex and return three properties from the parser.
Fourth, scanner not sending Enter. If the barcode value appears in the sidebar but doesn’t auto-save and you must hit Enter manually, your scanner likely isn’t configured to append Enter (newline). Check the scanner/app manual for how to enable this, usually by scanning a configuration barcode.
Conclusion
In this article, we built a Google Sheets barcode scan input system using HID barcode scanners or apps, an Apps Script sidebar UI, and a server-side handler that validates format, prevents duplicates, and writes to inbound/outbound sheets plus a scan log. It’s designed to be usable in real warehouse conditions, including basic concurrency control and clear error messages.
As an actionable next step, take one of your actual barcodes and write it down exactly, then adjust SCAN_CONFIG.BARCODE_REGEX and parseBarcode_ to match your own format. Once those two pieces reflect your internal rules, you can hook up your scanner and refine the scanner → Enter → auto-save flow directly in the sidebar. In the next part, we’ll add simple lookup and validation logic to this scan input so you can block incorrect locations or items ahead of time.
