Collaborative editing and Excellentable Cloud are finally here! This blog will go into detail as to how we were able to release this new version of Excellentable and achieve the title of the first app on the Atlassian Marketplace to support collaborative editing. We will provide technical details that go on behind the scenes of Excellentable. You will also see how these details were used to achieve a successful collaborative editing, BETA version. 

What is Collaborative Editing?

So what is Collaborative Editing and why is it such a big deal? Collaborative Editing is the ability for more than one end user to edit something at the same time as another end user. No need to wait for different versions of the same document to be merged. Collaborative editing allows for continuous collaboration on documents without ever having to wait for merge changes. These real time changes are visible to everyone and enables organizations to avoid bottle necks when working on documents. It also allows for multiple teams to collaborate on one single document. The old way of having to edit, send, wait for edits and then edit yourself again; is long gone. 

Give Excellentable a Try!

share-file.jpg


The Technical Details

Now for the nitty gritty. Without the developers, Excellentable wouldn't be the product that you love today. It took many versions to achieve all of the capabilities that you see on Excellentable. It also took many hours to complete to coding to create those functionalities. However, how were these developers able to get collaborative editing to work?! Many of you may be familiar with Google Sheets. The concept of their multi-user editing, is very similar to Excellentable's! Only difference is the ability to be used on Atlassian products.



FireBase Live Editing Model

Objective

In order to achieve live editing across multiple users, the actions performed must communicate to one another. By using Firebase Realtime Database, we can store and sync data between users, their edits and devices in real time. The Firebase Realtime Database is a cloud-hosted, no SQL database that updates data across connected devices within milliseconds of each other. This allows the data to remain available, even if your app goes offline. Which in turn, provides a great user experience regardless of network connectivity. Bonus points go to the fact you don't have to worry about your data being lost. 

Keeping Spreadsheets in Sync 

To keep the spreadsheet in sync across multiple users, all of the actions performed are recorded and shared with the other users that are editing. Similarly, all of the actions performed by the users are retrieved and applied onto all views of the spreadsheet.

There are two ways of recording and applying changes:

  1. The users actions are recorded as operations, with all of the same parameters that are required to perform the same action on all views of the spreadsheet.
  2. Actions are recorded as spreadsheetobject and the other spreadsheet views are completely replaced, without loosing the original user's session.

Action Handling

Below, are just a few of the different actions that are handled, to keep spreadsheets in sync. 

Actions to Exclude Actions to Send as Operation Actions to Send to Sheet Views Pending Actions
  • exportasxlsx
  • exportascsv
  • save, exit, cut, copy
  • deleterow & deletecolumn
  • functionlist
  • keyboardshortcuts
  • DragDropBlock
  • DragFillBlock
  • ValueChanged
    • Formulas
    • Cell Edits Sheet
  • Renaming 
  • DataValidationDialog
  • ConditionalFormatDialog
  • IconSetDialog
  • BorderDialog
  • Restore / Import
  • Undo / Redo
  • Copy / Paste 

Operation Based

Each operation performed is mapped to Firebase with the following information:

args: <params required to perform the operation>
created: <timestamp>
eventType: <System / Custom>
sEvent: <ValueChanged / DragFill / DragDrop> 
sessionId: <user Session ID>
sheetIndex: <Index of sheet on which action was performed>
userId: <Id of user reponsible for this action

Pros:

  1. Since operations are applied as individual actions by using the spread API, the changes are reflected faster and smoother to the users. 
  2. The changes are applied based on the timestamp, so data will always be in sync with current user edits.
  3. Performance is proven to be much better than the spreadsheet replacement method.

Spreadsheet Replacement

Any Operation which is too large or complex to handle, are sent as an entire spreadsheetobject. All other users will replace their current spreadsheetobject with the new spreadsheetobject. This will maintain the current user's location and changes that they are currently making. 

created:  <timestamp>
lastCompletedEvent: <Id of the last Operation performed on the sheet>
sessionId: <user Session ID>
spread: <Spread object will replace the current user sheet>
userId: <Id of user reponsible for this action>

Interesting Scenarios:

  1. Keep the spreadsheetobject replacement and operation based processes in sync with each other.
    In order to make sure that all operations are applied after a spreadsheet is replaced, each of the spreadsheet replacement entries has a lastCompletedEvent, which is a reference to the last operation performed on the sheet. When a spreadsheet is replaced with a new spreadsheetobject coming from a different user, all operations since the lastCompletedEvent are performed on the spreadsheet to keep all spreadsheet views in sync. 
  2. Pausing when a spreadsheet replacement might break the user's experience.
    Replacing a spreadsheet when a user is in the middle of the following operations will also break the same: 
    • Autocomplete
    • DragDrop
    • DragFill
    • Selection in Progress 

To avoid any distrubtions to the user's experience, we pause the spreadsheet replacement until the user completes an action. If multiple spreadsheet replacements are received at once, then the latest spreadsheet is used to replace the current spreadsheet view. 

Restoring User Sessions after a Spreadsheet Replacement

Replacing the current spreadsheet with incoming spreadsheetobjects will destroy the current user's changes. For example, cell edits that are in progress will be lost, since the edit operations are not yet completed. We decided that before spreadsheet replacement happens, we store the current user content which includes location. Since operations are applied as individual actions using the spread API, the changes are reflected faster and smoother. The changes are applied based on the timestamp so data will always be in sync. Resulting in much better performance than the spreadsheet replacement option. 

Pros:

  1. Since the spreadsheet is completely replaced, the data across users will always be the same and there are less possibility for deviations.

Cons:

  1. Spreadsheetobject replacements are complex, since all of the data has to be removed and new data has to be loaded to replace it. This will degrade performance, which is visible when more than five users are editing the spreadsheet at the same time.

Initializing a New User

The initializing a new user process starts when a user enters into edit mode in Excellentable. Taking security in mind, initializing a new user has two layers in between, making sure the user's data and their credentials are secured. Let's define some terms so going through the process below is a bit easier to understand. 

  1. Confluence Server: As the name suggests, it's where the Excellentable app is running on a Confluence server.
  2. Lambda: One of the middle layers mentioned for security.
  3. Firebase: The end landing point for all calls that are made.
  4. Client: User's browser of choice at the time of entering Excellentable.

Starting from the client, when a user enters edit mode, Excellentable makes a call to the Confluence Server verifying if collaborative editing is active on the instance. If it is enabled, then the client asks for the credentials required to connect to Firebase. From there, the Confluence Server makes a call to Lambda and retrieves all of the required information needed to connect to Firebase. Lambda then confirms all of the credentials and creates space on Firebase for that particular Excellentable. When the entire process of creating space on Firebase is complete, it returns with the credentials of that space to the Confluence Server. This information is returned to the client, with the latest data now present in the Confluence Server's database. This then concludes the initializing a new user process.

When other users enter edit mode of the same Excellentable, the above process is repeated with only one difference. Instead of the step of initializing the space on Firebase, it only generates the credentials for the new user within the same space as the first user. That way, they are editing the same view of the spreadsheet. As done previously, the client receives all of the actions compelted by previous users and the client version of Excellentable is updated and synced.

What Happens When a User Leaves a Spreadsheet?

Using the same terminology as above, when a client leaves the edit mode of Excellentable without saving it, then a process similar to initializing a new user begins. This process starts from a call to the Confluence Server announcing that a current user has left edit mode. Then the Confluence Server makes a call to Lambda announcing the same. Now, Lambda only removes the credentials for the user that is leaving. The rest of the information is maintained on Firebase. Information of the Excellentable is only removed from Firebase when the last user editing, publishes and leaves. If any unsaved changes are present, the draft of Excellentable will be maintained on Firebase for a certain period of time. 

Working of User Tracking 

User tracking can be considered as the easiest module to implement in collaborative editing. Tracking is done through exchanging a data structure with the help of Firebase. Data structure consists of data for every user location like sheet, row and column numbers. Part of this data structure is updated and shared with other collaborators whenever a user changes it's location. 

Next Steps

The ideal implementation must perform all actions as operations without the need for spreadsheet replacement at all. However, due to time constraints both techniques are currently implemented. All operations which results in spreadsheet replacement will eventually be moved to the Operation based process, as we make more improvements. 

Learn more about Excellentable