Why we moved our spreadsheet to the web
At CS we use spreadsheets to break down paper questionnaires into logical tabular entities, such as intro texts, items, skip conditions, pages and many more. The resulting relational data can then be imported into a relational database and used in SurveyLab projects. SurveyLab is our RAD framework for building complex online survey systems. For a number of years we used Excel to accomplish this task, however for reasons that will be detailed below, we found this not to be the optimal choice.
If you have ever had the need to collaborate on an Excel file, you know how problematic this can become. There is virtually no way of concurrently editing an Excel file, meaning time lost, as the various collaborators add their changes in series. At times you end up with several versions of the same file, only to realize that integrating these is near impossible or at least very error-prone. But wait - isn’t that why we have version control systems? Supposedly yes, however Excel uses binary file formats and therefore to diff and merge conflicting changes becomes infinitely more difficult.
Data portability and availability
If you want to move your spreadsheet data to a database or need to serve other applications with it, you must export it into appropriate formats and have these apps import the data again. If you still want to be able to change this data easily via spreadsheet editor, you have to repeat this process every time changes are made. Clearly, there are better, more centralized ways of handling this.
Excel obviously has many built-in functions. Yet, what to do when you need to implement your very own specialised validator? How about if you have to check for occurrences of foreign keys on another sheet? You can attempt this in Excel, but as you’ll find, it’s messy and hard to maintain.
Getting rid of technical debt
spreadJS by Wijmo
The Excel-like demo by Wijmo seemed very promising, so we committed some time to evaluating it. It has almost all the functions of Excel and provides an API for customization. However, in addition to being too expensive, there were other problems we discovered.
- It can basically replicate full Excel functionality
- It is in ongoing development
- Good support and fast forum responses by the community and Wijmo
- Expensive ( ~800€ )
- Requires Excel I/O service or client side software for some functionality like creating a basetemplate from an Excel file
Google Docs Spreadsheets
We also looked into using Google Spreadsheets, but that would have meant building a spreadsheet editor from scratch, due to the fact that we cannot implement our own functionality. So we could “just” have used the Google Spreadsheet API to store and read data, however, we wanted to host the editor ourselves, as our projects tend to contain sensitive data. Moreover, we like to stay independent.
We looked at some of the many smaller jQuery spreadsheet plugins that are out there, like flexigrid, jQuery.sheet and some others. Yet, most of them didn’t fulfil our prerequisites, which included not being in active development or having bad documentation, or being too limited in scope.
Our choice: handsontable
We decided to go with handsontable (hot), as it is cleanly and elegantly written, readily extensible, well-documented and updates are frequently released. The one troublesome thing is the large number of open issues on github, which may indicate a lack of contributors. So far we have been able to build clean workarounds for every problem we have encountered.
Reasons why we chose handsontable:
- Easily extensible
- Two way data binding: If the text inside a cell is changed, the data-object gets updated right away. It works the other way too - if new data is pushed to the editor the change is reflected in the UI after a re-render.
- Good documentation and active development
- Good API: hot supports a ton of functions you can call, and in addition defines a lot of hooks (events) which makes customizing the editor much easier. E.g. it has events, which are fired before and after data is changed inside a cell and also for failed validations.
It is fast: except for the scrolling performance, which can be slow at times, editing, validation, importing and exporting work rapidly.
- Small overhead for project files: the JSON data hot exports is pretty straightforward, with one array per table, which contains the rows represented by objects. This made it very easy to adjust our Surveylab importer to allow us to read those files.
- Custom cell types: hot comes with a lot of predefined cell types, such as dropdowns, numeric, text only etc.
- Cell validators: hot allows us to write custom cell validators. That was an important factor in the decision to use this program, as we needed to implement a foreign key check that would look for the existence of a key in other table instances.
- Context menu: hot has an build in context menu on right mouse click, which can be extended and customized with your own functionality
Below you see an image of the current version of the editor. Red buttons indicate sheets with failed validations.