This package provides an Eloquent model that sits on top of a Google Sheet. In order for it to work, there are two things your sheet needs to have. One is 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. The other is a primary key column. Eloquent assumes that your primary key column is named id. If it's not, set it in your model like you would normally.

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:

1. You can call the invalidateCache() method on the model with something like like YourGoogleSheetModel::first()->invalidateCache()

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

composer require grosv/eloquent-sheets

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

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();
}

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.

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.

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