SharePoint Online: Get Document Library Inventory (Folder-SubFolder-File Structure) using PowerShell
Requirement: Get the file-folder structure of a SharePoint document library and export it to CSV.
How to Get a List of All Files and Folders in a SharePoint Online Document Library?
If you need to get a list of all the files and folders in a SharePoint Online document library, there are a few different ways you can do it. Perhaps you need to clean up your library, or are just curious about what’s in there. In this article, we’ll show you how to get a list of all the files and folders in a document library and how to use PowerShell to get the information you need. Let’s get started!
In SharePoint Online, it’s easy to generate a document inventory report to get an overview of all the documents in a library. You can use the “Export to Excel” feature (not the “Export to CSV” – that exports only files and folders in the view) in the SharePoint Online document library to get an inventory of all files and folders from the library. Just navigate to your document library and click on the “Export to Excel” button in the command bar to export all files and folders list, along with their metadata, in a SharePoint Online document library.
This downloads a .iqy file, and opening the file in Excel provides us with the report:
This can be useful for troubleshooting or getting a quick overview of the contents of a library.
SharePoint Online: PowerShell to get the Document Library Inventory
Are you looking for a way to get an inventory of all the documents stored in your SharePoint Online Document Library? Well, Here is the handy PowerShell that can help! In this blog post, we will show you how to use PowerShell to generate a report that lists all the documents and their properties, so you can keep track of what documents are stored in the library.
Let’s export all folders, sub-folders, and files structure from a SharePoint Online document library to a CSV file:
#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"
$ListName = "Documents"
$CSVPath = "C:\Temp\DocumentLibraryRpt.csv"
$BatchSize = 500
#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 Document Library
$List =$Ctx.Web.Lists.GetByTitle($ListName)
#Define CAML Query to Get List Items in batches
$Query = New-Object Microsoft.SharePoint.Client.CamlQuery
$Query.ViewXml ="
<View Scope='RecursiveAll'>
<Query>
<OrderBy><FieldRef Name='ID' Ascending='TRUE'/></OrderBy>
</Query>
<RowLimit Paged='TRUE'>$BatchSize</RowLimit>
</View>"
$DataCollection = @()
Do
{
#get List items
$ListItems = $List.GetItems($Query)
$Ctx.Load($ListItems)
$Ctx.ExecuteQuery()
#Iterate through each item in the document library
ForEach($ListItem in $ListItems)
{
#Collect data
$Data = New-Object PSObject -Property ([Ordered] @{
Name = $ListItem.FieldValues.FileLeafRef
RelativeURL = $ListItem.FieldValues.FileRef
Type = $ListItem.FileSystemObjectType
CreatedBy = $ListItem.FieldValues.Author.Email
CreatedOn = $ListItem.FieldValues.Created
ModifiedBy = $ListItem.FieldValues.Editor.Email
ModifiedOn = $ListItem.FieldValues.Modified
FileSize = $ListItem.FieldValues.File_x0020_Size
})
$DataCollection += $Data
}
$Query.ListItemCollectionPosition = $ListItems.ListItemCollectionPosition
}While($Query.ListItemCollectionPosition -ne $null)
#Export Documents data to CSV
$DataCollection | Export-Csv -Path $CSVPath -Force -NoTypeInformation
Write-host -f Green "Document Library Inventory Exported to CSV!"
}
Catch {
write-host -f Red "Error:" $_.Exception.Message
}
Get File-Folder Structure in a Document Library using PnP PowerShell
Similarly, If you want to export all file-folder structures of a document library to a CSV file, use this PnP PowerShell script:
#Parameters
$SiteURL = "https://crescent.sharepoint.com/sites/RI/"
$ListName= "Non-Transaction"
$ReportOutput = "C:\Temp\ListInventory.csv"
#Connect to SharePoint Online site
Connect-PnPOnline $SiteURL -Interactive
#Array to store results
$Results = @()
#Get all Items from the document library
$List = Get-PnPList -Identity $ListName
$ListItems = Get-PnPListItem -List $ListName -PageSize 500
Write-host "Total Number of Items in the List:"$List.ItemCount
$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"]
CreatedBy = $Item["Author"].Email
CreatedOn = $Item["Created"]
})
$ItemCounter++
Write-Progress -PercentComplete ($ItemCounter / ($List.ItemCount) * 100) -Activity "Processing Items $ItemCounter of $($List.ItemCount)" -Status "Getting Metadata from Item '$($Item['FileLeafRef'])"
}
#Export the results to CSV
$Results | Export-Csv -Path $ReportOutput -NoTypeInformation
Write-host "Document Library Inventory Exported to CSV Successfully!"
And the output report of the script:
In summary, retrieving all documents from a SharePoint Online document library can be easily achieved by using the PowerShell script, as shown above. This script provides a comprehensive overview of the content stored in SharePoint document libraries, including information about file type, owner, and last modified date.
Just a quick “Thank you” for the lovely scripts 🙂
Hi Salaudeen,
Thanks for the wonderful scripts. I would like to know, if we can get the actual last modified dates of the root folders, as in many folders the subfolder and files have latest dates, whereas the root folder is still showing the older dates. ANy help would be greatly appreciated.
Thank you very much for sharing your knowledge. I am new to PS. I’ve tried to run these scripts in PowerShell (v7) via PnP.PowerShell and SharePoint Online Management Shell and I seem to get 95% of the way and then either get an empty txt file or an error. Are you running this all in the PS v7 context and if so what modules are required? Do you know of any recent module or .NET changes that impact the dependencies? I just need to export the full listing of folders and subfolders for an entire SP site. Power Automate take f o r e v e r…. to run and I don’t get all of the results and there are caps (TopCount) thresholds that rule that out as a solution. Seems like PowerShell or the SharePoint Online Management Shell would be the way to go if I could get a little help. Many Thanks again for sharing your knowledge and thanks in advance for any help you can offer.
How to export Item Counts for a entire site in SharePoint Online using powershell scripts
Hi,
what would be the exact term for the “Get-PnPListItem”? I have a big library with 4M files where I need to retreieve just the folder structure (of a specific subfolder). Even when I use “FolderServerRelativeUrl” and a PageSize of 5000, which definitly fits the folder count it throws me an error saying that it exceeds the max threshold…
Can we have any PowerShell script that we used for get user permission report for SharePoint site
Use this script: SharePoint Online: Generate User Permissions Report using PnP PowerShell
Can I just get the File name instead of File name with extension? (I need to remove extension!)
There are no properties to get just the file name. However, You can use: $Item.FieldValues.FileLeafRef.Replace(“.$($Item.FieldValues.File_x0020_Type)”,””)
How to get the file and folder structure from an entire site instead of changing document library name one by one?
Sure, Use this script: SharePoint Online: Site Documents Inventory Report using PowerShell
ok i didn’t realize you have to change the list name for each library and run the script again. Awesome! I got what I needed, thanks again for the excellently written script!
ok i got around the error messages by replacing the first two lines with:
[System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SharePoint.Client”)
[System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SharePoint.Client.Runtime”)
but for some reason, its only listing one file in the CSV 🙁 so close but I’m still missing something
thanks for this awesome script! saves me a lot of time (I hope) Do you happen to know what i’m doing wrong to get this error?
PS C:WINDOWSsystem32> #Load SharePoint CSOM Assemblies
PS C:WINDOWSsystem32> Add-Type -Path “C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\ISAPI\Microsoft.SharePoint.Client.dll”
Add-Type : Cannot bind parameter ‘Path’ to the target. Exception setting “Path”: “Cannot find path ‘C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll’ because it does
not exist.”
At line:1 char:16
+ … -Type -Path “C:Program FilesCommon FilesMicrosoft SharedWeb Serve …
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : WriteError: (:) [Add-Type], ParameterBindingException
+ FullyQualifiedErrorId : ParameterBindingFailed,Microsoft.PowerShell.Commands.AddTypeCommand
What if you only want the folder structure, no file names
Use the CAML Filter:1