A Super Simple PHP Google Spreadsheet API

Over this weekend I was doing a bit of tool development for my own company and needed to pull in and update some data from a Google Spreadsheet. I continue to be a huge fan of using a Google Spreadsheet to manage data accessed from PHP. The folks at Zend provide a fairly comprehensive API for working with Google Spreadsheets, but it was overkill for what I needed to accomplish.

After thinking over the problem I needed to solve, I determined there were only two functions I'd need: sgs_list($worksheet_name) and sgs_walk($worksheet_name, $callback). The description of each is below:

• sgs_list($worksheet_name) - given a worksheet name (the text in the tab at the bottom of a spreadsheet), pull back an array that represents each row of the spreadsheet. The array consists of key/value pairs, where the key is the heading of the row. Basically, this pulls in a Spreadsheet as standard a PHP array, and allows me to opperate on a spreadsheet much like a database result set.
• sgs_walk($worksheet_name, $callback) - this function is similar to sgs_list(...). But rather than returning an array of Spreadsheet rows (which are themselves an associative array), it invokes $callback once for each row. And then there's a tiny bit of magic: if $callback returns an array, the Worksheet is updated to reflect the changed array. If $callback returns true (or any non-array value), then no change to the spreadsheet is made.

sgs_walk is a fairly clean way of updating a spreadsheet without worry about the details of doing so. You're just setting and returning an array; it couldn't be easier.

Every time I use Google Spreadsheets from a programmable context I'm amazed the power and ease of accessing data that would normally seem out of reach.

You can download my trivial little API here. And you can download a sample of how it's used here. Feel free to hack this to bits; if it's useful, have at it!

	<?php
	/*
	* A truly trivial API for dealing with Google Spreadsheets. Designed to work
	* with spreadsheets in 'List Form'. The API is designed with absolute simplicity in mind:
	* you set a number of constants, and call either gs_list("worksheet name") or
	* gs_walk("Worksheet name", 'callback');
	*
	* And that's it.
	*/
	function sgs_list($ws_name) {
	$data = array();
	$feed = sgs_feed($ws_name);
	foreach($feed as $row) {
	$entry = array();
	foreach($row->getCustom() as $c) {
	$entry[$c->getColumnName()] = $c->getText();
	}
	$data[] = $entry;
	}
	return $data;
	}
	function sgs_walk($ws_name, $handler) {
	$client = sgs_client();
	$feed = sgs_feed($ws_name);
	foreach($feed as $row) {
	$entry = array();
	foreach($row->getCustom() as $c) {
	$entry[$c->getColumnName()] = $c->getText();
	}
	$result = call_user_func($handler, $entry);
	if(is_array($result)) {
	$client->updateRow($row, $result);
	}
	}
	}
	/* ------------------------------------------------------------------------
	* Private stuff down here. Move along, nothing to see.
	* ------------------------------------------------------------------------
	*/
	global $_sgs_client;
	$_sgs_client = null;
	function sgs_client() {
	global $_sgs_client;
	if($_sgs_client != null) {
	return $_sgs_client;
	}
	if(!defined('SGS_USERNAME') || !defined('SGS_PASSWORD') || !defined('SGS_SHEET_ID') || !defined('SGS_ZEND_LOADER')) {
	trigger_error("You must setup the environment before you can use SGS", E_USER_ERROR);
	}
	require_once(SGS_ZEND_LOADER);
	set_include_path(get_include_path() . PATH_SEPARATOR . dirname(SGS_ZEND_LOADER) . '/../');
	Zend_Loader::loadClass('Zend_Http_Client');
	Zend_Loader::loadClass('Zend_Gdata');
	Zend_Loader::loadClass('Zend_Gdata_ClientLogin');
	Zend_Loader::loadClass('Zend_Gdata_Spreadsheets');
	$authService = Zend_Gdata_Spreadsheets::AUTH_SERVICE_NAME;
	$httpClient = Zend_Gdata_ClientLogin::getHttpClient(SGS_USERNAME, SGS_PASSWORD, $authService);
	$_sgs_client = new Zend_Gdata_Spreadsheets($httpClient);
	return $_sgs_client;
	}
	function sgs_feed($ws_name) {
	$gd_client = sgs_client();
	$ws_id = false;
	$ws_query = new Zend_Gdata_Spreadsheets_DocumentQuery();
	$ws_query->setSpreadsheetKey(SGS_SHEET_ID);
	$ws_feed = $gd_client->getWorksheetFeed($ws_query);
	foreach($ws_feed->entries as $entry) {
	if($entry->title->text == $ws_name) {
	$ws_url_chunks = explode('/', $entry->id->text);
	$ws_id = $ws_url_chunks[8];
	break;
	}
	}
	if(!$ws_id) {
	trigger_error("No worksheet found named $ws_name.");
	return null;
	}
	$query = new Zend_Gdata_Spreadsheets_ListQuery();
	$query->setSpreadsheetKey(SGS_SHEET_ID);
	$query->setWorksheetId($ws_id);
	return $gd_client->getListFeed($query);
	}
	?>

view raw sgs.php hosted with ❤ by GitHub

	<?php
	/*
	* A PHP file for testing the Simple Google Spreadsheet API
	*/
	date_default_timezone_set('UTC');
	/* ------------------------------------------------------------------------
	* Start Configuration
	* ------------------------------------------------------------------------
	*/
	// Where's Zend's Loader.php
	define('SGS_ZEND_LOADER', dirname(__FILE__) . "/../shared/lib/Zend/Loader.php");
	// Define our connection info. Should use OAuth, but
	// for the sake of simplicity, using an explicit username/password.
	// May need to visit: https://accounts.google.com/DisplayUnlockCaptcha
	// to allow your usename/password to work.
	define('SGS_USERNAME', 'XXXXXXXXX@gmail.com');
	define('SGS_PASSWORD', 'XXXXXXXXXXXXXXXXXXX');
	// Lifted from the URL of Google Doc I want to work with
	define('SGS_SHEET_ID', '0Ap3sMu55xO1GdEhtekhmY1B0Tk5obFp1RUhLMmpsNnc');
	// Include the API
	require_once('../lib/sgs.php');
	/* ------------------------------------------------------------------------
	* End Configuration
	* ------------------------------------------------------------------------
	*/
	function add_last_accessed($data) {
	$data['notes'] = "Last accessed " . date('r');
	return $data;
	}
	sgs_walk("Address Book", 'add_last_accessed');
	/*
	* Now I can use sgs_list and sgs_walk as I like.
	*/
	?>
	<h2>The Address Book</h2>
	<ul>
	<? foreach(sgs_list("Address Book") as $row) { ?>
	<li>
	<?= $row['name'] ?>
	(<a href='mailto:<?= $row['e-mail'] ?>'><?=$row['e-mail']?></a>)
	<a href='tel:<?= $row['phonenumber'] ?>'><?=$row['phonenumber']?></a> | <?= $row['notes'] ?>
	</li>
	<? } ?>
	</ul>

view raw sgs_sample.php hosted with ❤ by GitHub