Office Web apps get more programmable with Excel Web JavaScript

JavaScript isn't replacing Office macros anytime soon, but the Office Web apps (and the SharePoint-based enterprise equivalents) are improving regularly. For users that means simple but useful features like the Excel Web app letting you select a group of cells and show you the sum and average the way desktop Excel does. But the new Excel Mashup site shows there's more power and programmability in the Office Web apps than most of us suspected.

Using data from Excel Web with JavaScript; explanations from the new Excel Mashup site

The desktop Office apps have been connecting to Office servers for years now, from the original Project Server that grew into SharePoint to automation that lets a business programmatically create documents. (At the risk of igniting an old argument, the ability to construct documents automatically using code is one of the main reasons for the Office XML file formats — you can't do that with a binary file format, but once your file format is XML, you can slice and dice, poke, prod and rebuild documents wholesale without ever firing up a ribbon-based application.)

One of the most popular Office Server tools is Excel Services. At one level, this avoids the multiplication of spreadsheets, where everyone take a copy, makes a couple of improvements and uses it to run their department; come the end of the year, someone has to make all the figures add up from multiple spreadsheets with multiple and subtly different dependencies. It's also part of making business intelligence and number-crunching more accessible. Keep the data in the database where it belongs, put the spreadsheet in SharePoint, serve out the calculations through Excel Services, lock down the parts that need to say the same, and everyone can work out what they need without changing the assumptions on which your budget is based.

Doing even a little of that in JavaScript is the first sign of opening up the OWA apps — in the same way Web Parts make SharePoint as much an application platform as a document library. The Excel Services REST and SOAP APIs introduced in SharePoint 2010 are SharePoint only, but with the Excel Services JavaScript API you can work with embedded workbooks on a host web page — from SkyDrive.

At its most basic, you can put an Excel spreadsheet (or just a table from a spreadsheet) on any web page, like your blog, and let people play around with the information or add their own figures to try out; you don't have to upload a document, share a link or write your own calculation engine. Just choose Share > Embed from the ribbon in your spreadsheet on SkyDrive and choose what to embed and what visitors can do with the data, and then copy the HTML or JavaScript.

The Excel Web App embed option

Where this gets interesting is when you start writing your own JavaScript to manipulate that embedded data. Add a Bing Maps control and your spreadsheet is now a mashup; play with the sample Destination Explorer for a simple example that pulls data from multiple sources, builds charts and shows a related map.

Excel Services in action on a Web page

If you're used to building your own Excel macros in VBA, don't get too excited; there's only a handful of objects and classes but you can do quite a lot with them. What you're getting access to isn't automating Excel (so this isn't the equivalent of either VBA or Google Apps Script), it's taking information directly out of an Excel worksheet and doing whatever you want with it in JavaScript — or putting values in. That could be information you preset — like a list of interest rates for calculating different savings accounts or pricing and discounts for your services so potential customers can see how much they'd be paying — but it's far more flexible to have Excel Services pull it from a web site or data feed and turn it into a spreadsheet. You can use that to generate charts automatically, or just let users work with the information like any other spreadsheet.

Also important is that you're doing it without ever going near SharePoint — although as the same engine drives the Office Web apps on SkyDrive as on SharePoint, so this raises some interesting questions about what you'll be able to do with SharePoint Online going forward. As of the first Service Update this autumn, SharePoint Online supports the Excel Services REST API, and a version of Business Connectivity Services for connecting external data sources. At this point it only works with Windows Communication Foundation services and you only get read-only access to external data, but given how recently Office 365 launched, that's a reasonable start.

The SkyDrive team claims that the site is "already getting 50% of the Google Docs page views"; along with the new SkyDrive APIs and Live SDK for accessing SkyDrive content and services from apps on multiple platforms, the ability to embed tables, ranges or entire spreadsheets in your own web pages as the basis of mashups should see that traffic increase significantly.

Mary Branscombe