Get Members of SharePoint Groups in Excel using Power Query

Challenge:

We have several business units. And in that, for each business unit, we have Approvers, Owners, Members and Viewers. The challenge is to get the members of all business units'  "Approvers" SharePoint group in Excel.

Resolution: 

In the previous blog, we have explained about Power Query in Excel and connecting the SharePoint Lists from SharePoint Online.

Now, using Power Query function we would be getting the list members of SharePoint groups. For this, we would be first creating a function which would get the members of any SharePoint group.

• For this, we would go to Data > Get Data > From Other Source(s) > From OData Feed. Then, as per the requirement, we need members of the Approvers groups for all business units. So, in this query, we would be fetching the names of the SharePoint groups having "Approvers" in their names and renaming this as "SPGroups".

https://<domain>/_api/web/SiteGroups?$filter=substringof('Approvers',Title)&$select=Title

• We have the list of SharePoint groups, and now, we would be fetching the members of each SharePoint group, by creating a Power Query function. For this, we would be adding a new blank query and rename it as "GetMemberByGroupName". We know the REST call to get group members is as below:

https://<SiteURL>/_api/web/sitegroups/getbyname('<GroupName>')/users

• So, we would add a parameter "GrpName" as text, which would make a REST Call for each group, and get all the members. The function (in the Advanced Editor) would be like below:

(GrpName as text) as table =>         
let
    Source= OData.Feed("https://<domain>/_api/web/sitegroups/getbyname('"&Text.From(GrpName)&"')/users?$select=Title")
in
    Source

• Now, we would go back to SPGroups (1st query) and in the ribbon Add Column > Invoke Custom function. We would select the function and the column name as a parameter and expand the table we get in Custom column.

• So, here we have the members as per the SharePoint group names.

• Expand "GetMemberByGroupName" column.
  
  
• So, members for the SharePoint group will be extracted here!
  

This way, we can have all the members of the selected SharePoint groups in the Excel file using Power Query.

If you have any questions you can reach out our SharePoint Consulting team here.