Update 6/14/2007: Dion Almaer has published a nice Javascript helper which makes working with spreadsheets much nicer, I’ve moved my stuff to it and it’s a big improvement over using the Google JSON api directly.
Publishing dynamic content on your website in a format that you can style with CSS has gotten a whole lot easier with Google’s simple JSON based Javascript API’s. The two I’ve found myself playing with lately are the Google Ajax Feed API for publishing RSS and Atom feeds and the Google Spreadsheet API
for publishing little snippets of text that should be easily editable.
Say you want a simple headline on your homepage that you can change daily. Here’s how you’d do it with the Google Spreadsheet API:
1. Create a spreadsheet making the first row the column headers (important because you’ll refer to the cell by the column header):
2. Go to the publish tab and publish the spreadsheet: http://spreadsheets.google.com/pub?key=pouqRkV5D_eZT_VdOKu7CQA. Note in this example the key is pouqRkV5D_eZT_VdOKu7CQA
3. Use a simple container div and some javascript to display the field:
<div id="headline"></div>
<script type="text/javascript">
function displayContent(json) { document.getElementById('headline').innerHTML = json.feed.entry[0].gsx$html.$t; }
</script>
<script type="text/javascript"
src="http://spreadsheets.google.com/feeds/list/<b>pouqRkV5D_eZT_VdOKu7CQA</b>/od6/public/values?alt=json-in-script&callback=displayContent">
</script>
Note: the spreadsheet key in the URL that needs to be replaced. Also, entry[0] refers to row 2 in the spreadsheet (because row 1 contains the column headers and is not considered an entry):
4. The text appears dynamically on the page:
Palm releases Linux based Foleo
5. When it’s time to update, make your changes to the spreadsheet, go the publish tab, and republish it and the changes will appear on your site!
The only real downside I can think of with this simple approach is that it’s not SEO friendly so if you want all search engine crawlers to follow those links you publish, you’re better off with a non-javascript solution. The Spreadsheets API google group is a good resource and Pamela Fox has some examples to look at.
if you’re just looking to get dynamic content displayed as it appears in the spreadsheet, you might actually be better off just using an iframe with the embedded published URL – this way you get all the formatting too… and no javascript…
Use the PUBLISH tab within the spreadsheet editor – then go to the “more publishing options” link – and select the “HTML to embed in a web page” option to generate the iframe code snippet… then adjust the iframe size to match you page/blog.etc…
You can check the “re-publish automatically” option or publish manually…
have fun!
Pingback: meneame.net
Anieto2k, thanks for the Spanish translation!
JR, the reason I don’t like the iframe approach is two fold:
1. Presumably you want more than one field on a page editable and rather than create one spreadsheet per field you want to edit and iframing each one, I prefer to put all the fields in one spreadsheet. If you’re just trying to republish a spreadsheet on your site though, then I agree that the iframe approach is nice!
2. With an iframe you have no control of the styling of the contents whereas with the JSON approach the text displays on your page and therefor inherits your regular CSS styles.
i can’t seem to get this to work.
aside from pasting the code into a page and then changing the URL to match the published spreadsheet. what else do i need to do?
http://spreadsheets.google.com/pub?key=pPhWzPZYf7OX04wmb_emDfw
I like this approach and changed to code to be a bit more universal and allow to fill multiple fields taken the corresponding id from the spreadsheet as well.
var cmstoken=””;
function displayContent(json) {
i=0;
while (json.feed.entry[i].gsx$field.$t !=””) {
document.getElementById(json.feed.entry[i].gsx$field.$t).innerHTML = json.feed.entry[i].gsx$content.$t.replace(/\n/g,””);
i++;
}
}
document.write (“”);
The last line should read:
document.write (“<scr”+”ipt type=’text/javascript’ src=’http://spreadsheets.google.com/feeds/list/”+cmstoken+”/od6/public/values?alt=json-in-script&callback=displayContent’></scr”+”ipt>”);
I would love to see a response to jgodsey – I am having the same problem.
I also tried to use the Spreadsheet helper from Dion and am having the same problem.
I recommend checking out http://www.freesmartwebtools.com. It allows you to implement a cms simply into basic html with no server-side programming and no database. It is so cool.
I am having the same problem,
side from pasting the code into a page and then changing the URL to match the published spreadsheet. what else do i need to do?
This is my code
function displayContent(json) { document.getElementById(‘headline’).innerHTML = json.feed.entry[0].gsx$html.$t; }
Great post. I just published my portfolio ( http://www.grifo.tv ) built with CoffeeScript, Backbone.js, jQuery Isotope, Paper.js and Google Docs spreadsheet as CMS. It is opensourced on Github ( https://github.com/grifotv/grifotv-portfolio ), feel free to fork it.
Wow, great work Danilo!