Google Sheets PHP API: Slow Read Response?

I’m tinkering with Google Sheets API, using the provided PHP library. Experiencing surprisingly long load times. The sheet I’m working with at the moment has a total of 25 pages, each ca. 10KB in size, avg. 100 rows x 10 columns.

Before sending the read request(s), my app handles the auth (new GoogleAuth) and the creation of a new service (new Google_Service_Sheets). These happen once per page load only. The read routine is standard API example stuff:

$response = $this->sheet->spreadsheets_values->get($sheet_id, $range);
$data = $response->getValues();

When I fetch a single page from the sheet, it takes ~0.6sec. When I fetch 10 pages, it’s 6+sec. Whether I use pageId for $range, or pageId!A1:K10 for example (first 10 rows), it takes as long. (In fact, it takes the same time to read a single cell!) If I cache this data locally and run it through the same hops, in both cases my app takes around 0.03 sec, so no code bottlenecks at my end. The response times are the same on my live server and in my local dev environment.

The codebase shipped for the API (via composer) includes a ton of material (total 28MB in 10K+ files!). I haven’t had the heart to dig in (yet) to see if there’s something I could shave off to make this perform half-respectably; feeling like I shouldn’t have to? One would expect Google to provide an API that somewhat performs.

Any clues, experiences, or suggestions on improving/troubleshooting Google Sheets API performance please, with PHP or otherwise? I’m having a hard time believing it should be this slow, bordering unusable with any more complex chores.

Update: I’ve re-run the PHP Quickstart stuff with time ticks to ensure this is not caused by any code of mine. There’s a 0.5+sec gap between the ticks around the spreadsheets_values->get() call. Also, tried with other (much lighter) spreadsheets. Same deal, the data’s long time a-comin’.

Answer

Based on experiences with PHP and Python (thanks @iamblichus), the Google Sheets API speed itself is indeed on the slow side. It seems there isn’t anything you can do to make a single API round-trip happen faster. In my initial code, I had the following code in a reader method:

$response = $this->sheet->spreadsheets_values->get($sheet_id, $range);
$data = $response->getValues();

In some cases, it was called several times to access separate ranges from a spreadsheet for one page render. This led to an unacceptably long response time that made the application unusable.

One suggested solution was the use of spreadsheets.get, which would fetch the entire spreadsheet in one go; and one would then extract the desired ranges locally. This leads to a fair bit of memory and network overhead; but is worth considering if you need the greater part of the sheet’s data in any case.

Since I only needed about 10% of the data from particular ranges, the solution I ended up implementing uses the batchGet() and getValueRanges() methods, in place of get() and getValues(). The API reader method was modified to handle either a string (one range) or an array (multiple ranges), and choose a method to match, as follows:

if (!is_array($range)) {
    $response = $this->sheet->spreadsheets_values->get($sheet_id, $range);
    $data = $response->getValues();
} else {
    $response = $this->sheet->spreadsheets_values->batchGet($sheet_id, ['ranges' => $range]);
    $data = $response->getValueRanges();
}

When multiple ranges are passed in an array to batchGet, the API response will contain an array of response objects matching the specified ranges, as follows:

[0] => Google_Service_Sheets_ValueRange Object [
    [collection_key:protected] => values
    [majorDimension] => ROWS
    [range] => fr!A1:K181
    [values] => [ ... ]
]
[1] => Google_Service_Sheets_ValueRange Object [
...

In contrast, a regular get call would simply return the contents of the values array seen above. Fetching nine ranges one-by-one with get took 9x 0.5+sec or ~5sec. Fetching them with the batchGet option took 1x 0.5+sec, the duration of a single API round-trip.

In conclusion, the amount of data fetched is inconsequential to the time spent waiting for Google Sheets API responses. What matters is simply the amount of separate API calls made. Bundle them up into aggregate requests to minimize the code-independent lag in your application.