Google Docs as a lightweight CMS Pt1
6 Comments
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
<html>
<head>
<title>Welcome!</title>
</head>
<body>
</body>
</html>
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 "<table class='csvTable'>";
# open the csv
$handle = fopen($csv, "r");
$data = fgetcsv($handle, 1000, ",");
echo "<thead><tr>";
# column headers from 1st row of csv:
foreach($data as $value) {
echo "<th>$value</th>";
}
echo "</tr></thead>\n<tbody>\n";
# data rows:
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
echo "<tr>";
foreach($data as $value) {
# write table cell data
echo "<td>$value</td>";
}
echo "</tr>\n";
}
echo "</tbody>\n</table>\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.
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.3.2/jquery.min.js"></script>
<script type="text/javascript">
$(document).ready(function() {
//for each table row, set it's background to light grey
$('tr:even').css('background','#eee');
});
</script>
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:
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.3.2/jquery.min.js"></script>
<script type="text/javascript" src="http://tablesorter.com/jquery.tablesorter.min.js"></script>
<script type="text/javascript">
$(document).ready(function() {
//for each table row, set it's background to light grey
$('tr:even').css('background','#eee');
//make it sortable
$(".csvTable").tablesorter();
});
</script>
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:
-
<html> <head> <title>Welcome!</title> <style> 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} </style> <script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.3.2/jquery.min.js"></script> <script type="text/javascript" src="http://tablesorter.com/jquery.tablesorter.min.js"></script> <script type="text/javascript"> $(document).ready(function() { //for each table row, set it's background to light grey $('tr:even').css('background','#eee'); //make it sortable $(".csvTable").tablesorter(); }); </script> </head> <body> <?php # 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 "<table class='csvTable'>"; # open the csv $handle = fopen($csv, "r"); $data = fgetcsv($handle, 1000, ","); echo "<thead><tr>"; # column headers from 1st row of csv: foreach($data as $value) { echo "<th>$value</th>"; } echo "</tr></thead>\n<tbody>\n"; # data rows: while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) { echo "<tr>"; foreach($data as $value) { # write table cell data echo "<td>$value</td>"; } echo "</tr>\n"; } echo "</tbody>\n</table>\n"; } else { echo "Can't connect to google, sorry!"; } ?> </body> </html>
cheers for this, interesting stuff as always.
really wonderful stuff, i will be including your site as my fav.
Awesome. Nice script and it looks good too!
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.
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?
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!