Development Blog 17th October 2021

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.


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->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.