Bye Bye Zend GData, Hello Custom PHP Google Spreadsheets API Access

For years now I've been leveraging the Google Spreadsheets API from within PHP. And while I was never a huge fan of it, the Zend GData code took care of the nitty gritty details that went into interacting with Google's API. That was until a few weeks ago when Google finally retired their non-OAuth based authentication, leaving Zend users apparently out of luck.

I poked around for an updated API option, but really didn't find one that appealed to me. Besides, I've been using this for years, I had a pretty solid idea of what my dream API would look like. So I give you my latest creation: google_sheets.php (and the accompanying google_auth.php). My goal was to create a simple Google Spreadsheet API that made accessing and updating a list-based document as painless as possible.

Here's how it all works.

First, there's the matter of authentication. I've tried to streamline this as much as possible, but there's still a few things you're going to need to do get this all up and running. You'll want to follow the instructions here and note the client ID and client secret you've setup. Then you'll need to arrange to invoke a couple of methods on the GoogleAuth class. I threw together this trivial PHP script to setup authentication:

<?php
require_once('../lib/google_sheets.php');
header("Content-Type: text/plain");

$ctx = array('client_id'      => 'XXXXXXXXXX',
             'client_secret'  => 'XXXXXXXXXX',
             'auth_file'      => __DIR__ . '/sheets.access');
$api = new GoogleSheets($ctx);

if(isset($_GET['c']));
  var_dump($api->auth->complete_auth($_GET['c']));
} else {
  echo $api->auth->start_auth();
}

?>

If you invoke this script without a URL parameter named c, it should respond with a URL you can copy and paste into your browser. This will grant access to the app from whichever Google Account you're logged in as. Once you've agreed to the permissions on the app you'll get a code. You can then hit the above script again with ?c=CODE where CODE is the value that were shown in your browser. Once you do this, the authentication data is saved in whatever file you set as auth_file. The GoogleSheets code will refresh the authentication as needed, so you can essentially discard the above script after you've successfully called $api->auth>complete_auth(...).

For more info on authentication check out my curl based experimentation over here. This code is written in a Google Drive context, which matches up well with the Google Sheets API. In fact, I used the shell scripts in that post to debug my PHP code.

After authenticating the real fun can begin.

The GoogleSheets object contains these high level functions:

1. $api->rows($doc_id, $worksheet_name) - return an array of all rows of a worksheet for a given spreadsheet .
2. $api->map($doc_id, $worksheet_name, $fn) - the classic map function: evaluate $fn for each row of a worksheet within a spreadsheet.
3. $api->walk($doc_id, $worksheet_name, $fn) - like map with two twists: (a) it doesn't return a value, (b) if you return an array from within $fn, it treats that data as an updated version of the record. It then saves this record back to the spreadsheet.
4. $api->reduce($doc_id, $worksheet_name, $fn, $init) - the classic fold function: calls $fn for each row of a worksheet it passes a carry value, the row and the underlying XML representation of the document. Using this method I was able to implement all of the above functions. This is the low level interface to the API.

A few conventions worth noting: $doc_id is the that big, ugly, unique identifier in the URL of your document. You can't miss it. $worksheet_name is the human readable name of the worksheet you want to work with. Internally this is converted to a unique identifier, but I've found it much easier to work with the names in the spreadsheet itself. A 'row' is represented as an associative array of column names and values.

OK, enough talk, let's see some code. Assuming that we want to work with the spreadsheet found here: the $doc_id would be 1WOflWqDRpRtvVwWdJi1-XxA6_ePW_uAtpkg9j80uUj8 and the worksheet name would be People.

<?php
/*
 * A PHP file for testing stuff out
 */
ini_set('display_errors', 1);
ini_set('error_reporting', E_ALL);

header("Content-Type: text/plain");
require_once('../shared/lib/google_sheets.php');

$ctx = array('client_id'      => 'XXXXXXXXXXXXXXXX',
             'client_secret'  => 'XXXXXXXXXXXXXXXX',
             'auth_file'      => '/XXXXXXXXXXXXXXXX');

$api = new GoogleSheets($ctx);

$api->walk('1WOflWqDRpRtvVwWdJi1-XxA6_ePW_uAtpkg9j80uUj8', 'People',
           function($row) {
             echo strtoupper($row['name']) . ": " . rand(100,999) . '-' . $row['phone'] . "\n";
           });

$api->walk('1WOflWqDRpRtvVwWdJi1-XxA6_ePW_uAtpkg9j80uUj8', 'People',
           function($row) {
             $row['luckynumber'] = rand(1000,9999);
             return $row;
           });

echo "\n";

$sum = array_sum($api->map('1WOflWqDRpRtvVwWdJi1-XxA6_ePW_uAtpkg9j80uUj8', 'People',
                           function($row) {
                             return $row['luckynumber'];
                           }));
echo "Sum = $sum (via array_sum)\n\n";

$sum = $api->reduce('1WOflWqDRpRtvVwWdJi1-XxA6_ePW_uAtpkg9j80uUj8', 'People',
                    function($carry, $row) {
                      return $carry + $row['luckynumber'];
                    }, 0);

echo "Sum = $sum (via reduce)\n\n";
?>

The above code performs three operations:

• A walk to output the name and phone field, showing how you can access individual fields
• A walk to generate and store a new lucky number for each row
• A map and reduce to demonstrate two methods for extracting and manipulating data in PHP

So there you have it. If you need access to Google Spreadsheets from PHP, feel free to grab the code and hack away!