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.