Laravel application for syncing GHG Calculator data to Google Sheets for visualization in Google Looker Studio.
This project implements a data bridge from Laravel to Google Sheets, enabling Google Looker Studio dashboards for GHG Calculator analytics. The integration uses the Google API PHP Client to securely sync data from your Laravel database to a Google Spreadsheet.
- ✅ Automated data sync from Laravel to Google Sheets
- ✅ Service account authentication (secure, no user interaction required)
- ✅ Flexible sync frequency (hourly, daily, weekly)
- ✅ Full or incremental sync options
- ✅ Automatic sheet tab creation
- ✅ Header row formatting
- ✅ Error handling and logging
- ✅ Laravel scheduled tasks integration
/app
/Console/Commands
SyncToGoogleSheets.php # Artisan command for syncing data
/Models
Calculation.php # GHG calculation model
Category.php # Emission category model
/Services
GoogleSheetsService.php # Google Sheets API service
/config
google-sheets.php # Configuration file
/database/migrations
create_calculations_table.php # Calculations table migration
create_categories_table.php # Categories table migration
-
Install dependencies:
composer install
-
Configure environment:
- Copy
.env.exampleto.env - Add Google Sheets configuration (see SETUP.md)
8000
- Copy
-
Run migrations:
php artisan migrate
-
Set up Google Service Account:
- See SETUP.md for detailed instructions
Sync data to Google Sheets:
php artisan sheets:syncPerform a full sync (clears existing data first):
php artisan sheets:sync --fullThe sync is automatically scheduled based on your GOOGLE_SHEETS_SYNC_FREQUENCY setting:
hourly- Runs every hourdaily- Runs once per dayweekly- Runs once per week
Ensure your cron job is configured:
* * * * * cd /path-to-project && php artisan schedule:run >> /dev/null 2>&1GOOGLE_SHEETS_SERVICE_ACCOUNT_JSON=storage/app/google-credentials.json
GOOGLE_SHEETS_SPREADSHEET_ID=your_spreadsheet_id
GOOGLE_SHEETS_SHEET_NAME=Main Data
GOOGLE_SHEETS_SYNC_FREQUENCY=hourlyConfiguration options are available in config/google-sheets.php.
The sync exports the following fields:
- Calculation ID
- Date
- CO2e Result
- Category
- User Type
- Status
- User ID
- Created At
- Updated At
Laravel Database
↓
GoogleSheetsService
↓
Google Sheets API (google/apiclient)
↓
Google Spreadsheet
↓
Google Looker Studio Dashboard
To add new fields to the sync:
- Update the
transformDataForSheets()method inSyncToGoogleSheets.php - Add the new column to the header row
- Include the data in the data rows
You can modify the sync logic in:
app/Services/GoogleSheetsService.php- API interactionsapp/Console/Commands/SyncToGoogleSheets.php- Data transformation and sync logic
Test the Google Sheets service:
php artisan tinker$service = app(\App\Services\GoogleSheetsService::class);
$data = $service->getSheetData('Main Data!A1:Z10');See SETUP.md for detailed troubleshooting steps.
Common issues:
- Service account credentials not found
- Spreadsheet access denied
- API not enabled in Google Cloud Console
- Setup Guide - Complete setup instructions
- Implementation Plan - Technical architecture
- Service account credentials should never be committed to version control
- Use least-privilege access for service accounts
- Regularly rotate service account keys
- Monitor API usage
This project is for internal use by the Métis Nation of Ontario (MNO) and Vincent Design.