So I’ve had folks say that they still cant make the leap and get my examples to work in Office 365 SharePoint Online, so this post is to Prove out that IT can be done, there is no smoke and mirrors, I just wanted folks to take what I have and use that to go to the next level. This is not like a live session where I have an interactive audience, so I am putting the info out there in his blog.
This post builds on a few out there that I have done explaining how to use SharePoint Designer 2013 with the Dictionary Object to data in JSON Format to be used inside Workflows. Now, I do admit that there are VERY FEW examples out there, if any, that show you, how to do what I am about to tackle in Office 365 SharePoint Online. So here goes.
As usual we have an Initialization Stage and a Workhorse Stage, in the Init stage we will set our variables and make our REST call, we will parse it and limit our dataset to what we need for our results. I think folks are getting tripped up on what is needed in the Headers, as what I am getting as feedback is that the Workflow Starts but it stalls with a message like “Retrying last request. Next attempt scheduled after…” with a correlation ID, it also gives you a Retry Now Link.
So the first thing i will show you is of course what is in my List NOW, I will show it to you via the browser and in Fiddler.
now here is the items in Fiddler, i do this so you can appreciate the actions I will take later on in SPD when parsing the JSON data.
Now lets look at how we construct the Header, in this post, i have explained why you need certain Headers, so i wont go into it here, the only difference is that I am extrapolating what I did in Fiddler and using it in SharePoint Designer… now for obvious reasons, I am NOT showing you all the Header Information since it is my Valid Token to O365.
Now with that done, I have constructed a Site Workflow that will loop through the items and Log it to History, what you do with it for your Use Case can be quite elaborate, you essentially can
- Communicate with any Office 365 SharePoint Online Tennant you want from a Single Workflow
- Cross Communicate between an On Prem Farm and a Cloud Farm in one Workflow
- Create List, Libraries, List Items, and do full CRUD operations
If you go and look at the REST API endpoints from that link i gave you before by Kirk Evans, you can see that its quite a bunch of things that are available now.
So. back to the story.
First Part of the Workflow
Second Pare of the Workflow
Next lets fire off this Workflow and look at the results.
Next we go to Site Contents, Site Workflows and click the one we just created after publishing
We should expect to see the below in our History List
and upon closer inspection of the Workflow
Now what I will say because I have worked quite a bit with SharePoint Online Wave 15 is that it can get squirrely and can crap out on you for no good apparent reason, it gives you obscure feedback when it does and often times it just says “something bad has happened..” and if you Refresh the browser, its back to normal. I don’t pretend to understand why, I don’t have eyes into the Data Centre nor access to the ULS Logs, so I am as much as in the blind as you are. BUT this is the reason why i do Fiddler first then SPD after…visibility.
I hope this helps! Cheers.
What do i really mean?
Now that I have got your attention with that eye catching title, let me expand and qualify it. So, what I will be doing exactly is
- Inspecting a List (a Task List actually) for Tasks that are Due and Overdue.
- I will be checking them nightly (every 24 hours to be exact)
- If task are Over Due, then I will take ‘some’ action on them, in this instance, I will be changing a KPI field but this can be quite exhaustive based on your own particular use case. see below for one i have in mind.
- Pause the Workflow for 24 hours and Repeat.
By the way, this CAN also be used in SharePoint Online
In this use case I have been charged to monitor a Task list (multiple task list from varying SharePoint Sites/ Site Collections) and aggregating them. Once complete, then we need to serve up the tasks that are overdue into a KPI dashboard and send the email link to managers of those offending taskees.
For my POC demo here I will show how to get 1 Site Collection but to get multiple all you have to do is make a separate REST call to that particular Lists API, and the rest is easy.
Pre-Requisites and Technologies Used
In this example I am using an On Premises SharePoint 2013 with Workflows enabled, I am also using SharePoint Designer 2013 as well, that is if for tooling. i.e. NO CODE. I will be using the SharePoint 2013 REST API to read the SharePoint Task Lists as my Entry Point URI. The generic url will be /_api/web/lists/getListByTitle(‘’)/items">/_api/web/lists/getListByTitle(‘’)/items">http://<sitename>/_api/web/lists/getListByTitle(‘<listNameHere>’)/items which fetches List Items from a Named list using oData. Now by default this will be returned to your in XML, so in order to use this in SharePoint Designer which requires JSON inside the Dictionary Object (see my post here if you need a refresher on that) you will need to modify the headers to accept JSON using the Accept Headers. The rest is a matter of using Loops to iterate through the List Items returned, and pausing for the time allotted.
As usual I put my URI inside a Variable to obscure the name & any API Keys if any, this time there was no need but it just makes for better programming especially if there are a lot and I want to swap them out from time to time without affecting the core program
the full list of properties from the Call HTTP web service is located below since it is truncated in the image
The Request Headers are “Accept: application/json; odata=verbose” and you can place that into a string type as well and call it into the HTTP Web Service Properties as well inside a Workflow Dictionary Variable
Once you have done that, then as seen in the first image and in my previous blog post on the same topic, you parse the JSON results to get the part of the dataset that you want, in this case i want what is under the “d/results” node and to do that I use the Get Item from Dictionary and parse out what i need then i count the items for good measure, save the “Count” as a variable to be used as my upper bound in my Loop Counter and log it out.
Do Business Logic
After setting up the stage to get the URI, return the JSON data, Parse It to the Node you want, and get a Count, the next thing is to do the business logic.
What you will see below in the image is us using the Variable Count as the upperbound in our Loop and then getting the JSON data into Local Variables to be manipulated in the Workflow Logic.
So, i have been talking about this for a while as most people who have known me for a long time, know be because of my work and efforts in SharePoint BCS. But i have actually moved my thoughts in some aspects to what BCS allows (even in 2013) and what Workflows gives you interms of interacting with External Data. You see my point is, if you do not need to look at that data, i.e. External List, or need to Search on that Data i.e. External Content Type as a Content Source, I THINK that it is to your best interest from a simplicity standpoint, a performance standpoint, best of breed standpoint… to do this as a Workflow calling External Web Services and manipulate the data how you want and then get rid of it (i.e. the Variables) when done. That way the data is only used for its intended purposes then goes away.
Back to our story, what we do next is inspect the Due Date and use Conditional Statements to affect changes to the Fields for what you need. in this instance I am updating a KPI field based on how far along or how overdue a Due date is on a Task List. Now the Use case here is that I can do this on Any Amount of List both On PREM and in Office 365 in the same logic, I have Scale and Scope down to a Tee 🙂
The logic loops through each item does a Check and then updates the loop counter until it hits the “Count” variable number then exits. Since this is a Timer Job then the next thing we need to do is Pause it and wait, see below
Now, this workflow was designed as a Site Workflow so it can be ran independently of any Library or Lists and this guy can run forever.
So the next time someone ask you to Do a Timer Job for them, especially if they say they want it on Office 365 (SharePoint Online) and it involves a use case similar to mine or at the very least is looking at affecting changes to a List/Library, consider doing a Site Workflow with Conditional Logic and Pause Duration. Its really is that simple.
If this was being done in SharePoint Online, you would also need to capture and pass along the oAuth Tokens FedAuth and rfTa in your dictionary object.
Cheers and Happy SharePointing
What Major Obstacles you will need to Overcome
This is what I found lacking when I did research on how to achieve the stated objectives above. Most of the examples just told you to either “..type this” or “create a dictionary object to to that…”, and some just omitted major steps that you will get tripped up in and no way to circumvent it. So, taking this by the numbers, lets first assume that you want to get back some List data from SharePoint 2013, we will do this first On-Prem then In-Cloud (Office 365).
- You will need to construct a Uri representing your target list such as : //_api/lists/getListByTitle(‘’)/items">http://<site>/<web>/_api/lists/getListByTitle(‘<listNameHere>’)/items in a browser (preferably Chrome) or Fiddler
- You will need to set some Headers, specifically the (a) Accept and (b) Content-Type Headers to accept ‘application/json;odata=verbose’ inorder to work with the return items in JSON format which if you are using this in SPD, you MUST do in order to use the Dictionary Object, or if you want to read the data in some logical hierarchy.
- If you are ON Prem, and using Fiddler inside “Composer” under Options, i usually set the Automatically Authenticate Flag like so, you will see me talk about Decrypting HTTPS Traffic later on in the Cloud or HTTPS targets later on. In doing so your Cookie header will be automatically set.
- Next ensure your Verb is set to GET and fire away.
In Da Cloud
- Well steps one (1) and two (2) are the same from the above, however step three (3) is a bit different.
- You will need to pass your Cookie Token for the Office 365 Site here, and simply put you will log into the Target Office 365 Site and using using Fiddler you set it to “Capture Traffic” after you log in. If/when you click on a List or Library as an example, inspect your Headers returned (assuming you have already gone under Tools / Fiddler Options and selected to Decrypt HTTPS Traffic) like so
- Once you do and inspect the Headers you will find cookies for FedAuth and rtFa under the “Cookie” header. Go ahead and copy and paste that in the Request Headers section of Fiddler
- Next ensure your Verb is set to GET and fire away.
What if i want to POST (Add) Data to my SharePoint List?
Well, if you do, you have to do a few more things before you can actually POST that data.
- You will need to get the X-RequestDigest Header to send along with your POST, notice this is “not” a GET anymore. Now referencing the post again above by Kirk Evans if you look almost 3/4’s away down on the page or just Cntrl-F to find the word “contextinfo” you will find a description on how to get that Header Information in the d:FormDigestValue node.
- Essentially you will need to change your Fiddler VERB from a GET to a POST and send along an empty Request Body, you may need to set a “Content-Length” Request Header as well, and i usually do 104 for that length.
- You will get back the information that you will need to add to your Fiddler statement.
- In addition you will need to construct a Key/Value Pair (hence working in JSON) to pass along in the Request Content that you want to Add
- Now there is one more obscure piece of information that ususally trips people up that you will also need to do, and this information will form a part of your Request Content that holds your Data you are trying to add.
- When you did your GET for the site, there was a Node called (underscore undescore metadata) __metadata. You will need to take that value and pass it first ahead of any data you are trying to add. You will see this example below
- Once you do all the above, you ensure that your Verb is set to POST and fire away.
Lets look at a few Examples
So taking this from the beginning we will use a use case of a List i have called “AndyTaskListOnPrem” and we will first do some Gets, then a Post and then finally verify our work. Here is an example of the list before the exercise.
Next lets look at how you need to construct the GET to look at the data in JSON format in Fiddler
And our Outcome. Pay attention to the Metadata Node and the type = SP.Data.AndyTAskListOnPremListItem value. you will need this later on for the Adding of List Item.
Next lets look at how to get the ContextData information.
Create a POST with the URI of /_api/contextinfo">http://<site>/_api/contextinfo as i have below
Ensure the Response Content is blank and execute that, your results should look like mine below. You will need to capture the “FormDigestValue” information for later on in your POST.
Next lets Create a List Item
Armed with that information we have enough to create a List Item in this SharePoint List using the
REST API and Fiddler and using the same information plug this into Dictionary Objects in SharePoint Designer along with the Make a HTTP Call to do the same, here’s how…
Paying attention to the POST Verb, the X-RequestDigest “Request Headers” and the __Metadata and Addition in the “Request Body” after execution the above you should get
a HTTP/1.1 201 Created response. and if we inspect the list we should expect to see
To round things off, lets assume our Target was Office 365 SharePoint Online, then we would expect to see our Headers contain our Auth Tokens. I will take this to a next level by using an example someone just asked me about. I will Create a List in Office 365 “not list ITEM, an actual LIST” using the same methods described above. the KEY takeaway here is I need to Pass my Auth Tokens and ofcourse I am using a different REST API Call
Lets Create a SharePoint List using REST and Fiddler
So, in the spirit of openness, here is the REST call to get my Auth Token… well some of the token, I wasnt born yesterday 🙂
Here is the Context Info, key here is remember its not HTTPS and you need pass the Token as well.
Finally here is me creating A NEW LIST in the Web
and with all the confidence in the world [well this is O365 so we also cross our fingers] we expect to get a 201 Create Response back from HTTP Header
and visually in the browser we should expect to see
So, in this exercise we accomplished the following
- Understanding of some of the SharePoint 2013 REST API
- Usages of that API in Fiddler and the Browser
- How to Add a SharePoint List Item using the REST API
- How to create a new LIST using the REST API
This should translate VERY easily into SharePoint Designer 2013 and Visual Studio 2012, that is the point of the exercise. Thats why I am NOT showing how to do it there, I have other post that talk about the Dictionary Object and how to create Headers, just use what I have here and apply it to one of those post and you should be golden. This is me teaching to fish.. 🙂 Cheers.
Year after year the organizers and volunteers that put on SharePoint Saturday New York City #spsnyc do an amazing job and the intensity and excellence keeps getting better year after year. Fantastic Job Rebecca “Becky” I, Greg G, Jason G, Tasha S, Tom D, Brandon B, Casey S, and the rest of the crew too many to name.
This year it felt like on all level it came together even with the late lunch 🙂 The audience was exceptionally knowledgeable and engaging, we had fun while learning; i guess it helps that I actually have been seeing them for many years know and we treat each other like extended family. Even though next year we will be in a new Venue, I will miss the Microsoft facility across form the Hilton where the Halal GUys have their truck.
Regarding my Preso, Deck, Session Information
So, as noted in my presentation, and for those of you that know the way I deliver sessions, I do a lot of demos, hardly any slides. This time however, my session was based on a 2 part blog post I did, I requested that my session attendees not take notes because the blog post will be more information that you had in the session, so please pick it up here
Finally a SharePoint Designer that Developers and BAs will Love – This is the Part 1 of what would have come before the session you sat in and
Part 2 of Finally a SharePoint Designer that Developer and BAs will Love – This is reflective of the session you had
Thank you all for attending, hope to see you again next year.
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…
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
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.
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
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
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
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