Design Google Sheets

Requirements: what to ask before drawing anything

A spreadsheet is a grid of cells. Each cell holds a value (a number, some text, a date) or a formula: a small program such as =A1*2 whose result depends on other cells. The grid looks simple, but a spreadsheet is really three products in one: a rendering engine that draws millions of cells, a small programming language with its own runtime, and a shared document that many people change at once.

The interviewer gave you a big name and little detail. Your first job is to shrink the problem to something you can design in an hour. Spend the first five minutes on questions. Each question below is worth asking because its answer changes a real decision later.

Functional requirements

• Open a file with several sheets (tabs); switch sheets.
• Scroll a large grid smoothly in both directions, with frozen header rows and columns.
• Select a cell, a range, whole rows or columns, and several ranges at once; move with the keyboard like a desktop spreadsheet.
• Type a value or a formula into a cell, or into the formula bar; see results update right away.
• Resize rows and columns; insert and delete rows and columns.
• Format numbers, dates, and currencies; text style such as bold and background color.
• Copy and paste inside the sheet and to and from other apps such as Excel or a web page.
• Undo and redo your own changes.
• See other editors' selections and their edits within a second.

Non-functional requirements, with numbers

• Scrolling at 60 frames per second: each frame has about 16 ms, and the browser needs some of it, so our drawing work per frame should stay under about 8 ms.
• Typing feels instant: a key press shows in the cell within one frame. Input delay is what users feel most; Interaction to Next Paint (INP) should stay under 200 ms even for heavy edits.
• Recalculation: a change that touches 10,000 dependent formulas should show new results within about 100 to 300 ms, and never block typing or scrolling while it runs.
• First view: the first screen of cells is visible within about 2 seconds on a laptop with a normal connection, even for a 50 MB file.
• Memory: a 20-million-cell file must fit in the tab. At 100 bytes per cell that is already about 2 GB, so we will need compact storage and lazy loading.
• Collaboration: other people's edits appear within about 1 second; nobody's edit is silently lost.
• Accessible: usable with the keyboard alone and with a screen reader.

Out of scope, and why

Charts, pivot tables, filter views, scripting (like Apps Script), and import of every Excel feature are each a full design question of their own. Say so out loud, and mention where they would plug in later: charts read ranges from the same cell store, and scripts call the same edit API that the UI uses. The backend storage design is also out of scope: we treat the server as a service that orders edits, stores them, and sends them back out.

The wireframe names the parts we will design: the toolbar and formula bar at the top, the column and row headers, the frozen panes, the main scrolling grid, the editor that appears over a cell, the colored boxes of other editors, and the sheet tabs at the bottom.

What makes a spreadsheet hard

Before picking any technology, name the forces that push on every later decision. A spreadsheet has five of them, and each one rules out the easy answer.

1. It is huge in two directions. A news feed is long but one item wide. A sheet with 100,000 rows and 50 columns has 5 million cells, and the user can jump to any of them with Ctrl+End or a scrollbar drag. We have to virtualize rows and columns at the same time.
2. Every cell can depend on every other cell. =SUM(B2:B9000) reads 8,999 cells. Change B5 and that sum must update, and so must every formula that reads the sum, and so on. A spreadsheet is a live program, and the user writes it one cell at a time.
3. The user expects desktop speed. People have used Excel for decades. They hold the arrow key down and expect the selection to glide; they paste 20,000 rows and expect it to work. Any pause over about 100 ms feels broken.
4. It is shared. Five people edit the same sheet. One inserts a row above row 10 while another types into A12. Both edits must land in the right place on every screen, and the formulas that point at moved cells must follow them.
5. The data is personal and precise. A wrong number in a budget is worse than a slow page. Recalculation must be correct, undo must undo exactly what you did, and a dropped connection must never lose an edit.

These forces map to the chapters that follow. Size drives rendering and loading; dependencies drive the formula engine and where it runs. Desktop speed drives the selection model, the editor, and the performance budget. Sharing drives the sync design and the data model; precision drives undo, clipboard handling, and number formats.

A quick estimate

Numbers make the problem concrete. Assume a laptop screen shows about 40 rows and 15 columns at once: 600 visible cells. The heavy sheet has 5 million cells, so we show about 0.01 percent of it at any moment.

If one cell costs about 1 KB of DOM and style data, rendering all of them would need about 5 GB: impossible. Rendering only the visible 600 plus a margin needs about 1 MB: easy. That ratio is the whole argument for virtualization, and we will build on it in the next chapter.

For formulas, assume 20 percent of the cells hold formulas: 1 million of them. A full recalculation that spends just 1 microsecond per formula takes 1 second, which is far too slow to run on every key press. So we must recalculate only the formulas that depend on the changed cell, and we must do it away from the main thread.

Rendering a huge grid: DOM or canvas

Start with the naive approach, because the interviewer wants to see you know why it fails. The naive grid is an HTML table with one td per cell. For a 20 by 20 sheet it is perfect: text selection, accessibility, and styling all work by default.

Now load the heavy sheet: 100,000 rows by 50 columns. That is 5 million td elements. At roughly 1 KB of memory per element with its styles, that is gigabytes, and every scroll or style change would make the browser recompute style and layout for a tree that size.

Long before that, at a few hundred thousand cells, building the table takes seconds and the tab stops responding. The naive grid dies long before we reach real sizes.

The fix is virtualization: draw only the cells inside the viewport plus a small margin, and fake the rest of the scroll height and width with an empty spacer element. The lesson on virtualization covers the one-direction case for lists. A spreadsheet needs it in two directions at once: a window of rows and a window of columns, and we draw their crossing. The real question is how we draw the cells inside that window.

How the canvas draw loop works

The canvas never moves. A scroll container with a spacer of the full sheet size gives us real scrollbars, trackpad momentum, and keyboard scrolling with no extra code. The canvas sits in that container with position: sticky at the top left.

When the container fires a scroll event, we don't draw right away: we mark the frame dirty and schedule one draw with requestAnimationFrame. Several scroll events in the same frame then cost one draw, not several.

function draw(ctx: CanvasRenderingContext2D, view: Viewport) {
  const rows = visibleRange(rowCache, view.scrollTop, view.height, 3);
  const cols = visibleRange(colCache, view.scrollLeft, view.width, 2);
  ctx.clearRect(0, 0, view.width, view.height);
  for (let r = rows.start; r <= rows.end; r++) {
    const y = rowCache.offsetOf(r) - view.scrollTop;
    for (let c = cols.start; c <= cols.end; c++) {
      const x = colCache.offsetOf(c) - view.scrollLeft;
      const cell = store.get(view.sheetId, r, c); // sparse: often undefined
      if (cell) paintCell(ctx, cell, x, y, colCache.sizeOf(c), rowCache.sizeOf(r));
    }
  }
  paintGridLines(ctx, rows, cols, view);
  paintSelections(ctx, selection, remoteSelections, view);
}

Four details separate a sharp, fast canvas grid from a blurry, slow one:

• High-density screens. Set the canvas's pixel size to its CSS size times window.devicePixelRatio, then call ctx.scale(dpr, dpr). Without this, text looks blurry on a 2x laptop screen. Listen for ratio changes (the user zooms the page or drags the window to another monitor) and resize.
• Text measurement is the slow part. measureText and font switches cost more than filling rectangles. Cache measured widths per font and string, set the font once per run of cells with the same style, and only measure when text might overflow its cell.
• Clip each cell. Long text must stop at the cell edge unless the next cell is empty, in which case spreadsheets let it spill over. Decide overflow first, then clip with a rectangle path; drawing outside a cell and painting over it later wastes time.
• Layers. Use two stacked canvases: one for cells and one for selection boxes and remote cursors. Moving the selection with an arrow key then repaints only the light top layer, not 1,000 cells.

The window is a little bigger than the viewport: we draw a few extra rows and columns, called overscan, so a small scroll doesn't reveal a blank edge before the next frame. Try it: scroll the lab below and watch how many cells are drawn compared to the sheet size.

Sizes, frozen panes, and smooth scrolling

The draw loop asked two questions every frame: "which row is at pixel 84,000?" and "where does row 4,000 start?". If every row had the same height, both answers would be one multiplication.

Real sheets are messier: the user drags row 3 to 60 px, wraps text in row 900, hides rows 10 to 50 with a filter, and widens column B. We need a structure that answers both questions fast for any mix of sizes. Call it a position cache, one per axis (rows and columns).

The naive answer adds up heights from row 0 every time we need an offset. Row 90,000 then costs 90,000 additions, and we ask that question for every visible row in every frame. At 60 frames per second it burns the whole frame budget on arithmetic.

A worked example shows why storing only the differences is compact. The default row height is 21 px; row 3 was resized to 60 px (a difference of +39) and row 7 is hidden (a difference of -21). So row 10 starts at 10 * 21 + 39 - 21 = 228 px. Two numbers stored, any offset answered.

class AxisCache {
  private tree: Float64Array; // Fenwick tree of (size - defaultSize)
  private sizes = new Map<number, number>(); // only non-default sizes
  constructor(private count: number, private def: number) {
    this.tree = new Float64Array(count + 1);
  }
  sizeOf(i: number) { return this.sizes.get(i) ?? this.def; }
  setSize(i: number, size: number) {
    const delta = size - this.sizeOf(i);
    this.sizes.set(i, size);
    for (let k = i + 1; k <= this.count; k += k & -k) this.tree[k] += delta;
  }
  offsetOf(i: number) { // start of item i, in px
    let sum = i * this.def;
    for (let k = i; k > 0; k -= k & -k) sum += this.tree[k];
    return sum;
  }
  indexAt(px: number) { // last item whose start is at or before px
    let lo = 0, hi = this.count - 1;
    while (lo < hi) {
      const mid = (lo + hi + 1) >> 1;
      if (this.offsetOf(mid) <= px) lo = mid; else hi = mid - 1;
    }
    return lo;
  }
}

Frozen rows and columns

Users freeze the header row and the first column so labels stay visible while they scroll. With frozen panes, the viewport is really four regions that scroll differently, and each one is a separate clip rectangle in the same draw pass:

• Corner: frozen rows times frozen columns. It never scrolls.
• Top band: frozen rows. It scrolls left and right with the body, never up and down.
• Left band: frozen columns. It scrolls up and down with the body, never left and right.
• Body: everything else. It scrolls both ways, and its visible window starts after the frozen rows and columns.

The row and column headers (the A, B, C strip and the 1, 2, 3 strip) are just two more bands drawn by the same rules. Hit testing a click follows the same split: first decide which region the point is in, then use that region's scroll offset to turn the pixel into a row and column. Selections that cross a frozen boundary are drawn in each region separately and clipped, so the blue box looks continuous.

Smooth scrolling, and the giant-sheet scrollbar problem

We keep native scrolling because it gives momentum on trackpads, scrollbar dragging, and accessibility with no extra code. Two problems remain.

First, browsers cap how tall an element can be. The exact limit differs by browser, but it is in the range of tens of millions of pixels. A sheet with 2 million rows at 21 px is 42 million pixels, which can be past the cap, so the spacer silently stops growing and the last rows become unreachable.

The fix is a scaled scrollbar: when the true size is over a safe limit (say 10 million px), the spacer is that limit, and we map the scroll position to the sheet with a ratio: sheetY = scrollTop * (totalHeight - viewport) / (spacerHeight - viewport). Small wheel movements are then handled in sheet pixels, so one notch still moves about three rows, not thousands.

Second, many spreadsheets snap vertical scrolling to whole rows, so the top row is never cut in half. It also makes the scaled math easier: map the scroll position to a row index first, then draw from that row. Horizontal scrolling usually stays pixel-smooth because columns are wide.

The selection model and keyboard navigation

Selection looks like a highlight, but it is the main input device of a spreadsheet. Almost every command (bold, delete, copy, sum, insert row) acts on "the selection". If the model is too simple, half the keyboard shortcuts become impossible to build.

The naive model is one rectangle: { top, left, bottom, right }. It breaks on the first real interaction.

Click B4, press Shift+Down twice, then Shift+Up once. Is the range now B4:B5 or B5:B6? It depends on which corner stayed fixed, and a plain rectangle doesn't remember. It also can't express Ctrl+click adding a second range, and it doesn't know which cell inside the range receives typing.

What a selection really holds

type CellRef = { row: number; col: number };
type Range = { top: number; left: number; bottom: number; right: number };

type Selection = {
  sheetId: string;
  ranges: Range[];       // one or more; Ctrl+click adds another
  activeRange: number;   // which range keyboard extension changes
  anchor: CellRef;       // the fixed corner while extending
  active: CellRef;       // the cell that receives typing; inside ranges[activeRange]
};

Three ideas live in that type. The active cell is the one with the thick border: typing goes there and the formula bar shows its content. The anchor is the corner that stays put while you extend with Shift.

The far corner is the one that moves; it is not the active cell, which surprises many people: in both Excel and Google Sheets, Shift+Down grows the range while the active cell stays where it was. And several ranges let a user pick A1:A10 and C1:C10 together to chart or format them.

The keyboard map

The keyboard navigation lesson covers roving focus and the ARIA grid pattern in general. A spreadsheet adds a long list of shortcuts that users have memorized. Each one is a small pure function from the old selection to the new one, which makes them easy to unit test.

The data edge rule for Ctrl+Down is worth stating precisely, because it is a classic follow-up. If the current cell and the one below are both filled, move to the last filled cell before a gap. Otherwise (the current cell or the one below is empty), jump to the next filled cell further down. If there is none, go to the last row of the sheet.

This needs a fast "next filled cell in this column after row r" query, so the cell store keeps each column's filled rows in sorted order (more in the data model chapter). Scanning 100,000 empty rows one by one on each key press would be too slow.

Mouse dragging and the fill handle

A drag selection starts on pointerdown, which sets the anchor and calls setPointerCapture so we keep receiving pointermove even when the pointer leaves the grid. When the pointer is near an edge, a timer scrolls the grid and keeps extending, so the user can drag-select 500 rows. On pointerup we release capture and stop the timer.

The fill handle is the small square at the bottom right of the selection. Dragging it repeats the selection into new cells: a single value is copied, a pattern like 1, 2 continues as 3, 4, 5, a date steps by a day, and formulas are copied with their relative references shifted (=A1*2 filled down becomes =A2*2, =A3*2). Fill is a single document edit that writes many cells, so it undoes in one step.

Editing a cell: modes, the editor overlay, and IME

Editing has to feel like a desktop app, and the same key means different things depending on what the user is doing. Arrow keys move the selection while navigating, move the text cursor while editing text, and pick a cell reference while typing a formula. So editing is a state machine, not a boolean isEditing flag.

• Navigating: the grid has focus; keys move and extend the selection.
• Quick entry: the user started typing on a cell. The old content is replaced. Arrow keys commit the value and move, so you can type a column of numbers with 5, Down, 7, Down.
• Full edit: entered with F2, Enter, or a double-click. The old content is kept, and arrow keys move the text cursor inside it.
• Pointing: while typing a formula, right after an operator or (, arrow keys and clicks insert a cell reference such as B7 instead of moving the text cursor. Excel and Google Sheets both behave this way.

The editor overlay

On a canvas grid, the cell you edit is a real DOM textarea placed exactly over the cell, with the cell's font, alignment, and background. It grows right and down as you type, and it can cover neighbor cells while editing. When the user commits, the overlay hides and the canvas paints the new value.

There is a subtle trap. In quick entry, the first key press must already land in a text field. If the grid has focus and we only create the editor on keydown, the first character can be lost, and the first key of an IME composition (the input method used to type Chinese, Japanese, Korean, and many other languages, where several key presses build one character) goes to the wrong element.

The common fix: keep one hidden but focusable textarea always focused while navigating. Keys that are commands (arrows, Ctrl+C) are handled on it; anything that produces text simply goes into it, and at that moment we reveal it over the active cell. The browser never sees a focus change in the middle of typing. (The accessibility chapter adjusts this while a screen reader is in use.)

• During composition, keydown events have event.isComposing === true. Ignore our shortcuts then: Enter confirms the IME candidate, it must not commit the cell. Safari fires compositionend before that Enter keydown, so also ignore a keydown whose keyCode is 229, as the rich text editor guide explains.
• Listen to compositionstart and compositionend to know when the draft text is final.
• Let the browser handle undo inside the editor (Ctrl+Z while editing undoes typing); our document undo starts only after the commit.

The formula bar

The formula bar above the grid shows the raw content of the active cell: the formula, not its result. It is a second editor for the same draft. Both editors bind to one draft value in the editing store, so typing in one updates the other. When the user types a formula, both color each reference (B2 blue, C3:C9 red) and the grid draws matching colored boxes around those cells, which is the most helpful thing a spreadsheet does for formula authors.

Committing an edit

On Enter, the raw text becomes a typed value. = starts a formula, which is parsed (next chapter). A leading apostrophe forces text, so '007 stays 007. Otherwise we try number, percentage, date, and boolean, using the file's locale: 1/2 is January 2 in the US, February 1 in the UK, and 1,5 is one and a half in Germany.

The typed value is then applied locally right away (an optimistic update), sent to the server as an operation, and passed to the formula engine to recalculate dependents.

The sequence shows why the user never waits for the network or the engine: the typed value appears in the next frame, the engine result arrives a few milliseconds later, and the server's acknowledgment arrives within a round trip. If the server rejects the edit (the user lost edit access, or the range is protected), the client rolls back that one operation and shows a message. Rollback is easy because the change is an operation with a known inverse, which we also need for undo.

The formula engine: parse, link, recalculate

The formula engine is the core of a spreadsheet and the part most candidates skip. It has three jobs: turn formula text into something it can run, know which cells depend on which, and after an edit, recalculate exactly the cells that need it, in an order where every input is ready before it is read.

Step 1: parse formulas into a tree

Running =SUM(B2:B9) * 1.2 + C1 by searching the string each time would be slow and fragile. Instead, a tokenizer splits the text into tokens (SUM, (, B2:B9, ), *, 1.2...), and a parser turns the tokens into an abstract syntax tree (AST): a tree where each node is an operation and its children are its inputs. Operator precedence (* before +) is decided once, during parsing. A small hand-written recursive descent or Pratt parser is enough: spreadsheet grammar is tiny compared to a programming language.

type Ref = { sheet?: string; row: number; col: number; rowAbs: boolean; colAbs: boolean };

type Node =
  | { kind: "number"; value: number }
  | { kind: "string"; value: string }
  | { kind: "ref"; ref: Ref }                      // B2, $B$2, 'Q1 Budget'!B2
  | { kind: "range"; from: Ref; to: Ref }          // B2:B9, or B:B for a whole column
  | { kind: "binary"; op: "+" | "-" | "*" | "/" | "^" | "&" | "=" | "<"; left: Node; right: Node }
  | { kind: "call"; name: string; args: Node[] };  // SUM(...), IF(...), VLOOKUP(...)

A neat trick: store references as offsets from the formula's own cell, not as fixed addresses. =A1*2 in B1 becomes "the cell one column to my left, same row, times 2". Fill it down 10,000 rows and every copy has the same relative form, so all of them can share one parsed tree.

Excel's file format does something similar with "shared formulas". Only absolute parts ($A$1) stay fixed. This also makes copy, paste, and fill almost free, as we'll see in the clipboard chapter.

Step 2: the dependency graph

After parsing, we walk the tree and collect every reference: the formula's precedents (the cells it reads). We store the reverse direction too: for each cell, its dependents (the formulas that read it). Together they form a directed graph. When B5 changes, we follow the dependents edges to find every formula that might need a new value.

Ranges need care. =SUM(B2:B9000) reads 8,999 cells. Adding 8,999 edges for one formula is wasteful, and 1,000 such formulas would create about 9 million edges.

Instead, keep range dependencies in a separate index of rectangles (per column interval lists, or a spatial index such as an R-tree). When B5 changes, we ask that index "which ranges contain B5?" and get the formulas back. Single-cell references stay as plain edges.

Step 3: a tiny recalculation engine

Here is the core, small enough to write in an interview. It uses Kahn's algorithm: count how many dirty inputs each dirty cell waits for (its in-degree), start with those waiting for none, and as each cell is computed, tell its dependents one fewer input is pending. Any dirty cell that never reaches zero is part of a cycle, or depends on one.

function recalc(changed: string[]): string[] {
  const dirty = new Set<string>();
  const stack = [...changed];
  while (stack.length) {                          // 1. mark everything downstream
    const c = stack.pop()!;
    for (const d of dependents.get(c) ?? []) {
      if (!dirty.has(d)) { dirty.add(d); stack.push(d); }
    }
  }
  const waiting = new Map<string, number>();
  for (const c of dirty) {
    waiting.set(c, formulas.get(c)!.deps.filter((d) => dirty.has(d)).length);
  }
  const ready = [...dirty].filter((c) => waiting.get(c) === 0);
  const done = new Set<string>();
  while (ready.length) {                          // 2. Kahn's algorithm
    const c = ready.shift()!;
    values.set(c, formulas.get(c)!.fn((ref) => values.get(ref) ?? 0));
    done.add(c);
    for (const d of dependents.get(c) ?? []) {
      if (!dirty.has(d)) continue;
      waiting.set(d, waiting.get(d)! - 1);
      if (waiting.get(d) === 0) ready.push(d);
    }
  }
  for (const c of dirty) if (!done.has(c)) values.set(c, "#CYCLE!"); // 3. never ready
  return [...done];
}

// setFormula(cell, deps, fn) stores the formula, adds the dependents edges, and computes it once.
test("diamond: each cell once, D1 last", () => {
  values.set("A1", 2);
  setFormula("B1", ["A1"], (g) => g("A1") * 10);
  setFormula("C1", ["A1"], (g) => g("A1") + 1);
  setFormula("D1", ["B1", "C1"], (g) => g("B1") + g("C1"));
  values.set("A1", 5);
  const order = recalc(["A1"]);
  assert.equal(order.length, 3);
  assert.equal(order.at(-1), "D1");
  assert.equal(values.get("D1"), 56);            // 50 + 6
});

test("cycle: E1 and F1, and G1 downstream, are flagged", () => {
  setFormula("E1", ["F1", "A1"], (g) => g("F1") + g("A1"));
  setFormula("F1", ["E1"], (g) => g("E1") + 1);
  setFormula("G1", ["E1"], (g) => g("E1") * 2);
  recalc(["A1"]);
  assert.equal(values.get("E1"), "#CYCLE!");
  assert.equal(values.get("G1"), "#CYCLE!");
  assert.equal(values.get("D1"), 56);            // unaffected by the cycle
});

Change the inputs in the lab below and watch the order of evaluation. Then close a loop and see which cells the engine refuses to compute.

Circular references, volatile functions, and dynamic references

• Circular references. Google Sheets shows a #REF! error with the message "Circular dependency detected"; Excel shows a warning. Both offer an opt-in iterative calculation setting that re-runs the cycle up to a maximum number of times or until values change by less than a threshold. Our engine supports that by evaluating the leftover cells in a bounded loop when the setting is on.
• Volatile functions. NOW(), TODAY(), RAND(), and RANDBETWEEN() have no cell inputs but change anyway. The engine keeps a set of volatile cells and marks them dirty on every recalculation. Spreadsheets also let editors control timing: Google Sheets has offered recalculating every minute or every hour on top of every change, which our engine handles with a timer that posts a tick message. Since September 2026 it also has a manual mode that waits until the user asks, which for us means holding the dirty set until then.
• Dynamic references. INDIRECT("B" & A1) builds a reference from text, so its inputs are only known after it runs. The safe choice is to treat such cells as volatile; Excel does the same for INDIRECT and OFFSET. A finer option records the inputs found during each run and updates the graph.
• Errors propagate. #DIV/0!, #VALUE!, #REF!, #NAME?, and #N/A are values, not exceptions. A formula that reads an error returns that error unless it handles it, as IFERROR does.

Where the engine runs: main thread, worker, or server

Our engine is correct, but correct is not enough. A user pastes 50,000 rows of prices into a column that 200,000 formulas read. Recalculation takes 2 seconds.

If it runs on the main thread, the page is frozen for those 2 seconds: no scrolling, no typing, the cursor stops blinking. The browser architecture lesson explains why: the main thread runs JavaScript, style, layout, and input handling one task at a time, so one long task blocks everything else.

Moving the engine doesn't make recalculation faster; it makes it harmless. The worker still needs 2 seconds for that paste, but during those 2 seconds the main thread keeps drawing frames and taking key presses. The cost is a second copy of the data and a message protocol between the two sides, which is the next thing to design.

The protocol between the UI and the worker

// main thread -> worker
type ToEngine =
  | { type: "load"; sheetId: string; chunk: ArrayBuffer }        // transferred, not copied
  | { type: "apply"; ops: Op[]; version: number }                // local or remote edits
  | { type: "viewport"; sheetId: string; rows: [number, number]; cols: [number, number] }
  | { type: "tick" };                                            // re-run volatile cells

// worker -> main thread
type FromEngine =
  | { type: "values"; version: number; cells: [row: number, col: number, value: CellValue][]; final: boolean }
  | { type: "error"; version: number; message: string };

• Small diffs, not snapshots. Structured clone copies every object, so sending a whole sheet would cost tens of milliseconds. Send only the changed cells. For loading, pass ArrayBuffer chunks in the transfer list, which moves them without copying.
• Viewport first. The worker knows the visible window and evaluates dirty cells there first, then posts final: false. It finishes the rest in the background and posts final: true.
• Versions. Every batch of edits has a version number. If the user types again while a long recalculation runs, the worker can stop early, apply the new edit, and continue; the UI ignores results for older versions.
• Stale markers. While a cell's new value is pending, the UI keeps showing the old one. For long runs (over about 500 ms), a small progress bar tells the user the sheet is still calculating.

The data model: sparse cells, chunks, and formats

The naive data model is a 2D array: cells[row][col]. For our heavy sheet that is 5 million slots, most of them empty, because real sheets are sparse: a column of 100,000 dates next to mostly blank space. Worse, a JavaScript object per cell costs tens of bytes before it holds any data. At 20 million cells, objects alone would use more memory than a browser tab can comfortably hold.

Three ideas fix this. Store only cells that have something in them (sparse storage). Group cells into chunks of rows that load, cache, and unload together. And separate what a cell is (its raw input), what it shows (its computed value), and how it looks (its format), because each changes for different reasons and lives in different places.

type CellValue = number | string | boolean | { error: "#DIV/0!" | "#REF!" | "#VALUE!" | "#NAME?" | "#N/A" };

type Cell = {
  input?: string;      // what the user typed: "=SUM(B2:B9)", "42", "'007"
  value: CellValue;    // computed by the engine; what the renderer shows
  styleId: number;     // index into the shared style table (0 = default)
};

type Style = { numberFormat?: string; bold?: boolean; fill?: string; align?: "left" | "center" | "right"; wrap?: boolean };

type Sheet = {
  id: string; name: string; index: number;
  rowCount: number; colCount: number;
  frozen: { rows: number; cols: number };
  rowSizes: Map<number, number>;  colSizes: Map<number, number>;  // only non-default sizes
  merges: Range[];
  chunks: Map<number, Chunk | "loading">;   // chunk index -> data, loaded on demand
};

type Workbook = { id: string; title: string; version: number; sheets: Sheet[]; styles: Style[]; locale: string; timeZone: string };

Where each piece of data lives

Chunks far from the viewport are evicted from memory with a least-recently-used rule once the store passes a budget (say 200 MB), because a long session that scrolls through a big file would otherwise keep every chunk forever. The memory leaks lesson covers the other usual suspects in a long-lived app like this one: listeners left on removed overlays, and caches with no limit, such as the text measurement cache.

Architecture: the pieces and how data flows

We now have enough parts to draw the whole client. The guiding rule: the main thread only does what must happen within a frame (input, drawing, the few DOM overlays), and everything heavy or slow (formulas, parsing large files, the network) happens somewhere else and reports back with small messages.

Two boundaries deserve a sentence each in the interview. The command layer is the single door for changes: the keyboard, the toolbar, paste, fill, and remote edits all become the same Op objects. That's what makes undo, collaboration, and recalculation consistent, because they all speak one language.

The grid view stays outside React's render cycle: React draws the toolbar and panels, but the canvas is drawn by a plain loop subscribed to the stores. A React re-render on every scroll frame or arrow key would break the 8 ms budget.

Data flow, end to end

Follow one edit around the loop. The user types 120 into B5 and presses Enter. The command layer creates setCell(sheet1, B5, "120"), applies it to the cell store (the grid repaints B5 next frame), pushes its inverse on the undo stack, sends it to the worker, and adds it to the outbox.

The worker recalculates and posts changed values, which update the cell store and repaint again. The sync client sends the outbox over the WebSocket; the server assigns the operation a version number, stores it, and broadcasts it to other editors, whose sync clients apply it through their own command layer.

Opening a file flows the other way: the shell loads, the loader fetches the workbook metadata and the chunks around the saved position, the cell store fills, and the grid paints computed values that the server stored. The worker loads formulas in the background and takes over recalculation once it is ready. The user can scroll and read long before the engine has finished loading.

Copy, paste, and undo

Copy and paste is how data gets into spreadsheets. Users paste from Excel, from web pages, from other sheets, and from your own app. Undo is how they recover when that goes wrong. Both are easy to build badly and hard to build well.

Copy: write several formats at once

On copy, we handle the copy event (fired for Ctrl+C and the browser's Edit menu) and call event.clipboardData.setData for two formats. Each target app picks the richest format it understands:

• text/plain as TSV (tab-separated values): cells joined by tabs, rows by newlines. A value that contains a tab, newline, or quote is wrapped in double quotes, with inner quotes doubled, the same way Excel writes it. Every text editor and spreadsheet can read this.
• text/html as a table, with formats (bold, colors, number formats) as inline styles. Excel, Google Docs, and email clients keep the formatting.
• Our own app also needs the formulas, not just the values. Spreadsheets commonly embed extra metadata in the copied HTML, so a paste back into the app can restore formulas and adjust them. Google Sheets' copied HTML, for example, has been seen to carry attributes such as data-sheets-value and data-sheets-formula on its cells; that format is undocumented and can change, so we design our own (a data attribute holding the source formulas and the source address).

Paste: read the richest format, safely

On paste, read text/html first, and if it contains our metadata, use the formulas. If it is a table from another app, parse it with DOMParser (which builds a document without running scripts or loading images) and read only cell text and a few safe styles. Never insert pasted HTML into the page. If there is no HTML, parse text/plain as TSV.

Large pastes (say 50,000 rows) must not freeze the grid. TSV text can be parsed in the worker. DOMParser doesn't exist in workers, so an HTML table is parsed on the main thread, and its cell text is pulled out in slices that yield between them. Either way, the result is applied as one operation, so one undo removes the whole paste.

Relative references move with the paste

Copy B2 containing =A1*2 and paste it into D5. The paste moved 3 rows down and 2 columns right, so every relative reference moves by the same amount: the result is =C4*2. Absolute parts stay: =$A$1*2 stays =$A$1*2, and the mixed =$A1*2 becomes =$A4*2.

If a reference would land off the sheet (pasting =A1 one row up from row 1), it becomes #REF!. Because we store references as offsets from the host cell (the formula engine chapter), copying a formula is just copying its relative form; nothing is rewritten until we print it as text.

Cut and paste is different from copy and paste. A cut moves cells, and formulas elsewhere that pointed at the moved cells follow them: if E1 = B2 and you cut B2 to D5, E1 becomes =D5. A copy leaves other formulas alone. Interviewers like this distinction because it shows you understand references as links, not text.

Undo and redo

Collaboration: merging edits cell by cell

The Google Docs guide covers the collaboration infrastructure: the WebSocket, the server that orders operations, and the client that keeps a queue of pending edits. A spreadsheet reuses all of that. What changes is the shape of the conflicts, and they are in some ways easier and in some ways harder than in text.

Easier: most edits touch different cells, and two edits to different cells never conflict. Two people filling in different rows of a budget can just both apply.

Harder: structure changes (insert or delete rows and columns, sort, move) shift the address of every cell below or to the right, and every formula pointing there. If Ana inserts a row above row 10 at the same moment Ben types into A12, Ben's edit was meant for the row that is now row 13. Applied blindly, it lands in the wrong row on Ana's screen.

type Op =
  | { type: "setCell"; sheet: string; row: number; col: number; input: string }
  | { type: "insertRows"; sheet: string; at: number; count: number }
  | { type: "deleteRows"; sheet: string; at: number; count: number };

function transformCell(op: Extract<Op, { type: "setCell" }>, prior: Op): Op | null {
  if (prior.sheet !== op.sheet || prior.type === "setCell") return op;
  if (prior.type === "insertRows") {
    return op.row >= prior.at ? { ...op, row: op.row + prior.count } : op;
  }
  // deleteRows
  if (op.row < prior.at) return op;
  if (op.row < prior.at + prior.count) return null; // the row is gone: drop the edit
  return { ...op, row: op.row - prior.count };
}

Formulas need the same shift. When an insertRows arrives, every client's engine rewrites references at or below the insert point (=SUM(B2:B20) with a row inserted at 10 becomes =SUM(B2:B21)), and a delete turns references to removed cells into #REF!. Because each client applies the same operations in the same server order with the same code, all formulas end up identical without sending them.

Dropped connections and the outbox

Every local operation sits in the outbox (memory plus IndexedDB) until the server acknowledges it with a version number. When the connection drops, the user keeps editing; operations pile up. On reconnect, the client says "I have everything up to version 4,180". The server sends the operations it missed, the client transforms its outbox against them, and resends.

If the gap is huge (thousands of operations, or someone restructured the sheet heavily), it is faster and safer to fetch a fresh snapshot of the affected chunks and rebase the outbox onto that. If an outbox edit was dropped by a transform (its row was deleted), tell the user rather than losing it silently.

The live protocol over the WebSocket

Live traffic travels on one WebSocket: our operations going up, everyone's operations and presence coming down. The choosing real-time lesson weighs the alternatives. For two-way, frequent, low-latency messages a WebSocket is the standard choice, and Server-Sent Events plus POST would work as a fallback. These are the messages, including the hello that starts the reconnect above.

// client -> server
type Up =
  | { type: "hello"; workbookId: string; lastVersion: number }              // on connect and reconnect
  | { type: "ops"; clientSeq: number; baseVersion: number; ops: Op[] }      // one user action, maybe many ops
  | { type: "presence"; sheetId: string; ranges: Range[]; editing?: CellRef };

// server -> client
type Down =
  | { type: "ack"; clientSeq: number; version: number }                     // your ops are now this version
  | { type: "ops"; version: number; author: string; ops: Op[] }             // someone else's edit, in order
  | { type: "presence"; userId: string; name: string; color: string; sheetId: string; ranges: Range[] }
  | { type: "reject"; clientSeq: number; code: "FORBIDDEN" | "PROTECTED_RANGE" | "INVALID" }
  | { type: "resync"; reason: "TOO_FAR_BEHIND" };                           // fetch fresh chunks

• baseVersion tells the server which version the client had seen when it made the edit. The server transforms the ops against anything newer before assigning the next version.
• clientSeq makes retries safe: if the client resends after a reconnect, the server recognizes an already-applied sequence number and just acks it again.
• reject triggers the rollback path from the editing chapter: undo that one action locally and show why.
• Batching. A paste of 50,000 cells is one ops message with a compact range op (setRange with a 2D array) rather than 50,000 setCell ops. Keep a size limit (say 1 MB per message) and split above it.

Presence: seeing other people's selections

Each editor's selection is drawn as a colored box with their name, on the selection canvas layer. Following the presence and cursors lesson, presence travels on the same socket but is not stored: send the active range at most about 10 times per second, drop old updates, and expire a user after a few seconds of silence.

Presence positions also go through transformCell-style shifts when rows are inserted, or the colored box would jump to the wrong row. When someone is editing a cell, show their color on it so others know not to type there; it doesn't lock the cell, it just prevents most same-cell conflicts socially.

Opening large files fast, and the loading API

The naive load fetches the whole workbook as one JSON document, parses it, builds the engine, and then shows the grid. For a 50 MB file that means downloading 50 MB, then JSON.parse blocking the main thread for a second or more, then building millions of objects, then calculating. The user stares at a spinner for 10 seconds to see the 600 cells that fit on the screen.

The goal is the opposite order: show the first screen of values quickly, let the user scroll and read, and load everything else behind it in priority order.

Three facts make that possible. The server stores computed values next to inputs, so we can paint without calculating. Chunks let us fetch only the rows the user is looking at. And other sheets are not visible at all until their tab is clicked.

Streaming and decoding chunks

• Stream, don't wait. Read response.body as a stream and decode one chunk record at a time (for example newline-delimited records). Cells appear as each record arrives, instead of after the last byte.
• Decode in the worker. Parsing 10 MB of records is real CPU work. The worker decodes, keeps formulas for itself, and transfers compact display data (typed arrays) to the main thread.
• Compress on the wire. Spreadsheet data repeats a lot (formats, similar formulas, numbers), so Content-Encoding: br or gzip often shrinks it several times.
• Cache with versions. Each cached chunk in IndexedDB carries the document version it reflects. On reopen, paint from the cache immediately, then ask the server for operations since that version. A file you opened yesterday opens almost instantly.
• Placeholders, not blanks. Cells in a chunk that is still loading draw as light gray bars, so the user can tell "loading" from "empty". An empty cell and an unloaded cell must never look the same in a spreadsheet.

Lazy loading other sheets needs one exception: cross-sheet formulas. If Summary!B2 = SUM(Data!C:C), the engine needs the Data sheet's values to recalculate B2, even if the user never opens that tab. The worker loads the inputs of formulas in the background, at low priority, separately from the display chunks. Until then, the server-computed value of B2 is shown and stays correct, because nothing it depends on has changed locally.

The loading API over HTTP

The client talks to the server over two channels. The WebSocket from the collaboration chapter carries the live traffic. Plain HTTP requests load data, because they are cacheable, easy to retry, and can stream. Loading needs two endpoints: metadata, then chunks.

GET /api/workbooks/wb_81f/meta
200 OK
{ "id": "wb_81f", "title": "FY26 budget", "version": 4180, "locale": "de_DE", "timeZone": "Europe/Berlin",
  "sheets": [{ "id": "s2", "name": "Data", "rowCount": 100000, "colCount": 50, "chunkRows": 1000,
               "frozen": { "rows": 1, "cols": 1 }, "rowSizes": { "0": 32 }, "colSizes": { "1": 180 } }],
  "styles": [{}, { "bold": true }, { "numberFormat": "#,##0.00" }, { "numberFormat": "yyyy-mm-dd" }] }

GET /api/workbooks/wb_81f/sheets/s2/chunks/52?version=4180
200 OK
Content-Type: application/x-ndjson
Content-Encoding: br
Cache-Control: private, max-age=31536000, immutable
{"row":52000,"cells":[[0,"2025-01-01",45658,3],[1,"=C52001*1.19",1190,2],[2,"1000",1000,2]]}
{"row":52001,"cells":[[0,"2025-01-02",45659,3]]}

Each cell in a chunk is a compact array: column, raw input, computed value, style id. Rows here count from 0, so the record for row 52000 is what users see as row 52001.

Tying a chunk URL to a version makes it immutable, so the browser and IndexedDB can cache it forever: a newer version is a different URL. If a chunk is requested for a version the server has compacted away, it answers 410 Gone and the client fetches the latest version of that chunk plus the operations since.

Numbers, dates, and locales

A spreadsheet is a numbers tool used in every country. The rule that keeps it correct: store raw values, format at the edge. A cell stores the number 1234.5; the format #,##0.00 and the locale decide whether the screen shows 1,234.50 (US English), 1.234,50 (German), or 1 234,50 (French, with a narrow space). The locale formatting lesson covers the Intl APIs in depth; here are the spreadsheet-specific parts.

• Dates are numbers. Spreadsheets store a date as a serial number of days, with the time as the fraction: Google Sheets counts from December 30, 1899 as day 0, so 45658 is January 1, 2025, and Excel's default date system gives the same serial for any date after early 1900. That's why =B2+7 adds a week and why you can sort dates as numbers. Formatting turns the serial into text with the file's time zone.
• Locale and time zone belong to the file. Google Sheets has a spreadsheet setting for both, so everyone sees the same dates and NOW() agrees on what "today" is. The viewer's browser locale only affects the menus.
• Parsing input uses the file's locale. Typing 1/2 means January 2 in a US file and February 1 in a UK file; 1,5 is 1.5 in a German file. The parser has to know which.
• Formulas are stored in one canonical form. In locales where the comma is the decimal separator, users write function arguments with semicolons: =SUM(A1;B1) and =ROUND(2,5;0). Store the formula once, in a canonical form with . decimals and , separators, and convert to and from the user's locale only in the editor and formula bar. Otherwise a formula typed in Berlin would break when opened in Boston.
• Cache formatters. Creating an Intl.NumberFormat or Intl.DateTimeFormat is much slower than calling format on one. The renderer keeps one formatter per style and locale and reuses it for every cell in every frame.

Number format patterns such as #,##0.00, 0.0%, $#,##0;($#,##0) (negatives in parentheses), and yyyy-mm-dd are a small language of their own. Parse each pattern once into a tiny formatter object and cache it with the style. Intl handles the locale-specific separators and digit grouping, and the pattern decides the decimals, the percent scaling, and the negative style.

Accessibility and performance budgets

Choosing canvas gave us speed and took away the accessibility the DOM gives by default. A screen reader sees one canvas element with nothing inside. We have to give it back on purpose, and the data table guide's ARIA grid is our starting point: here it becomes a mirror of the canvas, not the thing that draws.

An accessible DOM mirror

• A hidden ARIA grid. Next to the canvas, render a visually hidden element with role="grid" containing only the rows and columns around the active cell (say 20 by 10). aria-rowcount and aria-colcount give the full sheet size, and aria-rowindex and aria-colindex on each row and cell give their real positions, so a screen reader says "row 52,001 of 100,000" even though only 20 rows exist in the DOM.
• Cells carry the useful text. Each gridcell has the formatted value, and its accessible description includes the formula when there is one. Column headers come from the A, B, C strip, or from the frozen header row when the user marked one.
• Focus follows the active cell. The APG grid pattern either moves DOM focus to the active cell, or keeps focus on the grid container and points aria-activedescendant at the active cell. ARIA only allows that pointer to a descendant of the focused element, so our hidden textarea can't use it for cells outside it. A practical compromise: while a screen reader is in use, focus the mirrored active cell while navigating, and move focus into the editor when editing starts (Enter, F2, or a typed character). A polite aria-live region announces things the grid can't express, like "Selected B2 to D9, 24 cells" or "Sum: 1,190".
• An opt-in mode. Google Sheets has "Turn on screen reader support" and "Turn on braille support" options in its accessibility settings, and Google's help suggests turning braille support off when it isn't needed, because it can slow Sheets down. Building the full mirror only when such a mode is on saves work for everyone else, though the basics (labels, focus, keyboard) should always be on.
• Forced colors and zoom. Canvas ignores the operating system's high contrast mode, so check matchMedia("(forced-colors: active)") and switch to system colors yourself. In most browsers, page zoom changes devicePixelRatio; redraw at the new ratio so text stays sharp.

The keyboard map from the selection chapter is the other half: every action reachable with the mouse (insert row, resize, fill) must also have a key path, often through the menus, which are normal DOM and follow the ARIA menu patterns.

Performance budgets

A budget turns "fast" into numbers we can test and monitor. The runtime performance lesson explains how frames, long tasks, and INP are measured; these are the spreadsheet's numbers.

Measure these in the field, not just on a fast developer machine. Canvas content doesn't count as a Largest Contentful Paint candidate, so add a custom mark when the first chunk is painted (performance.mark("first-cells")). Report INP through PerformanceObserver with the Event Timing API, long frames through the Long Animation Frames API in Chromium, and a custom timer from commit to recalculated values. Slice them by file size, because a budget that holds for 1,000 rows and fails for 100,000 is the bug you most need to see.

Running it in the interview

You can't say all of this in 60 minutes, and you shouldn't try. The interviewer is checking whether you find the hard parts, make decisions with reasons, and go deep where it matters. Here is a plan that fits the time.

Draw the architecture before diving into any part, so every later detail has a place on the board. When you reach a decision, say the options and the trade-off in one or two sentences each, then commit. "DOM would be simpler and more accessible, but at 1,000 visible cells per frame the style and layout cost breaks 60 fps, so I'd draw cells on canvas and keep DOM for the editor and an accessible mirror" is a strong sentence.

Follow-up questions to expect