Using Google Apps with the Google Mashup Editor

The thing I like most about Google's ever expanding lineup of online applications is the corresponding set of programming services. RSS, ATOM, JSON, and Gdata feeds ensure there's plenty of ways to access your data from stored in a Google app. At Hit For Six (source code), three of the tabs are populated with information from a Google data source - Spreadsheets, Calender, and Reader. Here's how to use these feeds as data sources within Google Mashup Editor (GME) to create interactive web applications.

One of the most common mashup tasks is geocoding a location to place a marker on a Google map

One of the coolest new features in the GME is the dynamic geocoder. When your creating a maps mashup it's inevitable that you'll be geocoding a location to put a corresponding marker on a map. The gm:map tag in GME lets you specify 'lat' and 'long' fields in your atom / RSS feed.

gm:map id="liveMap" data="${liveList}" latref="geo:lat" lngref="geo:long"

But what if you only have a named location? Previously you had to handle this manually, either pre-geocoding the location (as I did using Yahoo Pipes), or using JavaScript to handle it client side. The latest release of GME automates this step by supporting a geolocationref parameter that takes a field from your feed and places a marker on your map based on the result from the Google Geocoder.

gm:map id="calendarMap" data="${calendarList}" geolocationref="gd:where/@valueString"

This is perfect for a Google Calendar source where you have a location but not the literal lat/long coordinates; like the 'upcoming fixtures' tab at Hit For Six. The dynamic geocoder is not the same as the Google Maps search bar, and you can't limit its scope. To get the best results you need to include as much location context as possible when passing values (city, state, countryS). Some countries (like India) return locations for certain named locations (like sporting venues) in Maps, but won't work when using the geocoder.

A calendar is useful as a database but the Calendar UI has been designed specifically with appointments in mind

A calendar is a perfect database for time based information. I use Google Calendar to track upcoming book releases, store sporting fixtures, and record timesheets. But the UI is designed with appointments in mind and none of these use cases are appointments. Instead we can use the GME to produce a custom GUI that's right for our data, and the best way to leverage the data stored in a Google Calendar in the GME is using the ATOM feed. You can pass query parameters in to your calendar feed to filter and sort the returned entries.

http://www.google.com/calendar/feeds/radioactiveyak.com_7qt1thddqotp3eic9lud1jelq8@group.calendar.google.com/public/full?futureevents=true&orderby=starttime&sortorder=ascending&max-results=100

Start with a feed that returns only the items we're interested in. Limit by date (show future events only) and cap the maximum results (no more than 100). Then sort (by start time) on the server side. Next create your UI using the GME tags, custom JavaScript, and HTML. In Hit For Six I want to see these events listed and plotted on a map. Using the map tag and the runtime geocoder we can display the calendar entries on a map with a single line of code.

gm:map id="calendarMap" data="${calendarList}" geolocationref="gd:where/@valueString"

It's worth noting that the calendar service is relatively slow, so it's worth the effort to limit the number of returned events to prevent any timeouts. If you're interested in writing data to the calendar as well as reading it, Google's recently released Javascript client might be what you're after.

Publishing your spreadsheets with ATOM lets you create a custom database with an XML feed that updates in real time

I love the idea of using Spreadsheets as an online database. Publishing your Google Spreadsheets lets you specify an arbitrary data source that updates in real time based on spreadsheet changes. Using the ATOM output makes using spreadsheet data in GME pretty painless.

Set up and populate your spreadsheet, select publish and grab the ATOM feed address, make sure you choose 'list feed'.


The default action for entry labels is gsx:columnname, but it's worth pasting your feed into the GME feed browser to confirm. You can set up 'structured queries' to filter your datasource, and it's good practice to do these sorts of filters 'server side'.

A nice trick with spreadsheets is using a 'table of contents' worksheet that provides the feed location of other worksheets that you will use as data sources. In the Hit For Six 'Venues' tab I provide a list of countries which, when selected, populates a country specific ground list based on the corresponding worksheet.

http://spreadsheets.google.com/feeds/list/o01765357489133287312.3601895615788609870/od7/public/values

I can then add new countries and only have to update the spreadsheet, the website will reflect this change automatically.

It's not all beer and skittles

There are a couple of things you need to keep in mind when using Google data feeds rather than 'regular' RSS feeds.

• You can't spoof the feed requests to force frequent updates. Google services require specific feed request parameters (you can't add 'fake' parameters like &random=12354 or &datetime=20071022 to force the GME feed cache to update the data deterministically). That said, the GME team have negotiated a deal with the team that handles the feed caching to provide more frequent updates (in the order of 10mins rather than 6hrs). If that's still not fast enough, you can funnel your feeds through Yahoo Pipes.


• GME expects RSS feeds rather than XML data feeds. RSS feeds tend to add new items rather than modifying or removing existing ones. If you have data that changes often you may want to pipe it through Pipes first. That will let you change the calling string (by adding a random or time based parameter to the feed address) and force GME to refresh it completely.


• It's all open. Note that if you're using Google services as backends you're making that backend public. Once you set it up for your GME application to use, everyone has access to the underlying data. If that's not open enough, your GME code is an open source project by default (and requirement), so nothing's secret.