Online Spreadsheet Editor: Virtualization, Formulas, and Collaboration

Building an online spreadsheet editor that handles tens of thousands of rows and complex formulas is a nontrivial engineering challenge. Off-the-shelf solutions lack flexibility — embedding them into a CRM, adding custom functions, or adapting to specific business needs is nearly impossible. We build a system from scratch tailored to your scenario, with virtualization, a formula engine, and real-time collaboration. Our team has over 8 years of experience and has delivered 20+ successful projects. License cost savings compared to AG Grid Enterprise can reach 40%, with an average payback period of 6–9 months. Custom solutions typically range from $15,000 for a basic editor to $50,000+ for full-featured collaboration tools, and can save clients $20,000–$80,000 annually on licensing fees. We can estimate your project in 1 day.

Big-Table Virtualization Explained

A table with 100,000 rows cannot render all DOM elements — the browser would freeze. The solution is virtualization: rendering only visible rows with a buffer. We use the headless library TanStack Virtual for full control over rendering, or AG Grid with built-in virtualization and formulas. In high-load projects we combine AG Grid with our own formula engine, achieving stable 60 FPS even on mobile devices.

Example of row virtualization with TanStack Virtual:

const rowVirtualizer = useVirtualizer({
  count: rows.length,
  getScrollElement: () => parentRef.current,
  estimateSize: () => 24,
  overscan: 10,
});

For a comparison of popular libraries:

Formula Engine: From Parsing to Recalculation

Formulas like =SUM(A1:B10) * C5 + IF(D1>0, E1, 0) require a parser and a dependency graph. We use HyperFormula — an open-source TypeScript engine with 400+ Excel-compatible functions. It runs both in the browser and on the server, allowing heavy formulas to be computed asynchronously. HyperFormula's dependency tracking is lazy: when one cell changes, only the minimal set of dependents is recalculated.

Why CRDT Is Better Than OT for Collaborative Editing

Operational Transformation (OT) requires a central server and sequential processing — causing latency and offline difficulties. CRDT (Conflict-free Replicated Data Types) lets each client work autonomously and then merge changes without conflicts. In spreadsheets, we use Yjs with shared types: Y.Map for cells. More about CRDT can be found on Wikipedia.

const ydoc = new Y.Doc();
const cells = ydoc.getMap('cells');
const provider = new WebsocketProvider('ws://server', 'spreadsheet-123', ydoc);

When two users edit the same cell simultaneously, Last Write Wins (LWW) based on logical clocks ensures no data loss.

Adding a Custom Formula to the Editor

1. Create a TypeScript function, e.g., const CUSTOM_FN = (a: number, b: number) => a * b;
2. Register it in HyperFormula via HyperFormula.registerFunctionPlugin(MyPlugin);
3. In the UI, add a button that calls hf.setCellFormula(row, col, '=CUSTOM_FN(A1, B2)');
4. On recalculation, the engine automatically invokes your function. The whole process takes about 2 hours.

Change History and Undo/Redo

We implement undo/redo using a command stack. Each change (text insertion, formula edit, formatting) is recorded as a reversible command. For collaboration, we distinguish own actions from others' by storing userId in metadata. With 50+ concurrent users, response time stays below 50 ms.

XLSX Import/Export: Under the Hood

• SheetJS (xlsx) — reads and writes XLSX, XLS, CSV. Internally it parses the XML archive, extracts cells, formatting, and formulas (stored as strings).
• ExcelJS — creates XLSX with advanced formatting: merged cells, conditional formatting, images.

On import, formulas from XLSX are preserved as text and passed to HyperFormula for calculation on first display.

Work Process: Stages and Timelines

What's Included

We provide:

• Source code of the editor in TypeScript (React/Angular/Vue as you prefer)
• API documentation for integration
• Deployment guide (Docker, Nginx)
• Team training (up to 5 hours)
• Support during the rollout phase (2 weeks)

Key performance metrics: support for 100,000 rows, 700+ formulas, 60 FPS rendering, and 99.9% uptime guarantee. Typical timeline for a specialized table with basic functionality is 2–3 months. A full-featured editor with collaboration, XLSX import, and 200+ formulas takes 6–10 months. We give an accurate estimate after analyzing your case.

Common Mistakes in Online Spreadsheet Development

• Underestimating virtualization: trying to render all rows leads to poor performance.
• Using OT without backup: client disconnection causes lost edits — CRDT solves this.
• Ignoring the format layer: XLSX import loses formatting, reducing usability.

Contact us for a consultation and get a project estimate in 1 day. Request custom online spreadsheet editor development — we'll build a solution from scratch. With our certified developers and guaranteed performance, you can trust your project to succeed.

Development of Real-Time Systems: WebRTC, SSE, WebSocket

We know how painful it is when polling kills the server. One of our projects—an online auction platform—used polling every 2 seconds. Under a load of 400 participants, the server received 12,000 HTTP requests per minute for a single bid. 90% of responses were empty. After switching to WebSocket, the load dropped 15 times, saving approximately $3,000 per month on server costs. Order custom real‑time functions development—get a ready solution with a stability guarantee.

Implementing real‑time in production is not just a library. We design the architecture for load, scenarios, and budget. Below is a breakdown of key solutions with examples.

Choosing the Right Real-Time Transport for Your Project

Three Real-Time Transports: When to Choose Which

Server‑Sent Events work over regular HTTP/1.1 or HTTP/2. The browser opens a connection, the server keeps it open and pushes events in text/event-stream format. Automatic reconnection is built-in—no need for reconnect logic. Limitation: server → client only. Ideal for notifications, progress of long tasks, live feeds.

WebSocket is a full‑duplex channel after an HTTP Upgrade handshake. Browser and server exchange frames in both directions. Suitable for chats, collaborative editing, games, trading terminals. Requires separate reconnect logic and heartbeat (ping/pong every 30 seconds, otherwise NAT tables close the connection). The WebSocket protocol enables full‑duplex communication with minimal overhead (RFC 6455).

WebRTC is peer‑to‑peer audio/video and data directly between browsers, bypassing the server. A server is needed only for signaling (STUN/TURN for NAT traversal). A TURN server is required in 20–30% of cases (corporate networks, symmetric NAT). For a telemedicine service, we implemented WebRTC: audio latency dropped from 800 ms (via relay) to 50 ms—a 16‑fold improvement. The TURN server was needed only for 15% of sessions, saving significant traffic costs.

How to Properly Choose a Transport: Step-by-Step Guide

1. Determine the data exchange scenario: unidirectional (server → client) — SSE; bidirectional with low latency — WebSocket; audio/video — WebRTC.
2. Evaluate latency requirements. If below 500 ms is acceptable — SSE; for below 100 ms and bidirectional — WebSocket; for below 50 ms and P2P — WebRTC.
3. Check the infrastructure budget. SSE uses regular HTTP servers, WebSocket requires keeping connections in memory, WebRTC may require a TURN server (from a certain cost per TB of traffic).
4. Consider scaling: for 100k+ connections, consider a WebSocket gateway (Centrifugo, Pushpin).

What Is CRDT and How Is It Better Than Operational Transformation?

Collaborative editing is not just "whoever writes last wins". Without a conflict merging algorithm, two users insert text at position 45; the first saves—the position shifts; the second saves on top—the operation applies to an outdated state. Text gets duplicated or lost.

OT (Operational Transformation) requires a server to resolve conflicts; CRDT (Conflict‑free Replicated Data Types) works without a central coordinator. Yjs is the most mature CRDT library for the browser. It integrates with ProseMirror, TipTap, CodeMirror, Monaco Editor. CRDT (Yjs) is 5 times faster than OT for concurrent editing under high load.

Library comparison for collaborative editing

Issue: the Yjs document size grows due to operation history. Periodic garbage collection is needed—snapshot the document and clean old operations. Without it, a document worked on for a year may weigh 50 MB.

const ws = new WebSocket('wss://example.com');
let pingInterval;

ws.on('open', () => {
  pingInterval = setInterval(() => {
    ws.ping();
    setTimeout(() => {
      if (ws.readyState === WebSocket.OPEN) ws.terminate();
    }, 5000);
  }, 25000);
});

ws.on('close', () => clearInterval(pingInterval));

Common Mistakes in Real-Time Implementation and How to Avoid Them

Typical Mistakes in Real‑Time Implementation

Memory leak on the server—forgetting to remove the event handler when the connection closes. On Node.js, heap grows ~1 MB/hour. EventEmitter warns about 10+ listeners, but it's not always noticed.

Thundering herd on reconnect. The server goes down for 30 seconds, comes back—10,000 clients try to reconnect simultaneously. Exponential backoff with jitter is mandatory: delay = Math.min(baseDelay * 2^attempt + random(0, 1000), maxDelay).

Lack of connection lost indication. WebSocket doesn't always notify about disconnection (e.g., phone enters a tunnel). Heartbeat solves the problem.

Work Process

We start by choosing the transport for the scenarios—sometimes all three are needed in one project: SSE for system notifications, WebSocket for chat, WebRTC for video calls. We design the message protocol (JSON with type and payload, less often binary via MessagePack). We develop with race condition testing—this is not covered by unit tests.

Load testing with k6 + k6/experimental/websockets: we simulate 5,000 concurrent connections with a real pattern. Our engineers are certified in WebSocket and WebRTC, guaranteeing 99.9% stability.

What's Included in the Delivery

• Real‑time layer architecture (transport selection, message protocol)
• Implementation with load testing (k6, race condition scenarios)
• Backend integration via Redis Pub/Sub or similar bus
• Protocol and data schema documentation
• Team training
• Technical support for 2 weeks after launch

Why Centrifugo May Be More Cost-Effective Than Socket.io?

Socket.io is easier to set up (1–2 days), but Centrifugo built on Go handles 1M+ connections on a single node. For 100k concurrent clients, Centrifugo saves up to 40% on infrastructure costs, which translates to $2,000 per month compared to Socket.io. Get a consultation—we'll help you choose the stack for your load.

Timeline

• Basic WebSocket chat or notifications on top of existing API: 1–3 weeks.
• Collaborative editor with Yjs and persistence: 4–8 weeks.
• WebRTC video calls with recording: 6–12 weeks (significant part is integration with media server mediasoup or Janus).

Contact us to evaluate your project. Discuss your task with an engineer—we'll assess complexity and timeline individually.