Build a realtime spreadsheets application - Part 1: Users and spreadsheets

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

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

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

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:

php
1protected function create()
2     {
3        return \App\Models\User::create([
4            'name' => $data['name'],
5            'email' => $data['email'],
6            'password' => Hash::make($data['password']),
7            'viewed_sheets' => []
8        ]);
9    }
Copy

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

php
1protected $fillable = [
2        'name', 'email', 'password', 'viewed_sheets'
3    ];
Copy

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:

php
1@extends('layouts.app')
2    
3    @section('content')
4        <div class="container">
5            <div class="row justify-content-center">
6                <div class="col-md-8">
7                    <div class="card">
8                        <div class="card-header">Dashboard</div>
9    
10                        <div class="card-body">
11                            <div class="text-center">
12                                <a class="btn btn-lg btn-primary" href="{{ route('sheets.new') }}">Create new spreadsheet</a>
13                            </div>
14    
15                            <div class="list-group">
16                                @if($sheets = \Auth::user()->viewedSheets())
17                                    @foreach($sheets as $sheet)
18                                            <a href="/sheets/{{ $sheet->_id }}" class="list-group-item">
19                                                {{ $sheet->name }}
20                                            </a>
21                                    @endforeach
22                                @endif
23                            </div>
24                        </div>
25                    </div>
26                </div>
27            </div>
28        </div>
29    @endsection
Copy

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
1<?php
2    
3    namespace App\Models;
4    
5    use Jenssegers\Mongodb\Eloquent\Model;
6    
7    class Sheet extends Model
8    {
9        protected $guarded = [];
10    }
Copy

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

php
1public function viewedSheets()
2    {
3        return \App\Models\Sheet::whereIn('_id', $this->viewed_sheets)->get();
4    }
Copy

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:

bash
1| A | B | C 
2--------------
31 |
42 |
53 |
64 |
Copy

The columns and rows will be represented as:

bash
1content = [
2      [
3        'A1', 'B1', 'C1'
4      ],
5      [
6        'A2', 'B2', 'C2'
7      ],
8      [
9        'A3', 'B3', 'C3',
10      ],,
11      [
12        'A4', 'B4', 'C4',
13      ],
14    ];
Copy

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:

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

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

php
1<?php
2    
3    namespace App\Http\Controllers;
4    
5    use App\Models\Sheet;
6    use Illuminate\Http\Request;
7    use Illuminate\Support\Facades\Auth;
8    
9    class SheetsController extends Controller
10    {
11    
12        public function __construct()
13        {
14            $this->middleware('auth');
15        }
16    
17        public function newSheet()
18        {
19            $sheet = Sheet::create([
20                'name' => 'Untitled spreadsheet',
21                '_owner' => Auth::user()->_id,
22                'content' => [[]]
23            ]);
24            return redirect(route('sheets.view', ['sheet' => $sheet]));
25        }
26    
27        public function view(Sheet $sheet)
28        {
29            Auth::user()->push('viewed_sheets', $sheet->_id);
30            return view('spreadsheet', ['sheet' => $sheet]);
31        }
32    
33        public function update($id)
34        {
35            $sheet = Sheet::where('_id', $id)->update(['content' => \request('content') ?: [[]]]);
36            return response()->json(['sheet' => $sheet]);
37        }
38    }
Copy

What’s left now is the view. We’ll be making use of Handsontable, a library that provides us with a spreadsheet interface. Create the file spreadsheet.blade.php with the following content:

php
1<head>
2        <title>{{ $sheet->name }}</title>
3        <meta name="csrf-token" content="{{ csrf_token() }}">
4        <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/handsontable/2.0.0/handsontable.min.css" t
5        <!-- Fonts -->
6        <link rel="dns-prefetch" href="https://fonts.gstatic.com">
7        <link href="https://fonts.googleapis.com/css?family=Raleway:300,400,600" rel="stylesheet" type="text/css">
8    
9        <!-- Styles -->
10        <link href="{{ asset('css/app.css') }}" rel="stylesheet">
11    </head>
12    
13    <br>
14    <h2>{{ $sheet->name }}</h2>
15    <div id="sheet"></div>
16    
17    <script src="https://cdnjs.cloudflare.com/ajax/libs/handsontable/2.0.0/handsontable.min.js"></script>
18    <script>
19        let csrfToken = document.head.querySelector('meta[name="csrf-token"]').content;
20        let data = @json($sheet->content);
21    
22        let container = document.getElementById('sheet');
23        let table = new Handsontable(container, {
24            data: data,
25            rowHeaders: true,
26            colHeaders: true,
27            minCols: 20,
28            minRows: 20,
29            afterChange: function (change, source) {
30                if (source === 'loadData') return;
31    
32                console.log(change, source);
33    
34                fetch('/sheets/{{ $sheet->_id }}', {
35                    method: 'PUT',
36                    body: JSON.stringify({content: data}),
37                    headers: {
38                        'X-CSRF-TOKEN': csrfToken,
39                        'Content-Type': 'application/json'
40                    },
41                    credentials: 'same-origin'
42                })
43            }
44        });
45    </script>
Copy

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