Building a spreadsheet with AngularJS

Shortly into Commusofts estimate module I came across a use case that included the words "spreadsheet like". The requirement was simple; users needed to get data into the system as quickly as possible.

After exploring a few "non spreadsheet like" ideas I realized that nothing could match the efficiency and familiarity users already had with a good old spreadsheet.

It's turned out to be one of the most interesting things I've built (yet). And I'd like to share some thoughts and insights into building a spreadsheet of my own!

Keyboard Shortcuts #

Keyboard shortcuts turned out to be the hardest problem to tackle. The difficulty lay in features that aren't found in your conventional spreadsheet. Things such as different row types, and non-selectable cells.

Here we can see 2 types of row: normal rows (highlighted in red), and associated rows (highlighted in blue). Normal rows are pretty self explanatory. In terms of data they are just a one dimensional array containing their cell models.

Associated row is the term I coined to describe a row that contains other rows that relate to one-another. They introduce difficulties (especially when dealing with shortcuts) and have a more complex structure; being two dimensional.

Associated rows and non-selectable cells come hand in hand. Non-selectable cells are either empty, or contain data that can't be edited.

The cell with the value "#install" (in row 3) has 3 empty cells below it. Pressing down selects the cell with the value "#certificate" in row 4; skipping the 3 empty cells in the middle.

The cells in the second column contain values that can't be changed, and act as labels for the cells to their right. If you look carefully you'll see that when the "Homebase" and "Wickes" cells are selected their left border flashes red for a split second. This is because the left key was pressed, which is an invalid shortcut (more on this in part 4).

Now the scene is set lets talk about the implementation. Initially I thought a shortcut could be handled on the fly, i.e when an arrow key is pressed find the closest cell in that direction and select it. This worked perfectly for spreadsheets with normal rows, but struggled to handle those with associated rows. In pseudo code the logic began to look like this:

IF cell to the right exists?
  IF cell to the right is selectable?
    focus it
  ELSE
    IF 2nd cell to the right exists?
      IF that cell is selectable?
        focus it
      ELSE
        IF 3rd cell to the right exists?
          IF that cell is selectable?
            ... I think you see the pattern
ELSE IF is there a row below?
  IF row below is an associated row?
    IF 1st cell in that row selectable?
      focus it
    ELSE IF 2nd cell in that row selectable?
      focus it
    ELSE
      ... same pattern
  ELSE
    IF 1st cell in that row selectable?
      focus it
    ELSE IF 2nd cell in that row selectable?
      focus it
    ELSE
      ... same pattern
ELSE
  shortcut is invalid, reflect this in the UI

As you can see it gets complicated pretty quickly, and is far too much overhead just to find a cell to select. (Imagine this block firing every time you pressed an arrow key!) Not surprisingly this approach scored no points performance wise and was fairly error prone. After a few goes at optimizing the logic I realized I needed to take a different angle.

An idea I started playing with was to pre-calculate a shortcut before a key was pressed. (Note: a spreadsheet is an object made up of lots of arrays). To move from one cell to another, all thats needed is the index of the cell we want to move to. If this index was already in memory performing the shortcut could be as easy as accessing an objects value, (i.e new active cell = dot.notation.pointing.to.new.cell). On paper this concept worked, it was now time to code!

Without giving a line by line account of what happened next lets skip to the important bits. Clicking on a cell assigned the following attributes to each cell model:

• down_ref: index of closest cell below
• left_ref: index of closest cell to the left
• right_ref: index of closest cell to the right
• up_ref: index of closest cell above

(Non-selectable cells were not assigned these attributes, as they would never be navigated to / from)

Which in the wild looks like this:

// The data model representing a cell in the spreadsheet:

{
  $$hashKey: "02T",
  down_ref: "[2][0].associated_rows[0][1]",
  index_ref: "[1][1]",
  input_type: "text",
  label: "Description",
  left_ref: "[1][0]",
  right_ref: "[2][0].associated_rows[0][0]",
  state: "active_mode",
  up_ref: "[0][1]",
  value: "Take a photo of the garage"
}

Equally important as the shortcut attributes is keeping track of the current active cell. (In the directive the current cell is saved to a variable called this.cell). Then when a shortcut is pressed, (lets say the right key) we can fulfil the shortcut with this.cell = this.rows[this.cell.right_ref]. (In actual fact the index has to be interpolated beforehand, but for demo purposes this will do).

Up until now we have only discussed how this solution works for pre-calculating shortcuts. But what happens when a new row is added, or an existing one is deleted? These changes to a spreadsheet would render the shortcut refs invalid. A ref could now point to a cell that no longer existed, or to one that lived in an associated row. The solution was simple: re-run the function that assigned the shortcut attributes to each cell model, and everything's hunky dory.

One thing to note is that shortcuts have different actions depending on the contexts in which they're used. For example if I'm editing a cell and press left, my intension is to move the cursor back 1 character. It shouldn't register as an attempt to select the cell on the left. As with jQuery, Angular provides an implementation for binding and un-binding events. This gives you full control over turning shortcut listeners on or off, as seen below:

// Bind event
$document.bind('keydown.spreadSheetShortcuts', function (event) {
  // code...
});

// Unbind the event
$document.unbind('keydown.spreadSheetShortcuts');

And thats it! Hopefully the ideas covered have been interesting, and illustrate some useful techniques for dealing with shortcut intensive applications.