Skip to content
Sushi-powered Eloquent Models for Google Sheets
PHP
Branch: master
Clone or download

Latest commit

Files

Permalink
Type Name Latest commit message Commit time
Failed to load latest commit information.
config Apply fixes from StyleCI Mar 2, 2020
src Do not try to delete the cache file if it does not exist Mar 31, 2020
tests Apply fixes from StyleCI Mar 31, 2020
.gitignore remove cache from repo Mar 2, 2020
README.md Allow for an array of headers to be defined in the model instead of a… Mar 28, 2020
composer.json Update composer.json Mar 31, 2020
phpunit.xml.dist

README.md

Eloquent Models for your Google Sheets

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

Latest Version on Packagist StyleCI Build Status

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:

  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.

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.

Screen Shot 2020-03-03 at 3 02 15 PM

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()):

Screen Shot 2020-03-11 at 4 48 48 PM


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

Screen Shot 2020-03-03 at 3 11 10 PM


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

Screen Shot 2020-03-03 at 3 13 33 PM


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

Screen Shot 2020-03-03 at 3 14 47 PM


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.

Screen Shot 2020-03-03 at 3 20 58 PM


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.

You can’t perform that action at this time.