Excel Web App API?

Hi Chris,

Currently there are no APIs exposed for Excel Web App, and we are not sure if this will be available in the future.

~ Clatonh MSFT

Thanks for the quick response, guys. My mistake, I'd seen the Excel Services REST API and assumed it was OData...

Really, the kind of scenarios I was thinking of were similar to the scenarios where the Excel Services API could be used (<heresy>or that the Google Docs API is used</heresy>). Imagine you were creating a simple web-based application for budgeting or planning: you'd set up an Excel spreadsheet, post it to the web, then get everyone to enter their numbers for the coming year into a table. You'd then be able to access the data in that table as an OData feed and be able to analyse it in PowerPivot. I'm a SQL Server business intelligence guy, so this kind of thing appeals to me :-)

Regards,

Chris

I'd like to second everything that Chris says here and, like him, my interest is also motivated by being a SQL Server BI guy. Exposing Excel data as OData would be incredibly compelling. I foresee issues around data-typing given that Excel is not particularly strict about such things but I'm sure that hurdle can be overcome.

I'd also like to raise the subject of URLs that could be used to expose such data (something I touched on previously atSocial Desktop extends REST to your desktop). If we could encode ranges into URLs such as:

http://cid-qwerty.skydrive.live.com/MyExcelFile.xls?Range=A2:C8

then we can even expose subsets of a spreadsheet as an addressable resource and that is, I believe, hugely compelling.

Regards
Jamie

Just to make one more point here: the big advantage of doing this would be that, when you published data to the Excel Web App, you'd be creating a resource that was simultaneously human-readable and machine-readable. Consider something like the Guardian Data Store (http://www.guardian.co.uk/data-store): their first priority is to publish data in an easily browsable form for the vast majority of people who are casual readers and just want to look at the data on their browsers, but they also need to publish it in a format from which the data can be retrieved and manipulated by data analysts. Publishing data as html tables serves the first community but not the second; publishing data in something like SQL Azure would serve the second community and not the first, and would be too technically difficult for many people who wanted to publish data in the first place.

The Guardian are using Google docs at the moment, but simply exporting the entire spreadsheet to Excel is only a first step to getting the data into a useful format for data analysts and writing code that goes against the Google docs API is a hassle. That's why I like the idea of exposing tables/ranges through OData so much: it gives you access to the data in a standard, machine-readable form with minimal coding required, even while it remains in the spreadsheet (which is essentially a human-readable format). You'd open your browser, navigate to your spreadsheet, click on your table and you'd very quickly have the data downloaded into PowerPivot or any other OData-friendly tool.

Chris 

A web-based spreadsheet without an easily accessed API amounts to cutting it off from the world of data around it. I do mashups against Google Spreadsheets all the time, and came over here looking to port the work, and make it Web-spreadsheet agnostic.

I'm disappointed. Just give me a credentials system and a REST API. I see the reference to having this in Sharepoint, but is there a lightweight way to do it? With login credentials and a restful API, we woudn't even need client libraries, Sharepoint, or any such software overhead. Just use the language of your choice, and bang against the spreadsheet.

A web-based spreadsheet without an easily accessed API amounts to cutting it off from the world of data around it. I do mashups against Google Spreadsheets all the time, and came over here looking to port the work, and make it Web-spreadsheet agnostic.

I'm disappointed. Just give me a credentials system and a REST API. I see the reference to having this in Sharepoint, but is there a lightweight way to do it? With login credentials and a restful API, we woudn't even need client libraries, Sharepoint, or any such software overhead. Just use the language of your choice, and bang against the spreadsheet.

[Slightly disappointed by the lack of response here from the Office Web Apps team since Chris, Mike and I replied but nevertheless I'll keep plugging away.]

A new site call The Archivist has just launched at http://archivist.visitmix.com. They archive Tweets and then allow you to export that data into Excel for further analysis.

This is a great example of why an API for the Excel Web App is needed. I would much rather export to the web app than a bog standard .xlsx file. I can of course upload that file into Office Web Apps but I really don't want to have to take that circuitous route!!!

Thanks
Jamie

From the Web App team, we completely understand the value of having APIs to get data in and out of Excel (and the other Web Apps).  Like everything, we have to prioritize, and that functionality hasn't made it in yet. 

From our POV, the most valuable thing is to hear/see examples of scenarios that can use these types of APIs.  There are some good examples here to start - do you have others (and the more specific the example, the better).

Cheers,
Dan [MS]

OK, let me think this through...

So what are the scenarios where someone would want to use the Excel Web App? I can think of two main ones:

1. They want to a central place to put a spreadsheet which they can access from multiple physical machines
2. They want to share a spreadsheet, or data on the spreadsheet, with friends, co-workers, customers or the general public

Clearly in (1) there's not much need for an API, so let's think about (2). Again there are two main scenarios here:

    2.1 The intent is to publish data for read-only consumption by friends, co-workers, customers and the general public

    2.2 The intent is that the friends, co-workers, customers and public will be able to edit the data on the spreadsheet and collaborate on some task

Examples of (2.1) include:

• Publishing data of interest to the general public, as in the case of the Guardian Data Store (mentioned above), government data, your top-ten favourite bands of the moment, reviews of restaurants in your area that you've visited, or a list of forthcoming performances by your theatre group
• Publishing a company's financial results, sales figures, targets
• Publishing reference data such as a list of the products your company sells, the members of your club/society, the codes and full names of all the states in the USA (seehttp://www.guardian.co.uk/news/datablog/2009/nov/24/iso-country-codes-reference-guide-rosetta-stone for example)

Examples of (2.2) include:

• Budgeting, where every manager in a company has to estimate the required budget for their department for the next financial year
• Running surveys, where feedback on a question is required from a number of people
• The costs for a new business venture are estimated by one person, and then submitted for review to their colleagues who may or may not correct them

All of the above scenarios are well-served by specialised apps, both on the web and on the desktop. And yet all of these are frequently performed in spreadsheets, often much to the horror of IT people like me. Why? Because people know how to use spreadsheets, can do what they want to do with them quickly, and don't want to learn new tools (sadly not everyone thinks playing around with computers is fun); and because a spreadsheet is so completely flexible - it's the ultimate easy-to-use, human-readable, schemaless database.

However as we all know there comes a time when the flexibility and schemalessness of a spreadsheet becomes more of a hindrance than a help. Tables and ranges allow us to impose some kind of order on the data in a spreadsheet, but the real problem comes when we as developers want to use the data in a spreadsheet for some other purpose - either in another spreadsheet, or in a business intelligence app like PowerPivot, or in another web page, or in an application we've built. We not only need to impose some kind of structure on the data, impose a schema on the previously schemaless database, but we also need to be able to query for that data. This is currently so difficult in desktop-based spreadsheets that this is the point that users are told to abandon their spreadsheets and learn to use a specialist app for budgeting/running surveys/managing reference data/publishing financial data. And the users naturally hate this because, as I said above, they don't like learning new software and (more reasonably) they don't see any benefit from doing this, at least in the short-term: they lose the flexibility and human-readability they prized so much, and it's only us as the developer that gets the benefits. What we really need is a compromise, where users get to keep their spreadsheets but developers can access that data as if it was in a database, and everyone would be happier.

So the scenarios where the Excel Web App would need an API would be all the scenarios where Excel is currently used as, or could be used as, a database; or where a database is necessary today but is overkill for the task in hand, or where users don't want to have to learn to use a database. And that's a lot of scenarios!

I'd like to see the Excel Web App do the following two things:

• Expose data in tables and ranges as an OData feed, and
• Consume data from an OData feed and expose it in a table

Let's consider one of the scenarios above in more detail in the light of this. Imagine you belong to an small theatre group, and as a vaguely techy guy you've been given the job of building the website; you have a page on that website that shows the date, venue, start time, play performed and entrance cost for your forthcoming tours. You could hard-code this into the web page itself, or put the data into a MySQL database and read it from there, but since you're not the guy in the group who organises tours and has this data, and the guy who does is not a techy, when you needed to update the data you'd have to ask the tour organiser for the new details and update your html/MySQL database yourself. However in the brave new world of the Excel Web App + API, you'd simply set up a spreadsheet with a table to contain the tour data, set up some formatting and validation rules to ensure that dates were dates, start times were times etc and send that to the tour organiser to fill in. They would then publish this spreadsheet to the Excel Web App and update the spreadsheet themselves when the data needed to change. Meanwhile you would build your website to read the data from this table in the spreadsheet via the OData API and bingo - you have a dynamic website that can be updated by a person with minimal technical knowledge and the tour organiser has an easy and familiar way of entering the list of tour dates.

I could imagine other, similar scenarios but I've probably gone on too long already. One last thing... Jon Udell blogged about this thread, and agrees with the need for an API:
http://blog.jonudell.net/2010/06/30/web-spreadsheets-for-humans-and-machines/

Chris