We created a website for a friend, who wanted to display a lot of tabular data. We couldn’t find a way to make it easy to update on a regular basis, so we cooked up a simple CMS using a little PHP, jQuery, HTML and Google Spreadsheets. Part 2 shows how to integrate Into WordPress.

Disclaimer: We are not a php or security experts, so we can’t be responsible for holes in these scripts – let us know if you spot any potential disasters!

View live demo

Create and publish a Google spreadsheet

Create or upload a test .csv in Google Docs

Click on the Share button and select ‘Publish as webpage’ with the following options:

• Start publishing
• Get a link to the published data – choose ‘CSV’ from the dropdown

Copy the link provided (or use my shared test data: http://spreadsheets.google.com/pub?key=t6wIcgSnjd6eJX1NbaOqWtg&output=csv)

Create Index.php

Create a new text file and save as Index.php and add the basic html structure

Retrieve and display the .csv with php

We will use the php function fgetcsv to retrieve and display the .csv file and render it as html. Change the csv URL to your Google Docs file you published earlier.

Essentially, this script loops through the .csv and recreates it row by row in html, with a specified css class to enable easy styling and scripting, which we’ll see in the next step.

# set your csv spreadsheet url here
$csv = "http://spreadsheets.google.com/pub?key=t6wIcgSnjd6eJX1NbaOqWtg&output=csv";

# if there is a spreadsheet...
if($csv !== '') {

# write an html table
echo "";
# open the csv
$handle = fopen($csv, "r");
$data = fgetcsv($handle, 1000, ",");
echo "";
# column headers from 1st row of csv:
foreach($data as $value) {
echo "";
}
echo "\n\n";
# data rows:
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
echo "";
foreach($data as $value) {
# write table cell data
echo "";
}
echo "\n";
}
echo "\n
$value
$value
\n";
}

else { echo "Can't connect to google, sorry!"; }

Style with CSS

So, now you can run your file in a browser (note – you’ll need to put it on a server that can run php files, or on your own local box if you use MAMP for example) but it’ll look real ugly. A bit of CSS will pretty up the table, put this in your html head:

body	 {font-family: arial; background: lightgrey}
table	 {margin: 30px auto; background: white; border-collapse: collapse}
th	 {text-align: left; padding: 20px 20px; cursor: pointer}
th:hover {background: white}
td	 {padding: 5px 20px; border: 1px solid lightgrey}

Stripe the table rows with jQuery

So now it’s looking pretty good, a great usability feature in tables is to colour the background of alternate rows to increase legibility. With one simple line – $(‘tr:even’).css(‘background’,’#eee’) – this is easily achieved with jQuery.

5. Make the table sortable with a jQuery plugin

A feature our friend requested was to be able to sort the table by clicking on the headers. This again is simply done with a great jQuery plugin, tablesorter from tablesorter.com. You should download the plugin locally, but for the sake of simplicity, we’ll hotlink to it. Update the javascript in your html header to the following:

Run the file and jobs a good’un, you have an easily updatable, styled and sortable lightweight data CMS!

Click here to see the complete Index.php source:

";
# open the csv
$handle = fopen($csv, "r");
$data = fgetcsv($handle, 1000, ",");
echo "";
# column headers from 1st row of csv:
foreach($data as $value) {
echo "$value";
}
echo "\n\n";
# data rows:
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
echo "";
foreach($data as $value) {
# write table cell data
echo "$value";
}
echo "\n";
}
echo "\n\n";
}

else { echo "Can't connect to google, sorry!"; }

?>

6 Responses

1. irk says:

   February 17, 2010 at 2:13 pm

   cheers for this, interesting stuff as always.

2. Sultan says:

   April 9, 2010 at 5:48 am

   really wonderful stuff, i will be including your site as my fav.

3. Promethius says:

   April 24, 2010 at 3:04 pm

   Awesome. Nice script and it looks good too!

4. Paul says:

   April 26, 2010 at 11:31 am

   Thanks! Works really nicely, and with no fuss.

   Hopefully one day (??) Google Docs Spreadsheet will allow some user control over their mad random AutoSave, which currently messes things up for many people.

5. anthony says:

   January 18, 2014 at 1:23 pm

   The script never shows the most recent version of the Google csv file for some reason. I make changes to the spreadsheet, force republish, but then when I come to my browser and hit reload, the table never changes. Any idea what’s up?

6. felix says:

   January 18, 2014 at 1:36 pm

   That’s odd, I’m using this code widely and it updates in minutes without even re-publishing. Maybe a client/server is caching? Are you using Varnish or a new version of MAMP as both cache aggressively. Try checking out the .csv link in a browser window and see if that updates. Cheers!