Tips for using SPD Workflows to talk to 3rd party web services

Hi all

One workflow action that anyone getting into SharePoint Designer 2013 workflow development needs to know is the Call HTTP Web Service action. This action opens up many possibilities for workflow developers, and in some ways, turns them into an option to be taken seriously for certain types of development in SharePoint – particularly in Office 365 where your customisations options are more restrictive.

Not so long ago, I wrote a large series of posts on the topic of calling web services within workflows and was able to get around some issues encountered when utilising Managed Metadata columns. Fabian WIlliams and Andrew Connell have also done some excellent work in this area too. More recently I have turned my attention to using 3rd party cloud services with SharePoint to create hybrid scenarios. After all, there are tons of fit-for-purpose solutions for various problems in cloud land and many have an API that supports REST/JSON. As a result, they are accessible to our workflows which makes for some cool possibilities.

But if you try this yourself, you find out fairly quickly that there are three universal realities you have to deal with:

1. Debugging SPD workflows that call web services is a total bitch
2. SPD Workflows are very fussy when parsing the data returned by web services
3. Web services themselves can be very fickle

In this brief post, I thought I might expand on each of these problem areas with some pointers and examples of how we got around them. While they may not be applicable or usable for you, they might give you some ideas in your own development and troubleshooting efforts.

Debugging SharePoint Designer Workflows…

The first issue that you are likely to encounter is a by product of how SharePoint 2013 workflows work. To explain, here is my all-time most dodgy conceptual diagram that is kind of wrong, but has just enough “rightness” not to get me in too much trouble with hardcore SharePoint nerds.

In this scenario, the SharePoint deployment consists of a web front end server and a middle tier server running Workflow Manager. Each time a workflow step runs, it is executed on the workflow manager server, not on the SharePoint server, and most certainly not on the users browser. The implication of this is that if you wish to get a debug trace of the webservice call made by the workflow manager server, you need to do it on the workflow manager server, which necessitates access to it.

Now typically this is not going to happen in production and it is sure as hell never going to happen on Office365, so you have to do this in a development environment. There are two approaches I have used to trace HTTP conversations to and from workflow manager: The first is to use a network sniffer tool like Wireshark and the second is to use a HTTP level trace tool like Fiddler. The Wireshark approach is oft overlooked by SharePoint peeps, but that’s likely because most developers tend not to operate at the TCPIP layer. It’s main pro is it is fairly easy to set up and capture a trace, but its major disadvantage is that if the remote webservice uses HTTPS, then the packet data will be encrypted at the point where the trace is operating. This rules it out when talking to most 3rd party API’s out in cloud land.

Therefore the preferred method is to use install Fiddler onto the workflow manager server, and configure it to trace HTTP calls from workflow manager. This method is more reliable and easier to work with, but is relatively tricky to set it all up. Luckily for all of us, Andrew Connell wrote comprehensive and clear instructions for using this approach.

SPD Workflows are very fussy when parsing the data returned by web services

If you have never seen the joys of JSON data, it looks like this…

{“d”:{“results”:[{“__metadata”:{“id”:”71deada5-6100-48a5-b2e3-42b97b9052a2″,”uri”:”http://megacorp/iso9001/_api/Web/Lists(guid’a64bb9ec-8b00-407c-a7d9-7e8e6ef3e647′)/Items(1)”,”etag”:”\”6\””,”type”:”SP.Data.DocumentsItem”},”FirstUniqueAncestorSecurableObject”:{“__deferred”:{“uri”:”http://megacorp/iso9001/_api/Web/Lists(guid’a64bb9ec-8b00-407c-a7d9-7e8e6ef3e647′)/Items(1)/FirstUniqueAncestorSecurableObject”}},”RoleAssignments”

Hard to read? yeah totally, but it was never meant to be human readable anyway. The point here that when calling a HTTP Web service, SharePoint Designer will parse JSON data like the example above into a dictionary variable for you to then work with. In part 7 of my big workflow series I demonstrated how you can parse the data returned to make decisions in your workflow.

But as Fabian has noted, some web services return additional data other than the JSON itself. For example, sometimes an API will return JSON in a JavaScript variable like so:

var Variable = {“d”:{“results”:[{“__metadata”:{“id”:”71deada5-6100-48a5-b2e3-42b97b9052a2″,”uri”:”http://megacorp/iso9001/_api/Web/Lists(guid’a64bb9ec-8b00-407c-a7d9-7e8e6ef3e647′)/Items(1)”,”etag”:”\”6\””,”type”:”SP.Data.DocumentsItem”},”FirstUniqueAncestorSecurableObject”:{“__deferred”:{“uri”:”http://megacorp/iso9001/_api/Web/Lists(guid’a64bb9ec-8b00-407c-a7d9-7e8e6ef3e647′)/Items(1)/FirstUniqueAncestorSecurableObject”}},”RoleAssignments”

The problem here is that the parser code used by the Call HTTP Web Service action does not handle this well at all. Fabian had this to say in his research…

What we found out is that if you have anything under the Root of the JSON node other than a JSON Array, for example as in the case of a few, the Version Number [returned as a JSON Object], although it works perfectly in a browser, or JSON Viewer, or Fiddler, it doesn’t make the right call when using SPD2013 or VS2012. If after modifying the data output and removing anything that is NOT a JSON Array from the Root of the Node, it should work as expected.

Microsoft documented an approach to getting around this when demonstrating pulling data from ebay which brings in XML format instead of JSON. They used a transformer web service provided by firstamong.com and then passed the URL of the ebay web service as a parameter to the transformer web service (ie http://www.firstamong.com/json/index.php?q=http://deals.ebay.com/feeds/xml. A similar thing could be done for getting cleaned JSON.

So what to do if you have a web-service that includes data you are not interested in? Before you swear too much or use Microsoft’s approach, check with the web service provider to see if they offer a way to return “raw” JSON data back to you. I have found with a little digging, some cloud providers can do this. Usually it is a variation of the URL you call or something you add to the HTTP request header. In short, do whatever you can to get back a simple JSON array and nothing else if you want it easily parsed by SharePoint.

Now speaking of request headers…

Web Services can be fickle…

It is very common to get a HTTP 500 internal server error when calling 3rd party web services. One common reason for this is that SPD workflows add lots of stuff to the HTTP request when calling a web service because it assumes it is going to be SharePoint and some authorisation needs to take place. But 3rd party webservices are likely not to care, as they tend to use API keys. In fact, sometimes it can cause you problems if the remote webserver is not expecting this stuff in the header.

Here is an example of a problem that my colleague Hendry Khouw had. After successfully crafting a REST request to a 3rd party service on his workstation, he tried to call the same web service using the Call HTTP Web Service workflow action. But when the webservice was called from the workflow, the HTTP responsecode was 500 (internal server error). Using Andrew Connell’s method of fiddler tracing explained earlier, he captured the request that was returning a HTTP 500 error.

Hendry then pasted the web service URL directly into Fiddler and captured the following trace that returned a successful HTTP 200 response and the JSON data expected.

By comparing the request header between the failed and successful request, it becomes clear that Workflow Manager sends oAuth data, as well as various other things in the header that were not sent when the URL was manually called. Through a little trial and error using Fiddler’s composer function, Hendry isolated the problem down to one particular oAuth header variable, Authorization: Bearer. By using Fiddler composer and removing the Authorization variable (or setting it as a blank value), the request was successful as can be shown on the second and third images below:

Now that we have worked out the offensive HTTP header variable that was causing the remote end to spit the dummy, we can craft a custom request header in the workflow to prevent the default value of Bearer being set.

Step 1. Build a dictionary to be included into the RequestHeaders for the web service call. Note the blank value.

Step 2. Set the RequestHeaders to use the dictionary we’ve just created.

Hendry then republished his workflow, the request was successful and he was able to parse the results into a dictionary variable.

Hope this helps others…

Paul Culmsee and Hendry Khouw

www.sevensigma.com.au