Before we start, if you’re just wanting to email all members of the group you can “select all” then choose “E-Mail Users” from the action menu when viewing the group membership within SharePoint. This opens an Outlook window with all the addresses pre-populated.
But, if you still need an excel sheet of membership for another purpose (perhaps to format a sign-in sheet, generate documents with mail merge, share the list with others, etc.) follow these steps:
- With the group open in SharePoint, copy the URL and note the ID number at the end of the URL
- Open a new excel workbook
- From the “Data” tab, select “New Query” –> “From Other Sources” –> “From OData Feed“
- Paste the group URL in the prompt but delete everything after the site address and replace with the following, replacing “6” with your group’s ID from step 1
/_api/Web/SiteGroups/GetByID(6)/Users
- When prompted for login credentials you’ll use “Windows” for on-prem/server and “Organizational account” for O365.
- Click “Edit” once the group loads so we can choose which columns to keep/delete
- Ctrl+click the column headers you want to keep
- Right-click a header of a column you’re keeping and select “Remove Other Columns”
- Close and Load
- You should now see your group membership and email addresses (and any other fields you kept). Save this somewhere and, if updates are made in the future, just click “Refresh all” to bring in new members and remove old.
Updated 8/8/2019: Added step 5 to show the login credentials prompt.
Thank you for the instructions.
Is there a way to export the “Job Title” from sharepoint permission group? The “Title” on Excel only shows people’s name.
Thanks.
Thanks for this. I have a followup. Is there anyway to access or import the users into a sharepoint list instead of into Excel?
Thank you Nate for these instructions. I have used this numerous times and it works great every time. Quick question: When I first navigate to “permissions for this list” there are SharePoint groups I can use the instructions for, but there are also individuals listed with permissions but I am not able to use your instructions to get them because there is no MembershipGroupID associated with this main list. Any tricks you know of to get it?
Boom still works in 2022 thanks so much!
Simply AMAZING!! I have looked for a solution to pull all names/emails from a sharepoint group for years and this was so clear and simple! So thankful that you posted this and I found it! Thank you!
Thank you very much 😀 🙂
This is great, I’ve been able to take this page and export my sharepoint user list, then some vba code to rearrange into a master list showing all users, and which groups they are in.
I’d like to take this one step further though and be able to go through each library on my site and get an export of which groups have permissions to which libraries.