EDIT 04/25/17: Updated Export Technique
Here's a topic that came up today with a user:
"I have a SharePoint Permissions Group with a few hundred people in it. Is there a way I can export the list of people in the group to Excel?"
After a bit of digging around in the Permissions, I found that SharePoint doesn't have this functionality built-in.
Google to the rescue.
*** I was able to find 2 solutions, neither one of which can pull user groups individually. Both solutions will pull all users in all groups. The only way to pull users by the group is using SQL Server queries against your database or by having access to the SharePoint servers to run PowerShell scripts. ***
Anyhow, you should be able to work with a complete dump of all users in all groups.
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.
After a few seconds, you will have a workbook full of users!
_________________________________________________________________________________
I was also able to uncover a little hack that allows the user to export a User Group to excel.
1. Go into Site Permission [Site Settings > Site Permissions]
2. Click on the User Group that you would like to export, and go into List Settings [Settings > List Settings]
.png)
3. Down at the bottom of the List Settings page, you'll see a couple of views. Here, we will click on the Detail View.
4. In the URL of the Detail View page, you will see that there is a List ID and a View ID. We want to copy both of these, making sure not to include the ? before List ID, and the & after View ID.
5. SharePoint contains built-in functionality for exporting other types of lists. It is accessible through URL manipulation.
http://<sitename>/_vti_bin/owssvr.dll?CS=109&Using=_layouts/query.iqy&List=<ListID>&View=<ViewID>&CacheControl=1
Here, you will see where the List ID and View ID come in to play. Simply enter your append the query string to your site URL with the appropriate List and View ID, press "Enter" and you should see a dialog box asking you to save file.
Save the file, and open it with Microsoft Excel.
___________________________________________________________________________________
Please post any comments or questions to below!
Check out MaintainYourRide.com if you need advice on DIY Automotive maintenance.
linkback: http://sharepoint-works.blogspot.com/2012/03/export-sharepoint-user-groups-in-excel.html#.UWRiTFewUfc
Here's a topic that came up today with a user:
"I have a SharePoint Permissions Group with a few hundred people in it. Is there a way I can export the list of people in the group to Excel?"
After a bit of digging around in the Permissions, I found that SharePoint doesn't have this functionality built-in.
Google to the rescue.
*** I was able to find 2 solutions, neither one of which can pull user groups individually. Both solutions will pull all users in all groups. The only way to pull users by the group is using SQL Server queries against your database or by having access to the SharePoint servers to run PowerShell scripts. ***
Anyhow, you should be able to work with a complete dump of all users in all groups.
SOLUTION 1
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.
After a few seconds, you will have a workbook full of users!
_________________________________________________________________________________
I was also able to uncover a little hack that allows the user to export a User Group to excel.
SOLUTION 2
1. Go into Site Permission [Site Settings > Site Permissions]
2. Click on the User Group that you would like to export, and go into List Settings [Settings > List Settings]
.png)
3. Down at the bottom of the List Settings page, you'll see a couple of views. Here, we will click on the Detail View.
4. In the URL of the Detail View page, you will see that there is a List ID and a View ID. We want to copy both of these, making sure not to include the ? before List ID, and the & after View ID.
5. SharePoint contains built-in functionality for exporting other types of lists. It is accessible through URL manipulation.
http://<sitename>/_vti_bin/owssvr.dll?CS=109&Using=_layouts/query.iqy&List=<ListID>&View=<ViewID>&CacheControl=1
Here, you will see where the List ID and View ID come in to play. Simply enter your append the query string to your site URL with the appropriate List and View ID, press "Enter" and you should see a dialog box asking you to save file.
Save the file, and open it with Microsoft Excel.
___________________________________________________________________________________
Please post any comments or questions to below!
Check out MaintainYourRide.com if you need advice on DIY Automotive maintenance.
linkback: http://sharepoint-works.blogspot.com/2012/03/export-sharepoint-user-groups-in-excel.html#.UWRiTFewUfc
This is the best solution i have come across so far.Thank you!
ReplyDeleteBut the problem is: You have to do it for each and every group in the site! Its a pain especially when we have large no. of SharePoint group. So the Optimal way is: Export SharePoint Users and Groups to Excel using PowerShell!
ReplyDeleteFind the PowerShell Script Here: Export SharePoint Users and Groups to Excel using PowerShell
Hi Adam,
DeleteThis solution was more meant for the users/administrators that don't have the ability to run PowerShell scripts on demand. Many organizations have restrictions that require approval prior to executing scripts on Production environments.
If you have access/ability to leverage PowerShell, by all means, use it!
The first option doesn't work, it seems to dump all the users, not the group members. And I don't see "List Settings" in the second option :/
ReplyDeleteWere you able to resolve the issue? Both methods do not return required group members; returns extra member details.
DeleteSame here, how to enable "List settings " in the menu
ReplyDeleteFor "List Settings" to be enabled, you must be a Site Collection Administrator. However, if you can get to the permissions group screen (/_layouts/people.aspx?MembershipGroupId=...), you should be able to collect all of the information necessary:
DeleteViewID can be found in the URL bar, and ListID can be found by right-clicking on the name of a user in the permissions group, and copying the link address. When you paste the address somewhere, the ListID will be part of the URL.
Thank you so much! Every other solution I looked up required powershell scripts! The second solution using url manipulation worked like a charm. I was actually looking to pull the email addresses for all my users, so before I grabbed the list ID and view ID, I added "work email" to the view. Worked perfectly :)
ReplyDeleteHi.. I tried the second option. But again, I'm getting all the users in my excel. Please explain how did it work for you guys?
ReplyDeleteThis thread is old but I want to point out a missing detail that might help someone else who finds this. You must be a Site Collection Administrator for List Settings to show as depicted in the Step 2 screenshot above. I verified this on two site collections; one I'm SCA and the other I'm just a site level admin. I haven't (yet) found a solution that does this for non-SCAs.
ReplyDeleteHas anyone found a way to get just the contents of a single group into Excel? I don't want a list of all SP users, just the list of users in a single group.
ReplyDeleteUsing solution 1, it worked for me but I had to make sure "Disable Web query redirections" is checked off. So, before step 5, click on the Options button. Check off Disable Web query redirections under Other Import settings section in the Web Query Options dialog box.
DeleteI used solution 1 to export users of a user group to Excel. There's an additional step that I had to take for solution 1 to work me. Before step 5, click on the Options button. Check off Disable Web query redirections under the Other Import settings section in the Web Query Options dialog box.
DeleteI 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:
ReplyDelete1. 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!