SharePoint Online: Export Document Library Metadata to Excel

Requirement: Export SharePoint Online Document Library Metadata to a CSV file.

Export Document Library Metadata to CSV using PowerShell:

To export a document library’s metadata into Excel, You can use: “Export to Excel” button on any SharePoint Online library. However, this functionality doesn’t give you details such as: Number of Versions, Filesize, etc. Here is the PowerShell CSOM script to extract the metadata of all documents under a specific document library and export them to Excel.

#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"
#Function to get all files of a folder
Function Get-FilesFromFolder([Microsoft.SharePoint.Client.Folder]$Folder)
    Write-host -f Yellow "Processing Folder:"$Folder.ServerRelativeUrl

    #Get All Files of the Folder
    $DataCollection = @()
    ForEach ($File in $Folder.files)
        #Get Metadata associated with the File
        $ListItem = $File.ListItemAllFields

        Write-host -f Green "Extracting Metadata from:"$File.Name
        $Data = New-Object PSObject
        $Data | Add-Member NoteProperty Name($File.Name)
        $Data | Add-Member NoteProperty Status($ListItem["Status"])
        $Data | Add-Member NoteProperty CreatedBy($File.Author.Title)
        $Data | Add-Member NoteProperty Size($File.Length/1KB)
        $Data | Add-Member NoteProperty Versions($File.MajorVersion)
        $Data | Add-Member NoteProperty CreatedOn($File.TimeCreated)
        $Data | Add-Member NoteProperty ModifiedBy($File.ModifiedBy.Title) #LoginName
        $Data | Add-Member NoteProperty LastModifiedOn($File.TimeLastModified)
        $Data | Add-Member NoteProperty URL($SiteURL+$File.ServerRelativeUrl)         
        $DataCollection += $Data
    #Export the metadata to CSV
    $DataCollection | Export-Csv $ReportOutput -Append -NoTypeInformation
    #Recursively Call the function to get files of all folders and SubFolders

    #Exclude "Forms" system folder and iterate through each folder
    ForEach($SubFolder in $Folder.Folders | Where {$_.Name -ne "Forms"})
        Get-FilesFromFolder -Folder $SubFolder

Function Extract-SPODocLibraryMetaData()
        [Parameter(Mandatory=$true)] [string] $SiteURL,
        [Parameter(Mandatory=$true)] [string] $LibraryName
    Try {
        #Setup Credentials to connect
        $Cred = Get-Credential
        $Cred = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.UserName,$Cred.Password)
        #Setup the context
        $Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
        $Ctx.Credentials = $Cred

        #Get the Library and Its Root Folder

        #Delete the Output Report, if exists
        If(Test-Path $ReportOutput) {  Remove-Item $ReportOutput }

        #Call the function to get Files of the Root Folder
        Get-FilesFromFolder -Folder $Library.RootFolder

    Catch {
        write-host -f Red "Error Getting Metadata from Library!" $_.Exception.Message
#Config Parameters
$SiteURL= ""
$LibraryName="Project Docs"
$ReportOutput ="C:\Temp\DocMetadata.csv"

#Call the function to get list items from folder
Extract-SPODocLibraryMetaData -SiteURL $SiteURL -LibraryName $LibraryName 

This PowerShell script for SharePoint Online gets list of files in document library and export all documents metadata into a CSV file.

export sharepoint online document metadata to CSV

If you need to get all documents from a SharePoint Online site collection, use: SharePoint Online: Get All Documents Inventory in a Site Collection using PowerShell

Export Documents Inventory to CSV using PnP PowerShell:

Similarly, you can use PnP PowerShell to Export documents inventory from SharePoint Online.

$SiteURL = ""
$ListName= "Documents"
$ReportOutput = "C:\Temp\DocInventory.csv"

#Connect to SharePoint Online site
Connect-PnPOnline $SiteURL -Credential (Get-Credential)

#Array to store results
$Results = @()

#Get all Items from the document library with given fields
$ListItems = Get-PnPListItem -List $ListName -Fields "FileLeafRef", "SMTotalFileStreamSize", "FileDirRef","Author","Created","File_x0020_Type"

#Iterate through each item
Foreach ($Item in $ListItems) 
    #Filter Files only
    If (($Item.FileSystemObjectType) -eq "File")
        $Results += New-Object PSObject -Property ([ordered]@{
            FileName          = $Item["FileLeafRef"]
            FileType          = $Item["File_x0020_Type"]            
            RootFolder        = $Item["FileDirRef"]
            RelativeURL       = $Item["FileRef"]
            FileSize          = ($Item["SMTotalFileStreamSize"]) 
            CreatedBy         = $Item["Author"].Email
            CreatedOn         = $Item["Created"]

#Export the results
$Results | Export-Csv -Path $ReportOutput -NoTypeInformation

Salaudeen Rajack

Information Technology Professional with Two decades of SharePoint Experience.

2 thoughts on “SharePoint Online: Export Document Library Metadata to Excel

  • January 16, 2019 at 7:07 PM

    when I run under my Sharepoint Online I get the following error

    PS C:365itclient> C:UsersthiagoDocuments_thiagoSPO-Export Document Library Metadata to Excel.ps1
    Processing Folder: /Current Clients
    Extracting Metadata from: T&B – MEDIA LIST.xlsx
    Error Getting Metadata from Library! Exception calling “ExecuteQuery” with “0” argument(s): “User cannot be found.”

    • February 18, 2019 at 8:47 PM

      That’s because: User in “Created By” or “Modified By” of the Document is either deleted or disabled from Active Directory (So they become orphans in SharePoint!) To mitigate, Remove Line#20 and 21 ($File.Author, $File.ModifiedBy) and use: $Item[“Author”].Email, $Item[“Editor”].Email


Leave a Reply