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.
- The full GitHub repo is available here
- 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!
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: Realtime APIs 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.
- 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
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 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:
-
afterChangewill be replayed using setDataAtCell -
afterCreateRowwill be replayed using alter -
afterRemoveRowwill also be replayed using alter -
afterColumnSortwill 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');
};
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.
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)