Skip to main content

Exporting SharePoint Group Members to Excel Without PowerShell

Author by Drew Madelung

I was at a client recently and was not allowed to run any powershell commands but needed a list of accounts that existed in a SharePoint Group in a table format.  I also was not allowed site collection administration permission.  I tried a few different options in which trying to manipulate the list view of the group and using Excel data connections to get back to SharePoint but no option worked very cleanly. 

What I ended up using was a REST call to get the users and then downloading the XML response and opening it with Excel.  Use this link to learn about the available REST api’s for users & groups

Here are the steps….

1.  Get a client that you can use to test REST calls

 

2.  Construct the REST call to get a list of users by group

The structure looks like this:  https://siteurl/_api/web/sitegroups/getbyid(groupid)/users

  • To get the group ID simply navigate to the members page of your SharePoint Group and look at the number at the end of the URL  Here is the URL of my "Product Members" group: https://concurrencyinc.sharepoint.com/sites/products/_layouts/15/people.aspx?MembershipGroupId=9

Here is my call: https://concurrencyinc.sharepoint.com/sites/products/_api/Web/SiteGroups/GetById(9)/Users

  • We will see the users returned in the entry area of the response.
image_thumb-(1).png
 

3.  Download the XML response

  • In the Advanced REST Client click Save as file and then Download in the response section
  • image_thumb1-(1).png
 

4.  Change the file type

  • The file will download as a .text-plain file type.  Edit the filename and change it to a .xml file type.
  • image_thumb2-(1).png

5.  Open with Excel!

  • In Excel browse and pick out the new .xml file you created and select open this file as an XML table.
  • image_thumb8.png
  • Take a second and look at your pretty data.
  • image_thumb4-(1).png

  • 6.  Remove duplicates

  • The data comes across in a way that there are 2 rows for each user.  We can clean that up by removing the duplicates based on the login name.  First click anywhere in the imported table and under the Data tab click Remove Duplicates.
image_thumb5.png
 
  • Click the Unselect All button then scroll down and check ns4:LoginName
image_thumb6.png
 
  • That will remove your duplicate logins and you will have emails and logins that you can use whatever way you need.
image_thumb7.png
 

Handy appendix?

I know this post is titled how to get a list without powershell but I wanted to just include this down here as this is an easier approach if you have the ability.  Here are the commands to get a list of users in a SharePoint Group via powershell.

  • Get-SPSite http://server/sites/yoursite | Select -ExpandProperty RootWeb | Select -ExpandProperty Groups | Where {$_.Name -EQ "group name"} | Select -ExpandProperty Users | Select Name, Email| Export-Csv c:\scripts\users.txt

Here is the command to do it with SharePoint Online

  • Get-SPOUser -Site https://contoso.sharepoint.com/sites/finance -Group "group name"

Handy links!


 
Author

Drew Madelung

Technical Architect