Build a realtime spreadsheets application - Part 3: Showing current viewers


This is part 3 of a 4-part tutorial. You can find part 1 here, part 2 here and part 4 here.

In part one and part two of this series, we built a spreadsheets app that supports simultaneous editing by multiple users, syncing changes across all clients in realtime.

In this article, we’ll add another useful piece of functionality present in Google Sheets, the ability to see who’s viewing the sheet in realtime. We’ll display avatars corresponding to each user currently viewing the sheet. Here’s how that will look in our app:



  • PHP 7.2 or higher, with the MongoDB driver installed. You can find installation instructions here.
  • Composer
  • MongoDB (version 3.4 or higher). Get it here.
  • A Pusher account. Create one here.

Setting up

Note: if you followed through with the second part and still have your project code, you can skip this section.

Clone the project from GitHub:

    git clone -b part-2-realtime-collaboration

Or download the source directly from this link.

Then cd into the project folder and install dependencies:

    composer install

Lastly, copy the .env.example to a new file called .env. Add your Pusher app credentials to the .env file:

2    PUSHER_APP_KEY=your-app-key
3    PUSHER_APP_SECRET=your-app-secret
4    PUSHER_APP_CLUSTER=your-app-cluster

Look for these lines of JavaScript in resources/views/spreadsheet.blade.php:

1let pusher = new Pusher('your-app-key', {
2        cluster: 'your-app-cluster'
3    });

Insert your Pusher app key and cluster in the appropriate places.

Run the following command to generate an application encryption key:

    php artisan key:generate

Note: if your MongoDB server requires a username and password, add those in your .env file as the DB_USERNAME and DB_PASSWORD respectively.

Implementing presence with Pusher

In the previous part of this series, we subscribe each user to a presence channel when they open a sheet. The name of this channel is tied to the sheet’s ID. This presence channel will provide the core of our “Now Viewing” functionality, because of the information presence channels provide us with about who is subscribed to a channel. We’ll hook into certain presence events fired by Pusher on the channel.

Here’s how we’ll do this:

  • When the user successfully subscribes to the channel, the event pusher:subscription_succeeded is triggered. The included payload contains a list of all the subscribed members. We’ll listen for this event and use the payload to display the avatars of everyone who was viewing the sheet when we opened it.
  • When a new user joins the channel (someone else opens the sheet), the event pusher:member_added is triggered. We’ll listen for this and add the avatar of the new member to our UI.
  • When a user leaves the channel (closes the browser window), the event pusher:member_removed is triggered. When this happens, we’ll remove the avatar of the member who left from our UI.

Let’s go!

Creating the markup

First, let’s create the markup for our avatars. The avatar is essentially the user’s initials on a colored circular background (similar to Google’s default user avatars). When we hover over it, it should display a tooltip containing the name of the user.

We’ll use Bootstrap and jQuery to style our avatars and tooltips. Open up your resources/views/spreadsheet.blade.php and include the Bootstrap and jQuery assets before the closing </head> tag:

2      ...
4      <link
5        rel="stylesheet" href="" 
6        integrity="sha384-BVYiiSIFeK1dGmJRAkycuHAHRg32OmUcww7on3RYdg4Va+PmSTsz/K68vbdEjh4u" crossorigin="anonymous">
7      <script src=""
8        integrity="sha256-FgpCb/KJQlLNfOu91ta32o/NMZxltwRo8QtmkMRdAu8="
9        crossorigin="anonymous">
10      </script>
11      <script src=""
12        integrity="sha384-Tc5IQib027qvyjSMfHjOMaLkfuWVxZxUPnCJA7l2mCWNIpG9mGCD8wGNIcPD7Txa"
13        crossorigin="anonymous">
14      </script>
15    </head>

You should have code like this shortly after the closing </head> tag:

1<h2>{{ $sheet->name }}</h2>
2    <div id="sheet"></div>

Modify it so it looks like this:

1<h2>{{ $sheet->name }}</h2>
2    <p>
3      <span style="float: right; margin-right: 50px; margin-bottom: 40px; font-size: 16px;">Now viewing: <span id="viewers"></span>
4      </span>
5    </p>
6    <br> <br>
8    <div id="sheet"></div>

Now, add the styles for the avatar class just before the closing </head> tag:

2        .avatar {
3            color: rgb(255, 255, 255);
4            background-color: #fc0093;
5            display: inline-block;
6            font-family: Arial, sans-serif;
7            font-size: 20px;
8            border-radius: 50%;
9            width: 36px;
10            height: 36px;
11            text-align: center;
12        }
13    </style>

Responding to presence events

Let’s modify our Pusher subscription code to respond to the events we considered above. In your resources/views/spreadsheet.blade.php, you should have code like this:

1pusher.subscribe("{{ $sheet->channel_name }}")
2        .bind('updated', function (message) {
3            let [rowIndex, columnIndex, oldValue, newValue] = message.change;
4            addCellValue(rowIndex, columnIndex, newValue);
5            table.loadData(sheetContent);
6        });

Modify those lines to look like this:

1pusher.subscribe("{{ $sheet->channel_name }}")
2        .bind('pusher:subscription_succeeded', (data) => {
3            Object.entries(data.members)
4                .forEach(([id, member]) => addViewer(member));
5        })
6        .bind('pusher:member_added', (member) => addViewer(
7        .bind('pusher:member_removed', (member) => removeViewer(member))
8        .bind('updated', function (message) {
9            let [rowIndex, columnIndex, oldValue, newValue] = message.change;
10            addCellValue(rowIndex, columnIndex, newValue);
11            table.loadData(sheetContent);
12        });

The last thing for us to do is to implement the addViewer and removeViewer functions. Add the following code to the end of your resources/views/spreadsheet.blade.php:

2        function addViewer(viewer) {
3            const userInitials =' ')
4                .reduce((initials, name) => {
5                    initials.push(name[0]);
6                    return initials;
7                }, []).join('');
8            let $avatar = $('<span>')
9                .addClass('avatar')
10                .attr('data-toggle', 'tooltip')
11                .attr('id', `avatar-${viewer._id}`)
12                .attr('title',
13                .text(userInitials);
14            $('#viewers').append($avatar);
15            // enable the tooltip
16            $avatar.tooltip();
17        }
19        function removeViewer(viewer) {
20            $(`#avatar-${}`).remove();
21        }
22    </script>

The addViewer function creates the avatar element and adds it to the DOM using jQuery, while the removeViewer locates the avatar corresponding to the specified viewer and removes it.

All done! Let’s test our application. Start your MongoDB server by running mongod. (On Linux/macOS, you might need to run it as sudo).

Then start your app by running:

    php artisan serve

Sign in to your app at http://localhost:8000 and create a new spreadsheet. In a different browser, sign up as a new user at http://localhost:8000/register. Then copy the URL of the spreadsheet you created in the first browser and open it in the second browser. You should see that the user avatars are displayed at the top right corner as they open and close the sheet.


Thus far, we’ve been able to build a spreadsheets application with realtime collaboration and a “Now viewing” feature, thanks to Pusher. In the next part, we’ll see how we can use Pusher to add another nifty Google Sheets feature—automatically deleting empty documents when they’re closed. Stay tuned!

You can check out the source code of the app on GitHub.