SharePoint Online: PowerShell to List All Files Inventory in a Document Library

Requirement: List All Documents in a SharePoint Online Document Library using PowerShell.

SharePoint Online PowerShell to get All Files from a Document Library

SharePoint Online: PowerShell to Get List of Files in Document Library

There may be times when you need to get a list of all the documents in a SharePoint Online document library. This can be helpful if you need to create a report of all the files in a library, just want an overview of the amount of data stored in your library, or if you need to move or delete all/some files in the library. In this post, we will be going over how to retrieve a list of all documents in a SharePoint Online document library. You can then export your results into a CSV format so that they are easier to work on as the report contains data points such as file extension, date modified, author, etc.

Here is the SharePoint Online PowerShell to list all documents:

#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-AllFilesFromFolder([Microsoft.SharePoint.Client.Folder]$Folder)
{
    #Get All Files of the Folder
    $Ctx =  $Folder.Context
    $Ctx.load($Folder.files)
    $Ctx.ExecuteQuery()
 
    #Get all files in Folder
    ForEach ($File in $Folder.files)
    {
        #Get the File Name or do something
        Write-host -f Green $File.Name
    }
         
    #Recursively Call the function to get files of all folders
    $Ctx.load($Folder.Folders)
    $Ctx.ExecuteQuery()
 
    #Exclude "Forms" system folder and iterate through each folder
    ForEach($SubFolder in $Folder.Folders | Where {$_.Name -ne "Forms"})
    {
        Get-AllFilesFromFolder -Folder $SubFolder
    }
}
 
#powershell list all documents in sharepoint online library
Function Get-SPODocumentLibraryFiles()
{
    param
    (
        [Parameter(Mandatory=$true)] [string] $SiteURL,
        [Parameter(Mandatory=$true)] [string] $LibraryName,
        [Parameter(Mandatory=$true)] [System.Management.Automation.PSCredential] $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 Library and Its Root Folder
        $Library=$Ctx.web.Lists.GetByTitle($LibraryName)
        $Ctx.Load($Library)
        $Ctx.Load($Library.RootFolder)
        $Ctx.ExecuteQuery()
 
        #Call the function to get Files of the Root Folder
        Get-AllFilesFromFolder -Folder $Library.RootFolder 
     }
    Catch {
        write-host -f Red "Error:" $_.Exception.Message
    }
}
#Config Parameters
$SiteURL= "https://crescent.sharepoint.com/Sites/marketing"
$LibraryName="Documents"

#Get Credentials to connect
$Cred = Get-Credential
 
#Call the function to Get All Files from a document library
Get-SPODocumentLibraryFiles -SiteURL $SiteURL -LibraryName $LibraryName -Credential $Cred

This PowerShell gets a list of files in a SharePoint Online document library. While the above script works fine for smaller libraries with < 5000 items in it, It gives the “The attempted operation is prohibited because it exceeds the list view threshold.” error on larger libraries. So here is the PowerShell script to batch process list items to mitigate the threshold exceeded issue.

PowerShell to List All Documents in SharePoint Online

Here is the PowerShell for SharePoint Online to get a list of files in the document library:

#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"
     
#Config Parameters
$SiteURL= "https://crescent.sharepoint.com/sites/marketing"
$LibraryName = "Shared Documents"
$CSVPath = "C:\Temp\DocumentsInventory.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 Web and List
    $Web = $Ctx.Web
    $Ctx.Load($Web)
    $List = $Web.Lists.GetByTitle($LibraryName)
    $Ctx.Load($List)
    $Ctx.ExecuteQuery()

    #Prepare the query
    $Query = New-Object Microsoft.SharePoint.Client.CamlQuery
    $Query.ViewXml = "@
    <View Scope='RecursiveAll'>
        <Query>
            <OrderBy><FieldRef Name='ID' Ascending='TRUE'/></OrderBy>
        </Query>
        <RowLimit Paged='TRUE'>2000</RowLimit>
    </View>"
 
    $Counter=1
    $DataCollection = @()
    #Batch Process items
    Do {
        #powershell sharepoint online list all documents
        $ListItems = $List.GetItems($Query) 
        $Ctx.Load($ListItems)
        $Ctx.ExecuteQuery()
        $Query.ListItemCollectionPosition = $ListItems.ListItemCollectionPosition

        #Iterate through each document in the library
        ForEach($ListItem in $ListItems| Where {$_.FileSystemObjectType -eq "File"})
        {
            #Display a Progress bar
            Write-Progress -Activity "Scanning Files in the Library" -Status "Collection file's Inventory '$($Item.FieldValues.FileRef)' ($Counter of $($List.ItemCount))" -PercentComplete (($Counter / $List.ItemCount) * 100)

            #Collect data        
            $DataCollection += New-Object PSObject -Property ([Ordered] @{
                FileName  = $ListItem.FieldValues["FileLeafRef"]
                RelativeURL = $ListItem.FieldValues["FileRef"]
                CreatedBy = $ListItem.FieldValues["Created_x0020_By"]
                CreatedOn = $ListItem.FieldValues["Created"]
                ModifiedBy = $ListItem.FieldValues["Modified_x0020_By"]
                ModifiedOn = $ListItem.FieldValues["Modified"]
                FileSize = $ListItem.FieldValues["File_x0020_Size"]
            })
            $Counter++
        }
    }While($Query.ListItemCollectionPosition -ne $Null)

    #Export Documents data to CSV
    $DataCollection | Export-Csv -Path $CSVPath -Force -NoTypeInformation
    Write-host -f Green "Documents Data Exported to CSV!"
}
Catch {
    write-host -f Red "Error:" $_.Exception.Message
}

This PowerShell lists all files and exports the data to a CSV report!

PnP PowerShell for SharePoint Online to List All Documents

This PnP PowerShell script gets you all documents in a given document library with each file’s file name and server relative path.

#Set Variables
$SiteURL= "https://crescent.sharepoint.com/sites/Marketing"
$ListName="Documents"
 
#Connect to PnP Online
Connect-PnPOnline -Url $SiteURL -Credentials (Get-Credential)

#Get All Files from the document library - In batches of 500
$ListItems = Get-PnPListItem -List $ListName -PageSize 500 | Where {$_["FileLeafRef"] -like "*.*"}
 
#Loop through all documents
[email protected]()
ForEach($Item in $ListItems)
{
    #Collect Documents Data
    $DocumentsData += New-Object PSObject -Property @{
    FileName = $Item.FieldValues['FileLeafRef']
    FileURL = $Item.FieldValues['FileRef']
    }
}
#sharepoint online get all files in document library powershell
$DocumentsData

Get All Documents Inventory from a SharePoint Online Document Library and Export to a CSV

Let’s add some progress bar to make it interactive and get all documents inventory list from a SharePoint Online document library and export it to a CSV file:

#Parameters
$SiteURL = "https://crescent.sharepoint.com/sites/mexico"
$ListName= "Documents"
$ReportOutput = "C:\Temp\mex-DocInventory.csv"
$Pagesize = 500
  
#Connect to SharePoint Online site
Connect-PnPOnline $SiteURL -Interactive

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

#Array to store results
$Results = @()
  
#Get all Documents from the document library
$List  = Get-PnPList -Identity $ListName
$global:counter = 0;
$ListItems = Get-PnPListItem -List $ListName -PageSize $Pagesize -Fields Author, Editor, Created, File_x0020_Type -ScriptBlock `
        { Param($items) $global:counter += $items.Count; Write-Progress -PercentComplete ($global:Counter / ($List.ItemCount) * 100) -Activity `
             "Getting Documents from Library '$($List.Title)'" -Status "Getting Documents data $global:Counter of $($List.ItemCount)";} | Where {$_.FileSystemObjectType -eq "File"}
 
$ItemCounter = 0
#Iterate through each item
Foreach ($Item in $ListItems)
{
        $Results += New-Object PSObject -Property ([ordered]@{
            Name              = $Item["FileLeafRef"]
            Type              = $Item.FileSystemObjectType
            FileType          = $Item["File_x0020_Type"]
            RelativeURL       = $Item["FileRef"]
            CreatedByEmail    = $Item["Author"].Email
            CreatedOn         = $Item["Created"]
            Modified         = $Item["Modified"]
            ModifiedByEmail    = $Item["Editor"].Email
        })
    $ItemCounter++
    Write-Progress -PercentComplete ($ItemCounter / ($List.ItemCount) * 100) -Activity "Exporting data from Documents $ItemCounter of $($List.ItemCount)" -Status "Exporting Data from Document '$($Item['FileLeafRef'])"         
}
 
#Export the results to CSV
$Results | Export-Csv -Path $ReportOutput -NoTypeInformation
  
Write-host "Document Library Inventory Exported to CSV Successfully!"

To get the inventory of the documents from all document libraries in a site collection, use: SharePoint Online: Site Documents Inventory (Library, Folder, Sub-Folder, and Files) Report using PowerShell

Salaudeen Rajack

Salaudeen Rajack is a SharePoint Architect with Two decades of SharePoint Experience. He loves sharing his knowledge and experiences with the SharePoint community, through his real-world articles!

11 thoughts on “SharePoint Online: PowerShell to List All Files Inventory in a Document Library

  • I’m getting the following error:
    Get-PnPListItem : Attempted to divide by zero.
    At line:1 char:14
    + … ListItems = Get-PnPListItem -List $ListName -PageSize $Pagesize -Fiel …
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : WriteError: (:) [Get-PnPListItem], RuntimeException
    + FullyQualifiedErrorId : EXCEPTION,PnP.PowerShell.Commands.Lists.GetListItem

    Reply
  • I want to get all files created (and in some cases, modified) in the past 1 Month! How do I do that?

    Reply
    • Let’s take the last example in this post:

      #Get the past 1 Month
      $Timestamp = (Get-Date).AddMonths(-1)
      
      #Filter Files created in the past 1 Month
      $Results | Where-Object {$_.CreatedOn -gt $Timestamp}
      
      #Similarly, to get Files mocified in the past 1 Month, use:
      $Results | Where-Object {$_.Modified -gt $Timestamp}
      
      Reply
  • ‘Get All Documents Inventory from a SharePoint Online Document Library and Export to a CSV’

    Worked like a charm!
    Thanks, you saved me a lot of time.

    Reply
  • I always get this error (it is a very large library ~200,000 items)
    Get-PnPListItem : The decryption operation failed, see inner exception.
    At C:\Users[removed]
    … ListItems = Get-PnPListItem -List $ListName -PageSize $Pagesize -Fiel …
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    CategoryInfo : WriteError: (:) [Get-PnPListItem], IOException
    FullyQualifiedErrorId : EXCEPTION,PnP.PowerShell.Commands.Lists.GetListItem

    Reply
  • I get the following two warnings when running the final script and wonder if you could please explain why and how to resolve these two messages for use when Web is removed?

    WARNING: Consider using -Interactive instead, which provides better functionality. See the documentation at https://pnp.github.io/powershell/cmdlets/connect-pnponline.html#interactive-login-for-multi-factor-authentication

    WARNING: Parameter ‘Web’ is obsolete. The -Web parameter will be removed in a future release. Use Connect-PnPOnline -Url [subweburl] instead to connect to a subweb.

    Reply
  • Error: Exception calling “ExecuteQuery” with “0” argument(s): “The sign-in name or password does not match one in the Microsoft account system.”

    Reply
  • Error: Exception calling “ExecuteQuery” with “0” argument(s): “The remote server returned an error: (502) Bad Gateway.”

    Reply

Leave a Reply