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

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.