Export SharePoint Group Users Into Excel 2013 Using OData And REST API

Once I came across a simple requirement from a site admin where he wants to export all users from various SharePoint groups to Excel sheet. In this case, number of users are in hundreds and admin wants to send these details for audit purpose.

We have export to excel functionality in SharePoint lists, but unfortunately this is not available in user information lists or SharePoint Groups. So we came up with a simple solution to export SharePoint Group users into excel using OData & REST. In this article, I explained how to achieve this.

Prerequisites:

1. SharePoint 2013 On-prem or O365 site, where you have sufficient permissions to perform REST query.
   
   
2. MS Excel 2013, where you already signed-in with your domain account (for On-prem) or O365 account.
   
   

Steps to export data from SharePoint to Excel

1. Open Excel 2013.
   
   
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 (see table above) and click on Next.
   
   REST Link format to get SPGroup users:
   
   https://<Your SP2013 Site>/_api/Web/SiteGroups/getbyname('<Group Name')/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.
   
   

Note: We can use same steps to get data from SharePoint lists into Excel with OData Data feed and REST API by providing a valid REST query in step #3.