DEV Community

Cover image for How I Built a Barcode Scanner PWA That Syncs to Spreadsheets in Real-Time
skycang
skycang

Posted on

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

#pwa #typescript #websocket #webdev

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:

  1. Works on any device with a browser
  2. Requires zero installation
  3. Syncs phone → desktop in real-time
  4. Works offline when the network drops
  5. 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│
                                      ───────────────┘
Enter fullscreen mode Exit fullscreen mode

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());
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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);
  });
});
Enter fullscreen mode Exit fullscreen mode

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))
  );
});
Enter fullscreen mode Exit fullscreen mode

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() });
}
Enter fullscreen mode Exit fullscreen mode

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()));
  }
};
Enter fullscreen mode Exit fullscreen mode

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;');
}
Enter fullscreen mode Exit fullscreen mode

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.

Top comments (0)