This post will show you how to make a REST call to the SharePoint API and process the JSON response using Microsoft Flow.
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:
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.
#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:
The <groupid> of 350 corresponds to my target group in SharePoint.
Hopefully you’ve noticed that in the Uri field I’ve added a $select value to my request.
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
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.
In my case the response from #1 looked like this:
You can get the response by running your Flow and then copying the response out from the Output.
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:
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
We need somewhere to store the emails or empty responses!
#4 – Use an Apply to Each action to append responses into the array
The act of parsing the JSON makes each node available as 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).
#5 – Get the number of users by counting the length of the array
Finally the length of the array is calculated using a Length expression to yield the number of users (which in this case is 2).