Use API access to Google Spreadsheets to automate processes

You have a long list in a Google spreadsheet that needs to be processed and the results written back to the spreadsheet. This article describes how the Spreadsheets API can be used to automate that process. Our spreadsheets contains two relevant columns, Data and Result. We're going to fill the result column based on the data.

The example code below uses the list-based API to retrieve a set of rows, do something with them and then write them back. That API requires that your spreadsheet has a header column as columns are identified using their header name.

We rely on the Zend_GData component, which is no longer actively maintained and might no longer work in the future. For the time being, it still works great.

The script requires three arguments, the user name, password, and the spreadsheet key. To avoid having to load too much into memory or reaching other limits, we search for 10 rows that don't have a result, process them, write the results back and repeat that until we can't find any remaining rows that don't have results.

Note that the list-based API operates on whole rows. Making manual changes on the same row while the script is processing it will result in a HTTP 409 error, so be careful with manual edits in the spreadsheet while the script runs.

setSpreadsheetKey($key); // Optionally specify a worksheet id, otherwise it uses the default. // $query->setWorksheetId($this->getWorksheetId()); // Search for rows that have no result and limit to 10 results. $query->setSpreadsheetQuery('result=""'); $query->setMaxResults(10); $feed = $client->getListFeed($query); if ($feed->count()) { // If we have results, do something with them. $data = $entry->getCustomByName('data')->getText(); print "Processing " . $data . ".... "; $result = $data * 2; // Update the row and save. $entry->getCustomByName('result')->setText($result); $entry->save(); print "OK\n"; } else { print "Finished\n"; } } while ($feed->count()); ?>