In this post, we will share CSOM based PowerShell script to get a list of all documents and metadata details from SharePoint Online or OneDrive for Business site. The script also exports file details to a CSV file.
In SharePoint Online, the document files are represented as List items under the Document Library list. We can easily get all list items with the required CAML query using the CSOM script. Using the below script, you can generate the following documents inventory reports.
- Export all documents and metadata details
- Find recently uploaded/created files
- Find recently modified files
- Find all files created by a specific user
- Find all files modified by a specific user
Export all documents and metadata details
The following script retrieves all the file documents from the given site document library and exports the document metadata details (such as file name, file path, document id, file size, file created time, last modified time, author name, and last modified user email ) to a CSV file. To use CSOM in PowerShell, we need to install Microsoft SharePoint Online Client SDK components and load the required assembly files.
#Add required references to SharePoint client assembly to use CSOM
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint.Client")
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint.Client.Runtime")
$Result = @()
#Specify SharePoint or OneDrive site admin account
$adminAccount = "[email protected]"
$adminPwd = "admin_password"
#Specify SharePoint Online Site URL or User's OneDrive Site URL
$siteURL = "https://contoso.sharepoint.com/sites/site_name"
#$siteURL = "https://contoso-my.sharepoint.com/personal/username_domainame_com"
$documentLibrary ="Documents"
#Connect and Load SharePoint Library and Root Folder
$secPwd = $(ConvertTo-SecureString $adminPwd -asplaintext -force)
$ctx = New-Object Microsoft.SharePoint.Client.ClientContext($siteUrl)
$credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($adminAccount,$secPwd)
$ctx.credentials = $credentials
$list = $ctx.Web.Lists.GetByTitle($documentLibrary)
$ctx.Load($list)
$ctx.ExecuteQuery()
$camlQuery = New-Object Microsoft.SharePoint.Client.CamlQuery
$camlQuery.ViewXml ="@<View Scope='RecursiveAll'><Query><Where><Eq><FieldRef Name='FSObjType' /><Value Type='Integer'>0</Value></Eq></Where></Query></View>";
$allItems=$list.GetItems($camlQuery)
$ctx.Load($allItems)
$ctx.ExecuteQuery()
$i = 0;
$TotoalFiles = $allItems.Count
foreach($item in $allItems)
{
$i++
Write-Progress -activity "Processing $($item["FileRef"])" -status "$i out of $TotoalFiles completed"
$Result += New-Object PSObject -property $([ordered]@{
FileName = $item.FieldValues["FileLeafRef"]
FileID = $item.FieldValues["UniqueId"]
FileType = $item.FieldValues["File_x0020_Type"]
RelativeURL = $item.FieldValues["FileRef"]
CreatedByEmail = $item.FieldValues["Author"].Email
CreatedTime = $item.FieldValues["Created"]
LastModifiedTime = $item.FieldValues["Modified"]
ModifiedByEmail = $item.FieldValues["Editor"].Email
FileSize_KB = [Math]::Round(($item.FieldValues["File_x0020_Size"]/1024), 2) #File size in KB
})
}
$Result | Export-CSV "C:\SharePoint-Files.CSV" -NoTypeInformation -Encoding UTF8
Find recently created/uploaded files
The above commands store the details in the array object “$Result“, we can generate the required report from this result array. Run the below commands to list the recently created (or uploaded) documents.
$Days = 30 #No of days - Get documents uploaded in last 30 days
$time = (Get-Date).Adddays(-($Days))
$Result | Where-Object { $_.CreatedTime -gt $time}
Find recently modified files
The below command lists the recently modified documents.
$Days = 30 #No of days - Get documents modified in last 30 days
$time = (Get-Date).Adddays(-($Days))
$Result | Where-Object { $_.LastModifiedTime -gt $time}
Get files created by a specific user
Run the following command to list files uploaded by a specific user.
$userEmail = "[email protected]"
$Result | Where-Object { $_.CreatedByEmail -like $userEmail }
Get files modified by a specific user
Run the following command to list documents modified by a specific user.
$userEmail = "[email protected]"
$Result | Where-Object { $_.ModifiedByEmail -like $userEmail }