Publishing Events - Mashing up Google Spreadsheets, Calendar and Base

Stay organized with collections Save and categorize content based on your preferences.
Ryan Boyd, Google Data APIs team
June 2007
  1. Introduction
  2. Design and Implementation
  3. Obtaining and Deploying the Event Publisher
  4. Running the Application
  5. Next Steps and Potential Improvements
  6. Appendix


Have you ever wanted to maintain a bunch of information about meetings, events, or conferences in a Google Spreadsheet and have that information be easily shared and searched? Well, at Google, we do just that with our Developers Event Calendar. The data that feeds into the calendar comes from a large variety of Googlers across the entire company -- many of whom have edit access to a Google Spreadsheet where the data is stored. In the spreadsheet, unconfirmed events exist along with well-publicized engagements. People even sometimes enter prospective events -- even before a proposal is submitted or a potential sponsorship investigated. An "editor" is responsible for publishing events and recording the published events on a column in the spreadsheet.

Why don't we maintain these events directly in Google Calendar? Calendar is fine for identifying the when's and where's of an event, but when it comes to maintaining a lot of structured information-such as speaker lists, submission deadlines, and so on-a spreadsheet is more suitable.

Screen capture of Google Spreadsheet with events
The source Google Spreadsheet with the list of events

One of my fellow Googlers suggested that we continue to maintain the events in Spreadsheets but remove the manual process of publishing them to our external public calendar. I also wanted an interesting project based on multiple Google Data APIs ("GData" for short) to demonstrate at Mashup Camp, and thus the Event Publisher was born.

Event Publisher is a quickly developed, proof-of-concept application that maintains a list of events in Spreadsheets and publishes them to both Calendar and Google Base. Google Calendar provides a great way to share events and give people an easy way to include the events in their own calendar view. While Calendar does provide the ability to search for events, Google Base provides yet another venue for publicizing events and excels at storing structured data in a way that's easy to search.

Spreadsheets, Calendar, and Base each provide an API that supports full read/write operations. Even better, each of these services exposes access to their data using the Google Data API protocol!

Design and Implementation

The design phase of this project was pretty simple: I knew that I wanted to make a web application for publishing spreadsheet data on a calendar. While the long-term goal was to build an application that could be used for the Developers Event Calendar, the short term goal was to produce a functioning proof-of-concept mashup that could be demonstrated during Mashup Camp. With these goals in mind, I chose the GData Java Client Library since it's one of the more mature client libraries, and I'm familiar with writing Java web apps. Using one of the provided client libraries freed me from having to worry about the raw GData protocol and XML data model and instead concentrate on implementing the relatively simple application logic required to publish events.

I downloaded the Java Client Library and started designing the class model for the event publisher. I created a servlet, aptly named EventPublisherServlet, which acts as the controller for all requests coming into the web application. I also created a class named EventPublisher to handle the business logic for interacting with Calendar, Base, and Spreadsheets. Finally, I wrote a Bean class for storing event data. The user interface of the application was developed using a handful of JSPs.

The beauty of working with multiple GData services is that they are consistent across their interfaces and many of the client libraries include service-specific helper classes for your favorite Google services. Since the Client Library had the same Service, Entry and Feed classes for the three services I was using, I was able to use very similar code for retrieving events from Spreadsheeds and publishing them to Base and Calendar. This shortened my development time significantly versus what would have been required to work with three distinct APIs. You may wish to examine the publishEventToCalendar() and publishEventToBase() methods in the EventPublisher to see the similarities between the different methods.

Obtaining and Deploying the Event Publisher

The Event Publisher application is distributed as a part of the Google Data Java Client Library download. After you download the Java Client Library, look in the directory java/mashups/eventpub. Please see the project structure section in the appendix for more information on the meaning of each file in the example.

The application has a number of dependent libraries which need to be downloaded before building the Event Publisher. After configuring the appropriate build and runtime options (see the README.TXT file), Ant can be used to compile the Java classes and generate a WAR file. This WAR file can then be deployed to your favorite servlet engine, such as Tomcat 5.5 which I used in testing this application. Tomcat, running under a default configuration, requires only that the generated deploy/EventPublisher.war file be copied to its webapps directory. It will then be auto-deployed and accessible via http://hostname:8080/EventPublisher.

Running the Application - Process

Screen capture of AuthSub authentication process
Authenticating to Google Spreadsheets account via AuthSub.
  • The application is loaded by visiting /EventPublisher on the host running the application. In my case, I'm running the application at http://localhost:8080/EventPublisher
  • Authentication for Google Spreadsheets
    • The user clicks on the "authenticate" link that is generated by the static function: AuthSubUtil.getRequestUrl().
    • The user is redirected to the Google Account services where, if the user is not already logged into Google, they are prompted for their credentials.
    • The user grants the Event Publisher permission to access their Spreadsheets data.
    • The user is redirected back to the Event Publisher with a single-use AuthSub token in the URL. The Event Publisher exchanges the single-use token for an AuthSub session token using the AuthSubSessionToken service.
    • Code: See EventPublisherServlet.processAcceptAuthSubToken()
    • Documentation: See the AuthSub documentation
  • Select spreadsheet/worksheet
    • The user is redirected by the Event Publisher to a list of the spreadsheets accessible by the authorized user.
    • After selecting a spreadsheet from which the event data should be retrieved, the user is also prompted to select the appropriate worksheet in the selected spreadsheet.
    • Code: See EventPublisher.getSsList() and EventPublisher.getWsList()
  • Screen capture of mapping fields Screen capture of mapping fields
    Mapping required data to spreadsheet columns and previewing events to be published.
  • Retrieve and map column headers
    • The application then uses the Spreadsheets cells feed to retrieve the first row of data from the spreadsheet -- this first row represents the column headers. Each piece of needed data is listed for the user and they are prompted to select the column header that best matches the data. In the screen capture to the right, each needed data type has a select box next to it where you'd choose the corresponding column header. This mapping is stored in the user's session using the servlet container's session handling mechanism.
    • Code: See EventPublisherServlet.processListEvents()
  • Retrieve entries and store in Beans
    • After the columns are mapped, the Event Publisher retrieves the Spreadsheets list feed containing a single entry for each row in the worksheet. Each row represents a single event, and the data from the row is populated into instances of the Event Bean. The collection of Beans is then stored in the session and is also outputted to the screen for confirmation -- using the outputEventList.jsp page.
  • Publish the Entries to Calendar and/or Base
    • The user selects the appropriate publishing target (Calendar, Base, or both) and clicks the 'Publish' button. Events are pushed from the collection of Event objects stored in the user's session to the appropriate services using the publishEventsToBase() and publishEventsToCalendar methods in EventPublisher. These methods create entry objects of the appropriate types for the services: CalendarEventEntry and GoogleBaseEntry. These entries are then posted via a HTTP POST performed by the service classes CalendarService and GoogleBaseService
    • As events are published, the edit URLs returned by each target service are stored in the Google Spreadsheet using the EventPublisher.updateSsEventEditUrl() method. For future iterations of the publishing process, events that contain an edit URL for each respective service in the Spreadsheet are edited rather than created. This prevents duplicated event data.

Next Steps and Potential Improvements

As mentioned earlier in this article, this application is currently only a prototype. It is not intended to be production-ready code. In order to make the Event Publisher production-ready, some error handling would need to be included. Currently, errors are only sent to stderr. Instead, errors occurring during the retrieval, publishing, or other actions should be displayed in the user's browser.

Additionally, it should be noted that Google Base, to maintain the integrity of its data set, expires items after a period of time if they are not updated. If an event has already been added to Google Base, the Event Publisher attempts to update the event using the edit URL stored in the Google Spreadsheet. If the event item in Base has expired, the edit URL is no longer valid and a 404 error will be returned at the time of publishing. A workaround would be to attempt an insert of the event upon receipt of a 404 response.

Another feature that could be added to the Event Publisher application is the ability to store and publish the exact times of events as opposed to only the date. As Google Base requires times to be attached to events, storing the time of each event would prevent arbitrary times from being associated with the events in the Base entries. Calendar can also present the events at the appropriate times.

You may also wish to have a persistent data storage mechanism to store configurations for particular spreadsheets -- including column mappings. This could take the form of a separate Google Spreadsheet, a database, or local disk storage.

I hope this article triggered your imagination to come up with some great new ideas for applications utilizing the Google Data APIs! If you're interested in looking at the source of this application or building it and modifying it to meet your needs, you can find it in the Google Data APIs Java Client Library under the mashups/eventpub directory. Also, we welcome your feedback on this article in the Google Data APIs developer forum. For questions related to a specific service, please post them in the service-specific discussion groups.


Additional Resources