Updating Web Sites with Google Spreadsheets

I’ve done a handful of web projects this year where it made sense to store data in Google Spreadsheets, and then use a bit of PHP code to make them be dynamically displayed on a web site. In many cases, these are tables of data that are parsed and presented nicely in the web site, but for a few NMC projects, it made sense as a way for a staff person to update data on our web pages w/o having to touch the pages.

As a first example, I am cleaning up an older WordPress site I use for logging my running/training; in the past, I kept a spreadsheet on my desktop for keeping a run log and then manually transferred the totals/averages/graphs to my web site by pasting into some text files (they are embedded with a PHP include). It worked, but it did have that tedious manual smell for something that should be more automated.

It seemed to make sense to transfer the spreadsheet to a Google Doc. I’m not going to detail all the bits in there, but essentially, for every run, I enter a distance and time on the appropriate date line, and the sheet calculates total/averages per week and on a final summary page.

This is just the data; I have another sheet that is just the table for the weekly totals of miles and time spent running; from here I can simply use Google’s built-in Publish Chart which provides a URL to embed this graphic:

This is subtle but powerful– this is an image, but not an image- as the data changes, so will the graphic. So I know longer have to manually make a graphic and upload it to my web site; I can simply use the HTML

<img src="http://spreadsheets.google.com/pub?key=tMIPDwydKpay-5Oq0_G769w&oid=4&output=image" width="100%"/>

The other piece uses Google’s ability to publish a worksheet as CSV- this is data you can then parse on your web site. So I have a sheet that simply does averages/totals for my running log:

To access this data, I click the Share button and select Publish As Web Page

The options in the top make it so the published data is made current whenever I request it; in the bottom I select the option to have it published as CSV (comma separated value), and I select just the spreadsheet I need.

Thus, this URL always gives me the data on the spreadsheet in a form I can use in my code…. this bit takes some PHP savvy to manage, but I use the same logic almost everywhere.

I use this on the sidebar of my blog to display an up to date listing of my totals. In my WordPress sidebar, I use a statement to bring in the code from an external file named totals.php that sits inside my theme directory:

I could have simply inserted the code into my sidebar template, but going modular like this makes it easier to separate code from format…

The basic logic is using the php file command to read int a remote file, which puts the contents into an array; where each array item is one line of data.

// get the CSV data as an array from the remote URL
$lines = file('http://spreadsheets.google.com/pub?key=tMIPDwydKpay-5Oq0_G769w&single=true&gid=0&output=csv');

// get rid of header row
$headers = array_shift($lines);

// Loop through data- therer is only one line hear
foreach ($lines as $line) {
	$ldata =  explode(',', trim($line)); // split row to its own array of elements

	if ($ldata[0] == '') break; // an empty line means we are done, so exit the foreach loop

      // now we can just output the information as an HTML list, referencing the appropriate array items
       echo '<li>Total Miles Biking  <strong>' . $ldata[0] . '</strong></li><li>Total Miles Running  <strong>' . $ldata[1] . '</strong></li><li>Total Hours Running  <strong>' . $ldata[2] . '</strong></li><li>Ave Pace Running  <strong>' . $ldata[3] . '</strong></li>';
}

And what is cool is there does not seem to be much of an impact of hitting Google each time. It must be ‘a caching. Good stuff.

This is pretty simple example. I am running some more complex examples on the NMC Virtual Worlds site where the reason to do this was to have a larger amount of tabular data be displayed in a more readable fashion on our web site– and to make it so someone other than me can be in charge of keeping the data up to date bu simply editing a Google doc (note that a spreadsheet used for this purpose must be public viewable, so don’t mix in any company secrets in there).

So for example, our Clients listing is coming from a google spreadsheet, and the parsing tests the existence of certain items to format the output– e.g. if the organization URL column has a value, we echo the name as a hyperlink; if we have the coordinates of their land in Second Life, we add a teleport link. The code that generates this page also sorts it on output.

This one technically is not hitting the spreadsheet every time…. I set this up so a unix cron (a timed script) calls every hour a PHP script which generates the formatted content as a text file on the web server, so the web page actually just reads it in via an include statement… this is a simple form of caching.

Another page has actually two chunks of data that are generated from different spreadsheets. Out current land availability page lists plots of Second Life land available from NMC for rental.

The main body content is a list of land with the output formatted around data in a spreadsheet. And the current land prices on the right side come form another sheet- this way we can make adjustments to the web page at any time simply by editing a spreadsheet, and never touch the web page- again it works like this:

1. Every hour a cron script on the server calls a PHP script.
2. The PHP script reads in CSV data, sucks it into an array (one line as an item), marches through items, and parses each item as an array representing the cells, and outputs the formatted as HTML to a small text file.
3. The WordPress page simply uses an include statement to display the content (I use the Exec-PHP plugin to be able to run statements in a page).

I am thinking more and more how I can use this process….

Of course, this is all baby stuff compared to the masterful data wrangling by the Jedi Master Tony Hirst….