How to Export SharePoint Site Users to an Excel Spreadsheet


When managing a SharePoint On-Premises environment, there are scenarios where you need to export site users into an Excel spreadsheet. Whether it’s for auditing, troubleshooting, or monitoring purposes, exporting users can help administrators ensure their sites are secure and compliant. In this blog, we’ll discuss how to achieve this using PowerShell scripts with SharePoint Add-Ins or PnP PowerShell, provide a high-level overview, and explore relevant use cases and challenges.


Overview

Exporting SharePoint site users to an Excel file involves extracting user details such as names, email addresses, group memberships, and permissions. This data can provide insights into user activity, access patterns, and help meet audit requirements. Depending on your SharePoint setup, you can use either SharePoint Add-Ins or PnP PowerShell to accomplish this task efficiently.


Use Cases and Scenarios
1. Audits and Compliance

Organizations often need to verify who has access to sensitive content and ensure compliance with internal and external regulations. Exporting user details makes this process easier.

2. Troubleshooting Permissions Issues

If users report issues accessing a site, exporting user details allows administrators to check permissions quickly.

3. User Activity Monitoring

Monitoring user access and identifying inactive users helps administrators maintain a clean and secure SharePoint environment.

4. Migrating Permissions

When migrating content from one SharePoint environment to another, having a list of existing users and their permissions ensures a smooth transition.

5. Documentation

Exported user data can serve as documentation for who had access to a particular site at a specific point in time, aiding in historical records.


Challenges and Issues
  1. Large User Lists: Sites with a significant number of users may require optimized scripts to handle large data volumes.
  2. Complex Permissions: Sites with broken inheritance or nested groups can complicate data extraction.
  3. Access Limitations: The script must be executed by someone with sufficient permissions to access user details.

PowerShell Script to Export Site Users

Here is a sample PowerShell script for SharePoint On-Premises that exports site user information to an Excel spreadsheet. The script uses SharePoint’s native libraries and is designed for environments without PnP PowerShell.

# Add SharePoint snap-in
Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

# Specify the site URL and output file path
$SiteUrl = "http://yoursitecollectionurl"
$OutputFilePath = "C:\SiteUsers.xlsx"

# Load SharePoint site
$Site = Get-SPSite $SiteUrl
$Web = $Site.RootWeb

# Prepare DataTable
$DataTable = New-Object System.Data.DataTable
$DataTable.Columns.Add("Name")
$DataTable.Columns.Add("Email")
$DataTable.Columns.Add("Group")

# Iterate through each user and their groups
foreach ($User in $Web.SiteUsers) {
    foreach ($Group in $User.Groups) {
        $Row = $DataTable.NewRow()
        $Row["Name"] = $User.DisplayName
        $Row["Email"] = $User.Email
        $Row["Group"] = $Group.Name
        $DataTable.Rows.Add($Row)
    }
}

# Export DataTable to Excel
$Excel = New-Object -ComObject Excel.Application
$Excel.Visible = $false
$Workbook = $Excel.Workbooks.Add()
$Worksheet = $Workbook.Worksheets.Item(1)

# Add headers
for ($i = 0; $i -lt $DataTable.Columns.Count; $i++) {
    $Worksheet.Cells.Item(1, $i + 1) = $DataTable.Columns[$i].ColumnName
}

# Add rows
for ($row = 0; $row -lt $DataTable.Rows.Count; $row++) {
    for ($col = 0; $col -lt $DataTable.Columns.Count; $col++) {
        $Worksheet.Cells.Item($row + 2, $col + 1) = $DataTable.Rows[$row][$col]
    }
}

# Save and close
$Workbook.SaveAs($OutputFilePath)
$Excel.Quit()

Write-Host "User details exported successfully to $OutputFilePath"

# Dispose objects
$Web.Dispose()
$Site.Dispose()

Using PnP PowerShell

If you’re using PnP PowerShell in a hybrid setup, the process becomes even easier. Here’s an example:

# Install PnP PowerShell if not already installed
# Install-Module -Name "PnP.PowerShell"

# Connect to SharePoint site
Connect-PnPOnline -Url "http://yoursitecollectionurl" -UseWebLogin

# Get site users
$Users = Get-PnPUser

# Export to CSV
$Users | Select DisplayName, Email, LoginName | Export-Csv -Path "C:\SiteUsers.csv" -NoTypeInformation

Write-Host "User details exported successfully to C:\SiteUsers.csv"

Exporting SharePoint site users to an Excel spreadsheet is an essential task for administrators managing SharePoint environments. Whether you use native PowerShell scripts or PnP PowerShell, the approach you choose depends on your requirements and environment. By regularly exporting and reviewing user data, you can enhance security, simplify troubleshooting, and ensure compliance with organizational policies. Have questions or additional tips? Let us know in the comments below!


Accounting.js Branding Cascading StyleSheet Cheat Sheet Connect Content Type CSS Currency Date Formats Dates Flows Hillbilly Tabs HTML5 Intl Javascript JavsScript JSON Format View Luxon NodeJs Numeral.js O365 OneDrive Out Of The Box Overflow Permissions PnP PowerAutomate Power Automate PowerShell Pwermissions ReactJs Rest Endpoint Send an HTTP Request to SharePoint SharePoint SharePoint Modern SharePoint Online SharePoint Tabs ShellScript SPFX SPO Styling Sync Teams App Transform JS TypeScript

Leave a Comment

Your email address will not be published. Required fields are marked *