Build a realtime spreadsheets application - Part 2: Implementing realtime collaboration

Introduction
Prerequisites
Setting up
Making editing realtime
Conclusion

Introduction

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

In the first part of this series, we created a basic spreadsheets application that allows a user to create spreadsheets. A user could also share the link to a sheet with another user, thereby giving them the ability to edit the spreadsheet.

In this article, we’ll bring our application functionality closer to that of Google Sheets by adding realtime collaboration functionality. This means that multiple users can work on the same sheet at the same time, and both of their changes show up in all other windows immediately. Here’s a glimpse of the end result:

Prerequisites

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 first part and still have your project code, you can skip this section.

Clone the project from GitHub:

    git clone -b part-1-users-spreadsheets https://github.com/shalvah/shoots.git

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

Making editing realtime

Our realtime sync has two parts:

Whenever a user opens a sheet, we'll subscribe them to a specific Pusher channel, identified by the sheet’s ID.
When a user makes changes to the sheet (Handsontable's afterChange event), we send the update via AJAX to the server. The server will then update the sheet's content in the database and broadcasts the change on the channel above to all subscribed users so the UI can be updated accordingly

Let’s do it! First, we’ll add a virtual property to our sheets, channel_name. This property will return the name of the Pusher channel we should subscribe to in order to be notified about changes to that sheet. Add the following method to your Sheet model (app/Models/Sheet.php):

1public function getChannelNameAttribute()
2    {
3        return "presence-sheet-$this->_id";
4    }

The name of the channel starts with presence-, to indicate that this is a presence channel. We’re using a presence channel because it provides us with additional information about who is subscribed to the channel, which we’ll need in the next part of this guide.

Now, on to our frontend. Replace the code in your resources/views/spreadsheet.blade.php with the following:

1<head>
2        <title>{{ $sheet->name }}</title>
3        <meta name="csrf-token" content="{{ csrf_token() }}">
4        <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/handsontable/2.0.0/handsontable.min.css" t
5        <!-- Fonts -->
6        <link rel="dns-prefetch" href="https://fonts.gstatic.com">
7        <link href="https://fonts.googleapis.com/css?family=Raleway:300,400,600" rel="stylesheet" type="text/css">
8    
9        <!-- Styles -->
10        <link href="{{ asset('css/app.css') }}" rel="stylesheet">
11    </head>
12    
13    <br>
14    <h2>{{ $sheet->name }}</h2>
15    <div id="sheet"></div>
16    
17    <script src="https://cdnjs.cloudflare.com/ajax/libs/handsontable/2.0.0/handsontable.min.js"></script>
18    <script>
19        let csrfToken = document.head.querySelector('meta[name="csrf-token"]').content;
20        let sheetContent = @json($sheet->content);
21    
22        let container = document.getElementById('sheet');
23        let table = new Handsontable(container, {
24            data: sheetContent,
25            rowHeaders: true,
26            colHeaders: true,
27            minCols: 20,
28            minRows: 20,
29            afterChange: function (change, source) {
30                if (source === 'loadData') return;
31    
32                console.log(change, source);
33    
34                fetch('/sheets/{{ $sheet->_id }}', {
35                    method: 'PUT',
36                    body: JSON.stringify({ change: change[0] }),
37                    headers: {
38                        'X-CSRF-TOKEN': csrfToken,
39                        'Content-Type': 'application/json'
40                    },
41                    credentials: 'same-origin'
42                })
43            }
44        });
45    </script>

Let’s take a closer look at the code. The afterChange event is fired by Handsontable whenever the data in the table changes, either due to loading data from the server or due to a user changing a cell. When this happens, our callback gets called with two parameters:

The change is an array containing the cell that was changed. The cell is represented as an array with four elements: the row index, columnindex, old value and new value.
The source is either “loadData” or “edit”, depending on which scenario mentioned above applies.

For instance, let’s suppose we have an empty sheet. When the page is loaded, the afterChange is fired with the change being an empty array and the source being “loadData”. Let’s suppose our table looks like this:

1|   A   |   B   |   C 
2---------------------------
31 |
42 |
53 |

Supposing a user edits cell A2 to contain the value “hi”:

1|   A   |   B   |   C 
2---------------------------
31 |
42 | hi
53 |

The afterChange event will be fired with the source as “edit”. The change parameter will look like this:

1[
2      [1, 0, null, 'hi']
3    ]

Then, supposing the user changes their mind and switches the “hi” to “hello”, change will be:

1[
2      [1, 0, 'hi', "hello"]
3    ]

In both these cases:

The first element is the row index (indexes start from 0, so row 2 is index 1)
The second element is the column index (again, zero-indexed)
The third element is the old value of the cell (null if the cell was empty)
The fourth element is the new value

In the callback we passed to afterChange above, we use Fetch to send the new change to the server. Let’s update our controller to handle this.

First, we need to install and configure the Pusher package, since we’ll be broadcasting the change to all connected clients. Run the following command:

1composer require pusher/pusher-http-laravel
2    php artisan vendor:publish --provider="Pusher\Laravel\PusherServiceProvider"

Sign in to your Pusher dashboard and create a new app. Copy your app credentials from the App Keys section and add them to your .env file:

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

Note: Laravel sometimes caches old configuration, so for the project to see your new configuration values, you might need to run the command php artisan config:clear

Modify the update method of your app/Http/Controllers/SheetsController.php to look like this:

1public function update($id)
2    {
3        $sheet = Sheet::findOrFail($id);
4        $change = \request('change');
5        [$rowIndex, $columnIndex, $oldValue, $newValue] = $change;
6        $sheet->content = $this->updateCell($rowIndex, $columnIndex, $newValue, $sheet->content);
7        $sheet->save();
8        \Pusher::trigger($sheet->channel_name, 'updated', ['change' => $change]);
9        return response()->json(['sheet' => $sheet]);
10    }
11    
12    protected function updateCell($rowIndex, $columnIndex, $newValue, $sheetContent)
13    {
14        // we expand the sheet to reach the farthest cell
15        for ($row = 0; $row <= $rowIndex; $row++) {
16           // create the row if it doesnt exist
17            if (!isset($sheetContent[$row])) {
18                $sheetContent[$row] = [];
19            }
20            for ($column = 0; $column <= $columnIndex; $column++) {
21                if (!isset($sheetContent[$row][$column])) {
22                    // create the column if it doesnt exist
23                    $sheetContent[$row][$column] = null;
24                }
25            }
26        }
27        $sheetContent[$rowIndex][$columnIndex] = $newValue;
28        return $sheetContent;
29    }

Here, we update the specific cell that was changed in the sheet’s content and then trigger an updated event via Pusher with the change as the payload.

Note: in our updateCell function, we expand the sheet to reach the farthest column, filling all empty cells with null. This is so our sheet doesn’t end up with missing rows and columns

Let’s head back to our frontend and add the code that handles this event. Add this to the bottom of your resources/views/spreadsheet.blade.php:

1<script src="https://js.pusher.com/4.2/pusher.min.js"></script>
2    <script>
3        let pusher = new Pusher('your-app-key', {
4            cluster: 'your-app-cluster',
5            authEndpoint: '/sheets/{{ $sheet->_id }}/subscription_auth',
6            auth: {
7                headers: {
8                    'X-CSRF-Token': csrfToken
9                }
10            }
11        });
12        pusher.subscribe("{{ $sheet->channel_name }}")
13            .bind('updated', function (message) {
14                let [rowIndex, columnIndex, oldValue, newValue] = message.change;
15                addCellValue(rowIndex, columnIndex, newValue);
16                table.loadData(sheetContent)
17        });
18      
19        function addCellValue(rowIndex, columnIndex, newValue) {
20          // we expand the sheet to reach the farthest cell
21          for (let row = 0; row <= rowIndex; row++) {
22              if (!sheetContent[row]) sheetContent[row] = [];
23              for (let column = 0; column <= columnIndex; column++) {
24                  if (!sheetContent[row][column])
25                    sheetContent[row][column] = null;
26              }
27          }
28          sheetContent[rowIndex][columnIndex] = newValue;
29    }
30    </script>

Replace your-app-key and your-app-cluster in the code above with your Pusher app key and cluster that you obtained from your dashboard earlier.

In this code:

We initialize our Pusher client and subscribe to the sheet’s channel.
Since presence channels require authentication, we provide an authEndpoint which Pusher will call to determine if the current user is permitted to subscribe to this sheet.
We bind to the updated event and update the sheet’s content similar to how we did on the server (in the addCellValue function), then we refresh the table UI by calling table.loadData.

The last piece of the puzzle we need to implement is our authentication endpoint. Let’s do that now. Add this to the end of your routes/web.php:

    Route::post('sheets/{id}/subscription_auth', 'SheetsController@authenticateForSubscription');

Now, add the authenticateForSubscription to your app/Http/Controllers/SheetsController.php:

1public function authenticateForSubscription($id)
2    {
3        $authSignature = \Pusher::presence_auth(
4            \request('channel_name'),
5            \request('socket_id'),
6            \Auth::user()->_id,
7            \Auth::user()->toArray()
8        );
9        return response()->json(json_decode($authSignature));
10    }

Great. Now, let’s test our app out. 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/login (or http://localhost:8000/register if you didn’t sign up in the previous part) and create a new spreadsheet. Copy the spreadsheet’s url from your browser and open it in a second tab (as a different user or the same user). You should be able to make changes to the sheet in both tabs and see them show up in realtime.

Conclusion

Well, that was fun, wasn’t it? But there’s more to come. In the next part of this series, we’ll replicate another nice feature of Google Sheets: showing which users are currently viewing the spreadsheet in realtime. Stay tuned. You can check out the source code of the app thus far on GitHub.

Docs

Tutorials

Support

Glossary

Build a realtime spreadsheets application - Part 2: Implementing realtime collaboration

Introduction

Prerequisites

Setting up

Making editing realtime

Conclusion

Products

Developers

Company

Legal