How to build a realtime spreadsheets application with Pusher Channels - Part 4: Automatically cleaning up empty documents
Tags: php, pusher, laravel, realtime
Published: 2018-06-06 14:31:43

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

![](https://d2mxuefqeaa7sj.cloudfront.net/s_75C08CE87DF85DBF796E5DB651FDC17E0FE378B8D0604878A0DCE11C3EE8A962_1524848789025_building-realtime-spreadsheets-app-laravel-cleaning-up-empty-documents.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 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.](https://github.com/shalvah/shoots/archive/part-3-whos-viewing.zip)

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:

```
PUSHER_APP_ID=your-app-id
PUSHER_APP_KEY=your-app-key
PUSHER_APP_SECRET=your-app-secret
PUSHER_APP_CLUSTER=your-app-cluster
```

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

```
let pusher = new Pusher('your-app-key', {
    cluster: 'your-app-cluster'
});
```

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:

1. A new spreadsheet is created in the database.
2. The user is redirected to the spreadsheet’s view, where they can edit the spreadsheet.
3. 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](https://pusher.com/docs/webhooks#channel-existence), 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:

```
public function isEmpty(): bool
{
    if ($this->content == [[]]) {
        return true;
    }

    foreach ($this->content as $row) {
        foreach ($row as $cell) {
            if (!is_null($cell)) {
                return false;
            }
        }
    }

    return true;
}
```

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

```
<?php

use App\Models\Sheet;
use Illuminate\Http\Request;

Route::post('sheets/webhook', function (Request $request) {
    $body = $request->post();
    foreach ($body['events'] as $event) {
        if ($event['name'] == 'channel_vacated') {
            $sheetId = str_replace('presence-sheet-', '', $event['channel']);
            $sheet = Sheet::find($sheetId);
            if ($sheet->isEmpty()) {
                $sheet->delete();
            }
        }
        http_response_code(200);
    }});
```

> 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](http://ngrok.com) is an easy\-to\-use tool that helps with this. Sign up on [http://ngrok.com](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:

![](https://d2mxuefqeaa7sj.cloudfront.net/s_75C08CE87DF85DBF796E5DB651FDC17E0FE378B8D0604878A0DCE11C3EE8A962_1524843624407_image.png)

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](https://app.pusher.com) 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:300/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](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](https://github.com/shalvah/shoots).

_This article was originally published on [Pusher's blog](https://pusher.com/tutorials/realtime-spreadsheets-part-4)._