Counting SharePoint users using Microsoft Flow

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

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

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

Send a HTTP request

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

ContractUsers

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:

  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

Parse 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

Parse JSON

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

metadata

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

Send an HTTP request to SharePoint

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:

Schema

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

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

Apply to each

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

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

Apply to each

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

Number of users

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

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].

Share this on...

Rate this Post:

Share: