How I Built a Barcode Scanner PWA That Syncs to Spreadsheets in Real-Time

skycang — Thu, 16 Jul 2026 16:02:55 +0000

I run a small inventory workflow. Every week, I scan hundreds of barcodes and manually type them into Google Sheets. It's slow, error-prone, and honestly painful.

One day I thought: why can't my phone camera just do this automatically?

So I built ScanSheet — a PWA that turns your phone into a barcode scanner and syncs data to your desktop spreadsheet in real-time. No app install needed.

Here's the full technical breakdown.

The Core Problem

Barcode data entry is still a manual process for millions of small businesses, event organizers, and warehouse workers. The existing solutions are either:

Expensive enterprise scanners ($500+ hardware)
Native apps that require installation and app store approval
Web apps that don't work offline (warehouses have terrible WiFi)

I wanted something that:

Works on any device with a browser
Requires zero installation
Syncs phone → desktop in real-time
Works offline when the network drops
Exports to CSV/XLSX/Google Sheets

Architecture Overview

 ──────────────┐                    ┌──────────────────┐
│   Phone      │                    │   Desktop        │
│   (Scanner)  │ ◄── WebSocket ──►  │   (Browser)      │
│              │                    │                  │
│  Camera API  │                    │  Data Table      │
│  ZXing       │                    │  localStorage    │
│  Decoder     │                    │  IndexedDB       │
└──────────────┘                    └────────┬─────────┘
                                             │
                                     ┌───────▼───────┐
                                     │  CSV / XLSX   │
                                     │  Google Sheets│
                                      ───────────────┘

The phone and desktop connect via a session-based WebSocket pairing model. No accounts needed for basic use.

Step 1: Phone Camera Barcode Scanning

The scanner uses the browser's getUserMedia() API to access the camera, then feeds frames to a barcode decoder.

// Start camera stream
const stream = await navigator.mediaDevices.getUserMedia({
  video: {
    facingMode: { ideal: 'environment' }, // prefer back camera
    width: { ideal: 1280 },
    height: { ideal: 720 }
  }
});

videoElement.srcObject = stream;
await videoElement.play();

// Decode barcodes from video frames
const codeReader = new BrowserMultiFormatReader();
const result = await codeReader.decodeFromVideoElement(videoElement);
console.log('Scanned:', result.getText());

Key UX decision: I added a skeleton screen while the camera initializes. Users expect the camera to "just work" — showing a blank screen for 2 seconds feels broken.

Step 2: Real-Time Phone-to-Desktop Sync

This was the hardest part. I needed sub-100ms latency between scanning a barcode on the phone and seeing it appear on the desktop.

Session Pairing Flow

1. Desktop generates a unique session ID (UUID)
2. Desktop displays a QR code containing the session URL
3. Phone scans the QR code → opens the scanner page
4. Phone connects to the same WebSocket session
5. Barcodes flow from phone → desktop in real-time

WebSocket Server (Node.js)

import { WebSocketServer } from 'ws';

const wss = new WebSocketServer({ server, path: '/ws' });

const sessions = new Map(); // sessionId → Set<WebSocket>

wss.on('connection', (ws, req) => {
  const url = new URL(req.url, `http://${req.headers.host}`);
  const sessionId = url.searchParams.get('session');

  if (!sessions.has(sessionId)) {
    sessions.set(sessionId, new Set());
  }
  sessions.get(sessionId).add(ws);

  ws.on('message', (data) => {
    // Broadcast scanned barcode to all clients in the session
    const message = JSON.parse(data);
    for (const client of sessions.get(sessionId)) {
      if (client !== ws && client.readyState === WebSocket.OPEN) {
        client.send(JSON.stringify(message));
      }
    }
  });

  ws.on('close', () => {
    sessions.get(sessionId)?.delete(ws);
  });
});

Why WebSocket instead of polling?

Metric	WebSocket	HTTP Polling (1s)
Latency	~20ms	~1000ms
Battery usage	Low (persistent connection)	High (repeated requests)
Server load	Low (one connection)	High (many requests)
Bidirectional	Yes	No

Step 3: Offline Support (The Hard Part)

Warehouses and storage rooms often have terrible connectivity. The app must work offline.

Service Worker — Network-First Strategy

// sw.js
const CACHE_NAME = 'scansheet-v1';

self.addEventListener('install', (event) => {
  event.waitUntil(
    caches.open(CACHE_NAME).then((cache) =>
      cache.addAll(['/app', '/manifest.webmanifest'])
    )
  );
});

self.addEventListener('fetch', (event) => {
  // Network-first: try network, fall back to cache
  event.respondWith(
    fetch(event.request)
      .then((response) => {
        const clone = response.clone();
        caches.open(CACHE_NAME).then((cache) => cache.put(event.request, clone));
        return response;
      })
      .catch(() => caches.match(event.request))
  );
});

Data Layer — Dual Write to localStorage + IndexedDB

// Every scan is written to both storage layers
async function saveScan(barcode: string) {
  // Fast read layer
  const records = JSON.parse(localStorage.getItem('records') || '[]');
  records.push({ barcode, timestamp: Date.now() });
  localStorage.setItem('records', JSON.stringify(records));

  // Structured query layer
  const db = await openDB();
  await db.put('scans', { barcode, timestamp: Date.now() });
}

Why dual write? localStorage for fast UI reads (no async needed), IndexedDB for structured queries (date range filtering, batch export).

Offline Queue

When the WebSocket disconnects, scans go into a queue. On reconnect, they flush automatically:

const offlineQueue: Scan[] = [];

function sendScan(scan: Scan) {
  if (ws.readyState === WebSocket.OPEN) {
    ws.send(JSON.stringify(scan));
  } else {
    offlineQueue.push(scan); // queue for later
  }
}

ws.onopen = () => {
  // Flush queued scans
  while (offlineQueue.length > 0) {
    ws.send(JSON.stringify(offlineQueue.shift()));
  }
};

Step 4: Export to CSV/XLSX

// CSV export with BOM for Excel Chinese character support
function exportCSV(records: Scan[]) {
  const BOM = '\uFEFF';
  const headers = 'Barcode,Quantity,Note,First Scanned,Last Scanned';
  const rows = records.map(r =>
    `${r.barcode},${r.quantity},"${r.note}",${r.firstScan},${r.lastScan}`
  );

  const csv = BOM + [headers, ...rows].join('\n');
  download(csv, 'scansheet-export.csv', 'text/csv;charset=utf-8;');
}

Formula injection protection: Any cell starting with =, +, -, or @ gets prefixed with a tab character to prevent CSV formula injection attacks.

The Tech Stack

Layer	Technology	Why
Frontend	TypeScript + Vite	Type safety, fast HMR, multi-page build
Styling	Custom CSS	No framework overhead, ~15KB total CSS
Server	Node.js + native HTTP	Single binary deploy, no Express needed
Database	SQLite (WAL mode)	Zero-config, backup = copy one file
Real-time	Native WebSocket	Sub-100ms latency, low battery usage
PWA	Service Worker + Manifest	Offline support, installable
Barcode	ZXing (browser port)	Pure JS, no native dependencies

Key Decisions & Tradeoffs

PWA vs Native App

Factor	PWA	Native App
Install friction	Zero (scan QR, start)	App store download
Updates	Automatic (service worker)	App store review
Camera access	Good (getUserMedia)	Best (native API)
Offline	Good (service worker)	Best (native storage)
Cross-platform	One codebase	iOS + Android separate

For a barcode scanner, PWA wins on distribution. Users scan a QR code and start scanning immediately — no app store, no download, no permissions dialog beyond the camera.

SQLite vs PostgreSQL

Single-server deployment means SQLite is the right choice:

Zero configuration
WAL mode handles concurrent reads
Backup = cp scansheet.db scansheet.db.bak
No separate database process to manage

Why Not React/Vue?

The app is ~2000 lines of TypeScript. A framework would add 50-100KB of bundle size for features I don't need (virtual DOM, component lifecycle). Direct DOM manipulation with a simple render() function is faster and smaller.

What I Learned

1. Camera UX is everything

Users expect the camera to "just work." I spent 3 days on camera initialization UX alone — skeleton screens, error states, permission handling, and camera switching (front/back).

2. Offline-first is a mindset, not a feature

Every action needs a local fallback. I queue scans in memory and flush on reconnect. The UI never shows "offline" — it just works.

3. The `[hidden]` attribute gotcha

CSS display rules can override the HTML hidden attribute. Every element using display in CSS needs a corresponding [hidden] { display: none !important; } rule. This caused a 2-hour debugging session.

4. WebSocket reconnection is harder than it looks

Mobile networks drop constantly. I implemented exponential backoff reconnection (1s → 2s → 4s → 8s → 16s) with a max of 5 attempts before showing a "reconnect" button.

What's Next

Google Sheets direct export via API integration
Multi-device sync — scan from 2 phones simultaneously
Batch barcode generation — print barcode labels from spreadsheet data
Team workspaces — shared sessions with role-based access

Try It

ScanSheet is live and free for up to 1,000 rows. No signup required for guest mode.

If you're building something similar or have questions about the architecture, drop a comment below! I'm happy to discuss the technical decisions in more detail.

DEV Community: skycang