Tag : http

Workflow – Useful REST calls – Getting User Properties

This Sharepoint Designer REST call fetches the manager associated with a user (in this case the person who has initiated the workflow), but it can also be used to get any other attribute associated with the user. The statements shown below have all been included in a single app step in the workflow (Make sure you read the Notes at the end of this blog)

Build a dictionary for a REST call

Add a ‘Build Dictionary’ action as show in the image below

ip0901

(When you add the ‘Build Dictionary’ action, the system will initially have ‘this‘ instead of the bracketed ellipsis ({…}). Click on ‘this‘ to add the Names ‘Accept’ and ‘Content-Type’ both with the value ‘application/json;odata=verbose‘ as shown below

ip0902

Set the string that you will use for the rest call

Create a string that you will contain the rest call that you will make as follows

ip0903

For clarity, the String Builder is as follows:

[%Workflow Context:Current Site URL%]_api/SP.UserProfiles.PeopleManager/GetUserProfilePropertyFor(accountName=@v,propertyName=’Manager’)?@v=’[%Workflow Context:Initiator%]’

Where:

[%Workflow Context:Current Site URL%]

The URL of your site. This is taken from the ‘Workflow Context’ Data Source but could also be hard-coded

_api/SP.UserProfiles.PeopleManager/GetUserProfilePropertyFor(accountName=@v,

This is the method used to get the properties. Enter this as is

propertyName=’Manager’)

This tells the system that the property we wish to retrieve is the ‘Manager’ (This is what we are retrieving in this example, but this could also be any of the other user properties such as ‘Department’, ‘PreferredName’, ‘PictureURL’ or any of the others. Search on the web for other properties or visit https://technet.microsoft.com/en-us/library/hh147510.aspx to get a list of a few of the properties)

@v=’[%Workflow Context:Initiator%]’

The user whose manager you wish to get (Note the single quotes). I used the ‘Workflow Context’ Data Source to get the Login name of the user who initiated the workflow, but you can use the ‘Created By’ value or a string value that is defined in the following format:

i:0#.f|membership|username@contosa.com

 Change certain reserved characters in the string

The user name has certain properties that need to be ‘translated’ for the rest call to work properly. Use the ‘Replace’ action in Designer to substitute i:0#.f with i%3A0%23.f in the string ‘String01′ (this is simply replacing the characters with their encoded ASCII value)ip0904

Write the final string to the log

To make sure that you the correct expression has been defined, write the final string to the log as shown below

ip0905

Call the REST service with the string you have defined

Insert a ‘Call HTTP Web Service’ action as shown below.

ip0906

Some things about this call.

The original ‘this‘ was changed to the following:

ip0908

Also, click on the down arrow of the action, select properties and then set ‘RequestHeaders to the dictionary variable you created earlier (Header) as shown below

ip0909

Get the Managers name (or other property)

Insert a ‘Get an item from a dictionary’ action to fetch the name of the manager (or other data requested) as shown below where I re-use the ‘String01’ variable to contain the result. The result is in the same format as the user login name you used in the REST request (i:0#.f|membership|username@contosa.com)

ip0911

The actual statement ‘Get‘ statement is ‘d/GetUserProfilePropertyFor‘ if you want to copy and paste.

Display the value that you you have retrieved to make sure it is what was expected 

ip0912

Display any errors that have been returned (See Workflow – Troubleshooting REST calls  for troubleshooting any issues)

ip0913

Notes

You need to at least have ‘Read’ rights on the social tenant. See Workflow – Scoping the App Step on how to set this up, but in short your ‘Permission Request XML’ should look similar to the following (The second line allows your workflows to update any data in the site collection, while the third line allows you access to the social data):

<AppPermissionRequests>
<AppPermissionRequest Scope=”http://sharepoint/content/sitecollection” Right=”FullControl” />
<AppPermissionRequest Scope=”http://sharepoint/social/tenant” Right=”Read” />
</AppPermissionRequests>


Workflow – Useful REST calls – Get foreign currency

I cannot remember the post that pointed me in this direction. While there are many Web Services that return currency cross rates, the site http://currency-api.appspot.com returns a restricted set of rates for free – all you need is to get an access key. The API is documented and easy to work with, but there some restrictions, namely how many rate requests you can make in a month.

To work with this Web Service:

Get you Free API Access key

Visit ‘http://currency-api.appspot.com/’ and click on the button ‘Get Your Free API Access Key’ . This will open up a new screen where you can sign up for free. Follow all instructions to get your key. The key is important

208-1

Create the REST call string

In your workflow, create a string with the following value

http://currency-api.appspot.com/api/EUR/USD.json?key=3af4391296f24977235b738d5f2e4680

208-2

This string converts from EUR to USD using my key:

(You can perform any desired substitutions in the string makeup with the variables available in your workflow)

Call the Web Service to get the rate

Build the ‘standard’ Header dictionary with the names ‘Accept’ and ‘Content-Type’ set to ‘application/json;odata=verbose’

208-3

Call the Web Service with a Call HTTP Web Service action using the string previously defined

208-4

Create the various dictionary items needed in the call and remember to set the ‘RequestHeaders’  to your Header dictionary previously defined by using the ‘Properties’ option of the call statement

208-6

This should return something similar to the following in the response:

{
successtrue,
source: “EUR“,
target: “USD“,
rate1.1366,
amount1.14,
message: “”
}

Check if data returned is OK

I then check if the Response code equals OK before I move to the next step. If it is not OK, I know something went wrong in the call, and I email the administrator

208-7

Get the exchange rate

The next steps involve getting the rate from the returned response. This is performed using a Get action as follows (remember to get the case correct):

Get rate from Variable:Response (Output to Variable:ExchangeRate)

Finally, you have the exchange rate of EUR to USD to do with as is your wont. Since the API has a restriction on the number of times it can be accessed, I allow the users to specify how often they would like the rate updated and in most cases, they selected a monthly update. But your user may require more up-to-date information so it could be that you have to buy ‘professional’ access to the API


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