Introducing the Excel Services Windows 7 Gadget

The past few posts have shown some of the basic REST functionality you can expect to get from the new Excel Services that will ship as part of SharePoint 2010. Before going into some of the more advanced features (well.. feature) of Excel Services REST, I thought I would show you a Windows Gadget we implemented for internal use. I will make the source code available either through the MSDN Code Library or through this blog in the very near future.

Now, gadgets are not hard to write, but they do require some knowledge of HTML and JavaScript. This Gadget is an absolutely great tool for people who have any sort of spreadsheet that contains any type of data that periodically changes and which is usually used as a sort of tracking application – instead of having to write the gadget yourself, you can just use the Excel workbook. You can think of any type of workbook – from the workbook that tracks your portfolio, to the one that displays information about your warehouse stock, to the one that tracks your project progress. This data is usually “locked” inside spreadsheets and one needs to manually open the workbook and actively look in. This gadget opens up an opportunity to use virtually any piece of information that resides within such workbooks and make it easily accessible right on your desktop – the gadget will make sure to periodically update the data so that with a simple glance, you can see exactly what’s going on!

Once installed, you add the gadget just like you would any other – run the “Desktop Gadget Gallery” in Windows 7 and double-click the Excel icon:


Once added to the desktop, you need to properly configure your gadget to show whatever information you want. In this example, we will show information about bug counts. For starters, you click the little wrench icon – that will get you to the settings screen:


Next, you type the name of the URL for the workbook in the “Workbook” field and hit Refresh. The Gadget will go and use REST discovery to figure out what elements exist in the workbook:


As you can see, the gadget lets you choose from any of the entities available in the workbook. For now, lets choose the “PersonalBugs” – it is what I like seeing on my desktop, so I can closely monitor my status.


As you can see, the chart is not really designed to be used in such a small space – clicking on the little arrow will “undock” the gadget (a term inherited from Vista) and will show the chart at full size:


Now, this looks much better. It can be even better though – if you noticed, in the first screenshot of the “Settings” screen, there are two combo boxes – one for the actual object you want to see and the other for the thumbnail. The nice thing is that you can set up the gadget to show you one element when it’s “minimized” and one when it’s “maximized”. In this example, we conveniently have a chart called “MiniPersonalChart” which contains the chart in a smaller format. Here is how you set the two objects up in the gadget:


Now, when the gadget is “minimized”, it will show the chart that’s better suited for smaller space (it has no header, legend or axis):


That’s it for this post! Next post will show how the gadget can show not only charts, but also show ranges!

Comments (13)

  1. SergeiG (aka XLGeek) says:

    Shahar, this is ABSOLUTELY AWSOME scenario.  There are several companies out there trying to charge money for what the users now can have out of the box with MS products: Excel CUBE OLAP functions and Excel Services on SharePoint 2010. REST API in combination with your gadget = real time BI on a user’s desktop. Best part is that the named range exposed via the gadget can come from the same workbook that contains all the rules and presentation layouts for some main corporate report or a scorecard. Single point for business rule definition.  Imagine the productivity (or potential error correction time) since there is no need to keep rule definitions in several places.

    Here is an example of a 3rd party commercial offering that ES REST + Gadget combo can easily replace with what comes out of the box:

  2. Gad Epstein says:

    Shahar, if I insert a rnage from Excel spreadsheet into my blog and see the cells, is it possible to change the cells in my blog and it will change it in the spreadsheet (wrtite back to the spreadsheet as oppsed to just reading from the spreadsheet)

  3. Shahar Prish says:

    You should be able to either do it, or get very close.

    i will make a post about this in the future – we’ll see how close we can get.

    you can for sure put a workbook in your blog for editing – the only question is how integrated will it look and whether you can limit for a single range.

  4. Jean-Phillippe says:

    That is a pretty cool scenario and I look forward to using it when it is available.  But I looked at the 3rd party offering and that PUSHBI does a lot more than the ES REST + Gadget combo.   As an end user, I can subscribe to KPIs, it feeds data to a mobile device, it can use any reporting solution for the click through.   I don’t have to upgrade my entire enterprise for hundreds of thousands of dollars…  :o)

    The ES REST scenario is very limited and really good need something simple and you have a technical business user.   Neat, but I wouldn’t over sell it with the whole selfservicebi thing too…

  5. SergeiG (aka XLGeek) says:

    Sounds like Jean-Phillippe works for the PushBI people.  REST + Gadget combo if you read and understand how it works allow the user to do exatly what you are describing – subscribe to any KPI in the published workbook.  Better yet, KPI in the gadget can be referenced by its plain name (member name from the cube), so any user of the master report can do it. The next posting in this blog also describes how just by hovering over a URL you can show additional detail about the data points.  Plus, support for hyperlinks enables linking to any detailed reports accesible via URI.  Excel provides HYPERLINK function that enables full URI parameterization. The possibilities for linking to additional content are limitless.  The mobile phone story is even simpler: REST is HTML visible anywhere on any device that doesn’t require a special app. New version of Excel Services although very valuable by itself is just a part of SharePoint 2010.  It is very well worth the upgrade in my opinion.

  6. David Webb says:

    I can't get the gadget to open my workbook.  I can open from IE.  But the Gadet says 'There was an error trying to fetch charts'

    Any clue why.

  7. Donex7 says:

    Hello everyone

    I need help why I can't connect to my excel file. There is an error message 'There was an error trying to fetch charts." My file location is C:UsersDonexDocumentsOffice Files2011 2nd Sem Target.xlsx. The content of my excel file is just a plain table with figures that I want to monitor in the side bar gadget. Please I need help on this. I have no indepth knowledge on programming. Thank you.

  8. Tom says:

    I'm with you Donex. I can't get it to work with a local file….

  9. Wind says:

    Anyone has been able to run this with a local xls or xlsx file?

  10. You know what'd be cool? A Windows Phone Live Tile that displays an always-up-to-date chart via Excel Services. All possible today…anyone out there wanna build it? 🙂

  11. Kunal Pathak says:

    Anyone to help or comment on if it is possible to make this work with LOCAL files. Any way out suggested or any other tool to accomplish the same.

    Any help would be much Appreciated.

  12. James says:

    Was excited to find this so I could do this..but the gadget platform has been completely scrapped thanks to stupid Microsoft and their piece of crap Windows 8.

  13. nahmed says:

    It looks very attarctive tool for me atleast.

    I am working windows 7, 64bit

    There is no automatic or maual option.

    I cant see the personalbugs options.

    There is no thumbnails seen there on my system.

    refresh every one minute

    Message showing that "Gadget has not been updated yet"

    Any body like to resolve this.