Tag : JSON

Workflow – Understanding returned JSON format

Sometimes you need to see the data that is returned by a REST call to be able to work out how to use it. To view the data returned, I copy the HTTP Call Response into a Multi-Line field in a SharePoint list, query the list item and then cut and paste the string into a JSON Viewer (Like http://codebeautify.org/jsonviewer). I do not display the results of the call in the log as the Response content is often over the 255 character SharePoint print limit, which would result in a hung workflow.

The following steps assume that your REST Call is contained in the workflow variable ‘String01’

Setup your RequestHeaders dictionary

Create a dictionary variable as follows

203-3

… then click on the ellipsis (…) to define the names ‘Accept’ and ‘Content-Type’ in the dictionary. Set the value for both of these names to

application/json;odata=verbose

202-3

Make the HTTP Get Web Service Call

Specify the HTTP Get Web Service call using your REST statement (In my example, this is defined in ‘String01’). Be sure to use the ‘Properties…’ option to specify the correct ‘RequestHeaders’ variable otherwise the data may not be returned in the correct format

202-8

204-1

Save the response into a multi-line text field

Copy the response into a SharePoint Multi-Line field. In this example, I update the ‘Results’ field in ‘Current Item’ with the response returned by the call

204-2

Copy the data returned

Open the list item in SharePoint and copy the string from the field ‘Results’ to the clipboard

204-3

The example I used copied the following data to the clipboard

{“d”:{“results”:[{“__metadata”:{“id”:”https:\/\/contosa.sharepoint.com\/sites\/dev\/_api\/Web\/Lists(guid’f2c6ac85-9999-2222-a306-9500622ff36d’)\/Items(154)\/AttachmentFiles(‘Ferrari 2.jpg’)”,”uri”:”https:\/\/contosa.sharepoint.com\/sites\/dev\/_api\/Web\/Lists(guid’f2c6ac85-9999-2222-a306-9500622ff36d’)\/Items(154)\/AttachmentFiles(‘Ferrari%202.jpg’)”,”type”:”SP.Attachment”},”FileName”:”Ferrari 2.jpg”,”ServerRelativeUrl”:”\/sites\/dev\/Lists\/Utility Jobs\/Attachments\/154\/Ferrari 2.jpg”},{“__metadata”:{“id”:”https:\/\/contosa.sharepoint.com\/sites\/dev\/_api\/Web\/Lists(guid’f2c6ac85-9999-2222-a306-9500622ff36d’)\/Items(154)\/AttachmentFiles(‘Ferrari.jpg’)”,”uri”:”https:\/\/contosa.sharepoint.com\/sites\/dev\/_api\/Web\/Lists(guid’f2c6ac85-9999-2222-a306-9500622ff36d’)\/Items(154)\/AttachmentFiles(‘Ferrari.jpg’)”,”type”:”SP.Attachment”},”FileName”:”Ferrari.jpg”,”ServerRelativeUrl”:”\/sites\/dev\/Lists\/Utility Jobs\/Attachments\/154\/Ferrari.jpg”},{“__metadata”:{“id”:”https:\/\/contosa.sharepoint.com\/sites\/dev\/_api\/Web\/Lists(guid’f2c6ac85-9999-2222-a306-9500622ff36d’)\/Items(154)\/AttachmentFiles(‘Maserati.jpg’)”,”uri”:”https:\/\/contosa.sharepoint.com\/sites\/dev\/_api\/Web\/Lists(guid’f2c6ac85-9999-2222-a306-9500622ff36d’)\/Items(154)\/AttachmentFiles(‘Maserati.jpg’)”,”type”:”SP.Attachment”},”FileName”:”Maserati.jpg”,”ServerRelativeUrl”:”\/sites\/dev\/Lists\/Utility Jobs\/Attachments\/154\/Maserati.jpg”}]}}

View the data in a JSON viewer

Open a JSON viewer in a browser (I’ll use http://codebeautify.org/jsonviewer) and paste the clipboard into the viewer. In the example shown below, I pasted the data into the ‘JSON Input’ field. Once that is done, click on the format button in the viewer (‘Beautify’) and the system will display the data in an easier to read format as shown below on the right

204-4

If your ‘HeaderRequest’ dictionary had the ‘Accept’ and ‘Content-Type’ names set to ‘application/json;odata=verbose’, then your values should almost always have a “d” as the first element in the data, but there are some exceptions, especially when you are evaluating returned errors

The JSON formatted data from the above request is shown here for convenience (I replaced the ‘contosa.sharepoint.com/sites/dev/_api/Web’ with ‘…’ to make it easier to read:

{
    “d”: {
        “results”: [
            {
                “__metadata”: {
                    “id”: “https://…/Lists(guid’f2c6ac85-9999-2222-a306-9500622ff36d’)/Items(154)/AttachmentFiles(‘Ferrari 2.jpg’)”,
                    “uri”: “https://…/Lists(guid’f2c6ac85-9999-2222-a306-9500622ff36d’)/Items(154)/AttachmentFiles(‘Ferrari%202.jpg’)”,
                    “type”: “SP.Attachment”
                },
                “FileName”: “Ferrari 2.jpg”,
                “ServerRelativeUrl”: “/sites/dev/Lists/Utility Jobs/Attachments/154/Ferrari 2.jpg”
            },
            {
                “__metadata”: {
                    “id”: “https://…/Lists(guid’f2c6ac85-9999-2222-a306-9500622ff36d’)/Items(154)/AttachmentFiles(‘Ferrari.jpg’)”,
                    “uri”: “https://…/Lists(guid’f2c6ac85-9999-2222-a306-9500622ff36d’)/Items(154)/AttachmentFiles(‘Ferrari.jpg’)”,
                    “type”: “SP.Attachment”
                },
                “FileName”: “Ferrari.jpg”,
                “ServerRelativeUrl”: “/sites/dev/Lists/Utility Jobs/Attachments/154/Ferrari.jpg”
            },
            {
                “__metadata”: {
                    “id”: “https://…/Lists(guid’f2c6ac85-9999-2222-a306-9500622ff36d’)/Items(154)/AttachmentFiles(‘Maserati.jpg’)”,
                    “uri”: “https://…/Lists(guid’f2c6ac85-9999-2222-a306-9500622ff36d’)/Items(154)/AttachmentFiles(‘Maserati.jpg’)”,
                    “type”: “SP.Attachment”
                },
                “FileName”: “Maserati.jpg”,
                “ServerRelativeUrl”: “/sites/dev/Lists/Utility Jobs/Attachments/154/Maserati.jpg”
            }
        ]
    }
}

Evaluate the data elements returned

Now, you go down the tree using a forward slash (/) between the levels of the elements (Values between square brackets ([]) are in themselves array elements and need to be accessed with a counter)

To get the full array of data, move the Response into a variable of type Dictionary called ‘CurrentResults’.
This is useful when you do not wish to make your code more readable by specifying a shorter key to get the data from ‘CurrentResult’ instead of from ‘Response’

Get d/results from Variable:Response (Output to Variable:CurrentResults)

To get all data of the first element into a variable of type String called ‘RespString’:

Get d/results(0) from Variable:Response (Output to Variable:RespString)

To get all data of the second element into a variable of type String called ‘RespString’:

Get d/results(1) from Variable:Response (Output to Variable:RespString)

To get the attribute ‘FileName’ of the 3rd element into a variable of type String called ‘FileName’:

Get d/results(2)/FileName from Variable:Response (Output to Variable:FileName)
After this statement, the variable ‘FileName’ will contain ‘Maserati.jpg’.
The statement Get CurrentResults(2)/FileName from Variable:Response (Output to Variable:RespString) would yield the same result

To get the attribute ‘ServerRelativeURL of the 3rd element into a variable of type String called ‘SRU’:

Get d/results(2)/ ServerRelativeURL from Variable:Response (Output to Variable:SRU)

Troubleshoot any errors returned

If you receive an error when issuing an HTTP Get action, the ‘ResponseStatusCode’ variable will have a negative comment such as ‘NotFound’ or something similar. In this case, the HTTP Get call will place the error results in JSON format into the ‘ResponseContent’ variable. As a standard I always write the ‘ReponseContent’ variable into the Workflow Log so I can tell if there was a problem with the call as follows (If you do not do this, it may be difficult to troubleshoot any problems):

204-5

In the screen shown above, if the HTTP Get Call was not successful (Variable:ResponseCode not-equal ‘OK’), I added some code to display the actual reason for the error

The Error message written to the ResponseContent variable does not have the same “d/results’ type format as the statement shown above. Instead, it returns a value similar to the one shown below:

{“odata.error”:{“code”:”-2146232832, Microsoft.SharePoint.SPException”,”message”:{“lang”:”en-US”,”value”:”Group cannot be found.”}}}

When you paste this message into the JSON viewer, it generates the following structure:

{
    “odata.error”: {
        “code”: “-2146232832, Microsoft.SharePoint.SPException”,
        “message”: {
            “lang”: “en-US”,
            “value”: “Group cannot be found.”
        }
    }
}

Using the same methodology as shown previously, we can move the error message into a variable with the following action

Get odata.error/message/value from Variable:Response (Output to Variable:ErrorMessage)

We can then display this variable in the workflow log to get more detail on our problem. In this example, ‘ErrorMessage’ contains the value ‘Group cannot be found’.  Now I know that the User Group was not found, probably because the name was incorrect