Blog

Read Google Sheets Data in Laravel with the GKit SheetsAPI

Use Google Sheets as a live data layer in Laravel - Http facade, Eloquent-style collections, and optional Cache facade caching.

Jun 30, 20264 min read

#laravel #php #google-sheets #api #tutorial

Google Sheets is a practical lightweight data store for small to medium datasets - your team can edit rows directly, there are no migrations to write, and the data is always visible. GKit SheetsAPI wraps your sheet in a REST endpoint so any backend can query it like a JSON API. Laravel's Http facade makes it trivial to build a clean, testable wrapper around that endpoint. This tutorial walks through a complete employee directory - service class, PHP 8.2 DTO, controller routes, and optional caching.

Sheet setup

Create a Google Sheet with the following columns in row 1, then connect it to GKit to get your user key and API token.

name	department	role	email	start_date
Alice Tan	Engineering	Senior Engineer	alice@example.com	2022-03-15
Bob Reyes	Marketing	Content Lead	bob@example.com	2023-07-01
Carol Müller	Engineering	Staff Engineer	carol@example.com	2021-11-20

Name the sheet tab employees. GKit uses that tab name as the {sheetName} path segment.

The SheetsAPI contract

Every request follows the same shape:

GET https://api.gkit.io/api/spreadsheets/{userKey}/{sheetName}
Authorization: Bearer sk_...

Query parameters:

Param	Example	Purpose
`search`	`department:Engineering`	Filter by field value
`sort`	`start_date:desc`	Sort by field
`limit`	`20`	Page size
`offset`	`40`	Skip N rows
`fields`	`name,email,role`	Return only these columns

The response is always:

{
  "data": [...],
  "meta": { "total": 42, "limit": 20, "offset": 0 }
}

Store credentials in `.env`

Add two environment variables so the key is never hard-coded in source:

# .env
GKIT_API_KEY=sk_your_key_here
GKIT_USER_KEY=your_user_key_here

Expose them through config/services.php so they are accessible via the config() helper - the preferred Laravel pattern over calling env() directly in application code:

// config/services.php
return [
    // ... other services
 
    'gkit' => [
        'api_key'  => env('GKIT_API_KEY'),
        'user_key' => env('GKIT_USER_KEY'),
        'base_url' => 'https://api.gkit.io/api/spreadsheets',
    ],
];

The `SheetsApiService` class

Create a service class that wraps the Http facade. All query-parameter forwarding lives here so controllers stay thin.

<?php
 
namespace App\Services;
 
use Illuminate\Http\Client\Response;
use Illuminate\Support\Facades\Http;
 
class SheetsApiService
{
    private string $baseUrl;
    private string $userKey;
    private string $apiKey;
 
    public function __construct()
    {
        $this->baseUrl  = config('services.gkit.base_url');
        $this->userKey  = config('services.gkit.user_key');
        $this->apiKey   = config('services.gkit.api_key');
    }
 
    /**
     * Query any sheet tab and return the decoded response array.
     *
     * @param  string               $sheetName  Tab name in your Google Sheet
     * @param  array<string, mixed> $params     search, sort, limit, offset, fields
     * @return array{ data: array<int, array<string, mixed>>, meta: array<string, int> }
     */
    public function query(string $sheetName, array $params = []): array
    {
        $response = Http::withToken($this->apiKey)
            ->get("{$this->baseUrl}/{$this->userKey}/{$sheetName}", array_filter($params));
 
        $response->throw(); // bubbles as HttpClientException on 4xx/5xx
 
        return $response->json();
    }
}

array_filter($params) removes any null values so only the params you explicitly pass are sent. withToken() sets the Authorization: Bearer ... header. throw() converts error responses into exceptions that Laravel's exception handler will catch and convert to a 500 response automatically.

`Employee` DTO

PHP 8.2 readonly classes make excellent value objects. They are immutable, self-documenting, and easy to test.

<?php
 
namespace App\Data;
 
readonly class Employee
{
    public function __construct(
        public string  $name,
        public string  $department,
        public string  $role,
        public string  $email,
        public string  $startDate,
    ) {}
 
    /**
     * Construct an Employee from a raw SheetsAPI row array.
     *
     * @param array<string, mixed> $row
     */
    public static function fromRow(array $row): self
    {
        return new self(
            name:       $row['name']       ?? '',
            department: $row['department'] ?? '',
            role:       $row['role']       ?? '',
            email:      $row['email']      ?? '',
            startDate:  $row['start_date'] ?? '',
        );
    }
}

Add a typed collection helper so callers always receive Collection<int, Employee> rather than plain arrays:

<?php
 
namespace App\Data;
 
use Illuminate\Support\Collection;
 
class EmployeeCollection
{
    /**
     * @param  array<int, array<string, mixed>> $rows
     * @return Collection<int, Employee>
     */
    public static function fromRows(array $rows): Collection
    {
        return collect($rows)->map(fn (array $row) => Employee::fromRow($row));
    }
}

Controller

use App\Http\Controllers\EmployeeController;
 
Route::get('/employees',       [EmployeeController::class, 'index']);
Route::get('/employees/{name}', [EmployeeController::class, 'show']);

Then write the controller:

<?php
 
namespace App\Http\Controllers;
 
use App\Data\Employee;
use App\Data\EmployeeCollection;
use App\Services\SheetsApiService;
use Illuminate\Http\JsonResponse;
use Illuminate\Http\Request;
 
class EmployeeController extends Controller
{
    public function __construct(private SheetsApiService $sheets) {}
 
    /**
     * GET /api/employees?department=Engineering
     */
    public function index(Request $request): JsonResponse
    {
        $params = array_filter([
            'search' => $request->query('department')
                ? "department:{$request->query('department')}"
                : null,
            'sort'   => $request->query('sort', 'name:asc'),
            'limit'  => $request->query('limit', 50),
            'offset' => $request->query('offset', 0),
        ]);
 
        $response  = $this->sheets->query('employees', $params);
        $employees = EmployeeCollection::fromRows($response['data']);
 
        return response()->json([
            'data' => $employees->values(),
            'meta' => $response['meta'],
        ]);
    }
 
    /**
     * GET /api/employees/{name}
     * Uses the search param to find a row by exact name match.
     */
    public function show(string $name): JsonResponse
    {
        $response = $this->sheets->query('employees', [
            'search' => "name:{$name}",
            'limit'  => 1,
        ]);
 
        if (empty($response['data'])) {
            return response()->json(['message' => 'Employee not found.'], 404);
        }
 
        return response()->json(Employee::fromRow($response['data'][0]));
    }
}

The ?department= query string is forwarded to search=department:Engineering - the controller does the translation so the API consumer never needs to know the SheetsAPI syntax.

Optional: Cache with `Cache::remember()`

SheetsAPI already handles rate limiting, but caching on the Laravel side is useful when the same sheet data is requested frequently and does not need to be real-time.

Add a thin caching layer inside SheetsApiService:

use Illuminate\Support\Facades\Cache;
 
public function queryCached(string $sheetName, array $params = [], int $ttl = 300): array
{
    $cacheKey = "sheets:{$sheetName}:" . md5(serialize($params));
 
    return Cache::remember($cacheKey, $ttl, fn () => $this->query($sheetName, $params));
}

Cache::remember() returns the cached value if the key exists, or runs the closure, stores the result, and then returns it. The default TTL is 300 seconds (5 minutes). Swap $this->query(...) for $this->queryCached(...) in any controller action where staleness of a few minutes is acceptable:

$response = $this->sheets->queryCached('employees', $params, ttl: 300);

Clear a specific sheet's cache when you know the data has changed:

Cache::forget("sheets:employees:" . md5(serialize($params)));

Or flush all sheet caches with a tagged cache driver (Redis, Memcached) by tagging entries:

Cache::tags(['sheets', "sheets:{$sheetName}"])->remember($cacheKey, $ttl, fn () => ...);
Cache::tags("sheets:employees")->flush();

What's next

Validation - add $request->validate(['department' => 'string|max:100']) before forwarding query parameters.
Pagination - the meta.total, meta.limit, and meta.offset fields from SheetsAPI map directly to Laravel's LengthAwarePaginator if you want to return a standard paginated response.
Testing - use Http::fake() to mock SheetsAPI responses in unit tests without hitting the network.

GKit SheetsAPI gives you a queryable REST layer over any Google Sheet in minutes. Combined with Laravel's Http facade and the Cache facade, you can build a maintainable, cache-friendly data layer with minimal boilerplate. Sign up at gkit.io to get your API key and connect your first sheet.

All posts