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.
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.
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).
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:
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:
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.
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.
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:
(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;
- 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.
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.
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.