Exporting SharePoint Members into Excel

Question

Is there a proper way to export a group of sp group members into excel?

I have been able to select a group from permissions and export them into excel. The problem I face was that even when I selected one group and exported, the entire list of members came along with it. This would have been ok, but when members were in more than one group, this information was not retained. I was left with a master list of members that retained all users, but lost information about if they were in more than one group.

What is the best way to go about creating either a master list of group members for all the groups I have OR getting an export on a group by group SharePoint Group member basis that I can aggregate later?

Answer 1

Below worked for me:

Steps:

1) Open Excel File.

2) Go to ribbon, select DATA and select From Other Sources, and click on From OData Data Feed.

3) In ‘Data Connection Wizard’ form, provide appropriate REST link and click on Next.

REST Link format to get Sp Group users:

https:// Your SP2013 Site /_api/Web/SiteGroups/GetById(9)/Users

4) In Next form, select ‘Users’ table and click Next.

5) In next form provide an appropriate data connection file name & friendly name (Note: this is not excel file name), check ‘Always attempt to use this file to refresh data’ and click Finish

6) In last form, ensure that ‘Table’ is selected. Select worksheet (by default its existing worksheet which is open) and click OK.

7) Users of that group will be exported to the excel sheet.

Reference: http://www.c-sharpcorner.com/UploadFile/a30324/export-sharepoint-group-users-into-excel-2013-using-odata-an/

Thanks,

Answer 2

You mentioned, you were able to export the groups in Excel what method you use for it?

Here is method I used.

1. Navigate to the People and Groups tab under Site Settings [Site Settings > People and Groups]

2. Copy the URL of the People and Groups page.

3. Open up your Excel workbook, and click on the Data tab.

4. Click "From Web" and paste the URL from the People and Groups page into the Address Bar.

5. After the page loads, you will see an arrow pointing to the list of group members. Upon hovering over this arrow, the list of group members will be highlighted. Click this arrow, then click Import, then OK.

Their is another method to use owssrv.dll, please check this blog for that.

http://graysick.blogspot.com/2013/04/export-user-groups-to-excel-sharepoint.html

Answer 3

I was able to pull the membership of a 2013 SharePoint permission group that had 2035 members using the following steps. Note that I wanted the account name only. You may need to modify this process slightly if you want a different field, or more than one field:

1. Page through the group to find out how many members it has
2. Go to Settings/Site Settings/People and Groups/ and choose any group.
3. Choose Settings/List Settings on the toolbar for the group membership
4. Create a new view with the column you want to export (I only wanted the account column), and set the Item limit to a few more than the number of members in the group you want to export (I set mine to show records in batches of 2100, then I called my view "2100 Items")
5. Go back to Settings/Site Settings/People and Groups/ and choose the group you want to export.
6. At the right end of the toolbar for the group membership, change the view selector to select your custom view.
7. Use Ctrl/A to select the entire page, then Ctrl/C to copy it.
8. Open an empty text file using Notepad and paste the contents of the page into it.
9. The copy operation will leave you at the bottom of the text file. Delete all the text below the last account.
10. Go to the top of the text file and delete all the text above the first account.

You now have a fairly clean list of all the group members. Note that it contains a trailing space at the end of each line. Depending on how you want to use the data, you may need to do some more cleanup in Word or Excel. (Cleanup hints: You can paste your list of accounts into Excel, then use Text to Columns to clean off the trailing space. Or, you can use Word and replace all occurrences of [space][paragraph mark] with just [paragraph mark] if you exported multiple columns.)

Happy SharePointing!

Answer 4

It seems that you need to pull the information from the data base because the method mentioned Wasqas Sarwar MCSE pulls the members from a view, not retaining all information about group membership.

You must use Powershell on the database or reference groups by hand and labele them with their memberships in excel.

Answer 5

I couldn't get the solutions above to work (I was getting EVERY user, but not the specific group), but I was able to figure out another manual way. Select all the users in the group, Actions --> Email Users. An email will be opened with the To: Filled in with those user emails. Copy email addresses and save to a text file (Wordpad, save as Text). Repeat if there were multiple pages in the permission group and append to the end of the last email address in the text file. Save. Open Excel, Data tab --> From Text --> Import from delimited file. You may then need to transpose if you prefer. This only works if the email addresses are descriptive using the person's name and not something that doesn't identify a person directly.

Answer 6

To get everyone who has access to your SharePoint site.

1. Open Excel
2. Go to 'Data' then 'Get Data' then 'From SharePoint Online List'
3. Put in your SharePoint site "home" directory https://sharepoint.com/sites/
4. From the list, choose 'User Information List'