@rowsncolumns/sharedb

ShareDB-based real-time collaboration adapter for Rows n Columns Spreadsheet.

This adapter stores spreadsheet data directly in a ShareDB document and uses Operational Transformation (OT) for real-time synchronization.

Features

• Real-time collaboration with OT-based conflict resolution
• Flat map data structure for efficient cell operations
• Presence awareness (see other users' cursors)
• Leader election for recalc coordination
• Full support for sheets, tables, charts, embeds, named ranges, and more

Why ShareDB for Spreadsheets?

ShareDB's Operational Transformation (OT) approach is particularly well-suited for spreadsheet collaboration compared to CRDT-based solutions like Yjs.

Advantages for Spreadsheets

| Aspect | ShareDB (OT) | Yjs (CRDT) | |--------|--------------|------------| | Server authority | Central server coordinates operations | Peer-to-peer, eventual consistency | | Recalculation | Leader election makes calc coordination simple | Requires additional coordination layer | | Conflict resolution | Server determines canonical order | Automatic merge, may produce unexpected results | | Audit trails | Sequential operation log | Distributed history | | Offline support | Requires server connection | Works offline-first |

Why OT Works Well for Spreadsheets

1. Cell-level granularity - The V3 flat map structure ("sheetId!A1" keys) maps perfectly to ShareDB's json0 OT type. Each cell is an independent key-value pair, so conflicts are rare.

2. Server-centric calculation - Spreadsheets typically need a server anyway for formula calculation, persistence, and permissions. OT's server authority aligns naturally with this architecture.

3. Simpler undo/redo - OT's sequential operation log makes history management straightforward. The server maintains a clear order of operations.

4. Predictable conflict resolution - When two users edit the same cell simultaneously (rare in practice), the server determines the winner. No surprising merged states.

5. Batched operations - Large operations (paste 1000 cells, delete rows) can be batched into a single atomic submitOp call, maintaining consistency.

When to Consider Yjs Instead

• Offline-first requirements - Users need to work without connectivity
• Peer-to-peer collaboration - No central server desired
• Decentralized architecture - No single source of truth needed

For most spreadsheet use cases with a server backend, ShareDB provides a cleaner, more predictable collaboration model.

Installation

npm install @rowsncolumns/sharedb sharedb

Quick Start

import { useShareDBSpreadsheet } from "@rowsncolumns/sharedb";
import ShareDBClient from "sharedb/lib/client";
import ReconnectingWebSocket from "reconnecting-websocket";

// Create ShareDB connection
const socket = new ReconnectingWebSocket("ws://localhost:8080");
const connection = new ShareDBClient.Connection(socket);

function SpreadsheetEditor() {
  const [sheetData, setSheetData] = useState({});
  const [sheets, setSheets] = useState([]);
  const [tables, setTables] = useState([]);

  const { onBroadcastPatch, users, synced, isLeader } = useShareDBSpreadsheet({
    connection,
    collection: "spreadsheets",
    documentId: "my-spreadsheet",
    userId: "user-123",
    title: "John Doe",
    sheetId: 1,
    activeCell: { rowIndex: 1, columnIndex: 1 },
    initialSheets: [],
    onChangeSheetData: setSheetData,
    onChangeSheets: setSheets,
    onChangeTables: setTables,
    enqueueGraphOperation: (op) => {
      // Handle dependency graph updates
    },
  });

  // Pass onBroadcastPatch to your spreadsheet component
  return (
    <Spreadsheet
      sheetData={sheetData}
      sheets={sheets}
      onBroadcastPatch={onBroadcastPatch}
      users={users}
    />
  );
}

Setting Up a ShareDB Server

Basic Server Setup

Create a file server.js:

const http = require("http");
const express = require("express");
const ShareDB = require("sharedb");
const WebSocket = require("ws");
const WebSocketJSONStream = require("@teamwork/websocket-json-stream");

// Initialize ShareDB
const backend = new ShareDB();

// Create Express app and HTTP server
const app = express();
const server = http.createServer(app);

// Create WebSocket server
const wss = new WebSocket.Server({ server });

// Handle WebSocket connections
wss.on("connection", (ws) => {
  const stream = new WebSocketJSONStream(ws);
  backend.listen(stream);
});

// Start server
const PORT = process.env.PORT || 8080;
server.listen(PORT, () => {
  console.log(`ShareDB server listening on port ${PORT}`);
});

Install Server Dependencies

npm install express sharedb ws @teamwork/websocket-json-stream

Run the Server

node server.js

Server with MongoDB Persistence

For production, you'll want to persist data. Install the MongoDB adapter:

npm install sharedb-mongo mongodb

Update server.js:

const http = require("http");
const express = require("express");
const ShareDB = require("sharedb");
const WebSocket = require("ws");
const WebSocketJSONStream = require("@teamwork/websocket-json-stream");
const ShareDBMongo = require("sharedb-mongo");

// Connect to MongoDB
const db = new ShareDBMongo("mongodb://localhost:27017/spreadsheets");

// Initialize ShareDB with MongoDB
const backend = new ShareDB({ db });

// ... rest of the server code remains the same

Server with PostgreSQL Persistence

For PostgreSQL:

npm install sharedb-postgres pg

const ShareDBPostgres = require("sharedb-postgres");

const db = new ShareDBPostgres({
  connectionString: "postgresql://user:password@localhost:5432/spreadsheets",
});

const backend = new ShareDB({ db });

Document Structure

The ShareDB document uses V3 format with a flat map for cell data:

type ShareDBSpreadsheetDoc = {
  // Cell data: flat map with keys like "1!A1" -> CellDataV3
  sheetData: Record<string, CellDataV3>;

  // Sheet definitions
  sheets: Sheet[];

  // Tables, charts, embeds
  tables: TableView[];
  charts: EmbeddedChart[];
  embeds: EmbeddedObject[];

  // Named ranges, protected ranges
  namedRanges: NamedRange[];
  protectedRanges: ProtectedRange[];

  // Conditional formats, data validations
  conditionalFormats: ConditionalFormatRule[];
  dataValidations: DataValidationRuleRecord[];

  // Pivot tables
  pivotTables: PivotTable[];

  // Cell formats and shared strings
  cellXfs: Record<string, CellFormat>;
  sharedStrings: Record<string, string>;

  // Recalc coordination
  recalcCells: RecalcCellEntry[];
};

Cell Key Format (V3)

Cell keys follow the pattern ${sheetId}!${A1Address}:

• "1!A1" - Cell A1 on sheet 1
• "2!B5" - Cell B5 on sheet 2
• "1!AA100" - Cell AA100 on sheet 1

CellDataV3 Structure

Each cell value is wrapped in a CellDataV3 object with flattened position:

type CellDataV3<T> = {
  value: T; // The cell data (formula, value, formatting, etc.)
  sId: number; // Sheet ID
  r: number; // Row index
  c: number; // Column index
};

V3 Format Benefits

• O(1) cell lookups: Direct key-based access instead of nested array traversal
• Sparse storage: No null padding for empty cells
• Simpler OT operations: Each cell is an independent key-value pair
• Better conflict resolution: Cell-level granularity for concurrent edits

Performance Optimizations

Batched Operations

All ShareDB operations are batched into a single submitOp call to avoid UI freezing during large operations like pasting hundreds of cells.

How it works:

Instead of calling doc.submitOp() for each cell individually (which would cause N network roundtrips and N internal ShareDB processing cycles), all operations are collected into a single array and submitted atomically:

// Internal implementation - operations are batched
const allOps: ShareDBOp[] = [];
const appendOps = (target: ShareDBOp[], ops: ShareDBOp[]) => {
  for (const op of ops) target.push(op);
};

// Collect ops from all patch types
appendOps(allOps, collectSheetDataOps(doc, patches, getSheetData));
appendOps(allOps, collectMapOps(doc, "cellXfs", cellXfsPatches));
appendOps(allOps, collectMapOps(doc, "sharedStrings", sharedStringsPatches));
appendOps(allOps, collectArrayOps(doc, "sheets", sheetsPatches));
// ... more patch types

// Single atomic submission
if (allOps.length > 0) {
  doc.submitOp(allOps);
}

Performance impact:

| Operation | Before (unbatched) | After (batched) | |-----------|-------------------|-----------------| | Paste 1000 cells | ~10-15 seconds freeze | < 500ms | | Delete 500 rows | ~5-8 seconds | < 200ms |

Helper Functions

The adapter provides helper functions for collecting ShareDB operations:

import {
  collectSheetDataOps,
  collectMapOps,
  collectArrayOps,
  type ShareDBOp,
} from "@rowsncolumns/sharedb";

// Collect operations without submitting (for custom batching)
const ops = collectSheetDataOps(doc, patches, () => doc.data?.sheetData);

// Or use legacy wrappers that submit immediately (not recommended for bulk ops)
import { applySheetDataPatches } from "@rowsncolumns/sharedb";
applySheetDataPatches(doc, patches, () => doc.data?.sheetData);

API Reference

useShareDBSpreadsheet

const { onBroadcastPatch, users, synced, isLeader } =
  useShareDBSpreadsheet(props);

Props

| Prop | Type | Required | Description | | ---------------------------- | ------------------------------------ | -------- | ----------------------------------- | | connection | ShareDBClient.Connection | Yes | ShareDB connection instance | | collection | string | Yes | ShareDB collection name | | documentId | string | Yes | Document ID | | userId | string \| number | Yes | Current user's ID | | title | string | Yes | User's display name | | sheetId | number | Yes | Currently active sheet ID | | activeCell | CellInterface | No | Current active cell position | | initialSheets | Sheet[] | Yes | Initial sheets if document is empty | | disable | boolean | No | Disable collaboration | | onChangeSheetData | Dispatch<SetStateAction> | Yes | Sheet data state setter | | onChangeSheets | Dispatch<SetStateAction> | Yes | Sheets state setter | | onChangeTables | Dispatch<SetStateAction> | Yes | Tables state setter | | onChangeCharts | Dispatch<SetStateAction> | No | Charts state setter | | onChangeEmbeds | Dispatch<SetStateAction> | No | Embeds state setter | | onChangeNamedRanges | Dispatch<SetStateAction> | No | Named ranges setter | | onChangeProtectedRanges | Dispatch<SetStateAction> | No | Protected ranges setter | | onChangeConditionalFormats | Dispatch<SetStateAction> | No | Conditional formats setter | | onChangeDataValidations | Dispatch<SetStateAction> | No | Data validations setter | | onChangePivotTables | Dispatch<SetStateAction> | No | Pivot tables setter | | onChangeCellXfs | Dispatch<SetStateAction> | No | Cell formats setter | | onChangeSharedStrings | Dispatch<SetStateAction> | No | Shared strings setter | | onChangeActiveSheet | (sheetId: number) => void | No | Active sheet change handler | | enqueueGraphOperation | (op: CalculationOperation) => void | Yes | Graph operation handler | | onError | (err: unknown) => void | No | Error callback |

Return Value

| Property | Type | Description | | ------------------ | ---------------- | ----------------------------------- | | onBroadcastPatch | Function | Callback to broadcast local changes | | users | Collaborator[] | List of connected collaborators | | synced | boolean | Whether initial sync is complete | | isLeader | boolean | Whether this client is the leader |

Types

import type {
  SheetData,
  ShareDBSpreadsheetDoc,
  CellDataV3,
} from "@rowsncolumns/sharedb";

Leader Election

The adapter uses a simple leader election mechanism based on presence IDs. The client with the lowest (alphabetically first) presence ID becomes the leader. The leader is responsible for:

• Coordinating recalc cell cleanup
• Full recalculation operations

const { isLeader } = useShareDBSpreadsheet({ ... });

if (isLeader) {
  // This client is responsible for leader tasks
}

Presence Awareness

Connected users are exposed via the users array:

const { users } = useShareDBSpreadsheet({ ... });

// users: Collaborator[]
// Each collaborator has: { userId, title, sheetId, activeCell }

Error Handling

Provide an onError callback to handle errors:

useShareDBSpreadsheet({
  // ...
  onError: (err) => {
    console.error("ShareDB error:", err);
    // Handle error (show toast, reconnect, etc.)
  },
});

License

MIT