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
