Frontend System Design16 min readPremium

Design a Spreadsheet – Frontend System Design

Design a Spreadsheet - Frontend System Design

We’ll walk through the frontend system design for a collaborative spreadsheet like Google Sheets. Our goal is a grid that can hold 10,000 × 100 cells, stay fast while you arrow around, evaluate SUM, AVERAGE, and refs like =A1+B2, and show other people’s selections without turning the sheet into a million DOM inputs.

Interviewers are listening for three ideas: a sparse cell map, a windowed grid with one editor input, and a formula dependency graph that dirties dependents instead of the whole workbook. Collaboration is an operation stream on cells, not a second copy of the grid. You’ll see estimates for a million-cell sheet, the view-model split, and the keyboard model that makes this feel like a spreadsheet.

Problem Statement

Build the frontend for a collaborative spreadsheet like Google Sheets.

Requirements:

  • A grid of cells with a formula bar
  • Edit a cell, compute formulas that reference other cells
  • Virtualize so a 10,000 × 100 sheet stays fast
  • Multiple users can edit at once, with live cell selections

Describe the grid, formula evaluation, selection, and collaboration.


Clarifying Questions

# Question Interviewer’s Answer
1 How big is a sheet? Up to 10,000 rows by 100 columns for this interview.
2 Which formulas? SUM, AVERAGE, and cell refs like =A1+B2.
3 Charts, pivot tables, scripts? Out of scope.
4 OT or CRDT? Same trade-off as Docs. Pick one.

The size answer is the design constraint. 10,000 × 100 is one million addresses. If your first sketch is a nested map of <input>s, stop and redo the grid. Formulas are a directed graph, not a string replace. Charts and Apps Script are a different product. OT vs CRDT is the same conversation as Docs — pick one and say why last-write-wins per cell is enough for this interview.


Requirements

  • Virtualized grid with sticky row/column headers
  • Keyboard navigation (arrows, Tab, Enter, F2)
  • Formula bar bound to the active cell
  • Local formula engine for the visible sheet
  • Live collaborator selections

Add undo for local edits, a loading path for a snapshot, and a reconnect that replays unacked cell ops. The formula bar and the in-cell editor are two views of the same raw string. They must not diverge.

Non-Functional Requirements

  • Arrow-key movement stays on the UI thread without layout thrash
  • Opening a sparse million-cell sheet does not allocate a million objects
  • Local eval feels instant; the server re-evals so a stale tab cannot poison values
  • Viewers cannot send edit ops
  • Cell text is never interpreted as HTML

Napkin Math

10,000 × 100 = 1,000,000 cells. You cannot put a million DOM inputs on the page. A viewport is ~30 × 12 = 360 cells. Render those, plus a small overscan.

Scalability Estimates

Treat the sheet as an address space, not as a DOM tree. The estimates below use the clarifying size and the viewport already in the napkin math.

Metric Estimate Calculation Notes
Addressable cells 1,000,000 10,000 × 100 Do not mount them
Viewport cells ~360 ~30 rows × 12 columns Same as the napkin paragraph
Overscan window ~650 (30+10) × (12+6) A few extra rows/cols for fast scroll
Sparse filled cells 50,000 5% of 1,000,000 Interview assumption. Empty cells are absent.
Client memory ~4 MB 50,000 × ~80 bytes (raw + value) Fits in a tab. A dense 2D array would not stay this small.
Formula dirty set Local Average ~3 dependents, topo order Never recalc the million addresses
Presence events 10–15 Hz each Debounce pointer/keyboard selection Overlays, not document ops

If you say “virtualize” without saying 360 painted cells vs 1,000,000 addresses, you have not done the estimate. The sparse map is why a mostly-empty finance sheet is a few megabytes and not a few hundred.


Architecture

Google Sheets Frontend System Design Architecture


<SheetContainer>
  useViewModel()
  <FormulaBar />
  <Grid>
    <HeaderRow />
    <HeaderCol />
    <Cell />
  </Grid>
  <SelectionOverlay />
</SheetContainer>

Store cells as a sparse map, not a 2D array:


type CellId = `${string}:${number}`; // "A:1"

export type Cell = {
  raw: string;           // what the user typed, e.g. "=A1+2"
  value: string | number | null;
  error?: string;
};

export type Sheet = {
  cells: Record<CellId, Cell>;
  rows: number;
  cols: number;
  active: CellId;
};

Empty cells don’t exist in the map. That’s how a million-cell grid stays small.

The view-model is the product. SheetContainer is a shell. The grid paints a window. The formula engine and the collab socket sit behind the view-model so a keypress does not talk to the network directly.

Component Structure and Responsibilities

  • SheetContainer: Loads the snapshot, owns useViewModel(), and hosts the formula bar, grid, and overlays. Does not compute SUM itself.
  • FormulaBar: Always-on editor bound to sheet.active. Shows raw while editing, committed raw otherwise. Enter commits and moves down; Escape restores the previous raw.
  • Grid: Scrollport. Translates scrollTop / scrollLeft into startRow..endRow and startCol..endCol. Absolutely positions (or canvases) only that window.
  • HeaderRow / HeaderCol: Sticky labels. They scroll on one axis and stay put on the other. Clicking a header selects the row or column; the overlay draws that range.
  • Cell: A painted value, not an input. Reads value (or error) from the sparse map. The active cell is still a painted cell until the user types or hits F2.
  • Editor: One <input> (or textarea) teleported onto the active cell. Mounted on edit start, unmounted on commit. This is the whole reason the grid stays fast.
  • SelectionOverlay: One absolutely positioned rectangle in grid coordinates, plus collaborator rectangles. Not a CSS outline on 400 cells.

Network Layer and Data Management

A spreadsheet client has a snapshot path and an op path. Do not refetch the sheet on every edit.

  • SnapshotClient: GET /api/sheets/:id returns cells, dimensions, and a revision. Used on first load and after a gap the socket cannot fill.
  • SyncSocket: WS /api/sheets/:id/sync. Sends cell edits and local presence. Receives other people’s edits and selections. Presence is not written into cells.
  • FormulaEngine: Parses raw, stores a dependency graph, evaluates in topo order, writes value / error. Runs on the client for instant feedback.
  • ViewModel: Viewport window, active cell, edit buffer, undo stack. The grid asks it for the cells in range. The engine asks it for raw on dirty ids.

The server re-evaluates on save so a stale tab can’t poison the sheet. Your client may show a value that the next snapshot corrects. If a formula errors locally, keep the raw — do not delete the user’s text.

Edit Workflow

  1. Load snapshot. Build the sparse map and the dependency graph from every raw that starts with =.
  2. Grid renders the window from scroll position. Headers stick. The overlay sits on active.
  3. Arrows / Tab / Enter move active and move the overlay. No editor yet. Formula bar shows that cell’s raw.
  4. The user types, or double-clicks, or presses F2. The single editor mounts. Keystrokes go to the edit buffer, not straight into cells.
  5. Commit (Enter, Tab, blur). Write raw into the map. Parse. Dirty that cell and everything that depends on it. Evaluate in topo order. Detect cycles as #REF!.
  6. Send { type: "edit", cellId, raw, rev } on the socket. Optimistic value stays on screen. If the server rejects (viewer, conflict), roll back that cell and its dependents.
  7. Remote edit: apply raw, dirty dependents, re-eval. If the user is mid-edit on that same cell, keep the local buffer and show a “updated elsewhere” hint on commit.

That sequence is the interview. Navigation is cheap. Editing is one input. Recalc is a subgraph.


Building the Canvas Grid

Sheets canvas grid with one teleported editor input
Google Sheets paints the grid. It does not mount a DOM node per cell. A 10,000 × 100 window of <div>s will hitch on scroll; a million-cell sheet is a non-starter. You draw cells onto a canvas (or a small stack of canvases) and teleport one HTML input onto the cell the user is editing.

A 45-minute answer can say “absolutely positioned divs plus one input.” That is an honest timebox. The production answer is the canvas. Walk it.

Coordinate system

Treat the sheet as a camera over a huge plane. Row 1 / column A is the origin. Everything else is a prefix sum.


const ROW_H = 24; // interview default; variable later
type ColWidths = number[]; // index 0 = A

function rowTop(r: number) { return (r - 1) * ROW_H; }
function colLeft(widths: ColWidths, c: number) {
  let x = 0;
  for (let i = 0; i < c; i++) x += widths[i] ?? 100;
  return x;
}
function cellRect(widths: ColWidths, id: CellId): Rect {
  const { col, row } = parseId(id);
  return { x: colLeft(widths, col), y: rowTop(row), w: widths[col] ?? 100, h: ROW_H };
}

function pointToCell(widths: ColWidths, p: { x: number; y: number }): CellId {
  const row = Math.floor(p.y / ROW_H) + 1;
  let x = 0;
  for (let c = 0; c < widths.length; c++) {
    x += widths[c] ?? 100;
    if (p.x < x) return toId(c, row);
  }
  return toId(widths.length - 1, row);
}

At 100 columns a linear walk is fine. At 1,000+ columns, binary-search a prefix-sum array. Variable-height rows are the same idea: a prefix sum of heights, startRow = upperBound(prefix, scrollTop).

The scrollport is a spacer div whose width/height is the full sheet (totalColsWidth × totalRowsHeight). The canvas is position: sticky inside it and sized to the visible client rect. You never create 10,000 row nodes to make the scrollbar work.

What you paint, in order

One rAF callback, never from scroll directly. Scroll and pointer handlers only set dirty = true.

  1. Clip to the viewport plus one row/col of prefetch so fast flicks do not flash empty cells.
  2. Grid lines — a single path of verticals and horizontals, not a stroke per cell.
  3. Fills — row-band, column-band, and the selection range. One fillRect for the whole selection, not per cell.
  4. Frozen panes — paint the corner, the frozen rows, and the frozen cols in their own clip regions so they do not scroll. Sheets keeps a separate canvas (or a clip + translate) for each pane so a scroll on the main pane does not redraw the frozen header.
  5. Text — for each cell in the window, look up the sparse map. Skip missing ids (empty). fillText the display value, clipped to the cell rect. Right-align numbers, left-align strings, center errors. Cache the font and the measured width so a 60fps scroll does not re-measure “Revenue” 200 times.
  6. Overlays that belong on the canvas: the selection border, the fill handle, remote presence rectangles, the dotted range while a formula is being written (=SUM(A1:A3) highlights A1:A3).

Headers can stay DOM (sticky <div>s) because there are ~20 of them on screen. Or paint them on the same canvas. Either is fine. Do not paint them twice.

Device pixel ratio: backing store is cssSize * dpr. Reset on resize. Same rule as Docs and the drawing tool.

The one HTML input

The grid is not editable. The editor is a single <input> (or textarea for wrapped cells) that you position over cellRect(active) when the user starts typing, double-clicks, or hits F2.

  • Until then, the active cell is just a painted border. Arrows move active and move that border. No input in the tree. This is how Sheets feels instant.
  • On edit start, mount the input, copy raw into it, focus it, select-all on type-to-overwrite, caret-at-end on F2.
  • Keystrokes go to the edit buffer, not into cells. The formula bar is a second view of the same buffer.
  • Commit (Enter, Tab, blur) writes raw, runs the engine, unmounts the input. Escape restores the previous raw and unmounts.
  • IME works for free because the focused node is a real input. You do not need Docs’ hidden-caret trick unless you also canvas-paint in-cell rich text (out of scope).

Never leave the input mounted on the previous cell. A leftover input is how you get a cell that “eats” arrow keys instead of moving the selection.

Hit-testing and selection

Pointer down on the canvas: pointToCell(scrollLeft + e.offsetX, scrollTop + e.offsetY). Shift-click extends the range. Drag with pointer capture paints the overlay on rAF. Headers select the whole row/col.

Selection is a range { r1, c1, r2, c2 }, not a CSS outline on 400 cells and not a list of ids. Collaborator presence is the same shape in a different color, mapped from their active + range on the socket.

Scroll-active-into-view: when Enter moves down off the bottom of the window, set scrollTop so that row is inside the viewport. Same for Tab and jump-to-cell (Cmd+G).

What you still use the DOM for

The formula bar, the sheet tabs, the input, and the charts you do not have time to canvas. Charts are HTML/SVG overlays positioned in grid coordinates. Comments are pins on a cell id, not pixels — map through cellRect so a column resize moves them.

Accessibility: a canvas grid is invisible to AT. Keep a hidden table (or a single role="grid" with the window of cells mirrored as role="gridcell") synced from the same sparse map, or put aria-activedescendant on a focusable grid and announce the active cell’s raw and value in a live region when it moves. The input, while mounted, is the accessible editor. Say this out loud or the canvas answer fails the a11y follow-up.

A 45-minute answer can start with a window of light DOM cells plus one input. Production Sheets paints the grid. Say the timebox, then walk the canvas. Interviewers who want native clipboard and IME “for free” are asking for that hybrid: canvas for paint, DOM for the active editor.

Structured ops, not a generic patch

SetCellInput, SetRangeFormat, and ResizeColumn are different operations. Do not flatten them into “here is the new sheet JSON.” Each UPDATE is { opId, sheetId, baseRev, type, range }. Resize does not rewrite formulas or shift addresses — it is metadata. Insert/delete row is the follow-up that needs stable row ids; leave it out of v1 unless they push.

Formatting precedence

A cell’s look is a stack, not a blob on every cell. Sheet default → column → row → cell override. Formatting column C updates one ColumnMeta, not 10,000 cell objects. The paint path resolves the stack for the visible window only.

Clipboard

Copy of A1:B3 writes text/plain (tab-separated) and a private application/x-sheet-range with raw + formats. Paste into Slack is TSV. Paste into the sheet applies SetCellInput per cell and dirties the union of dependents. Cut is copy plus clear. Do not use the browser’s HTML table paste as your model — Word soup will trash types.

Who evaluates the formula

The client dirties the loaded subgraph so A1 updates before the ACK. The server repeats the eval against the full sheet and broadcasts the authoritative values for that revision. If a visible formula depends on an offscreen cell you have not hydrated, show the optimistic number and mark it tentative until the server result lands. Range formulas (SUM(A:A)) must not explode into a million dependency edges — store the range, expand only for the dirty window.


Formulas

  1. Parse raw into an AST.
  2. Build a dependency graph (A1 depends on B2).
  3. On edit, dirty that cell and everything that depends on it (topo order).
  4. Detect cycles and show #REF!.

Evaluate on the client for instant feedback. The server re-evaluates on save so a stale tab can’t poison the sheet.

Keep two graphs if you want a clean undo: deps (this cell reads those ids) and rev (those ids must recompute when this cell changes). A parse of =SUM(A1:A3)+B2 expands the range into A1, A2, A3, B2. Ranges are why you do not store a single pair.

Unsupported functions become #NAME?. Divide by zero becomes #DIV/0!. Ref to a missing cell is 0 or blank depending on how you define empty — pick one and be consistent. Do not invent Excel’s whole error catalog; three errors is a complete interview.


function commit(sheet: Sheet, graph: DepGraph, id: CellId, raw: string) {
  sheet.cells[id] = { raw, value: null };
  graph.reindex(id, parseRefs(raw));
  const order = graph.topoFrom(id);
  if (!order) {
    sheet.cells[id]!.error = "#REF!";
    return;
  }
  for (const cellId of order) {
    evaluate(sheet, cellId);
  }
}

Collaboration

Cell edits are operations: { cellId, raw, actor, ts, baseRev }. Last-write-wins per cell is enough for an interview (Sheets is closer to OT on a grid). Two people typing in A1 do not character-merge — the later revision wins, the formula bar can flash. Presence is a colored selection rectangle, same as Docs: not part of the document.

Use the same one-in-flight rule as Docs, at cell granularity. Buffer local edits to different cells; do not send a second UPDATE for A1 until A1’s ACK lands. A reconnect sends your last sheet rev and any durable unacked cell batches. If the server dropped the tail, it returns a fresh snapshot plus SNAPSHOT_REQUIRED — rebase the queue, do not replay from cell A1 of time zero.

Same trade-off as Docs, applied to cells instead of characters. OT on a grid transforms ops against a revision. A CRDT would give you per-cell registers (LWW) plus a way to grow rows. For this interview, LWW per cellId is the honest default: two people typing in A1 do not character-merge, the later revision wins, and the formula bar can flash.

Presence rides the same socket with a different message type. Throttle it. A selection moving through 30 cells during a shift-arrow should not emit 30 packets — emit the range when it settles, or at 10–15 Hz while dragging.

Viewers subscribe to the socket and never send type: "edit". The client hides the editor; the server is the real gate.


State and Data Flow

Unidirectional, with the grid as a function of window + sparse map + overlay.

  1. User moves the active cell. ViewModel updates active and the overlay rect. Formula bar reads cells[active].raw. No network.
  2. User starts edit. ViewModel copies raw into an edit buffer. The Editor mounts. The grid keeps painting other cells from committed state.
  3. Commit writes the map, runs the engine, pushes an undo record { cellId, prevRaw }, and sends the op.
  4. Socket events arrive on the SyncSocket, apply through the same commit path (without echoing back), and the window re-paints only if a dirty id is on screen.
  5. Scroll updates the window. Cells that leave the range unmount (or recycle). Values stay in the map.

The important split: scroll does not touch the formula graph. A speak-equivalent here is “moving the selection.” If moving A1 to A2 re-evaluates the sheet, the data flow is wrong.


Data Model

The types already on the sheet are the core. Extend them with a viewport, a selection, and the op the socket understands.


type CellId = `${string}:${number}`; // "A:1"

export type Cell = {
  raw: string;
  value: string | number | null;
  error?: string;
};

export type Sheet = {
  cells: Record<CellId, Cell>;
  rows: number;
  cols: number;
  active: CellId;
};

type CellRange = { r0: number; c0: number; r1: number; c1: number };

type Viewport = {
  startRow: number;
  endRow: number;
  startCol: number;
  endCol: number;
};

type Presence = {
  actorId: string;
  name: string;
  color: string;
  range: CellRange;
};

type EditOp = {
  type: "edit";
  cellId: CellId;
  raw: string;
  actor: string;
  rev: number;
};

type PresenceOp = {
  type: "presence";
  range: CellRange;
  actor: string;
};

value is derived. Persist raw and rev. You can cache values in the snapshot to paint faster on load, but a client that cannot recompute from raw is not a spreadsheet.


API


// GET /api/sheets/:id              snapshot
// WS  /api/sheets/:id/sync         { type: "edit", cellId, raw }
//                                  { type: "presence", range }
// PUT /api/sheets/:id/meta         { title }
Action Method & Endpoint Description
Open sheet GET /api/sheets/:id Snapshot: { cells, rows, cols, rev, title }. Sparse map only.
Sync WS /api/sheets/:id/sync Edit ops and presence. Auth on connect. Server assigns rev.
Title PUT /api/sheets/:id/meta { title }. Not a cell op.
Catch-up GET /api/sheets/:id/ops?after=rev If the socket gap is small, replay ops instead of a full snapshot.

Example: Consuming a Remote Edit


socket.addEventListener("message", (ev) => {
  const msg = JSON.parse(String(ev.data)) as EditOp | PresenceOp;
  if (msg.type === "presence") {
    viewModel.setRemotePresence(msg.actor, msg.range);
    return;
  }
  if (msg.cellId === viewModel.editingId) {
    viewModel.flagCollision(msg.cellId);
  }
  commit(sheet, graph, msg.cellId, msg.raw);
  if (isOnScreen(msg.cellId, viewModel.viewport)) {
    grid.invalidate(msg.cellId);
  }
});

Invalidate one cell (and on-screen dependents), not the whole window. Presence never calls commit.


Optimizations

  • Virtualize rows and columns
  • One editor input
  • Memoize formatted values; dirty only dependents
  • Don’t re-render the whole grid when the active cell moves — move the overlay

The scroll handler should be math, not React state on every pixel.


const OVERSCAN = 5;

function windowFromScroll(
  scrollTop: number,
  scrollLeft: number,
  viewH: number,
  viewW: number,
  rowH: number,
  colW: number,
  rows: number,
  cols: number
) {
  const startRow = Math.max(0, Math.floor(scrollTop / rowH) - OVERSCAN);
  const endRow = Math.min(rows, Math.ceil((scrollTop + viewH) / rowH) + OVERSCAN);
  const startCol = Math.max(0, Math.floor(scrollLeft / colW) - OVERSCAN);
  const endCol = Math.min(cols, Math.ceil((scrollLeft + viewW) / colW) + OVERSCAN);
  return { startRow, endRow, startCol, endCol };
}

Compare the new window to the last one. If it did not change, skip. If it slid by one row, recycle the row that left and fill the row that entered. Moving the active cell updates overlay coordinates with transform, not a grid re-render.

Formatters (currency, percent) run when value changes, not on scroll. Store a display string next to value if formatting is expensive.


Handling User Edge Cases

Case Strategy
❌ Empty commit / Escape Escape restores previous raw and unmounts the editor. Empty raw deletes the map entry (cell becomes absent).
❌ Cycle in formulas Topo sort fails → #REF! on the cycle members. Keep raw so the user can fix it.
🔄 Reload mid-edit Uncommitted buffer is lost unless you stash it in sessionStorage keyed by sheet id + cell id. Say this out loud.
📶 Socket drop Queue outgoing edits. On reconnect, send the queue, then GET ops?after=rev. If the gap is huge, refetch the snapshot.
⏳ Stale tab Server values win on snapshot. Local unacked ops replay after. Show a toast if more than a few cells change.
🚫 Viewer types Do not mount the editor. Formula bar is read-only. Server rejects edit ops.
⌨️ IME / composition Do not commit on every keyDown during composition. Wait for compositionend.
🖱️ Drag-select off the window Auto-scroll the viewport while the pointer is at the edge. Presence sends the settled range.

Pro-Level Optimizations

Area Suggestion
Paint Recycle cell nodes. Or canvas the values and keep DOM only for the editor and overlays.
Recalc Batch dependent evals in requestAnimationFrame so a paste into 50 cells does one paint.
Undo Stack of per-cell raw snapshots. Redo is the inverse. Remote ops do not clear undo for other cells.
IME / formula bar One edit buffer, two surfaces. Sync on input; do not keep two sources of raw.
Large paste One op batch, one graph reindex, one topo. Do not fire 500 socket messages.
Worker If the dirty set is huge, eval in a Worker and post values back. UI thread keeps scrolling.

Accessibility and Security

  • role="grid", each cell role="gridcell", aria-rowindex / aria-colindex
  • Active cell is aria-activedescendant on the grid
  • Sanitize anything that looks like HTML in a cell (XSS)
  • Viewers cannot send edit ops

The grid is one tab stop. Arrows move the active descendant. Tab moves to the next cell (spreadsheet convention) and should not dump the user into the browser chrome until they leave the sheet. The formula bar is a second tab stop; F2 and the bar edit the same buffer.

Row and column headers need names (“Row 12”, “Column C”), not just painted letters. Errors should be in the accessible name (“C4, #REF!”), not only a red fill.

Never use innerHTML for a cell. A value that looks like <img onerror=...> is text. Collaboration does not change that — a remote raw string is as dangerous as a local one if you render it as HTML.


Conclusion: How to Design a Spreadsheet

Sheets is a virtualized sparse map plus a formula graph. If you mount a million inputs, you’re done. If you can walk a dependency graph and a windowed grid, you’re at senior level.

The rest is discipline about what is in the document. raw and revision are the document. value is a cache. Presence is an overlay. The editor is a single DOM node you place on the active cell. The socket sends cell ops, not whole rows.

Say the estimates out loud (a million addresses, ~360 painted cells), pick LWW-per-cell unless you want to spend the interview on OT, and keep scroll off the recalc path. That is the frontend system design. Everything else — charts, pivots, scripts — is a different hour.