Excel Web App API?

More on this (is anyone still reading?)...

I met up with some friends a couple of evenings ago that have built this site: http://www.thefantastictavern.co.uk/

They are running an event in a few months for which it is possible to register on the site (tho admittedly its not actually working at the moment due to a hosting issue). The point is, when someone registers the details simply get dropped into a Google Apps spreadsheet. Its simple. It works. It gets the job done.

Without wanting to sound too rude or dramatic...WAKE UP! People are actually using this stuff, they are using APIs for mashup capabilities and they are actually doing it for commercial purposes. Don't get left behind in this!

Dan, does that work as a specific use case?

http://sqlblog.com/blogs/jamie_thomson/ |@jamiet |About me

Scenarios? Building mash-ups against a Web-based spreadsheet that works very much like Microsoft's own Advertising Intelligence product, but hitting any data source to do the populating.

http://advertising.microsoft.com/learning-center/downloads/microsoft-advertising-intelligence

Microsoft Advertising Intelligence (formerly adCenter Add-in for Excel is a keyword research and optimization tool that operates seamlessly in Microsoft Office Excel 2007. It provides keyword expansion, research, pricing and KPI data, allowing you to maximize marketing ROI for your paid search and content ad campaigns. The Microsoft Advertising Intelligence tool lets you:

• Quickly and easily build out lists of suggested keywords and develop informed keyword strategies based on actual Bing and MSN query data, including: relevance, volume, cost history, demographic and geographic.
• Leverage actual historic and forecasted monthly query and content data to optimize your keyword campaigns based on what potential customers are actually doing, and spend more on what works and less on what doesn’t.
• Tailor your bidding strategy based on pricing data for keyword-specific metrics such as clicks, impressions, position, click-through rate and cost per click.
• Gather pricing KPIs for specific businesses to determine the monetization potential of a vertical and how well it is performing.

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]

Thanks for the reply Dan.

I do understand your need for real use cases however to me the only use case I personally would need would be "build it and they will come"! Providing an OData feed for data in an Excel web app provides great oportunities for leveraging that data in other apps (oh, OK, mashups).

If you want a more real-world example then consider those thigs that are the scourge of enterprises - data locked up in spreadsheets on people's own hard drives. If those spreadsheets could be hosted on Sharepoint (which they can be today) and then the data in them easily accessed via an API (which they can't) then we've got a great way of freeing that data so that it can be used around the enterprise.

Really really hoping we see this one day.

Regards

Jamie

http://sqlblog.com/blogs/jamie_thomson/ |@jamiet | About me

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/