.

We've talked in the past about a creative, non-gaming example that visualized Google spreadsheet data. Since that post, we've had a few people inquire about some of the details behind that application. There's a strong desire to visualize spreadsheet data, and this post outlines not only the Sharendipity components used, but also some of the hidden features of Google Docs that make the web services possible.

Web Service Components

The featured example uses a shared component type, "Google spreadsheet reader". Internally, this component uses "Google Service - cell spreadsheet reader" to interface with a specific Google Doc. Both components are configured with a URL describing where the spreadsheet has been published. Note that this URL is not the URL you would see in your browser. Rather, it is a specific URL describing the published version. The steps for acquiring the publishing URL are below.

Google spreadsheet reader

This spreadsheet reader was designed specifically for visualizing a two-column layout. It assumes that the first column contains labels and the second column contains values. It is intended to be a convenience interface for these types of data layouts. If you'd like to access arbitrary data layouts, I recommend building new logic around the generic cell spreadsheet reader defined below.
Methods:

• getValue(String label) - Returns the value that corresponds to a specified label.
• getRowTuple(int index) - Returns a string for the specified index into the values. The string returned is of the form "label,value".
• getValueMap() - Returns the hash map of all the data in the spreadsheet. It is a map of Strings and is keyed by the spreadsheet labels.
  

Google Service - cell spreadsheet reader

This web service is a raw conduit to your Google spreadsheet. It is the underlying web service used in the above reader as well. It assumes nothing about the layout of your data and provides a single method to access a specific cell. This reader uses a brute force technique for finding data so it is recommended that you cache the values once you've extracted them.
Methods:

• getCell(String cellID) - Returns the value for a specified cell. An example cellID would be "A3" or "D104", etc.

Accessing Google Doc Feeds

In order to access your Google spreadsheet through these components (or other Sharendipity web services) you must first publish the document. The components described in this post assume the data is published in a particular format as well. The following steps outline how to do this within Google Docs.

1. Open up your spreadsheet from Google Docs
2. Publish it using the blue "Share" button in the upper right hand corner of the document window. When the publishing dialog appears, click the "Start publishing" button to begin the publishing process.
3. Next, change the link type from "Web Page" to "ATOM"
4. Change the link type from "List" to "Cells"

The web service URL will be printed in the preview box. This is the URL that you will feed into the Google spreadsheet reader component using the URL property.

Consuming Google Components

The two existing Google components have customization points that make them easy to reuse. Each time you drag one of them out of the Explorer and into your application, you will be prompted to enter information for your spreadsheet, including the URL you configured in the previous section. The one caveat to this is if you define a variable inside a method or behavior that is of one of these types (rather than explicitly dragging it into your application). In that case, you must initialize the properties programmatically.

• The Google spreadsheet reader has the following configuration screen, and assumes a strict layout inside the spreadsheet. The screen has the same general form as other integration steps and was defined by the author of the component.

• The Google Service - cell spreadsheet reader has just one configuration step - describing the URL for your published spreadsheet feed.

These aren't the only elements in the Food Drive application. However, they are the foundation. Once you have access to the data, it's just a matter of using it to scale the objects you are using in the graph. In the Food Drive case, the bar graphs were created and their size was calibrated according to the appropriate value in the spreadsheet.
Now you're ready to start visualizing your Google spreadsheet data! These components don't meet your needs? You'd like to access the data in a different format? No problem... you can always create your own web service. We''ll follow up later with a post that describes this process in more detail.