Spreadsheets

Designing collaborative spreadsheets with 2D grids, cell addressing, and formula support.

Document Structure

There are two common approaches for cell storage, each with tradeoffs:

Approach 1: Nested Maps

Document
├── cells: Map<rowId, Map<colId, CellData>>
├── rowOrder: Array<rowId>
├── colOrder: Array<colId>
...

Pros: Efficient row operations (iterate all cells in a row), natural grouping. Cons: More complex access pattern, nested map setup for each row.

Approach 2: Flat Map with Composite Keys

Document
├── cells: Map<"rowId:colId", CellData>
├── rowOrder: Array<rowId>
├── colOrder: Array<colId>
...

Pros: Simpler access (cells.get(`${rowId}:${colId}`)), flat structure. Cons: Row iteration requires filtering all keys, slightly more parsing overhead.

Common Structure (Both Approaches)

Document
├── cells: (see above)
├── rowOrder: Array<rowId>
├── colOrder: Array<colId>
├── rowProps: Map<rowId, {height}>
├── colProps: Map<colId, {width}>
├── namedRanges: Map<name, Range>
└── styles: Map<styleId, Style>

Choose based on your access patterns: nested if you frequently operate on entire rows, flat if you primarily access individual cells.

Cell Addressing

Cells are addressed via row/column IDs, not traditional A1 notation. Convert for display:

// Internal: ID-based
const cellRef = { rowId: 'r8k2mf9n', colId: 'c3nd8k2m' }

// Display: convert to A1 when showing to user
function idsToA1(rowId: string, colId: string): string {
  const colIndex = colOrder.toArray().indexOf(colId)
  const rowIndex = rowOrder.toArray().indexOf(rowId)
  return indexToLetter(colIndex) + (rowIndex + 1)  // "B3"
}

Formula References

Use IDs, not indices. When rows/columns are inserted or deleted, A1-style references break. Store references as cell IDs:

interface ParsedFormula {
  expression: string
  references: Array<{
    type: 'cell' | 'range'
    rowId: string
    colId: string
    endRowId?: string  // For ranges
    endColId?: string
  }>
}

// Example: =SUM(B1:B10) stored as
{
  expression: 'SUM(range1)',
  references: [{
    type: 'range',
    rowId: 'r8k2mf9n', colId: 'c3nd8k2m',
    endRowId: 'r9m3kf2n', endColId: 'c3nd8k2m'
  }]
}

Dependency Tracking

Track which cells depend on which for efficient recalculation:

const dependencies = new Map<string, Set<string>>()

// On cell change, only recalculate affected cells
function onCellChange(cellKey: string) {
  const dependents = dependencies.get(cellKey)
  if (dependents) {
    for (const dep of dependents) {
      recalculateCell(dep)
    }
  }
}
Circular References

Implement cycle detection when evaluating formulas. Circular references should show an error rather than causing infinite loops.

Collaborative Features

Selection awareness: Share current cell selection via awareness (not CRDT):

awareness.setLocalStateField('selection', {
  user: { name: 'Alice', color: '#f783ac' },
  cell: { rowId: 'r8k2mf9n', colId: 'c3nd8k2m' }
})

Virtual rendering: For large sheets, only render visible cells based on viewport.

Common Mistakes

Using indices in formulas:

// WRONG: indices shift
formula: '=A1 * B2'

// CORRECT: use stable IDs
formula: { refs: ['r3k9mf8n:c8m2pt3q', 'r3k9mf8n:c2n7ks4w'], expr: '*' }

Recalculating everything:

// WRONG: O(n) on every change
cells.observe(() => recalculateAllCells())

// CORRECT: only affected cells
cells.observe(event => {
  event.changes.keys.forEach((_, key) => {
    recalculateDependents(key)
  })
})

See Also