Php Google Spreadsheet Client Laravel Package

Getting Started

Minimal Setup

1. Installation

   composer require asimlqt/php-google-spreadsheet-client
   

   • Verify composer.json includes the package under require.

2. Authentication

   • Obtain a Google Service Account JSON key from Google Cloud Console.
   • Share your spreadsheet with the service account email (found in the JSON file under "client_email").

3. First Use Case: Read a Cell

   use Asimlqt\GoogleSpreadsheetClient\GoogleSpreadsheetClient;
   
   $client = new GoogleSpreadsheetClient('path/to/service-account.json');
   $spreadsheetId = 'your-spreadsheet-id'; // Found in the spreadsheet URL
   $sheetName = 'Sheet1'; // Default sheet name
   
   $cellValue = $client->getCellValue($spreadsheetId, $sheetName, 'A1');
   echo $cellValue; // Outputs the value of cell A1
   

4. Key Files to Reference

   • src/GoogleSpreadsheetClient.php (Core class)
   • examples/ (if available in the repo; otherwise, check GitHub for community examples).

Implementation Patterns

Common Workflows

1. Reading Data

   • Fetch a range of cells (e.g., A1:D10):

     $rangeData = $client->getRangeValues($spreadsheetId, $sheetName, 'A1:D10');
     

   • Loop through rows/columns:

     foreach ($rangeData as $row) {
         foreach ($row as $cell) {
             echo $cell . ' ';
         }
     }
     

2. Writing Data

   • Update a single cell:

     $client->setCellValue($spreadsheetId, $sheetName, 'B2', 'New Value');
     

   • Append a row:

     $client->appendRow($spreadsheetId, $sheetName, ['Name', 'Email', 'Age']);
     

3. Batch Operations

   • Use getBatchUpdate() to combine multiple updates (e.g., formatting + data) into one API call for efficiency.

4. Handling Multiple Sheets

   • List all sheets in a spreadsheet:

     $sheets = $client->getSheets($spreadsheetId);
     foreach ($sheets as $sheet) {
         echo $sheet->getTitle() . "\n";
     }
     

5. Integration with Laravel

   • Service Provider Binding:

     // config/app.php
     'bindings' => [
         Asimlqt\GoogleSpreadsheetClient\GoogleSpreadsheetClient::class => function ($app) {
             return new GoogleSpreadsheetClient(config('services.google.spreadsheet.key'));
         },
     ];
     

   • Facade (Optional): Create a facade for cleaner syntax:

     // app/Facades/GoogleSpreadsheet.php
     namespace App\Facades;
     use Illuminate\Support\Facades\Facade;
     class GoogleSpreadsheet extends Facade {
         protected static function getFacadeAccessor() {
             return 'google.spreadsheet';
         }
     }
     

     Register in AppServiceProvider:

     $this->app->bind('google.spreadsheet', function () {
         return new GoogleSpreadsheetClient(config('services.google.spreadsheet.key'));
     });
     

     Usage:

     $value = GoogleSpreadsheet::getCellValue($spreadsheetId, 'Sheet1', 'A1');
     

6. Scheduled Jobs (Laravel)

   • Sync data nightly:

     // app/Console/Commands/SyncSpreadsheetData.php
     use Illuminate\Console\Command;
     use Asimlqt\GoogleSpreadsheetClient\GoogleSpreadsheetClient;
     
     class SyncSpreadsheetData extends Command {
         protected $signature = 'spreadsheet:sync';
         public function handle(GoogleSpreadsheetClient $client) {
             $data = $client->getRangeValues($spreadsheetId, 'Sheet1', 'A1:B100');
             // Process data (e.g., save to DB)
         }
     }
     

   • Schedule in app/Console/Kernel.php:

     protected function schedule(Schedule $schedule) {
         $schedule->command('spreadsheet:sync')->daily();
     }
     

Gotchas and Tips

Pitfalls

1. Authentication Issues

   • Error: Invalid Credentials or 403 Forbidden.
   • Fix:
     • Ensure the service account email is shared as an editor on the spreadsheet.
     • Verify the JSON key file is not corrupted (re-download from Google Cloud Console).
     • Check if the spreadsheet is domain-restricted (requires Google Workspace).

2. Rate Limits

   • Google Sheets API has quota limits (e.g., 500 requests/100 seconds for free tier).
   • Fix:
     • Implement exponential backoff for retries.
     • Cache responses (e.g., Laravel’s cache()->remember()).

3. Deprecated Methods

   • The package is last updated in 2016 and may not support newer Google Sheets API features.
   • Workaround: Use the official Google API PHP Client (google/apiclient) for advanced features, but wrap it in a Laravel-friendly facade.

4. Time Zone Handling

   • Dates/times in cells may not respect your server’s time zone.
   • Fix: Use Carbon to parse/format:

     use Carbon\Carbon;
     $rawDate = $client->getCellValue($spreadsheetId, 'Sheet1', 'A1');
     $carbonDate = Carbon::parse($rawDate);
     

5. Large Datasets

   • Fetching thousands of rows may time out.
   • Fix: Use pagination or batch processing:

     $batchSize = 100;
     for ($i = 1; $i <= 1000; $i += $batchSize) {
         $data = $client->getRangeValues($spreadsheetId, 'Sheet1', "A{$i}:B" . ($i + $batchSize));
         // Process $data
     }
     

Debugging Tips

1. Enable Logging Add this to your GoogleSpreadsheetClient constructor to log API responses:

   $client = new GoogleSpreadsheetClient('key.json', [
       'logger' => function ($message) {
           \Log::debug($message);
       }
   ]);
   

2. Check HTTP Status Codes

   • Wrap API calls in a try-catch to log errors:

     try {
         $data = $client->getRangeValues($spreadsheetId, 'Sheet1', 'A1:B10');
     } catch (\Exception $e) {
         \Log::error('Google Sheets Error: ' . $e->getMessage());
         \Log::error($e->getTraceAsString());
     }
     

3. Validate Spreadsheet ID

   • Ensure the spreadsheetId is correct (extract from the URL: https://docs.google.com/spreadsheets/d/SPREADSHEET_ID/edit).

Extension Points

1. Custom Cell Formatting

   • Extend the client to support rich text or conditional formatting:

     // Hypothetical extension
     $client->setRichTextCell($spreadsheetId, 'Sheet1', 'A1', [
         'text' => 'Hello',
         'bold' => true,
         'color' => '#FF0000'
     ]);
     

2. Webhook Integration

   • Use Google Apps Script to trigger a Laravel webhook when the spreadsheet changes.
   • Example script:

     function onEdit(e) {
       const url = 'https://your-app.com/api/google-sheets-webhook';
       const payload = {
         spreadsheetId: e.source.getId(),
         range: e.range.getA1Notation(),
         value: e.value
       };
       UrlFetchApp.fetch(url, {
         method: 'POST',
         payload: JSON.stringify(payload),
         headers: { 'Content-Type': 'application/json' }
       });
     }
     

3. Laravel Scout Integration

   • Index spreadsheet data for full-text search:

     // app/Providers/AppServiceProvider.php
     use Asimlqt\GoogleSpreadsheetClient\GoogleSpreadsheetClient;
     use Laravel\Scout\Builder;
     
     public function boot(GoogleSpreadsheetClient $client) {
         Builder::macro('fromSpreadsheet', function ($spreadsheetId, $sheetName, $range) {
             $data = $client->getRangeValues($spreadsheetId, $sheetName, $range);
             return collect($data)->flatMap(fn($row) => $row);
         });
     }
     

     Usage:

     $results = (new Post)->search('query')->fromSpreadsheet($