How to get email addresses from an AD Security Group inside a SharePoint Group for a Workflow

Question

I have a workflow that is supposed to send an email to a list of users based on a SharePoint Group. The issue I'm having is the group is populated from an AD group.

So I'm not sure of how to filter the data coming into my workflow or if an HTTP Request is the proper way to pull in that information.

And this is the SharePoint Group where I am pulling the data from:

Answer 1

If you want to get users from the AD group, you need to use the Azure AD connector. You also have to parse the JSON returned by the REST API to test if principals in the SharePoint group are users or AD groups.

So, let's go!

After the REST API, add an action to parse JSON.

The schema is:

{
    "type": "object",
    "properties": {
        "d": {
            "type": "object",
            "properties": {
                "results": {
                    "type": "array",
                    "items": {
                        "type": "object",
                        "properties": {
                            "__metadata": {
                                "type": "object",
                                "properties": {
                                    "id": {
                                        "type": "string"
                                    },
                                    "uri": {
                                        "type": "string"
                                    },
                                    "type": {
                                        "type": "string"
                                    }
                                }
                            },
                            "Alerts": {
                                "type": "object",
                                "properties": {
                                    "__deferred": {
                                        "type": "object",
                                        "properties": {
                                            "uri": {
                                                "type": "string"
                                            }
                                        }
                                    }
                                }
                            },
                            "Groups": {
                                "type": "object",
                                "properties": {
                                    "__deferred": {
                                        "type": "object",
                                        "properties": {
                                            "uri": {
                                                "type": "string"
                                            }
                                        }
                                    }
                                }
                            },
                            "Id": {
                                "type": "integer"
                            },
                            "IsHiddenInUI": {
                                "type": "boolean"
                            },
                            "LoginName": {
                                "type": "string"
                            },
                            "Title": {
                                "type": "string"
                            },
                            "PrincipalType": {
                                "type": "integer"
                            },
                            "Email": {
                                "type": "string"
                            },
                            "Expiration": {
                                "type": "string"
                            },
                            "IsEmailAuthenticationGuestUser": {
                                "type": "boolean"
                            },
                            "IsShareByEmailGuestUser": {
                                "type": "boolean"
                            },
                            "IsSiteAdmin": {
                                "type": "boolean"
                            },
                            "UserId": {},
                            "UserPrincipalName": {}
                        },
                        "required": [
                            "__metadata",
                            "Alerts",
                            "Groups",
                            "Id",
                            "IsHiddenInUI",
                            "LoginName",
                            "Title",
                            "PrincipalType",
                            "Email",
                            "Expiration",
                            "IsEmailAuthenticationGuestUser",
                            "IsShareByEmailGuestUser",
                            "IsSiteAdmin",
                            "UserId",
                            "UserPrincipalName"
                        ]
                    }
                }
            }
        }
    }
}

Next, add a foreach on the previous result.

In the loop, add a condition to separate groups from users.

Condition is:

LoginName starts with c:0t.c|tenant|

If "yes", it's a group.

You have to get group id from the login.

Create a variable with this expression:

split(items('EachGroups')?['LoginName'], '|')[sub(length(split(items('EachGroups')?['LoginName'], '|')), 1)]

Next, add Azure AD connector to get group's users.

You can add a loop to get every user email.

items('EachUser')?['userPrincipalName']