SharePoint Online: Export User Information List using PowerShell

Requirement: Export User Information List in SharePoint Online.

User Information List in SharePoint Online stores the information about users. It captures user metadata such as User Picture, Email, DisplayName, LoginName, etc. When we grant permissions to a user, they are added automatically to the hidden User Information list. SharePoint retrieves user-related metadata for fields such as Created By, last modified, etc., from the User Information List.

How to Export User Information List to Excel from SharePoint Online?

Are you looking for a way to export the user information list from SharePoint Online? In this guide, we’ll show you how to do just that with easy-to-follow steps! You’ll have your user data quickly exported and ready to work with in Excel. The exported spreadsheet will include the user’s display name, email address, and last logon date, etc.

1. To get started, open the Microsoft Excel desktop application.
2. Click on the “Data” tab >> Get Data >> From Other Sources > SharePoint List (In some versions, it is under: Data >> New Query >> From other sources >> SharePoint List)
3. Enter your SharePoint Online site URL and click on OK
4. Click on “Microsoft Account”>> Click on the “Sign in” button to log in to SharePoint Online and then click on “Connect”.
5. This will open a window with all the available lists on your site. Scroll down and select the User Information List and click on “Load”.

This loads the data from the user information list to Excel and you can view and analyze the data.

How to Access User Information list in SharePoint Online?

If you are looking for accessing the user information list in SharePoint Online, follow these steps:

1. First, log in to your SharePoint Online site. Next, click on the “Site Contents” link, which can be found in the left sidebar.
2. Once you are on the Site, append this URL to the site URL in the browser to get the “All People” view: /_catalogs/users/simple.aspx E.g., https://crescent.sharepoint.com/sites/Retail/_catalogs/users/simple.aspx

   

3. This will take you to the User Information page, which contains a list of all the users in your SharePoint Online account.

PowerShell to Export User Information List in SharePoint Online

This PowerShell script exports all user information to a CSV file from the user information list of any SharePoint Online site collection.

#Load SharePoint CSOM Assemblies
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"

#Parameters
$SiteURL="https://crescent.sharepoint.com/sites/marketing"
$CSVPath = "C:\Temp\UserInfo.csv"

#Get Credentials to connect
$Cred= Get-Credential

Try {
    #Setup the context
    $Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
    $Ctx.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.Username, $Cred.Password)

    #Get the User Information List
    $List=$Ctx.Web.SiteUserInfoList
    $FieldColl = $List.Fields
    $Ctx.Load($List)
    $Ctx.Load($FieldColl)
    $Ctx.ExecuteQuery()
 
    #Get All Items from User Information List
    $ListItems = $List.GetItems([Microsoft.SharePoint.Client.CamlQuery]::CreateAllItemsQuery())
    $Ctx.Load($ListItems)
    $Ctx.ExecuteQuery()

    #Array to Hold Result - PSObjects
    $ListItemCollection = @()
  
    #Fetch each list item value to export to excel
    ForEach($Item in $ListItems)
    {
        $ExportItem = New-Object PSObject 
        ForEach($Field in $FieldColl)
        {
            $ExportItem | Add-Member -MemberType NoteProperty -name $Field.InternalName -value $Item[$Field.InternalName]   
        }  
        #Add the object with property to an Array
        $ListItemCollection += $ExportItem
    }
    #Export data to CSV File
    $ListItemCollection | Export-Csv -Path $CSVPath -NoTypeInformation -Force

    Write-host "User Information List has been Exported to CSV!'" -f Green
}
Catch {
    write-host -f Red "Error:" $_.Exception.Message
}

This script generates the below CSV file (I’ve removed some of the columns from the CSV, BTW!)

Export User Data from User Information List using SharePoint Online Management Shell:

SharePoint Online Management Shell has a cmdlet Export-SPOUserInfo to export user data from the user information list. 

#Connect to SharePoint Online
Connect-SPOService -Url https://crescent-admin.sharepoint.com

#Export User Info
Export-SPOUserInfo -LoginName salaudeen@crescent.com -site https://crescent.sharepoint.com/sites/marketing -OutputFolder "C:\Temp"

PnP PowerShell to Export User Data from User Information List

We have an equivalent PnP PowerShell cmdlet Export-PnPUserInfo to export user data from the user information list:

#Config Variables
$SiteURL = "https://crescent.sharepoint.com/sites/Retail"

#Connect to PnP Online
Connect-PnPOnline -Url $SiteURL -Interactive

Export-PnPUserInfo -LoginName Salaudeen@crescent.com -Site $SiteURL | ConvertTo-Csv | Out-File C:\Temp\Userinfo.csv

In conclusion, exporting user information from SharePoint Online can be achieved by using a few methods, such as Microsoft Excel, CSOM, or PnP PowerShell, so that you can use the data for reporting, analysis, or import into other systems. The method you choose will depend on your specific requirements and the level of technical expertise you have.

Here is another post for SharePoint On-premises: How to Export User Information List in SharePoint?