Pusher Logo

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

    To follow this tutorial you will need PHP 7.2 or higher, with the MongoDB driver enabled. You will also need Composer, MongoDB and a Pusher account.

    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:

    realtime-spreadsheets-part-2-demo

    Prerequisites

    1. PHP 7.2 or higher, with the MongoDB driver installed. You can find installation instructions here.
    2. Composer
    3. MongoDB (version 3.4 or higher). Get it here.
    4. 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):

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

    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:

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

    Laravel PHP Online Presence Collaboration

    30 April 2018

    by Shalvah Adebayo