Build: Real-Time Collaborative Spreadsheets App

collaborative spreadsheets data synchronization tutorial

This tutorial was written Divyun Vaid of SourceFuse, a software development company that builds custom, full-scale web and mobile SaaS products, and has deployed over 1000 apps and solutions since their founding in 2005.

In the last decade, office suites have transformed from clunky, heavy pieces of desktop software taking up valuable space on your system into on-demand, lightweight, collaborative applications. This opened up a new world of real-time collaboration, empowering multiple users in the web browser to work together from anywhere on Earth.

That’s what we’ll be building today in this tutorial. We’ll focus on collaborative spreadsheets in particular, but these concepts and design patterns can apply to any type of collaborative document capable of incremental updates – text documents, spreadsheets, etc.

Before we begin, let’s set our goals.

You’re not alone: Show who’s online and currently working on the same spreadsheet.
Whatcha doin’?: Highlight the cells they are working on.
Do you see it yet?: Monitor changes and synchronize them across various spreadsheet editing sessions.
You did it!: Store and maintain an audit trail of who did what and use it get new collaborators up to date.

Tech Stack

Main Stack

PubNub:

and infrastructure for communicating our changes and synchronizing to other collaborators.

Handsontable: Our spreadsheet module of choice.

For the sake of the tutorial

Parcel: Zero-configuration web bundler for quick prototyping.

Development Toolchain

There are three steps to get started.

Initialize your package.json:

$ mkdir sync-sheet
$ cd sync-sheet
$ npm init -y

Install toolchain dependencies:

$ npm i -D parcel-bundler

Note: From this point forward, we don’t need to install any dependencies manually. Parcel takes care of it when we run it, but I’ll note the packages along with steps.

Do a minimal babel and browserlist setup to make sure we get consistency across the browser.

We want to support the last two versions of the browser. Add browserlist to package.json.

"browserslist": [
  "last 2 version"
]

Add a .babelrc to your root directory:

 {
  "presets": [
    [
      "@babel/preset-env",
      {
        "useBuiltIns": "entry"
      }
] ],
  "plugins": [
    "@babel/plugin-transform-runtime"
] }

Dev dependencies alert: @babel/core and @babel/plugin-transform-runtime.

Entry Point

We need an entry point which we can pass to Parcel. Let’s create the source folder:

$ mkdir src

Add our index file in the source folder:

$ touch src/index.html

File src/index.html

<!DOCTYPE html>
<html lang="en">
  <head>
    <meta charset="UTF-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1.0" />
    <meta http-equiv="X-UA-Compatible" content="ie=edge" />
    <title>Sheet Sync using pubnub</title>
</head>
<body>
    <!-- our spreadsheet goes here -->
    <script src="./main.js"></script>
  </body>
</html>

There is a script tag linking to main.js, so let’s create that as well.

$ touch src/main.js

Serve your index.html to start development:

$ npx parcel src/index.html

Tutorial

PubNub Add-ons

To use PubNub, you’ll need to first sign up for a PubNub account. Worry not, we’ve got a generous free-tier for all your prototyping and developing.

Once you’ve signed up, enable the following add-ons in PubNub Admin Dashboard:

Presence
Storage & Playback

The View

Our view will have the following sections:

The spreadsheet
Online users
A button to clear our spreadsheet
A button to seed our sheet with a fake row of data.

The body will look like this:

<body>
    <div id="online-users"></div>
    <button id="clear-data"> Clear </button>
    <div id="sheet"></div>
    <button id="seed-data"> Seed sample row </button>
    <script src="./main.js"></script>
</body>

Spreadsheet

Next up, let’s initialize handsontable and get ourselves a spreadsheet.

 
$ touch src/spreadsheet.js

File src/spreadsheet.js

import Handsontable from 'handsontable';
import 'handsontable/dist/handsontable.full.min.css';
const hotSettings = {
  columns: [
    {
      data: 'available',
      type: 'checkbox',
}, {
data: 'id',
      type: 'numeric',
    },
    {
      data: 'email',
      type: 'text',
}, {
      data: 'fullName',
      type: 'text',
    },
  ],
  stretchH: 'all',
  autoWrapRow: true,
  rowHeaders: true,
  colHeaders: [
    '',
    'Id',
    'Email',
    'Name',
],
  columnSorting: {
    indicator: true,
  },
  autoColumnSize: {
    samplingRatio: 23,
  },
  contextMenu: true,
  licenseKey: 'non-commercial-and-evaluation',
  customBorders: true,
  colWidths: ['7%', '16%', '38%', '39%'],
  className: 'sheet',
};
export default (data, elementId) => new Handsontable(
  document.querySelector(elementId), {
  data,
  ... hotSettings
});

Dependency Alert: handsontable

Import src/spreadsheet.js into our src/main.js. If you have a server where your store your spreadsheets, you should fetch it here and populate sheetData.

import Sheet from '~/spreadsheet'
const sheetData = [];
const sheet = Sheet('sheet', sheetData);

Now, we add a listener the button to seed a sample row:

import faker from 'faker';
document.getElementById('seed-data').addEventListener('click', () => {
  sheet.populateFromArray(data.length, 0, [[
    faker.random.boolean(),
    faker.random.number(),
    faker.internet.email(),
    faker.name.findName(),
]]); });

Dependency Alert: faker

User Session

As a user’s session usually persists over tabs, to sync changes across tabs, we’ll need another type of session. Let’s call this an editing session.

You may have your own auth logic, but for this tutorial, let’s keep things simple and mock a user session.

$ touch src/user.js

File src/user.js

import faker from 'faker';
const userFromLocalStorage = window.localStorage.user &&
JSON.parse(window.localStorage.user);
const user = userFromLocalStorage || {
  id: faker.random.uuid(),
  color: faker.internet.color(160, 160, 160),
  name: faker.name.findName(),
};
// editing session
user.sessionUUID = faker.random.uuid();
window.localStorage.user = JSON.stringify(user);
export default user;

Dependency Alert: faker

So that takes care of our user session but as you can see, we don’t pick up editing session from localStorage as we need this to be unique for every tab.

PubNub Connector

Using the editing session, let’s initialize our PubNub instance.

$ mkdir src/connectors
$ touch src/connectors/pubnub.js

File src/pubnub.js

import PubNub from 'pubnub';
import user from '../user';
const pubnub = new PubNub({
  publishKey: process.env.PUBNUB_PUBLISH_KEY,
  subscribeKey: process.env.PUBNUB_SUBSCRIBE_KEY,
  uuid: user.sessionUUID,
});
export default pubnub;

Dependency Alert: PubNub

Hooks

We’ll need two types of hooks:

Recording changes to the spreadsheet.
Replaying incoming changes on the spreadsheet.

File src/hooks.js

const hooks = {
  record: {},
  replay: {},
};

Record Hooks

We will utilize the following hooks provided by handsontable

AfterChange is triggered after any cell is changed.
AfterCreateRow is triggered after a row is added.
AfterRemoveRow is triggered after a row is removed.
AfterColumnSort is triggered after a sort.

Triggers provide us with a delta, which is then published to PubNub.

These would also be triggered after we replay the incoming changes, so we need a way to make sure we don’t get into an infinite loop. Fortunately for the first three triggers, we may look at the source of the change to avoid publishing it again to PubNub. For column sort, we’ll need an alternate solution.

Record hooks need the PubNub object and sheet’s name, which would be our channel name, to publish the delta to PubNub so our record function will accept PubNub and sheetName as arguments and return hook callbacks for handsontable.

Change

hooks.record.afterChange = (pubnub, sheetName) => function recordAfterChange(
  changes,
  source,
){
if (source === 'sync' || !changes) {
return; }
  // Publish all deltas to pubnub in sequence.
  changes.reduce(async (prev, [row, prop, oldValue, newValue]) => {
    await prev;
    return pubnub.publish({
message: {
        operation: 'afterChange',
        delta: {
          row, prop, oldValue, newValue,
        },
},
      channel: sheetName,
    });
}, true); };

Create Row

hooks.record.afterCreateRow = (pubnub, sheetName) => function afterCreateRow(
  index,
amount,
source, ){
  if (source === 'sync') {
    return;
}
  pubnub.publish({
    message: { operation: 'afterCreateRow', delta: { index, amount } },
    channel: sheetName,
}); };

Remove Row

hooks.record.afterRemoveRow = (pubnub, sheetName) => function afterRemoveRow(
  index,
amount,
source, ){
  if (source === 'sync' || source === 'ObserveChanges.change') {
    return;
  }
  pubnub.publish({
    message: { operation: 'afterRemoveRow', delta: { index, amount } },
    channel: sheetName,
  });
};

Column Sort

Unlike the previous hooks, sort doesn’t provide a source. Instead, we’ll maintain a variable lastSortFromSync where we store the last sort config which was synced to PubNub. If nothing changed in the current sort config, we skip publishing another delta to PubNub.

let lastSortFromSync;
hooks.record.afterColumnSort = (pubnub, sheetName) => function afterColumnSort(
  [currentSortConfig],
  [destinationSortConfig],
){
if (lastSortFromSync === destinationSortConfig) {
return; }
  if (
    lastSortFromSync && destinationSortConfig
    && lastSortFromSync.column === destinationSortConfig.column
    && lastSortFromSync.sortOrder === destinationSortConfig.sortOrder
){ return;
}
  pubnub.publish({
    message: {
      operation: 'afterColumnSort',
      delta: { currentSortConfig, destinationSortConfig },
    },
    channel: sheetName,
  });
};

You might be wondering, what if I need to sync these to my server? There are two ways to go about it:

Make the API call to your server with the delta and then publish it to PubNub.
Recommended: Publish it to PubNub and then use Functions to make an API call to the server.

Replay Hooks

Whatever was recorded by others has to be replayed. So we have our usual suspects:

afterChange will be replayed using setDataAtCell
afterCreateRow will be replayed using alter
afterRemoveRow will also be replayed using alter
afterColumnSort will be replayed using clearSort to reset a previous sort and sort to set the order.

These hooks will by our PubNub listeners, which we’ll get around to it further down in the tutorial

Change

hooks.replay.afterChange = function replayAfterChange(hot, {
  row, prop, newValue,
}) {
  hot.setDataAtCell(row, hot.propToCol(prop), newValue, 'sync');
};

Create Row

hooks.replay.afterCreateRow = function replayAfterCreateRow(hot, {
  index, amount,
}) {
  hot.alter('insert_row', index, amount, 'sync');
};

Remove Row

hooks.replay.afterRemoveRow = function replayAfterRemoveRow(hot, {
  index, amount,
}) {
  hot.alter('remove_row', index, amount, 'sync');
};

Column Sort

hooks.replay.afterColumnSort = function replayAfterColumnSort(hot, {
  destinationSortConfig,
}) {
  if (!destinationSortConfig) {
    hot.getPlugin('columnSorting').clearSort();
return; }
  hot.getPlugin('columnSorting').sort(destinationSortConfig);
};
export default hooks;

Status Hooks

We’ll track user activity, in other words, what cell user is working on or what cells user is highlighting. This isn’t supposed to be apart of the spreadsheet’s history or audit trail, so we’ll just set this as PubNub’s state.

Add hooks for after selection and deselection.

Let’s add these to src/main.js:

hot.addHook('afterSelectionEnd', (row, col, row2, col2) => {
  pubnub.setState(
{
state: {
        selection: {
          row, col, row2, col2,
},
user, },
      channels: [sheetName],
    },
); });
hot.addHook('afterDeselect', () => {
  pubnub.setState(
{
state: {
        selection: null,
user, },
      channels: [sheetName],
    },
); });

We’ll use setBorders to show what cells are other people working on.

const customBordersPlugin = hot.getPlugin('customBorders');
function setBorders({
  row, col, row2, col2,
}, color) {
  customBordersPlugin.setBorders([[row, col, row, col2]], {
    top: { width: 2, color },
  });
  customBordersPlugin.setBorders([[row2, col, row2, col2]], {
    bottom: { width: 2, color },
});
  customBordersPlugin.setBorders([[row, col, row2, col]], {
    left: { width: 2, color },
});
  customBordersPlugin.setBorders([[row, col2, row2, col2]], {
    right: { width: 2, color },
}); }

Presence and State

The following function fetches presence status from PubNub. Presence retrieves all the individual users who currently have the spreadsheet open and their statuses. We’ll then use users’ presence to populate #online-users and users’ state to highlight cells by calling setBorders.

 function fetchPresense() {
  pubnub.hereNow(
    {
      channels: [sheetName],
      includeUUIDs: true,
      includeState: true,
    },
    (status, { channels: { test_sheet: { occupants } } }) => {
      customBordersPlugin.clearBorders();
      const sessions = new Set();
      const html = occupants.reduce((acc, { state = {} }) => {
        if (!state.user || (state.user.uuid === user.uuid) ||
sessions.has(state.user.uuid)) {
return acc; }
        sessions.add(state.user.uuid);
        if (state.selection) {
          setBorders(state.selection, state.user.color);
}
        return `${acc}
          <span> ${state.user.name} is online </span> <br/>`;
}, '');
      document.getElementById('online-users').innerHTML = html;
    },
); }

Reset Table

Clearing the table involves two steps:

Resetting our data set.
Notifying others regarding the same.

document.getElementById('clear-data').addEventListener('click', () => {
  data.length = 0;
  pubnub.deleteMessages({
    channel: sheetName,
  }, renderHistory);
  pubnub.publish({
    channel: sheetName,
    message: { operation: 'afterClearHistory' },
  });
});

PubNub Listeners

There are two listeners to be added.

One will listen to the message. If the operation received is afterClearHistory, we clear the data. Otherwise, we call the corresponding replay hook.

The second is for presence, which we’ll call the fetchPresense function described earlier.

pubnub.addListener({
  message({ publisher, message: { operation, delta }, timetoken }) {
    if (publisher !== pubnub.getUUID()) {
      if (operation === 'afterClearHistory') {
        data.length = 0;
        hot.render();
      } else {
        hooks.replay[operation](hot, delta);
      }
} },
  presence({ uuid }) {
    if (uuid === pubnub.getUUID()) {
return; }
    fetchPresense();
  },
});

Audit Trail Replay

Because we’re limiting ourselves to the web browser, we’ll need a way to get new collaborators up and running with the most current state of the spreadsheet.

PubNub’s channel history will serve as an audit log for our spreadsheet. We can then replay these logs to deliver users the latest state.

src/main.js

pubnub.history({
  channel: [sheetName],
}, (status, { messages }) => {
  messages.forEach((message) => {
    hooks.replay[message.entry.operation](hot, message.entry.delta);
  });
});

Subscriptions and Initializing Hooks

We want to wait until we’ve replayed the history. After we are done replaying the logs, we subscribe to the channel to get new messages. Also, we’ll set the initial state of the user. Finally, we initialize all the record hooks.

We’ll add all this to history’s callback.

Append to src/main.js

pubnub.history({
  channel: [sheetName],
}, (status, { messages }) => {
  messages.forEach((message) => {
    hooks.replay[message.entry.operation](hot, message.entry.delta);
  });
  pubnub.subscribe({
    channels: [sheetName],
    withPresence: true,
});
  pubnub.setState(
    {
      state: {
        selection: null,
        user,
},
      channels: [sheetName],
    },
);
  Object.keys(hooks.record).forEach((hook) => {
    hot.addHook(hook, hooks.record[hook](pubnub, sheetName));
});
  fetchPresense();
});

Result

That’s it! We’re all set up, now let’s fire it up.

 
$ npx parcel src/index.html

Open up http://localhost:1234 in your browser. Open it up again in another tab. Then open up an incognito browser and do it again. How about another browser all together? Do as many as possible. Now make changes in any one of them and see it propagate across your tabs, windows and browsers.

Build a Real-time Collaborative Spreadsheets App

Tech Stack

Main Stack

For the sake of the tutorial

Development Toolchain

Entry Point

File src/index.html

Tutorial

PubNub Add-ons

The View

Spreadsheet

File src/spreadsheet.js

User Session

PubNub Connector

File src/pubnub.js

Hooks

File src/hooks.js

Record Hooks

Change

Create Row

Remove Row

Column Sort

Replay Hooks

Change

Create Row

Remove Row

Column Sort

Status Hooks

Presence and State

Reset Table

PubNub Listeners

Audit Trail Replay

src/main.js

Subscriptions and Initializing Hooks

Append to src/main.js

Result