Build a mini spreadsheet like Google Sheets with support for rich cell formatting, formulas, and live evaluation.

Design Google Sheets

Build a mini spreadsheet like Google Sheets with support for rich cell formatting, formulas, and live evaluation.

Problem Statement

Design a basic spreadsheet editor similar to Google Sheets with the following capabilities:

Editable grid of cells with dynamic sizing
Rich formatting (bold, italic, underline, font size, colors)
Cell formulas referencing other cells (e.g., =A1 + B2)
Circular dependency detection to prevent infinite loops
Live formula evaluation with real-time updates
Undo/Redo functionality for user actions
Data persistence and export capabilities

Clarifying Questions

Functional Requirements

Grid Size: What's the default number of rows/columns? Should it be expandable?
Formula Support: Should it support only basic math or advanced functions like SUM, AVG, COUNT, etc.?
UI Features: Will there be a formatting toolbar? Should it support cell merging?
Persistence: Is saving required? Should it support multiple file formats?
Reactivity: Should cell updates be instant across references?
Collaboration: Do multiple users need to edit simultaneously?

Technical Constraints

Performance: How many cells should it handle efficiently?
Browser Support: What browsers need to be supported?
Mobile Support: Should it work on touch devices?
Offline Support: Should it work without internet connection?

High-Level Architecture

1. Data Model

interface CellStyle {
  bold: boolean;
  italic: boolean;
  underline: boolean;
  fontSize: number;
  textColor: string;
  backgroundColor: string;
  textAlign: 'left' | 'center' | 'right';
  verticalAlign: 'top' | 'middle' | 'bottom';
}

interface Cell {
  id: string; // e.g., "A1", "B2"
  value: string; // Display value
  formula?: string; // Raw formula (e.g., "=A1+B2")
  computedValue?: string; // Calculated result
  style: CellStyle;
  isEditing: boolean;
}

interface Spreadsheet {
  id: string;
  name: string;
  grid: Cell[][];
  activeCell?: string;
  selection?: {
    start: string;
    end: string;
  };
}

2. Core Components

Grid Component

Renders the spreadsheet grid
Handles cell selection and navigation
Manages viewport and scrolling
Implements virtual scrolling for large grids

Cell Component

Individual cell rendering
Handles cell editing and formatting
Manages focus and keyboard events
Displays computed values vs raw formulas

Formatting controls (bold, italic, etc.)
Formula bar for editing
Undo/Redo buttons
Save/Export options

Formula Evaluation System

Core Logic

The formula evaluation system is the heart of the spreadsheet. Here's how it works:

interface FormulaContext {
  getCellValue: (cellId: string) => number;
  visited: Set<string>;
  maxDepth: number;
}

const evaluateFormula = (
  formula: string,
  context: FormulaContext,
  depth: number = 0
): number => {
  // Prevent infinite recursion
  if (depth > context.maxDepth) {
    throw new Error("Maximum formula depth exceeded");
  }

  try {
    // Replace cell references with their values
    const replaced = formula.replace(/[A-Z]+\d+/g, (match) => {
      // Check for circular dependencies
      if (context.visited.has(match)) {
        throw new Error(`Circular dependency detected: ${match}`);
      }

      context.visited.add(match);
      const cellValue = context.getCellValue(match);
      context.visited.delete(match);
      
      return cellValue.toString();
    });

    // Safely evaluate the formula
    return evaluateExpression(replaced);
  } catch (error) {
    console.error(`Formula evaluation error: ${error.message}`);
    return NaN;
  }
};

const evaluateExpression = (expression: string): number => {
  // Use Function constructor for safer evaluation
  // This is more secure than eval() but still be careful
  try {
    return new Function(`return ${expression}`)();
  } catch (error) {
    throw new Error(`Invalid expression: ${expression}`);
  }
};

Advanced Functions

const builtInFunctions = {
  SUM: (args: number[]) => args.reduce((sum, val) => sum + val, 0),
  AVERAGE: (args: number[]) => args.reduce((sum, val) => sum + val, 0) / args.length,
  COUNT: (args: any[]) => args.filter(arg => arg !== null && arg !== undefined).length,
  MAX: (args: number[]) => Math.max(...args),
  MIN: (args: number[]) => Math.min(...args),
  IF: (condition: boolean, trueValue: any, falseValue: any) => 
    condition ? trueValue : falseValue,
};

const evaluateFunction = (functionName: string, args: any[]): number => {
  const func = builtInFunctions[functionName.toUpperCase()];
  if (!func) {
    throw new Error(`Unknown function: ${functionName}`);
  }
  return func(args);
};

Styling and Formatting

Cell Styling System

const applyStyle = (cell: Cell, styleProperty: keyof CellStyle, value: any): Cell => {
  return {
    ...cell,
    style: {
      ...cell.style,
      [styleProperty]: value,
    },
  };
};

const getCellStyle = (cell: Cell): React.CSSProperties => {
  return {
    fontWeight: cell.style.bold ? 'bold' : 'normal',
    fontStyle: cell.style.italic ? 'italic' : 'normal',
    textDecoration: cell.style.underline ? 'underline' : 'none',
    fontSize: `${cell.style.fontSize}px`,
    color: cell.style.textColor,
    backgroundColor: cell.style.backgroundColor,
    textAlign: cell.style.textAlign,
    verticalAlign: cell.style.verticalAlign,
    border: '1px solid #ccc',
    padding: '4px 8px',
    minWidth: '80px',
    minHeight: '30px',
    outline: 'none',
  };
};

const FormattingToolbar: React.FC<{
  selectedCell: Cell | null;
  onStyleChange: (property: keyof CellStyle, value: any) => void;
}> = ({ selectedCell, onStyleChange }) => {
  return (
    <div className="formatting-toolbar">
      <button
        onClick={() => onStyleChange('bold', !selectedCell?.style.bold)}
        className={selectedCell?.style.bold ? 'active' : ''}
      >
        B
      </button>
      <button
        onClick={() => onStyleChange('italic', !selectedCell?.style.italic)}
        className={selectedCell?.style.italic ? 'active' : ''}
      >
        I
      </button>
      <button
        onClick={() => onStyleChange('underline', !selectedCell?.style.underline)}
        className={selectedCell?.style.underline ? 'active' : ''}
      >
        U
      </button>
      
      <select
        value={selectedCell?.style.fontSize || 14}
        onChange={(e) => onStyleChange('fontSize', parseInt(e.target.value))}
      >
        <option value={12}>12px</option>
        <option value={14}>14px</option>
        <option value={16}>16px</option>
        <option value={18}>18px</option>
        <option value={20}>20px</option>
      </select>
      
      <input
        type="color"
        value={selectedCell?.style.textColor || '#000000'}
        onChange={(e) => onStyleChange('textColor', e.target.value)}
        title="Text Color"
      />
      
      <input
        type="color"
        value={selectedCell?.style.backgroundColor || '#ffffff'}
        onChange={(e) => onStyleChange('backgroundColor', e.target.value)}
        title="Background Color"
      />
    </div>
  );
};

State Management and Reactivity

Grid State Management

const useSpreadsheet = (initialGrid: Cell[][] = []) => {
  const [grid, setGrid] = useState<Cell[][]>(initialGrid);
  const [activeCell, setActiveCell] = useState<string | null>(null);
  const [history, setHistory] = useState<SpreadsheetState[]>([]);
  const [historyIndex, setHistoryIndex] = useState(-1);

  const updateCell = useCallback((cellId: string, updates: Partial<Cell>) => {
    setGrid(prevGrid => {
      const [col, row] = parseCellId(cellId);
      const newGrid = prevGrid.map(row => [...row]);
      
      newGrid[row][col] = {
        ...newGrid[row][col],
        ...updates,
      };
      
      return newGrid;
    });
  }, []);

  const recalculateFormulas = useCallback(() => {
    setGrid(prevGrid => {
      return prevGrid.map(row =>
        row.map(cell => {
          if (cell.formula) {
            try {
              const result = evaluateFormula(
                cell.formula,
                {
                  getCellValue: (cellId) => getCellNumericValue(cellId, prevGrid),
                  visited: new Set(),
                  maxDepth: 100,
                }
              );
              return {
                ...cell,
                computedValue: isNaN(result) ? '#ERROR!' : result.toString(),
              };
            } catch (error) {
              return {
                ...cell,
                computedValue: '#ERROR!',
              };
            }
          }
          return cell;
        })
      );
    });
  }, []);

  // Recalculate formulas when grid changes
  useEffect(() => {
    recalculateFormulas();
  }, [grid, recalculateFormulas]);

  return {
    grid,
    activeCell,
    updateCell,
    setActiveCell,
    undo: () => {/* Implementation */},
    redo: () => {/* Implementation */},
  };
};

Complete Implementation

import React, { useState, useEffect, useRef, useCallback } from "react";

interface CellStyle {
  bold: boolean;
  italic: boolean;
  underline: boolean;
  fontSize: number;
  textColor: string;
  backgroundColor: string;
  textAlign: 'left' | 'center' | 'right';
  verticalAlign: 'top' | 'middle' | 'bottom';
}

interface Cell {
  id: string;
  value: string;
  formula?: string;
  computedValue?: string;
  style: CellStyle;
  isEditing: boolean;
}

const defaultStyle: CellStyle = {
  bold: false,
  italic: false,
  underline: false,
  fontSize: 14,
  textColor: "#000000",
  backgroundColor: "#ffffff",
  textAlign: 'left',
  verticalAlign: 'middle',
};

const parseCellId = (cellId: string): [number, number] => {
  const match = cellId.match(/^([A-Z]+)(\d+)$/);
  if (!match) throw new Error(`Invalid cell ID: ${cellId}`);
  
  const col = match[1].split('').reduce((acc, char) => 
    acc * 26 + char.charCodeAt(0) - 64, 0) - 1;
  const row = parseInt(match[2], 10) - 1;
  
  return [col, row];
};

const getCellId = (col: number, row: number): string => {
  const colStr = String.fromCharCode(65 + col);
  return `${colStr}${row + 1}`;
};

const getCellNumericValue = (cellId: string, grid: Cell[][]): number => {
  const [col, row] = parseCellId(cellId);
  const cell = grid[row]?.[col];
  if (!cell) return 0;
  
  const value = cell.computedValue || cell.value;
  const num = parseFloat(value);
  return isNaN(num) ? 0 : num;
};

const evaluateFormula = (
  formula: string,
  getCellValue: (cellId: string) => number
): number => {
  try {
    const sanitizedFormula = formula.replace(/[A-Z]+\d+/g, (match) => {
      return getCellValue(match).toString();
    });
    
    // Use Function constructor for safer evaluation
    return new Function(`return ${sanitizedFormula}`)();
  } catch (error) {
    console.error('Formula evaluation error:', error);
    return NaN;
  }
};

const CellComponent: React.FC<{
  cell: Cell;
  isActive: boolean;
  onUpdate: (updates: Partial<Cell>) => void;
  onActivate: () => void;
}> = ({ cell, isActive, onUpdate, onActivate }) => {
  const [isEditing, setIsEditing] = useState(false);
  const [editValue, setEditValue] = useState(cell.value);
  const inputRef = useRef<HTMLInputElement>(null);

  const handleDoubleClick = () => {
    setIsEditing(true);
    setEditValue(cell.formula || cell.value);
    setTimeout(() => inputRef.current?.focus(), 0);
  };

  const handleBlur = () => {
    setIsEditing(false);
    if (editValue !== cell.value) {
      if (editValue.startsWith('=')) {
        onUpdate({ value: editValue, formula: editValue.slice(1) });
      } else {
        onUpdate({ value: editValue, formula: undefined });
      }
    }
  };

  const handleKeyDown = (e: React.KeyboardEvent) => {
    if (e.key === 'Enter') {
      handleBlur();
    } else if (e.key === 'Escape') {
      setIsEditing(false);
      setEditValue(cell.value);
    }
  };

  const displayValue = cell.formula ? cell.computedValue || '#ERROR!' : cell.value;

  return (
    <div
      className={`cell ${isActive ? 'active' : ''}`}
      onClick={onActivate}
      onDoubleClick={handleDoubleClick}
      style={{
        ...getCellStyle(cell),
        border: isActive ? '2px solid #007acc' : '1px solid #ccc',
      }}
    >
      {isEditing ? (
        <input
          ref={inputRef}
          value={editValue}
          onChange={(e) => setEditValue(e.target.value)}
          onBlur={handleBlur}
          onKeyDown={handleKeyDown}
          style={{
            width: '100%',
            height: '100%',
            border: 'none',
            outline: 'none',
            background: 'transparent',
            fontSize: cell.style.fontSize,
            fontWeight: cell.style.bold ? 'bold' : 'normal',
            fontStyle: cell.style.italic ? 'italic' : 'normal',
            textDecoration: cell.style.underline ? 'underline' : 'none',
            color: cell.style.textColor,
          }}
        />
      ) : (
        <span>{displayValue}</span>
      )}
    </div>
  );
};

const SpreadsheetApp: React.FC = () => {
  const [grid, setGrid] = useState<Cell[][]>(
    Array.from({ length: 10 }, (_, row) =>
      Array.from({ length: 10 }, (_, col) => ({
        id: getCellId(col, row),
        value: "",
        style: { ...defaultStyle },
        isEditing: false,
      }))
    )
  );

  const [selectedCell, setSelectedCell] = useState<string | null>(null);

  const getCellValue = useCallback((cellId: string): number => {
    const [col, row] = parseCellId(cellId);
    const cell = grid[row]?.[col];
    if (!cell) return 0;
    
    const value = cell.computedValue || cell.value;
    const num = parseFloat(value);
    return isNaN(num) ? 0 : num;
  }, [grid]);

  const updateCell = useCallback((cellId: string, updates: Partial<Cell>) => {
    setGrid(prevGrid => {
      const [col, row] = parseCellId(cellId);
      const newGrid = prevGrid.map(row => [...row]);
      
      newGrid[row][col] = {
        ...newGrid[row][col],
        ...updates,
      };
      
      return newGrid;
    });
  }, []);

  const applyStyle = useCallback((property: keyof CellStyle, value: any) => {
    if (!selectedCell) return;
    
    updateCell(selectedCell, {
      style: {
        ...grid[parseCellId(selectedCell)[1]][parseCellId(selectedCell)[0]].style,
        [property]: value,
      },
    });
  }, [selectedCell, grid, updateCell]);

  // Recalculate formulas when grid changes
  useEffect(() => {
    setGrid(prevGrid => {
      return prevGrid.map(row =>
        row.map(cell => {
          if (cell.formula) {
            try {
              const result = evaluateFormula(cell.formula, getCellValue);
              return {
                ...cell,
                computedValue: isNaN(result) ? '#ERROR!' : result.toString(),
              };
            } catch (error) {
              return {
                ...cell,
                computedValue: '#ERROR!',
              };
            }
          }
          return cell;
        })
      );
    });
  }, [grid, getCellValue]);

  return (
    <div style={{ padding: "20px" }}>
      <div style={{ marginBottom: "10px" }}>
        <button onClick={() => applyStyle("bold", !grid[0]?.[0]?.style.bold)}>B</button>
        <button onClick={() => applyStyle("italic", !grid[0]?.[0]?.style.italic)}>I</button>
        <button onClick={() => applyStyle("underline", !grid[0]?.[0]?.style.underline)}>U</button>
        <input
          type="color"
          onChange={(e) => applyStyle("textColor", e.target.value)}
          title="Text Color"
        />
        <input
          type="color"
          onChange={(e) => applyStyle("backgroundColor", e.target.value)}
          title="Background Color"
        />
        <select
          onChange={(e) => applyStyle("fontSize", parseInt(e.target.value))}
        >
          <option value={12}>12px</option>
          <option value={14}>14px</option>
          <option value={16}>16px</option>
          <option value={18}>18px</option>
          <option value={20}>20px</option>
        </select>
      </div>
      
      <div
        style={{
          display: "grid",
          gridTemplateColumns: "repeat(10, auto)",
          gap: "1px",
          border: "1px solid #ccc",
        }}
      >
        {grid.map((row, rowIndex) =>
          row.map((cell, colIndex) => (
            <CellComponent
              key={cell.id}
              cell={cell}
              isActive={selectedCell === cell.id}
              onUpdate={(updates) => updateCell(cell.id, updates)}
              onActivate={() => setSelectedCell(cell.id)}
            />
          ))
        )}
      </div>
      
      <div style={{ marginTop: "20px", padding: "10px", backgroundColor: "#f5f5f5" }}>
        <strong>Instructions:</strong>
        <ul>
          <li>Click a cell to select it</li>
          <li>Double-click to edit</li>
          <li>Use formulas like =A1+B2</li>
          <li>Use the toolbar to format cells</li>
        </ul>
      </div>
    </div>
  );
};

export default SpreadsheetApp;

Best Practices

Performance Optimization

Virtual Scrolling: For large grids, implement virtual scrolling
Debounced Updates: Debounce formula recalculations
Memoization: Cache formula results to avoid redundant calculations
Lazy Loading: Load only visible cells

User Experience

Keyboard Navigation: Support arrow keys, Tab, Enter for navigation
Copy/Paste: Implement clipboard functionality
Undo/Redo: Track user actions for undo/redo
Auto-save: Save changes automatically

Error Handling

Circular Dependencies: Detect and prevent infinite loops
Invalid Formulas: Provide clear error messages
Data Validation: Validate cell inputs
Graceful Degradation: Handle edge cases gracefully

Interview Tips

What Interviewers Look For:

Formula evaluation logic and circular dependency handling
State management for complex grid data
Performance considerations for large datasets
User experience and interaction design

Sample Questions:

"How would you handle circular dependencies?"
"What if you have 10,000 cells with formulas?"
"How would you implement real-time collaboration?"
"How would you optimize rendering performance?"

Key Points to Mention:

Start with simple formulas, then add complexity
Consider edge cases (circular dependencies, invalid formulas)
Plan for scalability (virtual scrolling, memoization)
Think about user experience (keyboard shortcuts, undo/redo)

Next: Pinterest Design - Learn how to build a responsive image grid with infinite scrolling.

Design Google Sheets

On this page