Question
11684 views

Excel Web App API?

Christopher Webb asked on

I think I already know the answer to this one, but I was wondering whether there was an API for the Excel Web App? Specifically, I was wondering if it was possible (or if it will be possible in the future) to expose data in a spreadsheet in the Excel Web App as an OData feed, in the way that it is possible with Excel Services?

Thanks,

Chris


Blog: http://cwebbbi.spaces.live.com Consultancy: http://www.crossjoin.co.uk/
Blog: http://cwebbbi.spaces.live.com Consultancy: http://www.crossjoin.co.uk/
15 people had this question

Abuse history


The answered status icon Answer
Dan B [MS] replied on

Hi Chris,

Clayton is right that we don't have an API for the Excel Web App that's available through SkyDrive now, though we do have a few that are available through Excel Services that is part of SharePoint (I'm not sure which you are referring to).  In SharePoint we have:

  • a SOAP Web Service
  • a javascript OM available in the browser
  • a REST API

More details on all of these are available here: http://msdn.microsoft.com/en-us/library/ms582023.aspx

None of these return odata today, but I'd be very interested to hear the scenarios that you're thinking of for using Excel in this type of situation.

Cheers,
Dan

1 person found this helpful

Abuse history


Most Helpful Reply
Christopher Webb replied on

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

 

 


Blog: http://cwebbbi.spaces.live.com Consultancy: http://www.crossjoin.co.uk/
Blog: http://cwebbbi.spaces.live.com Consultancy: http://www.crossjoin.co.uk/
7 people found this helpful

Abuse history


progress