Using Google Spreadsheets for Content Management (part 2)

March 14, 2010

Now it's great to be able to publish Google Spreadsheet fields as a table in a learning management system (LMS) or other hosted platform. But what if you want to get rid of the table and have your Spreadsheet data to look more like your typical LMS content? Well, you're in luck. here's a script I adapted from Todd Huss that allows you to pull in Spreadsheet content as a JSON feed and publish it to your LMS. As mentioned before, 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:

Like the tutorial above, you'll need to set up a Google Spreadsheet with several column header categories. For the purposes of this tutorial, we'll use: "html", "description", and "duration".

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

Again, 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".

Now, you will have to get the link to share. On the right, click Share, then "Get the link to share". Make sure to check "Allow anyone with the link to view (no sign-in required)". Below there should be a link that looks like this:

Notice in the link there is a key:

https://spreadsheets.google.com/ccc?key=0Aq9IW8sjT2kIdGxSR2NYLWxHWHNVVVJOQm53U3BfUEE&hl=en

Copy and paste the key from this; we will use it 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). You will need to replace the area in red below with the key you saved earlier:

<script type="text/javascript">

function displayContent(json) {

var pTag = document.createElement("p");
endtable.appendChild(pTag);

for (i in json.feed.entry) {

var table = document.getElementById('endtable');

var divTag = document.createElement('div');
var pTag = document.createElement("p");
divTag.id = 'link' + i;
divTag.setAttribute("align","left");
divTag.style.fontWeight = "normal";
//divTag.style.color = "#666666";
endtable.appendChild(divTag);

var divTag2 = document.createElement('div');
divTag2.id = 'description' + i;
divTag2.setAttribute("align","left");
divTag2.style.fontWeight = "normal";
divTag2.style.color = "#666666";
endtable.appendChild(divTag2);

var divTag3 = document.createElement('div');
divTag3.id = 'duration' + i;
divTag3.setAttribute("align","left");
divTag3.style.fontWeight = "normal";
divTag3.style.color = "#666666";
endtable.appendChild(divTag3);
endtable.appendChild(pTag);

document.getElementById('link' + i).innerHTML = json.feed.entry[i].gsx$html.$t;
document.getElementById('description' + i).innerHTML = json.feed.entry[i].gsx$description.$t;
document.getElementById('duration' + i).innerHTML = json.feed.entry[i].gsx$duration.$t;

}

}
</script>

<script type="text/javascript"
src="http://spreadsheets.google.com/feeds/list/[replace with your key]/od6/public/values?alt=json-in-script&callback=displayContent">
</script>


Once you've done that, you should see the Spreadsheet data populated into your page with the appropriate formatting that we've indicated. This can includes links, bolding, italicizing, font colors, etc.

Again, would love to see any other uses of this code that you come up with. Please feel free to leave comments below on any successes you've had.

-cj-