DEV Community

Cover image for How to Build a Realtime Collaborative Spreadsheets App (ie. a Google Sheets Clone)
PubNub profile image Joe Hanson
Joe Hanson for PubNub

Posted on • Originally published at pubnub.com

How to Build a Realtime Collaborative Spreadsheets App (ie. a Google Sheets Clone)

#javascript #pubnub

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

alt text

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: Realtime APIs and infrastructure for communicating our changes and synchronizing to other collaborators.
  2. Handsontable: Our spreadsheet module of choice.

For The Sake Of The Tutorial

  1. Parcel: Zero-configuration web bundler for quick prototyping.
  2. Faker: 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
Enter fullscreen mode Exit fullscreen mode

Install toolchain dependencies:

$ npm i -D parcel-bundler
Enter fullscreen mode Exit fullscreen mode

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"
]
Enter fullscreen mode Exit fullscreen mode

Add a .babelrc to your root directory:

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

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
Enter fullscreen mode Exit fullscreen mode

Add our index file in the source folder:

$ touch src/index.html
Enter fullscreen mode Exit fullscreen mode

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>
Enter fullscreen mode Exit fullscreen mode

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

$ touch src/main.js
Enter fullscreen mode Exit fullscreen mode

Serve your index.html to start development:

$ npx parcel src/index.html
Enter fullscreen mode Exit fullscreen mode

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:

  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:

<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>
Enter fullscreen mode Exit fullscreen mode

Spreadsheet

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

$ touch src/spreadsheet.js
Enter fullscreen mode Exit fullscreen mode

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
});
Enter fullscreen mode Exit fullscreen mode

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);
Enter fullscreen mode Exit fullscreen mode

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(),
]]); });
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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: {},
};
Enter fullscreen mode Exit fullscreen mode

Record Hooks

We will utilize the following hooks provided by handsontable

  1. AfterChange is triggered after any cell is changed.
  2. AfterCreateRow is triggered after a row is added.
  3. AfterRemoveRow is triggered after a row is removed.
  4. 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); };
Enter fullscreen mode Exit fullscreen mode

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,
}); };
Enter fullscreen mode Exit fullscreen mode

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,
  });
};
Enter fullscreen mode Exit fullscreen mode

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,
  });
};
Enter fullscreen mode Exit fullscreen mode

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 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
  2. afterCreateRow will be replayed using alter
  3. afterRemoveRow will also be replayed using alter
  4. afterColumnSort will be replayed using clearSort to reset a previous sort and sortto 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');
};
Enter fullscreen mode Exit fullscreen mode

Create Row 

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

Remove Row 

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

Column Sort 

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

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],
    },
); });
Enter fullscreen mode Exit fullscreen mode

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 },
}); }
Enter fullscreen mode Exit fullscreen mode

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;
    },
); }
Enter fullscreen mode Exit fullscreen mode

Reset Table

Clearing the table involves two steps:

  1. Resetting our data set.
  2. 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' },
  });
});
Enter fullscreen mode Exit fullscreen mode

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();
  },
});
Enter fullscreen mode Exit fullscreen mode

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);
  });
});
Enter fullscreen mode Exit fullscreen mode

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();
});
Enter fullscreen mode Exit fullscreen mode

Result

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

$ npx parcel src/index.html
Enter fullscreen mode Exit fullscreen mode

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.

Or check out the live collaborative spreadsheets demo app here. Open it up in a couple of web browsers and see the realtime synchronization in action!

Again, for your reference, the full GitHub repo is available here.

Top comments (0)

Read next

otumianempire profile image

JavaScript Essentials: Part 7

Michael Otu -

andrewbaisden profile image

Deploying a React Watchlist Tracker App to Production Using DeployHQ

Andrew Baisden -

gautamkaran profile image

URL-Shortener-APIs

Karan Kumar Gautam -

devgauravjatt profile image

Creating an Interactive Color Picker with Svelte 5

Dev Gaurav Jatt -