Synchronize SharePoint Online Security Group Users To Lists Using MS Flow

Microsoft Flow is a SaaS-based platform that allows power users to send an email to a group of users. MS Flow doesn't support sending a direct email to SPO Group. Let's have a look at the scenario and solution to get started.

Scenario

If we need to send an email to SP Groups using MS Flow, it doesn’t support sending emails to SPO Groups. To make it work, a developer needs to store all the users as list items because an SPO List item can be used as “To” to send an email.

Start with the solution

Create a list with key & value pair as string type. Add Key as Group Name and MS Flow will add the users as the values against the defined key.

MS Flow at a glance

MS Flow is a scheduling activity that can be defined periodically as per the requirement, i.e., hourly, daily, weekly, monthly etc.

Let's start step by step.

Step 1

Recurrence is added as a trigger point to this flow. It can be defined as per the requirement and it works like a scheduled job.

Step 2

Initialize a variable, it will be used to store all email addresses with defined delimiter separator.

Variable name SPOGroupMembers of string type.

Step 3

Get Item from the list named GroupUsers. This list has two columns, consisting of a “Single Line Of Text”.

Step 4

Apply for each loop. The list can have multiple keys.

Add an action “Send an HTTP request to SharePoint”. Three properties are important for this action.

1. Site Address - It should be the URL of the site collection.
2. Method - Get request.
3. URL - “_api/Web/SiteGroups/getbyName(“Column Name”)/users?$select=Email

Step 5

Add the action Parse JSON. It expects the payload from the above request. To get the payload or JSON, the response can be obtained from the below article.

• How To Get Payload Request Data Using Microsoft Flow

Take out the payload from the HTTP request and paste it into the Schema section.

Step 6

Get the result after parsing the JSON. Loop through the result and store it into the above-created variable, i.e., SPOGroupMemebers with “;” as delimiter.

Step 7

Update the SPO List Item with SPOGroupMemebers Variable.

Output

SPO Group has two users -

SPList after running the flow will look like below.

Hope you will like it and Happy Coding.

How To Get Payload Request Data Using Microsoft Flow

To get the payload data from SharePoint Online, a developer needs to use either Postman or Custom Scripts. Usually, it’s a part of the solution or API. However, in some scenarios, developers need to use a payload outside the custom solution. Then, it’s not feasible to write a custom code or use Postman using the Client ID and Client Secret to get the payload or JSON response.

MS Flow is one of the easiest and quickest alternatives to get the JSON response from a SharePoint Online request. JSON response, i.e., the payload, can be used to identify the structure format, which helps to parse the data accordingly.

We can create a 3-step (trigger + action) flow that allows us to send an email quite securely (no need to handle the password separately).

Let’s explore how MS Flow is the quickest and easiest turnaround.

Step 1

Log in here with authorized credentials.

Step 2

Select My Flows -> New -> Create from blank.

Step 3

To start with the flow, we need to set the trigger point. So, I used recurrence as the trigger point to demonstrate.

Frequency can be set as per the requirement; I set it to 1 minute for demo purposes.

Step 4

Generate the payload for HTTP Request, i.e., Group Details - Group Name, “Approver” with the email selection.

Step 5

Send an email with the payload body.

Step 6

Save and wait for a minute. Also, you can trigger it manually.

Check your email account. You must have received a JSON payload with the data on the defined email address.

Example

1. {  
2.     "d": {  
3.         "results": [{  
4.             "__metadata": {  
5.                 "id": "https://mittal1201.sharepoint.com/sites/CommSite01/_api/Web/GetUserById(16)",  
6.                 "uri": "https://mittal1201.sharepoint.com/sites/CommSite01/_api/Web/GetUserById(16)",  
7.                 "type": "SP.User"  
8.             },  
9.             "Email": "demo@mittal1201.onmicrosoft.com"  
10.         }, {  
11.             "__metadata": {  
12.                 "id": "https://mittal1201.sharepoint.com/sites/CommSite01/_api/Web/GetUserById(6)",  
13.                 "uri": "https://mittal1201.sharepoint.com/sites/CommSite01/_api/Web/GetUserById(6)",  
14.                 "type": "SP.User"  
15.             },  
16.             "Email": "manoj@mittal1201.onmicrosoft.com"  
17.         }]  
18.     }  
19. }  

To verify the result, I have SP Group with two users. I have got the same in the above email.

That's it. I hope this article is helpful to you. Happy Coding!!!