The start page of Excel where you can get the Medal Tracker templateMedal Tracker is a new template in Excel that uses three apps for Office to quickly find and visualize information on Olympic medals. To try it for yourself, download Office 2013 Preview, open Excel, and choose Medal Tracker from the list of templates on the start screen. You can also watch the short demo video.

In this post we'll look at how the template was designed and focus on the Medal Tracker app, which controls the data in the workbook. The goal in creating the template was to make it easy for users get the data, organize it, and visualize what they're interested in. The following diagram shows the relationships among all the components of the template. To understand the design, we'll follow the flow of information from the bottom to the top of the diagram, starting with the client-side code that runs the app in Excel.

A diagram showing the architectural relationships among the components of the template

Medals data in Excel

The goal of this app is to bring the medals data to users so that they can use the power of Excel to organize and analyze the data. We'll start by showing two important patterns we use to give users control over the data.

The Add to Spreadsheet button that lets users insert data from the appNaturally, the most important operation is inserting the data. The simplest implementation would be to build the table of medals data The Medal Tracker app lets the user insert data as a table and bind to that tablein memory and then insert it as a table when the user clicks the Add to Spreadsheet button. The problem with this design is that the app no longer has any knowledge or control over that data once it's inserted. So immediately after insertion, we'll create a binding to that table. The two greatest benefits of creating a binding are being able to listen for data-change events in the bound data, and to write to the bound cells even when the user hasn't selected them. In this app, we make use of that second benefit with the Refresh button. The "Refresh the data" button in the Medal Tracker app updates all the information in the tables that are bound to the app.When the user clicks it, we not only update the information displayed with HTML in the app pane itself; we also overwrite the old data in all the different table bindings that the app has created in the workbook. The following code snippet shows how insertion and binding are accomplished together in the app:

// Insert the table with the Olympics data.
_doc.setSelectedDataAsync(olympicsTable, {
    coercionType:
"table", asyncContext: {
        olyBindingData: asyncResult.asyncContext.olyBindingData
    }
}, processOlympicTableData);

// Callback function: creates a binding once the data is inserted.
function processOlympicTableData(asyncResult) {
    if (asyncResult.status != "failed") {
        _doc.bindings.addFromSelectionAsync("table", {
            id: asyncResult.asyncContext.olyBindingData.excelBindingName, asyncContext: {
                olyBindingData: asyncResult.asyncContext.olyBindingData
            }
        }, hookOlympicBindingEvents);
    }
}

The Bing Maps app, showing all medals for each country/regionA user could then easily use parts of the bound table for an Excel chart or a visualization-style app like the ones on the right in the template. Then these visualizations can update in real time as often as the app modifies the data in its bound cells. For example, the Bing Maps app shows markers whose relative sizes indicate the medal counts for each country/region. Since the app is bound to the table from the Medal Tracker app, the marker sizes update whenever the table does.

The second pattern is the master-detail interface. This is the design that allows the app to show relevant information for any selected cell: as an example, selecting Selecting a sport in the table shows information on that sport in the Medal Tracker app.Canoe Sprint in the table causes the app to show the top canoeists and the top countries for that sport. This pattern is powerful but easy to implement. The app simply catches selection-changed events and then retrieves the information on the selected cell and the table it's in. This data is used to display information that's relevant to what the user is interested in at each moment. You may notice that the Bubbles app is also using a master-detail pattern to show contextual data visualizations. When you click on a country in the table, it shows animated bubbles for every sport that country won medals in. This makes clicking any cell in a table quite a rich experience, and it shows how anyone can make their document interactive by adding apps that listen for changes in selection.

The performance of the master-detail interface is critical because the value of the design is that it's much faster than letting a user type in a search field. To improve the performance for this app, we cache the data and images for every selection-changed event with Internet Explorer. That way, we eliminate unneccessary web service calls if the data hasn't been updated.

The top of the Medal Tracker taskpane uses two useful UI elements: the title bar and navigation tabs.The user interface (UI) is often included as an afterthought in application design, but we think it's important that apps for Office have great interface design. HTML, CSS, and public libraries like JQuery make UI design exciting and easy. To help focus your design, we provide UX Guidelines. Suggested elements for Task Pane apps that we use in the Medal Tracker app include the tabs near the top and the header bar with branding or a title.

Medals data on the web

So we know what to do with our medal-tracking data once we have it. But how did all this great data get to our app in the first place?

The Bing News feature of the Medal Tracker app gets recent articles on the Olympics.First we'll look at the Bing News service: when users click the News tab in the Medal Tracker app, they get the most recent articles on the Olympics. Getting news articles isn't the main feature of the Medal Tracker app, but it was the easiest part to implement. The Bing News APIs expose REST and return JavaScript Object Notation (JSON). We could call the service directly from the client-side JavaScript, but there's a problem with that design: it would expose the developer ID that should be kept secret. Our solution is to add our own server-side layer: the Medal Tracker Web Service. Then we expose this service using Windows Communication Foundation (WCF) so that it can return JSON to our client-side code. We expect this type of solution to be popular in apps for Office, since they will often be pulling data from existing sources that require developer secrets. Another advantage of a server-side layer is the protection of intellectual property, since that code is not publicly available.

The Stats.com service is where we get all the data on medal counts, athletes, and events. Partial diagram showing the relationships among the three web services used in the Medal Tracker appUnlike the Bing News service which returns JSON, Stats.com returns only XML. To unify the two formats, we convert the XML to JSON in the Medal Tracker Web Service. That way our JavaScript client-side layer can get Bing News data and Stats.com data as JSON.

Once the data gets to our client-side app code, we can expose it to users through HTML/CSS and through the JavaScript APIs for Office.

Building the app      

I'll end with a note on our tools for creating the Medal Tracker app. As you can see in the design diagram, we used Windows Azure Web Sites for the web services and the client-side code. You can try hosting with Windows Azure yourself with the 90-day free trial. We used Team Foundation Server source code control so that multiple developers could work concurrently on the app. The publishing functionality of Visual Studio made it easy and quick to deploy and test. Overall we were impressed with how seamlessly these products and services worked together to make the development experience simple and painless.

Feel free to ask questions about the design for this template or your own apps in the comments section, or visit the Apps for Office forum. Have fun designing!