Google Sheets API Tips
There are a couple of PHP / Laravel packages for interacting with Google sheets but I found the official package to be the most straightforward.
https://github.com/googleapis/google-api-php-client
Authentication
I went with the Authentication with Service Accounts approach which uses a JSON file to authenticate with Google. I found a great guide on how to create one of these here.
Once we have our JSON credentials file we can put it in our codebase and add a simple helper function to generate the service like so:
/**
* Return a google sheets service
* google/apiclient. Uses stuartcusackie
* google application credentials.
*
* @return GoogleServiceSheets
*/
function get_gsheet_service() {
$client = new \Google_Client();
$client->setApplicationName('PHPSheets');
$client->setScopes([\GoogleServiceSheets::SPREADSHEETS]);
$client->setAccessType('offline');
$client->setAuthConfig(base_path() . '/gsheetscredentials.json');
return new \GoogleServiceSheets($client);
}Writing To Google Sheets
I ran into a lot of problems when working with a large amount of rows, so here's some advice:
Google sheets should comfortably handle 5 million rows, if used correctly.
When working with a large amount of rows (150,000+) you will hit major performance problems when outputting one row at a time. It is much better to export rows in batches.
Updating existing rows in a Google Sheet is a very complicated process. The best approach seems to be adding developer meta data per row, which enables you to find and update a specific row. Again, this can cause major performance problems when working with a large number of rows.
The best approach I found was to completely regenerate the Google Sheet each night using a scheduling system. This avoided all performance problems caused by writing to large sheets, and it's much more straightforward to output an entire sheet at once rather than try and update a segment of rows.