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
.envfile as theDB_USERNAMEandDB_PASSWORDrespectively.
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
afterChangeevent), 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></title>
<meta name="csrf-token" content="">
<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="" rel="stylesheet">
</head>
<br>
<h2></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/', {
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 (
nullif 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: 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
updateCellfunction, we expand the sheet to reach the farthest column, filling all empty cells withnull. 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//subscription_auth',
auth: {
headers: {
'X-CSRF-Token': csrfToken
}
}
});
pusher.subscribe("")
.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
authEndpointwhich Pusher will call to determine if the current user is permitted to subscribe to this sheet. - We bind to the
updatedevent and update the sheet’s content similar to how we did on the server (in theaddCellValuefunction), then we refresh the table UI by callingtable.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.
This article first appeared on Pusher's blog.
Top comments (0)