Limitations when using SharePoint 2013 Workflows to return JSON data 3

Précis

I struggled with the title for this blog; dare i say, after spending almost two days trying to make SharePoint Designer 2013 and Visual Studio 2012 Workflow return data from a few notable providers [YouTube, Twitter, iTunes] using the Dynamic Value / Dictionary Data Type in JSON format, and not being able to as documented, i was frustrated to say the least. I would have been Ok, if it wasnt for the fact that the same exact Logic/Process works for other providers such as [FreeWeatherOnline, etc] returning data in JSON format, the key difference being the structure of the JSON data being returned.

This is for a few SharePoint Community Events that I have been scheduled to present this material. So after beating my head against the wall, sending out S.O.S tweets, and pinging a few folks that are versed on the topic, I ended up doing my Demos with the REST API provider that returns the JSON data in the way that works OOB and documented in both SharePoint Designer 2013 (SPD2013) and Visual Studio 2012 (VS2012). Nevertheless, this was going to haunt me if i cant get it to work, so i went back at it and with the help of Bart @bart_tubalinal Tubalinal who is the self professed and "fabian validated" Pound for Pound Undisputed Developer in the World, we proved out that if you altered the return JSON data from the providers that didn’t work as expected, and then make a call from SPD2013 and VS2012 using the DynamicType / Dictionary variable, it worked as expected.

I will describe the process we used to determine this below…

Background Research

Below, I will show you three data calls to REST API providers returning in JSON format, showing you both in the browser and in the JSON viewer, I call your attention especially to the JSON viewer and the hierarchy.

Screen clipping: Google YouTube API Above

Screen clipping: FreeWeatherOnline API Above

Screen clipping:  Twitter API Above

Screen clipping: YouTube JSON Data Returned Above

Screen clipping:  FreeWeatherOnline JSON Data Returned Above

Screen clipping:  Twitter JSON Data Returned Above

Hypothesis

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.

The first thing to prove it out is to have SPD2013 or VS2012 make a call to a URI that returns JSON, so that can easily be accomplished via Visual Studio Project to create a new Site, and then drop a file with the removing anything in the root that is not a JSON Array (Collection) and calling that Web Site from inside for example SPD2013 then it should return the requisite data than can be consumed by the Dictionary Object.

Test/Prove Out

To begin, the first thing we need to do is remove the "appVersion: 2.1" JSON object out of the data returned, below is what is looks like without any adjustment

after you remove it, the raw file looks like below

just to verify, ill put it into a JSON Viewer to make sure it is what i want

then drop that raw file into a text document and place it at the root of the newly created Visual Studio Project Web Site

verify that you can get to it from a browser call

Analysis

Now, below I will show you WITHOUT adjustment how the FreeWeatherService REST API works right off the bat, you will recall above that the only thing at the JSON root node is the JSON Array. So we put it into SharePoint Designer as below

Screen clipping: See the URI and Process Above

Screen clipping: Above you’ll notice the return data goes to the Dictionary DataType

So after you publish the Site Workflow and run it on the Site, you get what you expect to see below

Screen clipping: SharePoint Promoted Links List with JSON Data Returned

Showing the YouTube Working with the Mods to the Data Set and Without the Mods

So, below you will see how we make a call to the Virtual ASP Visual Studio Site [look at the URI call line you will see it set to localhost and port number]

Screen clipping: SPD2013 WF with the Modded JSON data call Above

After publishing the Site Workflow to the Promoted Links List we can see the output works as expected.

Screen clipping:  Promoted Links List showing the Top 10 YouTube Videos displayed with Modded JSON data Call

Now Showing it NOT working as Expected

Below you will see the same code as in the one that worked, except the URI is set to the Google YouTube REST API and no Mods will be done to this data set.

Screen clipping: Above the same Logic/ Process except this call is made directly to the YouTube REST API

Now pay attention to the logging above, you will see that when the Workflows is published and deployed and ran, that the line for the call fails.

Screen clipping:  Above is me executing the Site Workflow

Below you will see the Workflow status showing that the process halted

Screen clipping:  The Workflow Status showing it didnt work

Report

So, I originally called this a BUG, but for now it is just an observation. Ill be happy to know from our community folks, MVPs, MCMs, and other folks that come across this blog your thoughts. Sadly, I have turned off comments on my blog, but you can always shoot me a tweet at @fabianwilliams

thanks cheers.

Other Notables but I wont write it up, you can discern yourself

See me making it work in a Custom Action and also Not working using one REST API result that is in the format I said will work, and the other with it not.

Screen clipping taken: 12/31/2012 3:32 PM

Screen clipping taken: 12/31/2012 3:32 PM

Screen clipping taken: 12/31/2012 3:34 PM