How to Export SharePoint Group Members and Email Addresses to Excel
Question details
The user needs a method to extract the names and email addresses of SharePoint group members into a spreadsheet to review and manage the membership list outside of the SharePoint platform.
- Product
- Microsoft Excel / SharePoint
- Device & OS
- not provided
- Scenario
- Managing and auditing user access by extracting a SharePoint site's member list and their email credentials into Excel for external review.
- Observed behavior
- The user intends to use Excel Power Query to pull data from a SharePoint REST API endpoint, but may encounter empty results if the site utilizes Microsoft 365 groups instead of native SharePoint groups.
Ensure you have the correct SharePoint group ID and sufficient site permissions to access the user directory before attempting to connect via Excel Power Query.
Export Using Excel Power Query and OData Feed
Use the Get Data feature in Excel to connect directly to your SharePoint site's REST API and import the member roster seamlessly.
This method utilizes Microsoft Excel's built-in Power Query to fetch live data from a SharePoint OData feed endpoint. Once established, this connection can be refreshed at any time to get an updated list of group members.
Navigate to your SharePoint site in a web browser, go to Site Permissions, and open the specific group you want to export. Look at the browser's address bar and note the number at the end of the URL (e.g., MembershipGroupId=5).
Open Microsoft Excel. On the ribbon, click the 'Data' tab, select 'Get Data', choose 'From Other Sources', and click on 'From OData Feed'.
In the URL box, type your site URL followed by the API path: /_api/Web/SiteGroups/GetByID(YOUR_GROUP_ID)/Users. Replace 'YOUR_GROUP_ID' with the number you found in the first step, and click OK.
If prompted for credentials, select 'Organizational account' and click 'Sign in'. Go to Data Source Settings > Edit Permissions, and ensure the privacy level is set to 'Organizational'.
The Power Query Editor window will appear showing the retrieved data. Select the columns you need, such as 'Title' for names and 'Email' for addresses, then click 'Close & Load' in the top-left corner to populate your spreadsheet.
Manage Your Exported Data Effortlessly with WPS Office
While extracting live OData feeds from SharePoint requires Microsoft Excel, WPS Office is an excellent, free alternative for opening, formatting, and analyzing your exported .xlsx or .csv files without expensive subscription fees.
- 1. Download and Install: Get WPS Office for free from the official website and install it on your computer.
- 2. Open the Exported Roster: Launch WPS Spreadsheet and open the .xlsx file containing your newly exported SharePoint group data.
- 3. Format and Analyze: Use WPS Spreadsheet's robust suite of tools to filter out inactive users, sort email addresses, or format the table for presentation.
Frequently Asked Questions
Why does my SharePoint group show no members in Excel even though the site has members?
If your SharePoint site is connected to a Microsoft 365 group, the site members are managed centrally via the M365 group rather than a native SharePoint group. In this scenario, the standard SharePoint REST API URL will return an empty list. You will need to extract members using the Microsoft Graph API or export them from the Microsoft 365 Admin Center.
How do I find the Group ID for my SharePoint group?
Navigate to your SharePoint site, click the gear icon to access Site Settings, and open 'Site permissions'. Click on the specific group you want to export. Look at the URL in your browser's address bar; the Group ID is the number located at the very end of the URL after 'MembershipGroupId='.
Can I automatically refresh this exported data in Excel?
Yes. Because the data is imported using Excel Power Query, it maintains a live connection to SharePoint. You can simply go to the Data tab in Excel and click 'Refresh All' to update the spreadsheet with any new or removed SharePoint group members without having to re-enter the URL.
