Integrating Google Sheets API with Website
Google Sheets as a database is a popular solution for small projects: managers work in a familiar interface, data is accessible in real-time, and no separate CMS is needed. Through the Sheets API, the website reads data from the spreadsheet and displays it on the page.
Authentication via Service Account
use Google\Client;
use Google\Service\Sheets;
$client = new Client();
$client->setAuthConfig(storage_path('app/google-service-account.json'));
$client->addScope(Sheets::SPREADSHEETS_READONLY);
$service = new Sheets($client);
$spreadsheetId = '1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgVE2upms';
Reading Data
$response = $service->spreadsheets_values->get($spreadsheetId, 'Sheet1!A2:E');
$rows = $response->getValues();
$items = array_map(fn($row) => [
'name' => $row[0] ?? '',
'price' => (float) ($row[1] ?? 0),
'category' => $row[2] ?? '',
'active' => ($row[3] ?? '') === 'TRUE',
], $rows);
Caching
Google Sheets API has a quota: 300 requests per minute. Making an API request on every user visit is not feasible:
$items = Cache::remember('sheets_catalog', 300, function () use ($service, $spreadsheetId) {
$resp = $service->spreadsheets_values->get($spreadsheetId, 'Catalog!A2:F');
return array_map(fn($row) => mapRow($row), $resp->getValues());
});
A 5-minute cache provides sufficient data freshness for most use cases.
Implementation time: 1 business day.