How to build a realtime spreadsheets application with Pusher Channels - Part 1: Users and spreadsheets
Published: 2018-06-06 14:09:00

Apps like Google Docs and Google Sheets are very popular today, partly because they allow users to easily share documents for others to collaborate. They also enable multiple users to work on the same document simultaneously without clashes or fear of lost data.

In this four\-part guide, we’ll build a spreadsheet app that works similar to Google Sheets. A user can create spreadsheets and share the link to the sheet with someone else, giving that person the ability to collaborate on the sheet in realtime without overwriting each other’s edits. Our app will also display the users who are currently viewing the sheet.

## 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 the app

Laravel by default uses SQL databases as the backend for its Eloquent models, but we’re using MongoDB in this project, so we’ll start off with a Laravel installation configured to use MongoDB. Clone the repo by running:

```
git clone https://github.com/shalvah/laravel-mongodb-starter.git
```

You can also download the source directly from [this link.](https://github.com/shalvah/laravel-mongodb-starter/archive/master.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.

## User authentication

We’ll take advantage of the inbuilt user authentication system that comes with Laravel by running:

```
php artisan make:auth
```

We’ll need to configure a few things. Replace the `create` method of your `app/Http/Controllers/Auth/RegisterController.php` with the following:

```
 protected function create()
 {
    return \App\Models\User::create([
        'name' => $data['name'],
        'email' => $data['email'],
        'password' => Hash::make($data['password']),
        'viewed_sheets' => []
    ]);
}
```

Then in your `app/Models/User.php`, add `'``viewed_sheets``'` \`\`as an entry in the `$fillable` array:

```
protected $fillable = [
    'name', 'email', 'password', 'viewed_sheets'
];
```

The `viewed_sheets` property is where we’ll store the IDs of all sheets the user has opened recently, so we can display them on the user’s dashboard.

## Building the user dashboard

Now, let’s build the dashboard that a user sees when they log in. Similar to Google Sheets, we’ll display a list of spreadsheets they’ve viewed recently, along with a button to create a new spreadsheet. Replace the contents of your `resources/views/home.blade.php` with the following:

```
@extends('layouts.app')

@section('content')
    <div class="container">
        <div class="row justify-content-center">
            <div class="col-md-8">
                <div class="card">
                    <div class="card-header">Dashboard</div>

                    <div class="card-body">
                        <div class="text-center">
                            <a class="btn btn-lg btn-primary" href="{{ route('sheets.new') }}">Create new spreadsheet</a>
                        </div>

                        <div class="list-group">
                            @if($sheets = \Auth::user()->viewedSheets())
                                @foreach($sheets as $sheet)
                                        <a href="/sheets/{{ $sheet->_id }}" class="list-group-item">
                                            {{ $sheet->name }}
                                        </a>
                                @endforeach
                            @endif
                        </div>
                    </div>
                </div>
            </div>
        </div>
    </div>
@endsection
```

We’ll add a new method to our `User` model, `viewedSheets`. This method will search for all sheets whose IDs are in the `viewed_sheets` property and retrieve them. First, create the `Sheet` model \(`app/Models/Sheet.php)` with the following content:

```
<?php

namespace App\Models;

use Jenssegers\Mongodb\Eloquent\Model;

class Sheet extends Model
{
    protected $guarded = [];
}
```

Then add the `viewedSheets` method to your `app/Models/User.php`:

```
public function viewedSheets()
{
    return \App\Models\Sheet::whereIn('_id', $this->viewed_sheets)->get();
}
```

## Working with sheets

We have a few more things to achieve at this point:

* Clicking the **Create a Spreadsheet** button should create a new, empty sheet and open it up for editing
* Clicking on a spreadsheet in the list of recently viewed sheets should also open it up for editing
* Our app will have autosave enabled; we’ll save changes to a sheet via AJAX as the user makes them. This means we need an endpoint for updating our sheets

Our sheets will have the following properties:

* An **ID.** MongoDB automatically generates this for us as `_id`
* A **name.** \(for now, sheets will be called “Untitled spreadsheet”\)
* An **owner**. We’ll store this as `_owner`. \(The `_` indicates that it’s an ID.\)
* **Content** in rows and columns. We’ll store this as an array of rows. Each rowis an array with each entry being a column.

For instance, with a table like this:

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

The columns and rows will be represented as:

```
content = [
  [
    'A1', 'B1', 'C1'
  ],
  [
    'A2', 'B2', 'C2'
  ],
  [
    'A3', 'B3', 'C3',
  ],,
  [
    'A4', 'B4', 'C4',
  ],
];
```

Let’s create the routes we need: one each for creating, viewing and updating a sheet. Add the following to the end of your `routes/web.php`:

```
Route::get('sheets/new', 'SheetsController@newSheet')->name('sheets.new');
Route::get('sheets/{sheet}', 'SheetsController@view')->name('sheets.view');
Route::put('sheets/{id}', 'SheetsController@update');
```

Now, we’ll implement the logic for these in the controller. Create the file `app/Http/Controllers/SheetsController.php` with the following content:

```
<?php

namespace App\Http\Controllers;

use App\Models\Sheet;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Auth;

class SheetsController extends Controller
{

    public function __construct()
    {
        $this->middleware('auth');
    }

    public function newSheet()
    {
        $sheet = Sheet::create([
            'name' => 'Untitled spreadsheet',
            '_owner' => Auth::user()->_id,
            'content' => [[]]
        ]);
        return redirect(route('sheets.view', ['sheet' => $sheet]));
    }

    public function view(Sheet $sheet)
    {
        Auth::user()->push('viewed_sheets', $sheet->_id);
        return view('spreadsheet', ['sheet' => $sheet]);
    }

    public function update($id)
    {
        $sheet = Sheet::where('_id', $id)->update(['content' => \request('content') ?: [[]]]);
        return response()->json(['sheet' => $sheet]);
    }
}
```

What’s left now is the view. We’ll be making use of [Handsontable](https://github.com/handsontable/handsontable), a library that provides us with a spreadsheet interface. Create the file `spreadsheet.blade.php` with the following content:

```
<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 data = @json($sheet->content);

    let container = document.getElementById('sheet');
    let table = new Handsontable(container, {
        data: data,
        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({content: data}),
                headers: {
                    'X-CSRF-TOKEN': csrfToken,
                    'Content-Type': 'application/json'
                },
                credentials: 'same-origin'
            })
        }
    });
</script>
```

Here’s what’s happening here:

* We initialize our Handsontable spreadsheet containing the data in the `content` of our sheet. The `data` variable is bound as a reference. This means that whenever a user makes a change to the spreadsheet, the value of `data` is automatically updated by Handsontable to include the new changes
* We listen for the `afterChange` event. This event is fired whenever a user finishes editing a cell \(for instance, he changes a value in a cell and presses **Enter**\). When this event is fired, we grab the current value of `data` and make the HTTP request \(using [Fetch](https://developer.mozilla.org/en-US/docs/Web/API/Fetch_API)\) to our backend to update the sheet in the database.

Start your MongoDB server by running `mongod`. Note: on Linux/macOS, you might need to run it as `sudo`.

Then start your app by running:

```
php artisan serve
```

Create a new user at http://localhost:8000/register. You should be able to create a new spreadsheet and edit it. On refreshing the page, you’ll see the changes you make are saved.

## Conclusion

In the next part, we’ll add collaboration to our app. We’ll see how we can enable realtime editing of the same document by different users using Pusher. You can check out the source code of the app thus far [here](https://github.com/shalvah/shoots/tree/part-1-users-spreadsheets).

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