Using Google Desktop Gadgets to Promote Content

March 16, 2010

Google makes a bunch of free desktop gadgets (also known as widgets) that you can customize for your own use. Today we're going to look at a Google gadget you can use to push out content from a Google Spreadsheet, which I've found really useful for promoting changes to our Learn and Help Centers. It allows you to push out new information to clients on an opt-in basis without having to spam them with email.


First you will have to create a Spreadsheet with the information you want to push out. In this example, I've set up a spreadsheet with the headers: "title" and "answer". I use these to give each entry a title, as well as some html on the page that will slide out when clicking the link. I include a link to the actual page at the top of the slide-out window.



Once you have populated your spreadsheet, you must first make the spreadsheet public, which means you should not publish any data that you wouldn't want to be public facing. Go to the Share button on the right and click "Get the link to share". Make sure you have checked "Allow anyone with the link to view (no sign-in required). You will need to do this to allow the gadget to access your spreadsheet.

To bring in your feed, you will also need to include a key from your spreadsheet that will allow the gadget to talk with the spreadsheet. To get the key, go to Share and click "Publish as a web page". You should see a link that looks like:

http://spreadsheets.google.com/pub?key=tu8X155o4B-Pr4IcgZj27tg&output=html

Copy and paste your key. You will use this key in the gadget javascript. Now download the gadget to your desktop (make sure it's not in any temporary download folders or it won't work) and unzip it so the folder "Google Gadget with RSS feed" is on your desktop. If you open this folder, you can click gadget.gmanifest to install the gadget (note that you need to have Google Desktop already installed). You can see what this gadget looks like:


To bring in the feed to your spreadsheet, open the "editme" folder. Inside you will see a file named "config_constants.js". Here you can set the key for your URL feed, the refresh interval, number of entries, etc. At the top of the screen is a bit of code:

var CONFIG_FEED_URL = 'http://spreadsheets.google.com/feeds/list/tu8X155o4B-Pr4IcgZj27tg/od6/public/basic';

You will need to simply update the part in red with the key you copied earlier. Then you should be able to bring in your content easily. Anyone who installs this gadget will be able to view the content you're pushing out.

You'll also want to change the title and description of your gadget. To do this go to the folder: C:\Documents and Settings\cjennings\Desktop\Google Gadget with RSS feed\en and open up the strings.xml file. Here you can change the title and descriptions.

Finally, you can upload a graphic header. To do so, go into the C:\Documents and Settings\cjennings\Desktop\Google Gadget with RSS feed\default folder and replace the frame_TopLogo.png file with your own graphic of the same size.


And presto, you've built your first Google Desktop gadget. Please let me know if you make any improvements or have any successful implementations of this gadget by commenting below.

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-

Using Google Spreadsheets for Content Management (part 2)

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-