Wednesday, October 23, 2013

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!

1 comment:

  1. Anonymous8:01 PM

    With this code I can insert in blank cells? If yes, how?