Google Docs as a lightweight CMS Pt1

5 Comments | | February 16th, 2010

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

Disclaimer: I am not a php or security expert, so I can’t be responsible for holes in these scripts – let me know if you spot any potential disasters!

View live demo

1. Create a Google Docs spreadsheet and publish it

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)

2. 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>

3. 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.

<?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!"; }
	
?>

4. 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:

		<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>

5. 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 my 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:

	
	<!-- load jquery and tablesorter plugin -->
	<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>

5 Responses to “Google Docs as a lightweight CMS Pt1”

  1. cheers for this, interesting stuff as always.

    irk
    17 Feb 10 02:13

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

    Sultan
    09 Apr 10 05:48

  3. Awesome. Nice script and it looks good too!

    Promethius
    24 Apr 10 03:04

  4. 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.

    Paul
    26 Apr 10 11:31

  5. An outstanding share! I have just forwarded this onto a colleague
    who had been doing a little homework on this. And he actually bought me breakfast because I found it for him.

    .. lol. So allow me to reword this…. Thanks for the meal!

    ! But yeah, thanks for spending the time to discuss this topic here on your website.

    ingersoll rand
    07 May 13 06:48

Leave a Reply