For up-to-date Handsontable documentation, see handsontable.com/docs.

We all love keyboard shortcuts as they boost our productivity and save lots of time. It’s easy to get used to working with them, but it’s painful when we cannot use them anymore. Handsontable, as a web-based spreadsheet, also allows you to use the most popular keyboard shortcuts. This provides users with an experience they’re already familiar with from industry behemoths such as Microsoft Excel and Open Office Calc.

In this article I will focus mainly on showing how Handsontable’s shortcuts can be extended and customized beyond what’s available in Excel and Calc.

Custom shortcuts

Each of us use a different set of shortcuts, depending on what we do in a spreadsheet. For instance, if you do a comparison of products in your e-commerce website it might be handy to be able to automatically fill the adjacent cells with the names of product categories or labels. Accountants, on the other hand, more often use formulas in their spreadsheet, hence the extensive use of related keyboard shortcuts such as ‘show all formulas’ (Ctrl+~), or ‘show formula help’ (Shift+Fn+F1).

Handsontable gives you a fair amount of flexibility here. Since it’s a JavaScript library with open API, you can bind any function to custom shortcuts. Below I have created four different examples of this that can be used to modify the content of a cell or add a comment.

Convert text into a hyperlink

In this example I use custom shortcuts to turn text inside a cell into a hyperlink leading to Google search results. You can use either CTRL+M to affect one cell, or CTRL+Q to change all cells in a sheet into links.

To do this, I use the keydown event listener. I’m listening to events fired when particular keys are pressed, and then call setCellsMeta to use the right renderer. To change all cells at once I use the updateSettings method for the entire spreadsheet instance.

The getSelected method that we use to track which cells have been selected allows us to track the selection direction. To simplify the process, I am using the Math function to normalize the coordinates that are then used in the setCellMetaof the FOR loop.

Change the color of text

Using the same method as described in the previous example, I bound CTRL+M/Q to a function that allows me to change the color of the text inside a cell. I call the render method to refresh the table each time the combination of keys is pressed.

Apply validation to a cell

In cases where you need to validate your data on demand (read more about validation rules), use the validateCells method to achieve that. In this example, we check all the rendered cells against a pre-defined validation rule. When you hit CTRL+M you should see how all the non-numeric values turn red.

Add a comment with pre-defined text inside

Here I use the CTRL+M shortcut to call the setCommentAtCell method, which adds a comment with pre-defined text inside (“Accepted”).

Conclusion

Handsontable gives you the ability to boost your productivity with custom key combinations bound to JavaScript functions. However, remember that it is good practice to not overwrite the default shortcuts of a browser, at least not without giving it some thought.