Thanks to Christian Stich for putting together this series on the REST API.
Beginning our journey on all things Excel Services 2010, I thought Iâd start off with a feature thatâs been getting a lot of buzz lately since its introduction at the SharePoint Conference.
What is it?
The Excel Services 2010 REST API is a new programmability framework that allows for easy discovery of and access to data and objects within a spreadsheet. âProgrammabilityâ is almost a bit of a misnomer because in its simplest incarnation it doesnât require any knowledge of programming to use. If you can craft a URL, you can use the REST API. This makes the REST API accessible to non-developers, yet it offers capabilities that make it a very powerful tool for professional developers as well.
For example, say you have a spreadsheet saved to a SharePoint document library that looks like the one below:
Now, letâs say you are writing an internal blog post about the effect of taxes on revenue, and youâd like to embed this Excel chart into your blog post. Simple, just craft a REST URL (technically, itâs a URI, but letâs ignore that for now) that looks like the following:
and paste this into the Insert Image function in your blog software, and out comes a chart that looks like this:
Congratulations! Youâve just used the REST API in Excel Services 2010. Pretty easy, right? But wait, thereâs more!
The REST API in a Nutshell
By crafting the proper URI, the REST API allows you to:
- Discover the items that exist in a workbook, such as tables, charts and named ranges
- Retrieve the actual items in the workbook in one of the following formats:
- ATOM feed
- Excel workbook
- Set values in the workbook and recalculate the workbook before retrieving items
Is the Data Static?
The data, including charts, that is returned by the REST API is not static â itâs live and up-to-date.
With the REST API, any changes in the workbook are reflected in the data that is returned. This includes the latest edits made to the workbook, functions that have recalculated (including User Defined Functions), and external data that is refreshed.
The REST API can also push values into the workbook, recalculate based on those changes, and return the range or chart you requested after the effects of the change have been calculated. For example, in the sample spreadsheet above, the chart is displaying the effects of a 10% tax rate. With REST, you can request a chart displaying the effects of a 5% tax rate by adding a parameter to the URI that changes the value of cell B1 to 5%.
The opportunities for using REST are virtually endless. To spur your imagination, hereâs a sampling of some ways to use the REST API:
- Embed a chart or range in a web page, blog post, etc.
- Embed a chart as a âlinkedâ image into a Word document, PowerPoint presentation, or Outlook mail, and have always up-to-date information.
- Build a âmash-upâ that uses Excel Services for calculation, charting, and/or as a data source.
- Build a Windows 7 gadget that displays information from an Excel workbook
- â¦and many more ideas I am sure our customers will think up.
I hope this has given you a sense of the capabilities and power of the REST API. In the next post Iâll dive into the details of the URI syntax. Stay tuned.