Building a Spreadsheet Application in JavaScript: A Step-by-Step Guide

Spreadsheets have become an integral part of modern-day computing. They allow users to organize, manipulate, and analyze data in a tabular format. Applications like Google Sheets have set the standard for powerful, interactive spreadsheets.

In this blog post, we will walk you through the process of building a spreadsheet application using JavaScript. We'll focus on key programming concepts, explore JavaScript features, and include detailed code snippets with explanations.

The entire source code is available here in my Codepen.

What Is Google Spreadsheet?

Google Spreadsheet is a web-based application that allows users to create, edit, and collaborate on spreadsheets online. It provides features like formulas, data validation, charts, and conditional formatting.

Our project emulates some core features of Google Spreadsheet, focusing on:

1. Editable cells.
2. Formula parsing and evaluation.
3. Live updates through a Pub/Sub model.
4. Keyboard navigation and cell selection.
5. Dynamic dependency evaluation between cells.

Features of This Project

1. Editable cells: Allows users to input text or equations into cells.
2. Formula support: Processes formulas starting with = and evaluates expressions.
3. Live updates: Changes in dependent cells trigger updates using a Pub/Sub model.
4. Keyboard navigation: Enables movement between cells using arrow keys.
5. Dynamic evaluation: Ensures real-time updates for formulas dependent on other cells.
6. Error handling: Provides meaningful error messages for invalid inputs or circular dependencies.
7. Scalable design: Allows easy extension to add more rows, columns, or features.

Key Components of the Application

1. Mode Management

const Mode = {
  EDIT: 'edit',
  DEFAULT: 'default'
};

This enum defines two modes:

• EDIT: Enables editing of a selected cell.
• DEFAULT: Allows navigation and interaction without editing.

Why Use Modes? 

Modes simplify the management of the UI state. For example, in DEFAULT mode, keyboard inputs move between cells, while in EDIT mode, inputs modify cell content.

2. Pub/Sub Class

The Pub/Sub model handles subscriptions and live updates. Cells can subscribe to other cells and update dynamically when dependencies change.

class PubSub {
  constructor() {
    this.map = {};
  }

  get(source) {
    let result = [];
    let queue = [...(this.map[source] || [])];

    while (queue.length) {
      let next = queue.shift();
      result.push(next.toUpperCase());
      if (this.map[next]) queue.unshift(...this.map[next]);
    }
    return result;
  }

  subscribeAll(sources, destination) {
    sources.forEach((source) => {
      this.map[source] = this.map[source] || [];
      this.map[source].push(destination);
    });
  }
}

Key features:

• Dynamic dependency management: Tracks dependencies between cells.
• Propagation of updates: Updates dependent cells when source cells change.
• Breadth-first search: Avoids infinite loops by tracking all dependent nodes.

Example usage:

let ps = new PubSub();
ps.subscribeAll(['A1'], 'B1');
ps.subscribeAll(['B1'], 'C1');
console.log(ps.get('A1')); // Output: ['B1', 'C1']

3. Creating Rows and Cells

class Cell {
  constructor(cell, row, col) {
    cell.id = `${String.fromCharCode(col + 65)}${row}`;
    cell.setAttribute('data-eq', '');
    cell.setAttribute('data-value', '');

    if (row > 0 && col > -1) cell.classList.add('editable');
    cell.textContent = col === -1 ? row : '';
  }
}

class Row {
  constructor(row, r) {
    for (let c = -1; c < 13; c++) {
      new Cell(row.insertCell(), r, c);
    }
  }
}

Key features:

• Dynamic table generation: Allows adding rows and columns programmatically.
• Cell identification: Generates IDs based on position (e.g., A1, B2).
• Editable cells: Cells are editable only if they are valid (non-header rows/columns).

Why Use Dynamic Rows and Cells? 

This approach allows the table size to be scalable and flexible, supporting features like adding rows or columns without changing the structure.

4. Event Handling for Interaction

addEventListeners() {
  this.table.addEventListener('click', this.onCellClick.bind(this));
  this.table.addEventListener('dblclick', this.onCellDoubleClick.bind(this));
  window.addEventListener('keydown', this.onKeyDown.bind(this));
}

Key features:

• Click event: Selects or edits cells.
• Double-click event: Enables formula editing.
• Keydown event: Supports navigation with arrow keys.

5. Formula Parsing and Evaluation

function calcCell(expression) {
  if (!expression) return 0;

  return expression.split('+').reduce((sum, term) => {
    let value = isNaN(term) ? getCellValue(term) : Number(term);
    if (value === null) throw new Error(`Invalid cell: ${term}`);
    return sum + Number(value);
  }, 0);
}

Key features:

• Dynamic calculation: Computes formulas referencing other cells.
• Recursive evaluation: Resolves nested dependencies.
• Error handling: Identifies invalid references and circular dependencies.

6. Error Handling for User Input

function isValidCell(str) {
  let regex = /^[A-Z]{1}[0-9]+$/;
  return regex.test(str);
}

Key features:

• Validation: Ensures input references valid cell IDs.
• Scalability: Supports dynamic table expansion without breaking validation.

JavaScript Topics Covered

1. Event Handling

Manages interactions like clicks and key presses.

window.addEventListener('keydown', this.onKeyDown.bind(this));

2. DOM Manipulation

Creates and modifies DOM elements dynamically.

let cell = document.createElement('td'); cell.appendChild(document.createTextNode('A1'));

3. Recursion

Processes dependencies dynamically.

function calcCell(str) {  if (isNaN(str)) {    return calcCell(getCellValue(str));  } }

4. Error Handling

Detects invalid cells and circular dependencies.

if (!isValidCell(p)) throw new Error(`invalid cell ${p}`);

Conclusion

This project demonstrates a powerful spreadsheet using JavaScript. It leverages event handling, recursion, and Pub/Sub patterns, laying the foundation for complex web applications. Expand it by adding features like exporting data, charts, or formatting rules.

References

1. MDN Web Docs - Event Handling
2. MDN Web Docs - DOM Manipulation
3. JavaScript.info - Recursion