Laravel Google Sheets Laravel Package

Getting Started

Minimal Setup

1. Installation:

   composer require revolution/laravel-google-sheets
   

   Publish the config file:

   php artisan vendor:publish --provider="Revolution\GoogleSheets\GoogleSheetsServiceProvider"
   

2. Configuration: Edit .env with your Google API credentials (OAuth, Service Account, or API Key) and configure in config/googlesheets.php:

   GOOGLE_SHEETS_AUTH=service_account
   GOOGLE_SHEETS_CREDENTIALS=path/to/service-account.json
   

3. First Use Case: Fetch a sheet as a Laravel Collection:

   use Revolution\GoogleSheets\Facades\GoogleSheets;
   
   $data = GoogleSheets::spreadsheet('your-spreadsheet-id')
       ->sheet('Sheet1')
       ->get()
       ->toCollection();
   

Implementation Patterns

Core Workflows

1. CRUD Operations

• Read Data:

  $data = GoogleSheets::spreadsheet('id')
      ->sheet('Sheet1')
      ->range('A1:B10')
      ->get()
      ->toCollection();
  

  • Chain range() to specify cell ranges (e.g., 'A1:C100').
  • Use toCollection() for Laravel Collection integration.

• Write Data:

  GoogleSheets::spreadsheet('id')
      ->sheet('Sheet1')
      ->range('A1')
      ->update([['Name', 'Age'], ['John', 30]]);
  

  • Use update() for bulk writes or set() for single-cell updates.

• Append Data:

  GoogleSheets::spreadsheet('id')
      ->sheet('Sheet1')
      ->range('A1')
      ->append([['New', 'Data']]);
  

2. Authentication Patterns

• Service Account (Server-to-Server): Configure in .env and use default facade methods. Ideal for automated scripts.

• OAuth (User-Specific): Redirect users to Google OAuth flow:

  $authUrl = GoogleSheets::auth()->getAuthorizationUrl();
  

  Exchange code for tokens:

  $tokens = GoogleSheets::auth()->exchangeCodeForToken($code);
  

  Store tokens in the database for future use.

• API Key (Public Data): Use for read-only access to public sheets:

  config(['googlesheets.auth' => 'api_key']);
  config(['googlesheets.api_key' => 'YOUR_API_KEY']);
  

3. Drive Integration

• List spreadsheets:

  $spreadsheets = GoogleSheets::drive()->list();
  

• Create a new spreadsheet:

  $spreadsheet = GoogleSheets::drive()->create('New Spreadsheet');
  

4. Batch Operations

• Use batch() to group multiple operations:

  GoogleSheets::spreadsheet('id')->batch(function ($batch) {
      $batch->sheet('Sheet1')->range('A1')->update([['Data', 1]]);
      $batch->sheet('Sheet2')->range('B1')->set('Updated');
  });
  

5. Laravel Integration

• Macros for Custom Methods: Extend the facade in a service provider:

  GoogleSheets::macro('exportToCsv', function ($spreadsheetId, $sheetName, $filePath) {
      $data = $this->spreadsheet($spreadsheetId)->sheet($sheetName)->get()->toArray();
      file_put_contents($filePath, $this->arrayToCsv($data));
  });
  

  Usage:

  GoogleSheets::exportToCsv('id', 'Sheet1', 'export.csv');
  

• Events: Listen for sheet updates:

  GoogleSheets::spreadsheet('id')->sheet('Sheet1')->onUpdate(function ($data) {
      Log::info('Sheet updated:', $data);
  });
  

Gotchas and Tips

Pitfalls and Debugging

1. Authentication Errors:

   • Issue: Invalid credentials or User does not have permission.
   • Fix:
     • Verify .env and config/googlesheets.php settings.
     • For OAuth, ensure the user has granted access to the sheet.
     • For Service Accounts, add the email (from the JSON key) as an editor to the sheet.

2. Rate Limiting:

   • Google Sheets API has quotas (e.g., 500 requests/100 seconds/user).
   • Tip: Use GoogleSheets::spreadsheet()->batch() to reduce API calls. Cache responses when possible:

     $data = Cache::remember("sheet_{$spreadsheetId}", now()->addHours(1), function () {
         return GoogleSheets::spreadsheet($spreadsheetId)->sheet('Sheet1')->get();
     });
     

3. Data Type Mismatches:

   • Google Sheets returns data as arrays of arrays. Laravel Collections may throw errors if data isn’t uniform.
   • Fix: Normalize data before conversion:

     $collection = GoogleSheets::spreadsheet('id')->sheet('Sheet1')->get()
         ->map(function ($row) {
             return collect($row)->zip(['Name', 'Age'])->toArray();
         });
     

4. Time Zones and Dates:

   • Google Sheets stores dates as serial numbers (e.g., 44561.542 = 2021-01-01 13:00:00).
   • Tip: Convert using Carbon:

     $date = Carbon\Carbon::createFromTimestamp($serialNumber);
     

5. Large Datasets:

   • Reading large sheets (>10,000 rows) may time out.
   • Tip: Use range() to fetch specific chunks or paginate:

     $batchSize = 1000;
     for ($i = 1; $i <= 100; $i++) {
         $data = GoogleSheets::spreadsheet('id')
             ->sheet('Sheet1')
             ->range("A1:A{$batchSize}")
             ->get();
         // Process $data
     }
     

Configuration Quirks

1. Service Account Scopes:

   • Ensure https://www.googleapis.com/auth/spreadsheets and https://www.googleapis.com/auth/drive are included in the JSON key file.
   • Fix: Regenerate the service account key with the correct scopes.

2. Caching:

   • The package caches the Google client by default. Clear it if credentials change:

     GoogleSheets::clearCache();
     

3. Environment-Specific Config:

   • Use Laravel’s config() method to override settings dynamically:

     config(['googlesheets.auth' => env('GOOGLE_SHEETS_AUTH', 'service_account')]);
     

Extension Points

1. Custom Responses:

   • Override the default response handling by extending the Revolution\GoogleSheets\Sheets\Sheet class:

     namespace App\Extensions;
     
     use Revolution\GoogleSheets\Sheets\Sheet;
     
     class CustomSheet extends Sheet {
         public function toCustomFormat() {
             return $this->get()->map(fn ($row) => (object) $row);
         }
     }
     

   • Bind the extension in a service provider:

     GoogleSheets::extend('custom', function () {
         return new CustomSheet();
     });
     

   • Usage:

     GoogleSheets::spreadsheet('id')->custom()->toCustomFormat();
     

2. Webhooks:

   • Use Google Drive Events to trigger Laravel jobs when sheets are updated. Example:

     GoogleSheets::drive()->pushNotification(
         'https://your-app.com/google-sheets-webhook',
         ['spreadsheets']
     );
     

   • Handle the webhook in a route:

     Route::post('/google-sheets-webhook', function (Request $request) {
         $channelId = $request->input('channel.id');
         GoogleSheets::drive()->acknowledge($channelId);
         // Process update
     });
     

3. Testing:

   • Mock the Google Sheets API in tests using the GoogleSheets facade:

     GoogleSheets::shouldReceive('spreadsheet')
         ->once()
         ->with('test-id')
         ->andReturnSelf();
     
     GoogleSheets::shouldReceive('sheet')
         ->once()
         ->with('TestSheet')
         ->andReturnSelf();
     
     GoogleSheets::shouldReceive('get')
         ->once()
         ->andReturn(collect([['Test', 'Data']]));