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 Representation
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>)/usersendpoint:
Send a HTTP request
The <groupid> of 350 corresponds to my target group in SharePoint.
ContractUsers
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:
- 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.
- It includes the Email of each user. (Where the user is a group this value will be blank)
#2 – Parse the return as JSON
Parse JSON
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.
Parse JSON
In my case the response from #1 looked like this:
metadata
You can get the response by running your Flow and then copying the response out from the Output.
Send an HTTP request to SharePoint
Schema
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
Initialize a variable
We need somewhere to store the emails or empty responses!
#4 – Use an Apply to Each action to append responses into the array
Apply to each
The act of parsing the JSON makes each node available as dynamic content:
Dynamic content
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).
Apply to each
#5 – Get the number of users by counting the length of the array
Number of users
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'))
The length of the array
Reference:
Denton, S (2018). Counting SharePoint users using Microsoft Flow. Available at: https://buildbod.com/2018/06/20/counting-sharepoint-users-using-microsoft-flow/ [Accessed: 2 August 2018].
