Pushing a Microsoft API to the Limits

I’ve wanted to build a plugin for Microsoft Excel for a really long time, but not being a .NET developer I assumed this was something we’d have to outsource at a later date.

Then a few months ago I discovered two things: 1) Microsoft Office 2013 has a new “app” system for HTML5 apps and 2) You can build apps in a complete online development environment called Napa.

Not having to download any software and being able to write code in HTML/javascript made the concept of Excel integration something I could prototype myself, which meant we could try it out right away without spending any money up front and invest in backwards compatibility if things worked out.

This was going to be easy, I thought, and fun!

I was only half right.

The Agony and Ecstasy of Microsoft
Like most developers of my generation, I started out on Windows. For years I knew every trick to hack Microsoft technology, pushing machines that were 10~15 years old to their limits, cobbling together custom machines with spare parts pulled from dead units … that was the way you did it back then. Linux was barely an operating system and Mac restricted your software options too much. We were all on Windows.

What’s weird about Microsoft as a product line, if not a company, is that they don’t generally lose because their competitors beat them. They lose because they sabotage themselves. What finally got me off Windows was an external wifi card. The wifi card was specifically designed for Windows XP machines. I plugged it in to a laptop specifically designed for Windows XP with a valid version of Windows XP freshly installed … and the damn thing didn’t work. No matter what I tried the machine and wifi card would not play nicely together.

But when I threw out Windows altogether, installed Red Hat and jerry-rigged a Linux driver from another manufacturer everything worked perfectly. I had it set up in under an hour and the configuration worked up until the day the machine died.

That’s when I learned an important life lesson:

Microsoft + Microsoft + Microsoft == FAIL

We revisited this lesson as a company a few months ago when we attempted to move Exversion to Azure servers. I was actually really excited about this move. I liked what Microsoft Evangelists had to say about Azure. I thought perhaps Microsoft had been humbled enough to learn from its mistakes. I was eager to give them a second chance.

And then we actually tried to do it and the move was an unmitigated disaster. Azure’s system is pretty robust and visually quite appealing, but its documentation is spread out over multiple sites, blogs, and mailing lists. Deprecated information is almost never properly identified. Basic configuration changes often required destroying the server and starting over. After hours of research I still do not understand how to set up backups. Too many tutorials are written with the assumption that you are on a Windows machine, interfacing with your Windows server through Microsoft PowerShell.

While Microsoft Evangelists were quick to celebrate Linux servers as a general idea, essential documentation was always written as if Ubuntu was a fringe product only a small group of extremists would prefer over the Microsoft alternative.

It was incredibly annoying and resulted in the loss of valuable time and money. In fact I would go as far as to say it nearly killed the company.

The Developer Friendly Microsoft?
But the opportunity to finally crack open the Excel market was just too good to pass up. I was ready to give Microsoft another chance and try to build something really powerful and awesome.

And for the most part I think it paid off. I’m pretty happy with the alpha of Exversion for Excel. Even if the development process was slowed down a little by typical Microsoft BS, because this is HTML5 and javascript prototyping was still pretty speedy.

Here are some things to consider before you get started:

  • The javascript API is young and still a bit immature in the options it gives you.
  • While documentation is still on multiple sites for multiple versions with very little indication what has survived previous incarnations and what has been scrapped completely, the information on the main site is generally sufficient to answer most questions. It’s safe to say that if it’s not on that site, the information you are reading is wrong, out-of-date or not relevant. (I expect that as the API matures Microsoft’s apparent inability to keep all their documentation in one authoritative place will be more of a problem)
  • You DO NOT need to download Visual Basic Studio or any other Microsoft product in order to develop.
  • Microsoft’s Developer site for Office 365 is a labyrinth worthy of a David Bowe cameo. Took me forever to figure out that in order to continue working on my new app the day after I created it I had to go Login > Admin > Office 365 > Build Apps and then choose Napa Office 365 Development Tools OVER the link to my app that appeared right above it on the same list of links

Building: What are Microsoft’s Actual Goals?
The main problem I had with using Microsoft’s javascript API was that the API did not seem to be designed for ambitious projects. It’s one thing for the tutorials to focus on small simple example apps (the Hello Worlds and whatnot), but I’m not sure how Microsoft expects developers to build anything more complicated than a few flashy alternatives to Google charts. Maybe that’s all they really want out of this.

Here’s some of the issues I had to work around in building Exversion for Excel:

  • Grabbing data from a selection does not return any information about WHERE in the workbook the selection is. You can get the number of rows and the number of columns, but not the sheet info or the exact location of the rows and columns within the sheet.
  • Listeners on bound areas do not persist, which means running the entire app on one HTML page or iterating through all the bindings and resetting their listeners every time a user moves from one section of the app to the next. While this is almost certainly the nature of javascript, I would have expected Microsoft’s default library to manage this concern for me.
  • There is no way to manipulate the size of a selection programmatically. Exversion for Excel loads data in and out of Excel through Exversion’s API. However the method to read/write data in a spreadsheet is ridiculously anal. It needs the selected area to match the rows and columns of the data EXACTLY. Fine when we’re reading– the user expects to have to highlight all the data they want– but a real royal pain in the ass when writing data to the sheet. The application now has to instruct the user to highlight EXACTLY five columns and three thousand rows … yeah right. Even if the entire sheet is empty the API will not insert unless the selection is exactly the size of the data to be written. A couple of methods to adjust the size of the selected area programmatically would open up a whole WORLD of options to the developer. Sadly it does not exist.
  • Adding new data requires the user to understand what’s going on under the hood. If you’re going to add data to a section, where are you most likely to add it? To the next available empty row … which is outside of the bound area … ergo the listener will not realize that the data in the bound area has changed and fire. Since we cannot change the selected area programmatically we cannot add a few extra empty rows to our bound area to control for this possibility, and even if we could the API would complain about the selected area being TOO BIG for all our data. Since the api does not return any information about the location of the selection we can’t even give the user the option to highlight the new data and add it in to the bound section either.
  • When a listener fires it returns not what has been changed, but all the data in the selection. This makes sense for a graphing app, but not much sense beyond that use case. It means in order to find the change the app has to iterate through the data. The good news is that the listener fires every single time a change is made and the changes that can be made on multiple rows at once are pretty limited in Excel 2013. So once you’ve found the difference you can stop. The bad news is I can’t imagine this is going to scale well to thousands of rows.

Microsoft Being Microsoft
I managed to find user friendly work arounds for most of those issues. I finished debugging and testing Exversion for Excel. I was satisfied that we were ready for an alpha launch, but I wanted to run the app on the real deal first. See how it handled, push it to insane limits and begin to figure out what to work on for future iterations.

That’s when Microsoft went back to being Microsoft.

See one of the nice things about Microsoft’s Developer’s Accounts is that you get copies of the latest version of Office for free. But when I downloaded Excel for Mac and booted it up I couldn’t figure out how to install an app. I looked under all the menus, digging around in plugins and add-ons… nothing about apps.

Then I went to Google and found this.

For starters, Office 2013 doesn’t mean anything to the Mac: it’s for Windows computers only. A subscription to Office 365 ($10 per month, or $100 per year) gives you the right to download Office software to up to five computers. For Mac users, what you’ll download is Office for Mac 2011—it’s pretty much the same version of the suite that we’ve been using for a couple of years now, but it’s been updated to include activation for Office 365 Home Premium.

So BAM! in the blink of an eye the available user base for my brand new app is slashed. I knew the number of people using Office 2013 verses some later version would be small, but I figured that would make it the ideal testing ground. Now we’re looking for a tiny number of people who 1) use Excel for most of their data work AND 2) are on Windows AND 3) have Excel 2013 or prefer Office 365 to Google Docs.

Dear Microsoft: If you are wondering why there are barely over 100 apps built for Office 2013 this might be why. Go to some non-Microsoft developer events, a hackathon maybe, sit in on a few computer science classes. It is a SEA of MACS. You have the right idea with supporting HTML5, but the developers you’re looking to bring back to the Microsoft fold are all on Linux or Mac. The main reason why developers work on independent projects like apps is that they want to build something they themselves will use.

Thank God for Virtual Machines.


2 thoughts on “Pushing a Microsoft API to the Limits

  1. Interesting post. Though looking into the Store, I see well over a thousand apps (reminds me of the conversation we had during WPC with the app numbers). It seems that they are only showing the top 100 or so in the searches for each query (e.g. your link which shows apps for Excel).

    • Hmmm. Well removing Excel as a search term only returns about 900 results. I have noticed that Microsoft basically refers to all applications as apps now, so maybe that’s where the difference comes from?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s