Olamilekan Lamidi

Posted on May 20

Building A Laravel Google Sheets Package That Imports, Exports, Caches, Formats, And Tests Cleanly

#laravel #php #developertools #opensource

Google Sheets often starts as a quick operational tool. A support team tracks users in a sheet, finance exports monthly reports, or an internal dashboard needs a simple spreadsheet backend. The challenge is keeping that workflow Laravel-friendly once it grows beyond a few API calls.

This package wraps the Google Sheets API with a fluent Laravel API for common application tasks: importing rows, exporting reports, managing multiple spreadsheet connections, caching reads, formatting tabs, and testing without hitting Google.

Installation

composer require olamilekan/laravel-google-sheets
php artisan vendor:publish --tag=google-sheets-config

Add your service account credentials path:

GOOGLE_SHEETS_CREDENTIALS_PATH=/path/to/service-account.json

Then configure named spreadsheet connections:

'sheets' => [
    'users' => [
        'spreadsheet_id' => env('GOOGLE_SHEETS_USERS_SPREADSHEET_ID'),
        'sheet' => 'Users',
    ],

    'reports' => [
        'spreadsheet_id' => env('GOOGLE_SHEETS_REPORTS_SPREADSHEET_ID'),
        'sheet' => 'Monthly',
    ],
],

Import Users From Google Sheets

For a simple import, read rows from a named connection:

$rows = GoogleSheets::connection('users')->all();

For a reusable import, create an import class:

use App\Models\User;
use Olamilekan\GoogleSheets\Imports\SheetImport;

class UsersImport extends SheetImport
{
    public function rules(): array
    {
        return ['email' => ['required', 'email']];
    }

    public function model(array $row): User
    {
        return User::updateOrCreate(
            ['email' => $row['email']],
            ['name' => $row['name']]
        );
    }
}

Run it from code:

GoogleSheets::import(new UsersImport(), 'users');

Or from Artisan:

php artisan google-sheets:sync "App\\Imports\\UsersImport" users

Export Reports To Google Sheets

Export classes keep reporting logic out of controllers and commands:

use App\Models\Report;
use Olamilekan\GoogleSheets\Exports\SheetExport;

class ReportsExport extends SheetExport
{
    public bool $replace = true;

    public function headings(): array
    {
        return ['Date', 'Name', 'Total'];
    }

    public function collection()
    {
        return Report::query()
            ->latest()
            ->get()
            ->map(fn (Report $report) => [
                $report->created_at->toDateString(),
                $report->name,
                $report->total,
            ]);
    }
}

Then export:

GoogleSheets::export(new ReportsExport(), 'reports');

Header-Aware Appends And Upserts

Sheets usually have headers. Instead of manually ordering every cell, append associative arrays:

GoogleSheets::connection('users')->appendAssoc([
    ['name' => 'Alice', 'email' => 'alice@example.com', 'role' => 'admin'],
]);

Upsert rows by a key column:

GoogleSheets::connection('users')->upsert('email', [
    ['name' => 'Alice Updated', 'email' => 'alice@example.com', 'role' => 'owner'],
    ['name' => 'Bob', 'email' => 'bob@example.com', 'role' => 'user'],
]);

Validation And Required Headers

Catch bad spreadsheet data before it reaches your app:

GoogleSheets::connection('users')->requireHeaders(['name', 'email', 'role']);

$rows = GoogleSheets::connection('users')->validate([
    'name' => ['required', 'string'],
    'email' => ['required', 'email'],
]);

Multi-Connection Workflows

Named connections make it easy to separate workflows:

$users = GoogleSheets::connection('users')->all();

GoogleSheets::connection('reports')->append([
    ['2026-05-17', 'Monthly Revenue', 15000],
]);

Caching

Enable caching with Laravel's cache system:

GOOGLE_SHEETS_CACHE_ENABLED=true
GOOGLE_SHEETS_CACHE_STORE=redis
GOOGLE_SHEETS_CACHE_TTL=600

Or turn it on per call:

$rows = GoogleSheets::connection('users')->enableCache(300)->all();

When writes happen, the package clears remembered read cache keys for that spreadsheet so later reads can refresh.

Formatting, Formulas, And Named Ranges

Reports often need more than raw data:

GoogleSheets::connection('reports')
    ->sheet('Monthly')
    ->boldHeader()
    ->freezeRows()
    ->autoResizeColumns(1, 4);

GoogleSheets::connection('reports')->append([
    ['Total', GoogleSheets::formula('SUM(C2:C100)')],
]);

$summary = GoogleSheets::connection('reports')
    ->namedRange('MonthlySummary')
    ->get();

Testing

You can fake Google Sheets in tests:

$fake = GoogleSheets::fake([
    'users' => [
        ['name' => 'Alice', 'email' => 'alice@example.com'],
    ],
]);

GoogleSheets::connection('users')->appendAssoc([
    ['name' => 'Bob', 'email' => 'bob@example.com'],
]);

$fake->assertAppended('users', ['name' => 'Bob', 'email' => 'bob@example.com']);

Useful Commands

php artisan google-sheets:list users
php artisan google-sheets:clear reports --sheet=Monthly --range=A2:D100
php artisan google-sheets:sync "App\\Exports\\ReportsExport" reports

Closing

The goal is to make Google Sheets feel like a natural Laravel integration: fluent for simple reads and writes, structured for import and export classes, cache-aware for production use, and fakeable in tests.

DEV Community