Google Sheets as a REST API in PHP and Laravel

composer require guzzlehttp/guzzle

<?php
 
use GuzzleHttp\Client;
 
$client  = new Client(['base_uri' => 'https://sheetsapi.gkit.mreshank.com']);
$userKey = 'YOUR_USER_KEY';
$apiKey  = 'sk_your_api_key_here';
 
$response = $client->get("/api/spreadsheets/{$userKey}/Products", [
    'headers' => ['Authorization' => "Bearer {$apiKey}"],
    'query'   => ['limit' => 20, 'sort' => 'name'],
]);
 
$body = json_decode($response->getBody(), associative: true);
 
echo "Total: {$body['meta']['total']}\n";
foreach ($body['data'] as $product) {
    echo "{$product['name']} - \${$product['price']}\n";
}

SHEETS_USER_KEY=YOUR_USER_KEY
SHEETS_API_KEY=sk_your_api_key_here
SHEETS_BASE_URL=https://sheetsapi.gkit.mreshank.com

// app/Services/SheetsApiService.php
<?php
 
namespace App\Services;
 
use Illuminate\Http\Client\Response;
use Illuminate\Support\Facades\Http;
 
class SheetsApiService
{
    private string $base;
    private string $userKey;
    private string $apiKey;
 
    public function __construct()
    {
        $this->base    = config('services.sheetsapi.base_url');
        $this->userKey = config('services.sheetsapi.user_key');
        $this->apiKey  = config('services.sheetsapi.api_key');
    }
 
    public function list(string $sheet, array $params = []): array
    {
        $response = Http::withToken($this->apiKey)
            ->get("{$this->base}/api/spreadsheets/{$this->userKey}/{$sheet}", $params)
            ->throw();
 
        return $response->json();
    }
 
    public function addRow(string $sheet, array $row): array
    {
        return Http::withToken($this->apiKey)
            ->post("{$this->base}/api/spreadsheets/{$this->userKey}/{$sheet}", $row)
            ->throw()
            ->json();
    }
}

'sheetsapi' => [
    'base_url' => env('SHEETS_BASE_URL'),
    'user_key' => env('SHEETS_USER_KEY'),
    'api_key'  => env('SHEETS_API_KEY'),
],

// app/Http/Controllers/ProductController.php
<?php
 
namespace App\Http\Controllers;
 
use App\Services\SheetsApiService;
use Illuminate\Http\JsonResponse;
use Illuminate\Http\Request;
 
class ProductController extends Controller
{
    public function __construct(private SheetsApiService $sheets) {}
 
    public function index(Request $request): JsonResponse
    {
        $validated = $request->validate([
            'search' => 'nullable|string|max:100',
            'sort'   => 'nullable|string|in:name,-name,price,-price',
            'page'   => 'nullable|integer|min:1',
        ]);
 
        $limit  = 20;
        $offset = ($validated['page'] - 1) * $limit;
 
        $params = [
            'limit'  => $limit,
            'offset' => $offset,
        ];
        if (!empty($validated['search'])) {
            $params['search'] = "name:{$validated['search']}";
        }
        if (!empty($validated['sort'])) {
            $params['sort'] = $validated['sort'];
        }
 
        $result = $this->sheets->list('Products', $params);
 
        return response()->json($result);
    }
 
    public function store(Request $request): JsonResponse
    {
        $validated = $request->validate([
            'name'     => 'required|string|max:255',
            'price'    => 'required|numeric|min:0',
            'category' => 'required|string|max:100',
        ]);
 
        $row = $this->sheets->addRow('Products', $validated);
 
        return response()->json($row, 201);
    }
}

// routes/api.php
Route::apiResource('products', ProductController::class)->only(['index', 'store']);

use Illuminate\Support\Facades\Cache;
 
class SheetsApiService
{
    // ... (constructor as above)
 
    public function listCached(string $sheet, array $params = [], int $ttl = 60): array
    {
        $key = "sheets:{$sheet}:" . md5(serialize($params));
 
        return Cache::remember($key, $ttl, fn () => $this->list($sheet, $params));
    }
 
    public function invalidate(string $sheet): void
    {
        // Use cache tags if your driver supports them (Redis, Memcached)
        Cache::forget("sheets:{$sheet}:*");
    }
}

public function listCached(string $sheet, array $params = [], int $ttl = 60): array
{
    $key = md5(serialize($params));
 
    return Cache::tags(["sheets", "sheet:{$sheet}"])
        ->remember($key, $ttl, fn () => $this->list($sheet, $params));
}
 
public function invalidate(string $sheet): void
{
    Cache::tags(["sheet:{$sheet}"])->flush();
}

public function fetchAll(string $sheet): array
{
    $all      = [];
    $limit    = 100;
    $offset   = 0;
 
    do {
        $result = $this->list($sheet, ['limit' => $limit, 'offset' => $offset]);
        $all    = array_merge($all, $result['data']);
        $offset += $limit;
    } while (count($all) < $result['meta']['total']);
 
    return $all;
}

// app/Jobs/SyncProductsJob.php
<?php
 
namespace App\Jobs;
 
use App\Services\SheetsApiService;
use Illuminate\Bus\Queueable;
use Illuminate\Contracts\Queue\ShouldQueue;
use Illuminate\Foundation\Bus\Dispatchable;
 
class SyncProductsJob implements ShouldQueue
{
    use Dispatchable, Queueable;
 
    public int $tries = 3;
    public int $backoff = 30;
 
    public function handle(SheetsApiService $sheets): void
    {
        $products = $sheets->fetchAll('Products');
 
        foreach ($products as $product) {
            \DB::table('products')->updateOrInsert(
                ['name' => $product['name']],
                [
                    'price'      => $product['price'],
                    'category'   => $product['category'],
                    'updated_at' => now(),
                ]
            );
        }
    }
}
 
// Dispatch from a controller or scheduled command:
SyncProductsJob::dispatch();

$schedule->job(new SyncProductsJob)->hourly();

use Illuminate\Http\Client\RequestException;
 
try {
    $result = $this->sheets->list('Products', $params);
} catch (RequestException $e) {
    $status = $e->response->status();
 
    if ($status === 429) {
        $retryAfter = $e->response->header('Retry-After') ?: 10;
        sleep((int) $retryAfter);
        // retry once
        $result = $this->sheets->list('Products', $params);
    } elseif ($status === 404) {
        abort(404, 'Sheet not found');
    } else {
        logger()->error('SheetsAPI error', ['status' => $status, 'body' => $e->response->body()]);
        abort(502, 'Upstream error');
    }
}