Build a realtime spreadsheets application - Part 4: Automatically cleaning up empty documents

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

Welcome to the last part of our quest to build a Google Sheets-like spreadsheets editor. In the earlier parts of this series, we’ve implemented simultaneous editing by multiple users in realtime.

In this article, we’ll expand our app functionality even more by using Pusher to automatically clean up empty documents, so we don’t leave them lying around in the user’s history (and our database).

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 earlier parts of this series and still have your project code, you can skip this section.

Clone the project from GitHub:

    git clone -b part-3-whos-viewing 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. Add your Pusher app credentials to the .env file:

bash
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
Copy

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

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

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.

The problem

Currently, here’s what happens when a user clicks the Create New Spreadsheet button on the dashboard:

• A new spreadsheet is created in the database.
• The user is redirected to the spreadsheet’s view, where they can edit the spreadsheet.
• As the user edits the sheet, changes are synced to the backend and saved to the database.

Question: what happens if the user changes their mind after step two and closes the browser or leaves the page without making any changes to the sheet?

Right now, nothing special. The document still exists, and it shows up in the user’s list of documents on the dashboard.

However, in web applications like Gmail (when composing a new email) or Google Docs/Sheets (editing a new document), if you exit the page without making any changes, the document or draft is automatically discarded.

So, let’s do this. Rather than clog the user’s document history with empty documents, we’ll get rid of documents that the user leaves unchanged.

Our solution

To implement this, we’ll be making use of Pusher’s channel existence webhooks, specifically the [channel_vacated](https://pusher.com/docs/webhooks#channel_vacated) event. This event is fired whenever all the members of a channel have left. Here’s how we’ll implement this:

• When all users have left the document channel (by closing the document), Pusher will fire the channel_vacated event to our webhook.
• Our webhook code will retrieve the corresponding sheet from the database and check if it’s still empty. If it is, we delete it.

Let’s do this. First, we’ll add a utility method to our Sheet model (app/Models/Sheet.php) to determine if a sheet is empty or not:

php
1public function isEmpty(): bool
2    {
3        if ($this->content == [[]]) {
4            return true;
5        }
6    
7        foreach ($this->content as $row) {
8            foreach ($row as $cell) {
9                if (!is_null($cell)) {
10                    return false;
11                }
12            }
13        }
14    
15        return true;
16    }
Copy

Next, we’ll implement the webhook route in our routes/api.php:

php
1<?php
2    
3    use App\Models\Sheet;
4    use Illuminate\Http\Request;
5    
6    Route::post('sheets/webhook', function (Request $request) {
7        $body = $request->post();
8        foreach ($body['events'] as $event) {
9            if ($event['name'] == 'channel_vacated') {
10                $sheetId = str_replace('presence-sheet-', '', $event['channel']);
11                $sheet = Sheet::find($sheetId);
12                if ($sheet->isEmpty()) {
13                    $sheet->delete();
14                }
15            }
16            http_response_code(200);
17        }});
Copy

Note: we’re putting this route in routes/api.php rather than routes/wen.php because the route will not be called from a browser and we don’t want the web middleware provided by Laravel to apply to it.

Lastly, we need to register this webhook with Pusher. Since the application currently lives on our local machine, we need a way of exposing it via a public URL. Ngrok is an easy-to-use tool that helps with this. Sign up on http://ngrok.com and follow the instructions to install ngrok. Then expose http://localhost:8000 on your machine by running:

    ./ngrok http 8000

You should see output like this:

Copy the second Forwarding URL (the one using HTTPS). Your webhook URL will then be <your-ngrok-url>/api/sheets/webhook (for instance, for the screenshot above, my webhook URL is https://fa74c4e1.ngrok.io/api/sheets/webhook).

Now you need to register the webhook on Pusher. Log into your Pusher app dashboard and select your spreadsheets app. Click on the Webhooks tab and select the channel existence radio button. In the text box, paste the URL of the webhook you obtained above, and click Add.

Now, let’s test it 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, in a new terminal window:

    php artisan serve

Sign in to your app at http://localhost:8000/login (or register at http://localhost:8000/register if you haven’t signed up before) and create a new spreadsheet. Navigate away from the page by visiting another URL. Then come back to the dashboard (http://localhost:8000/home). You should see that the spreadsheet you just created does not show up.

Note: you might need to refresh our app dashboard one or more times before the document is removed from the list. This is because, since we’re loading our dashboard from our local machine, it’s much faster than the deletion process (which has to travel over the Internet to Pusher and back). In a production server, this typically wouldn’t happen.

Conclusion

Over the course of this four-part series, we’ve built a spreadsheets application that leverages different features of Pusher to provide some of the functionality of Google Sheets. I hope you’ve followed along and enjoyed this series as much as I have! You can check out the full source code on GitHub.