Excel Services REST API returns 401 Unauthorized in Workflow

Question

I am trying to make a call to the Excel Services REST API in a workflow to retrieve information on a workbook's charts.

The URL format is

siteurl/_vti_bin/ExcelRest.aspx/Workbooks/Chart%20Test.xlsx/model/Charts?$format=json

When I use Fiddler and make the call from a browser, I see two requests to the URL. The first response returns a 401 Unauthorized but includes this in the envelope:

Proxy-Support: Session-Based-Authentication

And then my browser sends a second (silent?) request

Authorization: Negotiate (Kerberos ticket token string)

And it gets a 200 OK and the data is presented in the browser window.

When I make the same call in a workflow, it returns a 401 Unauthorized which I logged but no subsequent request.

SharePoint 2013 and the Workflow Manager are both running with the same service account on the same machine.

What else do I need to check to get the REST call to work when invoked with a workflow?

Answer 1

The problem for me was in the request header. By default Sharepoint Workflow's REST calls are assumed to be against OAuth-compatible REST APIs (such as the listsvc in SHarePoint) and so it adds a bearer token to the header which is in the form of

Authorization: Bearer 

and an Authorization-Token component with the token itself.

Unfortunately, this prevents the Sharepoint workflow from correctly responding to the authorization challenge from the non-OAuth-compliant Excel Services. Instead it just says, "Oh well, I got a 401, we must not be authorized," and continues on its merry way.

The solution was to explicitly add a request header to my Excel Services call of

Authorization: (empty string)

The challenge process then worked and the results were returned.

For some extra fun, though, the Excel REST Services doesn't return its JSON with an application/json Content-Type, but rather a text/javascript Content-Type. So be sure to again deviate from all the collective Internet wisdom on making REST calls in Sharepoint WOrkflows and set the Content-Type in the request header to text/javascript or otherwise you'll get an incompatible format response from your request.

Answer 2

Thank you! Setting Authorization to null in the Request Header fixed the 401 issue.

A bonus answer here is on what you do after the 401 issue is fixed since SP workflow requires JSON if you are going to do anything useful after you call the API. As was pointed out Excel REST only creates JSON under a text/javascript content type, not application/json. However, the answer to set Content-Type to text/javascript did not work, in fact SP will ignore the Content-Type but will obey Accept. So setting Accept to text/javascript in the Request Header worked for me. For more info on actually creating a dictionary variable to store Request Header items see...

http://www.cleverworkarounds.com/2014/02/05/tips-for-using-spd-workflows-to-talk-to-3rd-party-web-services/

In addition, anyone playing at this level should use Fiddler to compose and test Request Headers (making your life much easier!).