Bringing it all back home – using advanced REST functionality with the Excel Services Gadget

In the past two weeks I detailed both the new REST functionality in Excel Services as well as the Excel Services Gadget (which will hopefully be available for download soon). In the previous post I showed how you can use URL parameters to REST URLs to modify the result you get back. In this post, I will show how you can create an incredibly compelling story for users by using both that and the gadget that I introduced earlier this week.

To start, I have a workbook that contains information about my team bug counts. On top of everything else inside that workbook, it also contains a cell where, when an alias (email) is entered, pars of the workbook recalculate to show the bug trend/counts for that specific person:

If you click on the image, you will see the cell called “Alias” (the one that currently has “shaharp” inside it). the green table you see contains information specific to the Alias cell (so, if you change the cell to another value, the data will update accordingly). On top of that we also have a table of links in the workbook:

For each person there are two links – one under the alias of the person and the other called “Home Site”. Here are the formulas for both:

Under the alias, the formula is: =HYPERLINK("https://tsdlt2/_vti_bin/ExcelRest.aspx/Docs/TeamBugs.xlsx/Model/Charts('PersonalBugs')?Ranges('Alias')=" & A1, A1)

Under the Home Site the formula is:=HYPERLINK("https://tsdlt2/PersonalPages/" & A1 & ".aspx", "Home Site")

As you can see, the first formula resolves to a hyperlink to a REST URL where the object shown is the same “PersonalBugs” chart but where for each person, a value is set into the “Alias” cell. In this way, a single workbook/chart can serve multiple purposes. Here, we pass in the alias to a cell and then get the chart that corresponds to that person.

Here’s how this works. First, you can see that the link show up in the gadget itself:

Now, remember, there are two links here. The Home Site one is just a run of the mill link and when clicked, will open up in your default browser:

As you can see, the link corresponds to an alias which then opens up in the browser according to the formula.

The interesting part is what we can do with the other link though. The gadget recognizes that the link is actually a REST link – and so it decides that it can actually display it as part of the gadget in a flyout:

Once the gadget loses it’s focus, or you click inside it, the flyout will collapse back into the gadget.

And that’s it for this week. This week I tried to show you some interesting ways of using the new Excel Services REST APIs and some of the new and exciting opportunities it gives developers and users. There was a post about the “essentials” of REST in Excel Services – a kind of “cheat sheet” for all the things REST can do. There were two posts about the Excel Services Gadget which showed the concept and then a post that explained the more advanced stuff REST can do by passing parameters into the workbook via the URL.

Next week will show the actual code running behind the gadget. Have a good weekend!