How to build a realtime spreadsheets application with Pusher Channels - Part 2: Implementing realtime collaboration
Tags: php, laravel, pusher, realtime
Published: 2018-06-06 14:28:13

---
title: How to build a realtime spreadsheets application with Pusher Channels - Part 2: Implementing realtime collaboration
published: true
description: 
tags: php, laravel, pusher, realtime
---


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:


![](https://d2mxuefqeaa7sj.cloudfront.net/s_91E250D49C2F46522A90C69B0F2BA43B443748C32F623C6DFC50A0C7F5A0C71E_1524778753133_building-realtime-spreadsheets-app-laravel.gif)

## Prerequisites
1. PHP 7.2 or higher, with the MongoDB driver installed. You can find installation instructions [here](http://php.net/manual/en/mongodb.installation.php).
2. Composer
3. MongoDB (version 3.4 or higher). Get it [here](https://docs.mongodb.com/manual/installation/).
4. A Pusher account. Create one [here](http://pusher.com/signup).


## 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.](https://github.com/shalvah/shoots/archive/part-1-users-spreadsheets.zip)

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](https://github.com/handsontable/handsontable) `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`):


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

The name of the channel starts with `presence-`, to indicate that this is a [presence channel](https://pusher.com/docs/client_api_guide/client_presence_channels). 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:


    <head>
        <title>{{ $sheet->name }}</title>
        <meta name="csrf-token" content="{{ csrf_token() }}">
        <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/handsontable/2.0.0/handsontable.min.css" t
        <!-- Fonts -->
        <link rel="dns-prefetch" href="https://fonts.gstatic.com">
        <link href="https://fonts.googleapis.com/css?family=Raleway:300,400,600" rel="stylesheet" type="text/css">
    
        <!-- Styles -->
        <link href="{{ asset('css/app.css') }}" rel="stylesheet">
    </head>
    
    <br>
    <h2>{{ $sheet->name }}</h2>
    <div id="sheet"></div>
    
    <script src="https://cdnjs.cloudflare.com/ajax/libs/handsontable/2.0.0/handsontable.min.js"></script>
    <script>
        let csrfToken = document.head.querySelector('meta[name="csrf-token"]').content;
        let sheetContent = @json($sheet->content);
    
        let container = document.getElementById('sheet');
        let table = new Handsontable(container, {
            data: sheetContent,
            rowHeaders: true,
            colHeaders: true,
            minCols: 20,
            minRows: 20,
            afterChange: function (change, source) {
                if (source === 'loadData') return;
    
                console.log(change, source);
    
                fetch('/sheets/{{ $sheet->_id }}', {
                    method: 'PUT',
                    body: JSON.stringify({ change: change[0] }),
                    headers: {
                        'X-CSRF-TOKEN': csrfToken,
                        'Content-Type': 'application/json'
                    },
                    credentials: 'same-origin'
                })
            }
        });
    </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:


      |   A   |   B   |   C 
    ---------------------------
    1 |
    2 |
    3 |

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


      |   A   |   B   |   C 
    ---------------------------
    1 |
    2 | hi
    3 |

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


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

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

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

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](https://developer.mozilla.org/en-US/docs/Web/API/Fetch_API) 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:


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

Sign in to [your Pusher dashboard](http://app.pusher.com) and create a new app. Copy your app credentials from the **App Keys** section and add them to your `.env` file:


    PUSHER_APP_ID=your-app-id
    PUSHER_APP_KEY=your-app-key
    PUSHER_APP_SECRET=your-app-secret
    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:


    public function update($id)
    {
        $sheet = Sheet::findOrFail($id);
        $change = \request('change');
        [$rowIndex, $columnIndex, $oldValue, $newValue] = $change;
        $sheet->content = $this->updateCell($rowIndex, $columnIndex, $newValue, $sheet->content);
        $sheet->save();
        \Pusher::trigger($sheet->channel_name, 'updated', ['change' => $change]);
        return response()->json(['sheet' => $sheet]);
    }
    
    protected function updateCell($rowIndex, $columnIndex, $newValue, $sheetContent)
    {
        // we expand the sheet to reach the farthest cell
        for ($row = 0; $row <= $rowIndex; $row++) {
           // create the row if it doesnt exist
            if (!isset($sheetContent[$row])) {
                $sheetContent[$row] = [];
            }
            for ($column = 0; $column <= $columnIndex; $column++) {
                if (!isset($sheetContent[$row][$column])) {
                    // create the column if it doesnt exist
                    $sheetContent[$row][$column] = null;
                }
            }
        }
        $sheetContent[$rowIndex][$columnIndex] = $newValue;
        return $sheetContent;
    }

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: we're using a pretty simplified method here: replace the old content of the cell with the new content. In a large app, though, this could lead to clashes, so we would typically use a more sophisticated algorithm such as operational transformation (beyond the scope of this article).

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


    
    <script src="https://js.pusher.com/4.2/pusher.min.js"></script>
    <script>
        let pusher = new Pusher('your-app-key', {
            cluster: 'your-app-cluster',
            authEndpoint: '/sheets/{{ $sheet->_id }}/subscription_auth',
            auth: {
                headers: {
                    'X-CSRF-Token': csrfToken
                }
            }
        });
        pusher.subscribe("{{ $sheet->channel_name }}")
            .bind('updated', function (message) {
                let [rowIndex, columnIndex, oldValue, newValue] = message.change;
                addCellValue(rowIndex, columnIndex, newValue);
                table.loadData(sheetContent)
        });
      
        function addCellValue(rowIndex, columnIndex, newValue) {
          // we expand the sheet to reach the farthest cell
          for (let row = 0; row <= rowIndex; row++) {
              if (!sheetContent[row]) sheetContent[row] = [];
              for (let column = 0; column <= columnIndex; column++) {
                  if (!sheetContent[row][column])
                    sheetContent[row][column] = null;
              }
          }
          sheetContent[rowIndex][columnIndex] = newValue;
    }
    </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`:


    
    public function authenticateForSubscription($id)
    {
        $authSignature = \Pusher::presence_auth(
            \request('channel_name'),
            \request('socket_id'),
            \Auth::user()->_id,
            \Auth::user()->toArray()
        );
        return response()->json(json_decode($authSignature));
    }

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.](https://github.com/shalvah/shoots/tree/part-2-realtime-collaboration)


_This article first appeared on [Pusher's blog](https://pusher.com/tutorials/realtime-spreadsheets-part-2)._