How to Build a Realtime Collaborative Spreadsheets App (ie. a Google Sheets Clone)
Tags: javascript, pubnub
Published: 2019-05-22 23:15:26

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](https://www.pubnub.com/solutions/collaboration/). This opened up a new world of realtime 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.

- The [full GitHub repo](https://github.com/sourcefuse/sheet-sync) is available here
- Check out the [live collaborative spreadsheets demo app here](https://sourcefuse.github.io/sheet-sync/?sheet=blogpostversion). Open it up in a couple of web browsers and see the realtime synchronization in action!

![alt text](https://i.imgur.com/HHoRB2m.gif "realtime spreadsheet tutorial")

Before we begin, let’s set our goals.

1. You’re not alone: Show who’s online and currently working on the same spreadsheet.
2. Whatcha doin’?: Highlight the cells they are working on.
3. Do you see it yet?: Monitor changes and synchronize them across various spreadsheet editing sessions.
4. 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

1. [PubNub](https://pubnub.com/): Realtime APIs and infrastructure for communicating our changes and synchronizing to other collaborators.
2. [Handsontable](https://handsontable.com/): Our spreadsheet module of choice.

### For The Sake Of The Tutorial

1. [Parcel](https://parceljs.org/): Zero-configuration web bundler for quick prototyping.
2. [Faker](https://github.com/marak/Faker.js/): Generating fake data (in this case, the sample information populating the cells.

## 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

```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.](https://dashboard.pubnub.com/signup) 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:](https://admin.pubnub.com/)

1. Presence
2. Storage & Playback

### The View

Our view will have the following sections:

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

The body will look like this:

```html
<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

```javascript
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

```javascript
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

```javascript
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

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

### Record Hooks

We will utilize the following hooks provided by `handsontable`

1. [AfterChange](https://handsontable.com/docs/7.0.0/Hooks.html#event:afterChange) is triggered after any cell is changed.
2. [AfterCreateRow](https://handsontable.com/docs/7.0.0/Hooks.html#event:afterCreateRow) is triggered after a row is added.
3. [AfterRemoveRow](https://handsontable.com/docs/7.0.0/Hooks.html#event:afterRemoveRow) is triggered after a row is removed.
4. [AfterColumnSort](https://handsontable.com/docs/7.0.0/Hooks.html#event: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

```javascript
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

```javascript
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

```javascript
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.

```javascript
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:

1. Make the API call to your server with the delta and then publish it to PubNub.
2. _Recommended:_ Publish it to PubNub and then use [PubNub Functions](https://www.pubnub.com/products/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:

1. `afterChange` will be replayed using [setDataAtCell](https://handsontable.com/docs/7.0.0/Core.html#setDataAtCell)
2. `afterCreateRow` will be replayed using [alter](https://handsontable.com/docs/7.0.0/Core.html#alter)
3. `afterRemoveRow` will also be replayed using [alter](https://handsontable.com/docs/7.0.0/Core.html#alter)
4. `afterColumnSort` will be replayed using [clearSort](https://handsontable.com/docs/7.0.0/ColumnSorting.html#clearSort) to reset a previous sort and [sort](https://handsontable.com/docs/7.0.0/ColumnSorting.html#clearSort)to set the order.

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

### Change

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

### Create Row

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

### Remove Row

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

```javascript
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`:

```javascript
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](https://handsontable.com/docs/7.0.0/CustomBorders.html#setBorders) to show what cells are other people working on.

```javascript
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`.

```javascript
 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: 
  1. Resetting our data set.
  2. Notifying others regarding the same.

```javascript
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.

```javascript
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

```javascript
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

```javascript
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](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.

Or check out the [live collaborative spreadsheets demo app here](https://sourcefuse.github.io/sheet-sync/?sheet=blogpostversion). Open it up in a couple of web browsers and see the realtime synchronization in action!

Again, for your reference, the [full GitHub repo](https://github.com/sourcefuse/sheet-sync) is available here.