How to Export SharePoint Site Owners and Members to CSV with PowerShell
Question details
SharePoint administrators need to list hub sites with their names, URLs, owners, and members, and export the comprehensive results to a structured CSV file.
- Product
- SharePoint Online
- Device & OS
- not provided
- Scenario
- Administrators auditing or managing SharePoint hub site permissions and memberships.
- Observed behavior
- Needs to retrieve structured data in a CSV format rather than plain text output, enabling further analysis in spreadsheet software.
Ensure you have the SharePoint Online Management Shell installed and are logged in with an account that has SharePoint Administrator privileges.
Export Structured Data to CSV using Export-Csv
Create custom PowerShell objects to collect site details and group memberships, then output to a CSV file for easy filtering in spreadsheet applications.
While Out-File creates plain text, Export-Csv produces structured data that can be filtered and opened directly in spreadsheet applications like WPS Spreadsheet.
Open PowerShell as administrator and connect to your SharePoint Admin center using 'Connect-SPOService -Url https://yourdomain-admin.sharepoint.com'.
Use 'Get-SPOSite' to loop through the sites in your hub. For each site, execute 'Get-SPOSiteGroup -Site $Site.Url' to retrieve its groups.
Construct a '[PSCustomObject]' for each site, mapping properties like Site Name, URL, Owners, and Members to the data retrieved by the cmdlets.
Pipe your collection of custom objects to the Export-Csv cmdlet by appending '| Export-Csv -Path "C:\output\SitePermissions.csv" -NoTypeInformation' to your script.
Redirect Output to a Plain Text File
If you only need a quick visual readout of the groups without structured formatting, you can output the raw console text to a file.
Open and Analyze Your CSV Files with WPS Office
Once you have exported your SharePoint permissions to a CSV file, you need a powerful spreadsheet tool to filter, sort, and analyze the data. WPS Office is a lightweight, free alternative to Microsoft Office that handles CSV files seamlessly.
- 1. Install WPS Office: Download and install the free WPS Office suite from the official website.
- 2. Open WPS Spreadsheet: Launch WPS Spreadsheet and click on Menu > Open.
- 3. Import CSV Data: Navigate to the SitePermissions.csv file you exported via PowerShell, select it, and let WPS automatically parse the comma-separated data into neat columns.
Frequently Asked Questions
Why is my CSV file showing garbled text when I open it?
If your SharePoint data contains special characters, you need to enforce UTF-8 encoding. Add the '-Encoding UTF8' parameter to your Export-Csv command in PowerShell.
How do I filter the PowerShell script to only show Site Owners?
You can filter the groups by looking for the word 'Owners' in the group title. Use a Where-Object clause like 'Where-Object { $_.Title -like "*Owners*" }' before extracting the users.
Can I run these cmdlets without SharePoint Admin privileges?
No, you must have SharePoint Administrator or Global Administrator rights to connect via Connect-SPOService and retrieve comprehensive site and permission data.
How do I open the exported CSV in WPS Spreadsheet?
Launch WPS Spreadsheet, click on Menu > Open, navigate to your saved CSV file, and select it. The application will automatically detect the comma delimiters and organize your data into rows and columns.
