Using Google Spreadsheets for Content Management (part 1)

March 14, 2010

If you're looking to use Google Spreadsheets as a content management system to populate your learning management system (LMS) or other hosting platform, you've come to the right place. I'm going to show you two scripts that allow you to send out data from Spreadsheets using javascript.

The first is a script that Ruben Perez in our EMEA region adapted from this javascript query api to automatically publish webinar registration content into our Learn.com Learn Center. The great thing about this script is that it can limit the amount of items shown and has a trigger to automatically unpublish each row's content once the registration date has passed. Keep in mind, however, this is NOT intended for sensitive data. Because you must keep the spreadsheet public, any information in the spreadsheet will not be secure, so you should only use this for non-sensitive publishing of content.

First, you'll need to create a Google Spreadsheet with several column header categories. For the purposes of this tutorial, we'll use: "Title", "Description", "Date", "Time" and "Registration".

Once you create the spreadsheet, you can populate each row with your data:

Now, you will have to make the spreadsheet publicly available. On the right, click Share, then "Publish as a web page". From the pull-down menu, select the page you wish to publish and click "Start Publishing". Be sure that the link to the published data is set to "Web page" and "All sheets". Then copy and paste the link below. Notice in the link there is a key:

http://spreadsheets.google.com/a/google.com/pub?key=tGz1WNlJv-oP5qc-4Oq1u_A&single=true&gid=1&output=html

We will copy and paste that key to use in our javascript below.

Once you have set up your spreadsheet, you'll need to embed the javascript on the intended publishing page to pull in the data. You can use this code below (adapting it as you need):

<div id="table"></div>

<script type="text/javascript" src="http://www.google.com/jsapi">//<!--
//-->
</script> <script type="text/javascript">//<!--
google.load('visualization', '1', {packages: ['table']});
//-->
</script> <script type="text/javascript">//<!--

var options = {allowHtml: true,'showRowNumber': false};
var data;
var queryInput;
var formatter_date;
var formatter_time;
var formatter_link;

var query = new google.visualization.Query(
'http://spreadsheets.google.com/tq?range=A1:E76&gid=1&headers=-1&key=[insert key here]');

// Apply query language.
query.setQuery('SELECT A,B,C,D,E WHERE (C is not null and C > todate( now())) order by C asc limit 20 ');
function sendAndDraw() {

// Send the query with a callback function.
query.send(handleQueryResponse);
}

function handleQueryResponse(response) {
if (response.isError()) {
//alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
var displayError=document.getElementById("table");
displayError.innerHTML='Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage()
return;
}
data = response.getDataTable();
formatter_link = new google.visualization.TablePatternFormat('<a href="{0}" target="_blank">register</a>');
formatter_time = new google.visualization.TablePatternFormat('{0} EDT');
formatter_date = new google.visualization.DateFormat({formatType:'long'});
formatter_link.format(data,[4]);
formatter_date.format(data,2);
formatter_time.format(data,[3])

var table = new google.visualization.Table(document.getElementById('table'));
table.draw(data, options);

}

function setQuery(queryString) {
// Query language examples configured with the UI
query.setQuery(queryString);
sendAndDraw();

}

google.setOnLoadCallback(sendAndDraw);

//-->
</script>


Once you do that, the contents of your spreadsheet should publish into a nice table on your page that looks something like this:

Note: very rarely the Google hosting for this javascript goes down, so you may wish to provide a direct link to the spreadsheet just in case. Something like this should suffice:

<div id="text">If the registrations do not appear below, please access our <a href="http://spreadsheets.google.com/a/google.com/pub?key=tGz1WNlJv-oP5qc-4Oq1u_A&single=true&gid=1&output=html">webinar registration page</a>.</div>

Would love to see any other uses of this code. Please feel free to leave comments below on any successes you've had.

-cj-