How to Export SharePoint Person Columns as Email Addresses
Question details
Users need to export the email addresses from a SharePoint list's Person or Group column, rather than the default display names.
- Product
- Microsoft SharePoint
- Device & OS
- not provided
- Scenario
- Exporting a SharePoint list containing Person or Group columns for external data analysis, email marketing, or communication purposes.
- Observed behavior
- When exporting the list, SharePoint defaults to extracting the user's display name and provides no built-in UI option to change the output to email addresses.
Ensure you have the appropriate SharePoint site permissions to run PowerShell scripts or access the site via the SharePoint REST API.
Extract Email Addresses using PowerShell or REST API
Since SharePoint does not natively support exporting email addresses directly from the list view, utilizing a data-extraction method like PowerShell is the most effective workaround.
PowerShell allows you to query the SharePoint list directly and specify exactly which user properties (such as the Email field) you want to retrieve. Alternatively, developers can use the SharePoint REST API to pull this data programmatically.
Launch the SharePoint Online Management Shell or your preferred PowerShell IDE.
Use the Connect-PnPOnline cmdlet to authenticate and connect to the specific SharePoint site containing your list.
Write a script utilizing Get-PnPListItem to iterate through the list. Ensure your script explicitly requests the 'Email' property from the Person or Group column object.
Use the Export-Csv cmdlet to output your newly formatted dataset, which will now include the actual email addresses, to a local file.
Submit Feedback for Native Support
If you prefer a built-in solution for the future, you can submit a feature request to Microsoft.
Manage and Analyze Your Exported SharePoint Lists with WPS Office
Once you have successfully exported your SharePoint list data using PowerShell or APIs, you need a reliable spreadsheet tool to view and analyze your CSV or Excel files. WPS Office provides a lightweight, highly compatible alternative to Microsoft Office for seamlessly managing your data.
- 1. Open WPS Spreadsheet: Launch WPS Office on your computer and click to open the Spreadsheet module.
- 2. Import your dataset: Click 'Open' and locate the CSV or Excel file you just exported from SharePoint.
- 3. Analyze your data: Use the built-in filtering, sorting, and formatting features to clean up your email address list for your communication or reporting needs.
Frequently Asked Questions
Why does SharePoint export display names instead of email addresses by default?
When you use the native 'Export to Excel' or 'Export to CSV' button in a SharePoint list, the system retrieves the default display value of the Person or Group column. For user accounts, this default text value is the display name, not the underlying email address.
Is there any hidden SharePoint setting to change the export format?
No, Microsoft currently does not provide any built-in UI setting or hidden toggle within the SharePoint list views to change the export output from display names to email addresses.
Can I use Power Automate to extract the email addresses instead of PowerShell?
Yes, you can create a Power Automate flow that triggers manually or on a schedule. The flow can use the 'Get items' action to retrieve the list data, extract the 'Email' property from the Person column, and append it to a new CSV file or Excel table stored in OneDrive or SharePoint.
