Build

Build a Real-time Collaborative Spreadsheets App

9 min read Michael Carroll on Apr 18, 2019

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.

HHoRB2m Gif

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:
and infrastructure for communicating our changes and synchronizing to other collaborators.
  • Handsontable: Our spreadsheet module of choice.
  • For the sake of the tutorial

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

    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>

    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:

    1. Recording changes to the spreadsheet.
    2. 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

    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); };

    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:

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

    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' },
      });
    });

    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.

    0