This is a strange post to write as the combined topics are things only likely to appeal to a select few people on this planet. Nonetheless, it was an interesting mix of technologies and frustration points, so I'm writing it up in the hope that someone else out there finds it interesting.
Introduction
I'm currently involved in a project where I'm using Microsoft Project 2013 (client only, no Project Server) to help manage things for an external customer. There's a couple of key systems that I need data from to support the project plan, one is our internal timesheeting system (to retrieve hours spent on a particular task), and the other is the customer's VersionOne instance (to retrieve hours remaining on a piece of work).
It became apparent very quickly that constantly flicking between these three systems for the next couple of months was going to end up being very frustrating, so I decided to look at whether there was anything I could do to speed things up.
Creating a Microsoft Project App
Having had some previous exposure to Visual Studio Tools for Office (aka VSTO), that is where I was expecting this journey to start out. However some research turned up something which looked even more exciting: How to: Create your first task pane app for Project 2013 by using a text editor.
A text editor! I'm sold! I was a little surprised that it appeared I'd be doing this entirely with JavaScript, and that VSTO wasn't going to feature at all, but such is progress.
While this is a very broad generalisation, no matter what you're planning on doing with a Project 2013 App, odds are that the sample app found at that link is going to be a really great starting point. The link gives you everything you need to know about the hoops you have to jump to in order to install an app, and the download gives you working code for common tasks that will "run" on any existing project plan (you may also find this link to be useful if you want to start more from scratch rather than editing an existing manifest/HTML file).
The format of these project apps is basically an XML manifest file which points to a HTML file. Both need to be loaded from a "trusted location" such as a network share (the link goes through this in detail), but once loaded you basically have a mini web app running in a side bar inside Microsoft Project. The page is rendered using some flavour of IE, so you're free to use any JavaScript plugins/libraries you want such as jQuery and Handlebars to compliment the methods you'll be using from the Office JavaScript SDK. These libraries can be bundled with your app or loaded remotely (which allows you to use CDNs for common libraries if you so desire).
It feels strange at first, but run with it.
Working with Project data and calling an external system
For my purposes all I needed from the Project side of things was to access a custom text field in the currently selected task. In my project plan this text field stores a unique identifier which is used in both the external systems I wanted to query. The plan was to use this identifier to query my external systems and then do something with the returned values.
It turned out that getting the data I wanted from the selected task was trivially easy. Here's a code sample where I'm getting the currently selected task, reading data from a column I specify, and using that to call an external system to get back some data. The code is run by clicking a button inside my App’s main HTML file:
In the example above I'm grabbing the value in "Text1", which is a custom text field, but you can do this for any field on the task at all. Simply change the enumeration as required from the list provided here: ProjectTaskFields enumeration (JavaScript API for Office). Note that you need to call getSelectedTaskAsync first to give the app some context before you're able to access the task's fields.
For completeness, renderData is a very simple function which uses a Handlebars template defined in the App's HTML file to display the returned data:
When it comes to using the returned values, I'd initially hoped to use them to directly update one of the task's fields, however this isn't possible with the JavaScript SDK (not that I could see anyway, if I'm wrong please let me know). Instead I simply display the values in the App's UI (which as this is simply a HTML page is a div), and can then copy/paste or type the value into the task if needed.
Initially this felt like a disappointment, but on reflection it feels like the right way to do it. It gives you time to look at the value and think if it feels right or not, that momentary pause to look at the old value versus the new one before updating it forces you to think more about the values rather than just mindlessly entering them. It also feels right from a security model point of view - it reduces the risk that rogue apps can violate the integrity of your project plan's data.
It's also worth noting that the debugging options for these apps seem somewhat limited. I didn't spot a JavaScript console, and the debugging choice of champions (using alert) doesn't work either. There may be some debugging tools available, but I simply didn't look that hard, and ended up just setting values in a div with an id of error-output when needed.
Accessing the VersionOne API
I need to start out by stating I'm not a fan of VersionOne. We use it over a VPN where the endpoint is about as far away from NZ as is technically possible, so its combination of countless AJAX calls combined with huge page size footprints has never sat too well with me. It’s sluggish and has always felt convoluted to use. So the thought of leveraging the API to allow me to spend less time using the actual product made me incredibly happy.
The documentation indicates that there's a number of different options for programmatically using VersionOne. I'd played with some of the C# SDK examples before, but in this case the fact that the Project App was very much JavaScript driven made it pretty clear that my only option was to use their RESTful API (which they name "rest-1.v1", because, why not?).
VersionOne has 2 different endpoints you can use - rest-1.v1 and query.v1. Here’s a screenshot of the main table from the page where they explain the differences between the two:
The table makes it sound like if you want XML then you use rest-1.v1, if you want JSON then you use query.v1. I'd tinkered about with the rest-1.v1 syntax in a browser address bar and pretty quickly found a URL which would give me what I needed. Here's an example:
http://yourendpoint/VersionOne/rest-1.v1/Data/Story?sel=Name,Number,Estimate,Status,ToDo&where=Number=%27B-06793%27
(It's worth noting briefly that ToDo is case sensitive and it DOES matter and that the value specified in the where clause needs to be wrapped in escaped quotes, and obviously updated to include the value you’re querying for)
This was giving me the data I wanted, but obviously it was in XML. Which was expected, because the document makes it clear that XML is all you can get using rest-1.v1. This presented me with 2 issues;
- Working with XML in JavaScript is a quick route to an early death in my humble opinion (although I was pretty surprised to see that jQuery now has some inbuilt XML parsing functionality to let you query an XML document – I was interested in giving this a try, and would have if it hadn’t been for point 2)
- I established pretty quickly that I was going to need to make my AJAX calls using JSONP, which means that call expects/needs JSON.
I could have worked around the first issue, but the second was non-negotiable, despite what the interwebs said. By that I mean that a number of examples out there seem to imply that you can easily authenticate against VersionOne using 9 lines of jQuery code. Here's a fairly recent blog post that gives a sane looking example: http://blogs.versionone.com/agile-development/2013/02/07/query-the-versionone-api-with-jquery-and-9-lines-of-code/
I tried a number of variations of the code contained in the above link, and it simply didn't work. I'm not sure if there's some VersionOne settings you can tweak to change the same origin policy or something like that, but as I don't administer the VersionOne system that wouldn't have helped much in my case anyway. The example everyone was giving simply didn't work.
At this point I looked into using the query.v1 syntax, and no matter what I tried (and I tried a lot) I always got back an empty JSON string. Again, not having control of the system (there may well be options and settings to enable/tune the query.v1 endpoint) all I could do was frustrate myself to the point where I was happy to say I'd exhausted all possibilities and move on back to the RESTful API.
Back with rest-1.v1 and JSONP, I tried implementing a custom dataFilter and a custom converter inside my $.ajax call. This sounded good in theory – grab the XML that’s returned, translate/convert it to JSON so that the data is in a format that jQuery is expecting. I got hopeful that one of those two options was going to work, but alas, no joy - my $.ajax call wasn't happy getting back XML from a JSONP call, but it needed the call to be via JSONP to satisfy the security requirements.
After a lot of frustration I decided to scale down my efforts. My downscaled plan revolved around having my App generate a clickable link which would take me into VersionOne so at least I could quickly get to the current task, even if I had to switch to another application in order to do it.
However while I was clicking around doing some tests on URL syntax, I noticed something interesting in Fiddler, which I'd left running on another monitor. That was that the VersionOne web front end was calling the REST API, and it was getting back JSON. I took a quick look at the request - apparently, if you add "&Accept=application/json" to the URL of your API calls then you receive JSON. I didn't see this anywhere in the documentation. Maybe I missed it, maybe not. If I’d missed it, I think you could probably forgive me for doing so based on the table describing their endpoints.
With a haze of red (for the time spent so far) mixed with a touch of glee (for realising I had a way forward) I dropped the new URL into my app alongside a chunk of code which is similar to the first example I pasted.
var url = "http://yourendpoint/VersionOne/rest-1.v1/Data/Story?sel=Name,Number,Estimate,Status,ToDo&where=Number=%27B-06793%27&Accept=application/json";
…and it worked. Weeeeeeeeeeeeeee.
Summary
What I've ended up creating here is going to save me countless hours over the duration of this project. It’s amazing how much more focussed you feel when you don’t need to constantly switch between three applications constantly – being able to stay in Project lets me stay focussed and get bored less quickly when updating the project plan.
The Microsoft Project side of things was surprisingly easy, and the sample app gives you exactly what you need to get going. The need to host the manifest and code on a network share is kind of quirky, and the JavaScript API has some security restrictions, however those were things that were easy to work around in my case - maybe less so if you're working in a locked down enterprise environment.
Getting what I needed out of VersionOne was frustrating, but that was expected. The documentation was incorrect, and there were a number of half completed examples lurking on forums. In the end the solution was simple, and I was so pleased when this worked. Anyone who's used VersionOne will know that the less time you spend using it, the more productive you will be.
If you’re one of the few individuals who have found this interesting then please leave a comment below – I’d really like to know if it was helpful.
Posted on Sunday, April 12, 2015 10:43 PM |