This post will show you how to make a REST call to the SharePoint API and process the JSON response using Microsoft Flow.

Background

As part of a solution I was building I needed to count the number of users in a SharePoint permission group. Unfortunately, the number of users is not returned in the SharePoint Users, groups, and roles REST API UserCollection resource. A GET request to the UserCollection returns:

odata

On inspection of the response I could see that if I could parse the JSON to extract the ‘Email’ addresses into an array I could then count them.

Steps

steps

#1 – Get the data from SharePoint using a HTTP request action

The first action is to issue a GET request to the _api/web/sitegroups(<group id>)/users endpoint:

1

The <groupid> of 350 corresponds to my target group in SharePoint.

1a

Hopefully you’ve noticed that in the Uri field I’ve added a $select value to my request.

_api/web/sitegroups(350)/users?$select=Email

The $select value has two effects:

  1. It reduces the size of the object returned to only those defined as required items i.e. not the full list shown above. Through my experimentation I have learned that the __metadata array is defined as required in the API and so it is provided with every call. The remainder is defined as optional by default.
  2. It includes the Email of each user. (Where the user is a group this value will be blank)

#2 – Parse the return as JSON

2c

The Parse JSON action is not well documented… It is a member of the Data Operations actions. The action includes a nice feature that allows you to construct a schema based off an example response.

2d

In my case the response from #1 looked like this:

2

You can get the response by running your Flow and then copying the response out from the Output.

2a

In order to use the schema feature you have to remove all spaces out of the response and reduce it to a single line. (Though the feature will not tell you that. It will simply keep reporting invalid characters at line x until you do!). Once you have removed the spaces and reduced it to a single line on clicking Done the feature should produce a schema that you can use. In my case the schema is:

2e

Note if you then reopen the sample schema generator it will wipe out the schema currently held by the action so it is worth saving the schema somewhere safe!

#3 – Initialize a variable to hold the parsed JSON for use in an Apply to Each action

3

We need somewhere to store the emails or empty responses!

#4 – Use an Apply to Each action to append responses into the array

4a

The act of parsing the JSON makes each node available as dynamic content:

4

The results of #3 are stepped through with the value of the current ‘Email’ node added to the array using a Append to array variable action (from the Data Operations actions).

4b

#5 – Get the number of users by counting the length of the array

4a

Finally the length of the array is calculated using a Length expression to yield the number of users (which in this case is 2).

length(variables('userEmails'))

6

 

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s