Turning an Excel Sheet into a Collaborative Web Application

Recently I faced the problem of using a diagram available as a protected Excel template file. I had to fill out more columns and rows as were provided in the template, but unfortunately the password for unprotecting was not available. My first idea was to import the file into Google Sheets, which normally automatically unprotects the file and makes it editable. However, the layout was broken after the import and could not be easily fixed due to some diagonal cells and other quirks of the original sheet.

Luckily around the same time, Google came out with its Google Drive Realtime API. Anyway, I was keen to try out my new skills in jQuery UI that I had just enquired some days before, building another prototype. Finally, developing a custom web app turned out to be the solution for the restrictions of the Excel file. Here I want to discuss some pitfalls I encountered while building the app.

The underlying template is the “House of Quality”, a diagram and methodology for creating products that translates the “Voice of the Customer” in terms of customer requirements into the “Voice of the Company” aka engineering terms. It is basically a matrix where on the left side, user requirements are listed that in one of the next steps are matched to concrete quantifiable requirements listed on the top. The latter are also evaluated in a correlation matrix (“the roof”) that estimates the expected influence of requirement A to B.

About Google Drive Realtime API

The Drive Realtime API by Google, launched on March 19th, 2013, is a client-side JavaScript API that allows to build near-realtime collaborative web applications that automatically save their data, distribute changes to contributors and resolve conflicts. Moreover, together with the Drive SDK, web applications can be integrated into Google Drive, a one-stop-shop for document management tools already known from Google Docs like interfaces for renaming the document or inviting collaborators.

The API itself comes as a JavaScript library that features collaborative versions of common objects together with an event-based model for receiving updates on both awareness events like user joins, and model events such as changes in a specific string object. There are three collaborative objects available, the CollaborativeString, CollaborativeList and CollaborativeMap. All three data types can be created using a factory that builds these objects and ensures that future updates, both local and remote, are saved on the server and propagated to all other contributors.

Furthermore, custom objects can be created by registering their type and members to the Drive API. Some pitfalls apply when it comes to initialize these custom objects. To ensure that initializers are only executed once in the (collaborative!) lifetime of the object (and not on each contributor’s model separately), initializer methods need to be registered:

// create Product class
HouseOfQuality.Product = function() {};

// register custom collaborative object 'Product'
gapi.drive.realtime.custom.registerType(HouseOfQuality.Product, "Product");
HouseOfQuality.Product.prototype.name = gapi.drive.realtime.custom.collaborativeField("name");

// register custom initializer
gapi.drive.realtime.custom.setInitializer(HouseOfQuality.Product, function(name) {
    this.name = name;
});

/**
 * ...
 * Creating document and obtaining data model...
 * ...
 */

// create instance
var product = model.create(HouseOfQuality.Product, "Apache Wave");

This steps (registering the custom object) have to be executed before creating a concrete document.

Now let me guide you through the steps that were needed to create a collaborative web application based on the Excel template.

Building the HTML5 Interface

In the first iteration, my goal was to create an interface based on HTML5, CSS and JavaScript that has all the functionality for editing the diagram. I used the MVC pattern, so the idea was that when wiring up the app to Google Drive, I should only on one hand replace the local model objects (arrays, custom objects) against their collaborative counterparts and the other listen to remote model changes in the controller to change the view accordingly.

At first try, I thought best practice would be to leave the table, tr and td elements aside and create a table layout using the CSS display attribute to have certain div elements take the role of table-row or table-cell. Unfortunately this turned out to be a dead end, as I did not find a cross-browser way to group certain sequences of table-cells together as I used to in plain HTML with colspan and rowspan. So in the end I changed all div elements back to their tr or td equivalent. However I kept the CSS so in the future this may work out.

For the editable cells, I decided for the Jeditable in-place editor, as it comes with some handy functionality like select boxes and callbacks for getting the result of an edit. Below is an example of how to make all cells with the CSS class hq_matrix_cell_custreq editable:

// make the customer requirements’ name editable
$(".hq_matrix_cell_custreq").editable(function(value, settings) {
    HouseOfQuality._onCellChangedUserRequirementName(value, settings, $(this));
    // return the entered text as output
    return(value);
},
        {
            type: "text",
            tooltip: "Click here to change the value",
            onblur: "submit"
        }
);

Here, a crucial benefit of using jQuery is apparent: To wire events to a certain element, we only have to query with the appropriate selector once to work on all respective elements. On the bottom part of this code, we tell Jeditable to use a “text” input field as in-place editor. The onblur preference key lets us define that the in-place-editor should submit the change when the input field becomes blurred again (thus replaced by a text node). In the upper part of the code, we define a callback function that is executed on submit. We basically pass on the arguments to another function while adding a reference to the element scope which is the element the in-place-editor was placed in.

The data model behind the interface is a simple array-based structure with custom objects for the rows aka customer requirements and columns aka engineering terms. First are related to the latter using JavaScript dictionaries. New objects get assigned a custom UUID that are also reflected in the interface using HTML5 data-attributes, to later identify the correct model value in the Jeditable callback.

The diagonal matrix at the „roof“ of the diagram was the roadblock when importing the Excel file to Google Drive. It was also a major challenge when it came to recreate it in HTML5; finally I ended up using a Canvas and some nice algorithms that both draw the diagonal lines and calculate the relative position of the input elements within the matrix.

To make the amount of rows and columns flexible, I added buttons on the top of the app that simply initialize new data objects and insert new elements into the HTML5 page.

Wiring the UI up to Google Drive

As last step, I wired the data model up to the Google Drive API. Therefore, I followed the advice of the “Writing your first Realtime API app” video by the Google Drive team that says developers should stick to the helper class provided in the Realtime API Quickstart tutorial. Indeed the realtime-client-utils.js does a great job in initializing the app, as it comes with ready-made functionalities for letting the user grant access to their account as well as creating a new file or reading out file-parameters out of the URL when starting your app from within Google Drive. It helps a lot for doing everything in the right order: it comes with callbacks for registering custom types, initializing the model and loading a file.

First, we need to register custom types. As mentioned above, I employ some custom objects that needed to be transformed to their collaborative counterpart. To make them collaborative, all you need to do for every property is calling the collaborativeField() method and the setInitializer() for any initializer. In my case, the initializer for the requirement’s type creates a simple collaborativeMap instead of the JavaScript dictionaries I used before; also the Jeditable callback functions were slightly changed to work on collections instead of pure JavaScript types.

My initializeModel() callback constructs collections for all of the app’s main member objects. Again, it is important to note that this method is only called once in the collaborative lifetime of the app, that is once, no matter how many users later connect to the same document. At this point, all changes made in the interface are already saved on the server in near-realtime!

To load the saved data when loading the data later, the onFileLoaded() callback takes care of reflecting the model state in the interface. It also adds listeners for model updates, which is a bit tricky when it comes to collaborative types within another custom collaborative object. For that case, listeners have to be appended recursively:

// add listener for the list of user requirements
HouseOfQuality._userRequirements.addEventListener(gapi.drive.realtime.EventType.VALUES_ADDED, HouseOfQuality._userReqValuesAdded);

// add listener for every user requirement
var userReqArray = HouseOfQuality._userRequirements.asArray();
for (var i=0; i<userReqArray.length; i++) {
    var userReq = userReqArray[i];
     userReq.addEventListener(gapi.drive.realtime.EventType.VALUE_CHANGED, HouseOfQuality._userReqValueChanged);
    // add listener for funcReq relationships
    userReq.relationships.addEventListener(gapi.drive.realtime.EventType.VALUE_CHANGED, (function(uuid) {
        return function(e) {
            HouseOfQuality._userReqRelationshipsValueChanged(e, uuid);
        };
    })(userReq.uuid));
}

First, a VALUES_ADDED listener is added to the main list of user requirements that gets called when a new user requirement is added. Then, a listener for VALUE_CHANGED events is appended to every list member. It is called on any changes on the user requirement object itself. Finally, a listener is added to the relationship map within each user requirement object.

Please note the last part of the code, where I return a function that simply puts the current parent object’s ID into the event envelope because of the specifics of JavaScript closures. There is no way to obtain the affiliation of collection’s members within a custom object to their parent object.

It is important to say, that these events get fired for all model changes, even the local ones. To account for the optimistic model behind the underlying Operational Transformation algorithm, UI updates should be immediately performed locally, not only after a server round-trip. That’s why the origin of incoming events can simply be checked with the isLocal flag that comes with every event. In my case, I simply ignore those updates.

Having said that, the collaborative web application is already ready-to-use. To make it more comfortable to load any diagrams created with my app, I also added the “Drive Integration” within the Google API Console. The only data required is an app icon as well as the URL of your app that gets appended with the document ID when clicking your file within Google Drive.

Conclusion

The Drive Realtime API makes it fairly easy to create collaborative near-realtime web application. For me, it took more time to build the interface than to wire it up to Google Drive. Some duties still remain for developers when transforming a single-user app to a multi-user collaborative app, like creating and using a data model that builds on the API’s collaborative types.

The biggest benefit I experienced was that the transformed app automatically comes with the save and load functionality of Google Drive. Even if you don’t go for it because of the collaborative features first, you get a neat integration to Google Drive that makes it easy for your users to come back to your application to load their files. In contrast to other collaborative editing frameworks like ShareJS, you don’t need your own server infrastructure (except for hosting your app files).

Still, the application I created is currently far from being a perfect collaborative application. For example, I did not include neither undo functionality nor a single awareness tool for letting the users know that others are also contributing to the document. This is a crucial, first of all because there are even event callbacks available in the Drive Realtime API that keep you in the know of other users’ edits, like means for saving different users’ cursor position. Anyway, it’s a great starting point and I would love to see a community project to create some awareness widgets for apps built on the Realtime API.

Feel free to try out my app. Let me know if any of my tricks helped you with your app!

Kommentare sind deaktiviert.