Eloquent Models for your Google Sheets

A package that lets you lay Eloquent on top of a Google Sheet.

This package provides an Eloquent model that sits on top of a Google Sheet. You can use a heading row that holds the name of your columns. This defaults to row 1 (the top row) but it can be any row in the sheet. Or you can define an array of headings in your model. If you choose not to have a primary key column in your spreadsheet, each column will get an id (or whatever you're calling your primary key column in your model) auto-incremented.

When you use this package, an initial invocation of the model will read the sheet and store each row as a record in a table inside a file-based sqlite database. Subsequent invocations of the model use that sqlite database so changes to the spreadsheet won't be reflected in the database. However, there are two ways that you can invalidate the sqlite cache and cause it to be recreated:

You can call the invalidateCache() method on the model with something like like YourGoogleSheetModel::first()->invalidateCache()
A macro that you can attach to your Google sheet. The macro listens for edits within the sheet and when one happens, it sends a request to a route provided by this package that deletes the sqlite database forcing a fresh load the next time the model is used.

Installation

composer require grosv/eloquent-sheets

Configuration

This package relies on revolution/laravel-google-sheets. You must handle the configuration for that package and its dependencies for this package to work. Follow the instructions in their readme (though you can skip the composer require bit because I do that already in here).

Usage

Consider the following Google Sheet. We want to lay an Eloquent model on top of it.

php artisan make:sheet-model

Step 1 - Enter the full path to the directory where you want to create the model file (defaults to app_path()):

Step 2 - Enter the name you want to use for your model class:

Step 3 - Paste the edit url of your Google Sheet from the browser address bar:

Step 4 - Confirm that the path and full classname look right:

Step 5 - And you will receive the template of a macro that you can attach to your sheet that will tell your site that the sheet has changed so a new cache has to be built.

You can use something like this to enable the macro generated on your sheet as an installable trigger:

function createSpreadsheetOpenTrigger() {
  var ss = SpreadsheetApp.getActive();
  ScriptApp.newTrigger('onEdit')
      .forSpreadsheet(ss)
      .onEdit()
      .create();
}

The Resulting Model Class

use Grosv\EloquentSheets\SheetModel;

class YourGoogleSheetsModel extends SheetModel
{
    protected $spreadsheetId = '1HxNqqLtc614UVLoTLEItfvcdcOm3URBEM2Zkr36Z1rE'; // The id of the spreadsheet
    protected $sheetId = '0'; // The id of the sheet within the spreadsheet (gid=xxxxx on the URL)
    protected $headerRow = '1'; // The row containing the names of your columns (eg. id, name, email, phone)
}

This model can do your basic Eloquent model stuff because it really is an Eloquent model. Though it's currently limited to read / list methods. Update and insert don't currently work because you do those things by editing your spreadsheet.

What's Missing

Eventually I'd like to add insert and update methods that will let you append rows to your spreadsheet and edit existing rows. I already have the most important part done... a method to invalidate the cache when we update or insert. Now I just need the update and insert methods.

Acknowledgements

This package wouldn't be possible without Sushi by Caleb Porzio. If you're not sponsoring him on GitHub you should.

syahzul/eloquent-sheets