In this is post we will see how to get the details of all Documents items using CSOM power shell using from all the sub sites in a site collection in SharePoint online.
This post is the fourth post in series of the Power shell Scripts for Getting the data from site collection in SharePoint Online.
Get all Sub site details in Site Collection in SharePoint online using Power shell .
Get All Lists details in a site collection Using Power shell in SharePoint Online
Get All Lists items in a site collection Using Power shell in SharePoint Online
Note: please check the above link before this post before to proceed further , since this is continuation of the first post and we are using the excel generated in the above post as an input for the script below.
1) Copy the below script to notepad and save the file with Get all Document Info from all Sites.ps1
#Add - PSSnapin Microsoft.SharePoint.PowerShell
$host.Runspace.ThreadOptions = "ReuseThread"
#Definition of the function that allows to create a new view in a SharePoint Online list
function Get-ListItemInfo-ByList
{
param ($sCSOMPath,$sSiteID,$sSiteName,$sSiteUrl,$ListID,$ListTitle,$ListViewUrl,$BaseTemplate,$BaseType,$ItemCount,$sUserName,$sPassword,$sWeburl,$FileName)
try
{
#Adding the Client OM Assemblies
$sCSOMRuntimePath=$sCSOMPath + "\Microsoft.SharePoint.Client.Runtime.dll"
$sCSOMPathdll=$sCSOMPath + "\Microsoft.SharePoint.Client.dll"
Add-Type -Path $sCSOMPathdll
Add-Type -Path $sCSOMRuntimePath
#SPO Client Object Model Context
$spoCtx = New-Object Microsoft.SharePoint.Client.ClientContext($sSiteUrl)
$spoCredentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($sUserName, $sPassword)
#$spoCredentials = New-Object System.Net.NetworkCredential($sUserName, $sPassword)
$spoCtx.Credentials = $spoCredentials
$web = $spoCtx.Web
$List = $web.Lists.GetByTitle($ListTitle);
$caml="<View Scope='RecursiveAll'><RowLimit>5000</RowLimit><Query><Where><Eq><FieldRef Name='FSObjType' /><Value Type='Lookup'>0</Value></Eq></Where></Query></View>"
$cquery = New-Object Microsoft.SharePoint.Client.CamlQuery
$cquery.ViewXml=$caml
DO
{
$listItems = $List.GetItems($cquery)
$spoCtx.Load($List)
$spoCtx.Load($listItems)
$spoCtx.ExecuteQuery()
# items.AddRange(listItemCollection);
$cquery.ListItemCollectionPosition = $listItems.ListItemCollectionPosition;
$AllListItems = @()
foreach ($listitem in $listItems)
{
$Title =""
$FileType =""
$FileRef =""
$FileSize ="0 KB"
$CreatedBy = "System Account"
$ModifiedBy = "System Account"
If([string]::IsNullOrEmpty($listitem.FieldValues["FileLeafRef"]))
{
$Title = "No Title"
}
else
{
$Title =$listitem.FieldValues["FileLeafRef"]
}
If([string]::IsNullOrEmpty($listitem.FieldValues["File_x0020_Type"]))
{
$FileType = "None"
}
else
{
$FileType =$listitem.FieldValues["File_x0020_Type"]
}
If([string]::IsNullOrEmpty($listitem.FieldValues["FileRef"]))
{
$FileRef = "None"
}
else
{
$FileRef =($sWeburl + $listitem.FieldValues["FileRef"])
}
If([string]::IsNullOrEmpty($listitem.FieldValues["File_x0020_Size"]))
{
$FileSize = "0 KB"
}
else
{
$value = [math]::Round(($listitem.FieldValues["File_x0020_Size"]/1024),2)
$decimal = "{0:N2}" -f $value
$FileSize = ($decimal + " KB")
#Write-Host " Field Size2 = $($FileSize)"
}
$AllListItems += New-Object -TypeName PSObject -Property @{
SiteID = $sSiteID
SiteName = $sSiteName
SiteUrl =$sSiteUrl
ListID = $ListID
ListTitle = $ListTitle
ListViewUrl =$ListViewUrl
BaseTemplate= $BaseTemplate
BaseType=$BaseType
DocumentID=$listitem.ID
FileName=$Title
FileType= $FileType
FileSize= $FileSize
FileRef= $FileRef
CreatedBy = $listitem.FieldValues["Created_x0020_By"]
ModifiedBy = $listitem.FieldValues["Modified_x0020_By"]
CreatedDate = $listitem.FieldValues["Created_x0020_Date"]
ModifiedDate = $listitem.FieldValues["Last_x0020_Modified"]
} | Select SiteID,SiteName,SiteUrl,ListID,ListTitle,ListViewUrl,BaseTemplate,BaseType,DocumentID,FileName,FileType,FileSize,FileRef,CreatedBy,ModifiedBy,CreatedDate,ModifiedDate
}
$AllListItems| Export-CSV ($sCSOMPath+"\"+$FileName+".csv") -NoTypeInformation -Append #-Encoding UTF8
} While ($cquery.ListItemCollectionPosition -ne $null)
$spoCtx.Dispose()
}
catch [System.Exception]
{
$FailedSite = @()
$FailedSite += New-Object -TypeName PSObject -Property @{
SiteID = $sSiteID
SiteName = $sSiteName
SiteUrl =$sSiteUrl
ListID = $ListID
ListTitle = $ListTitle
ListViewUrl =$ListViewUrl
BaseTemplate= $BaseTemplate
BaseType=$BaseType
ItemCount=$ItemCount
Exception=$_.Exception.Message
} | Select SiteID,SiteName,SiteUrl,ListID,ListTitle,ListViewUrl,BaseTemplate,BaseType,ItemCount,Exception
$FailedSite| Export-CSV ($sCSOMPath+"\"+$FileName+"_Failed.csv") -NoTypeInformation -Append #-Encoding UTF8
Write-Host -ForegroundColor Red $_.Exception.ToString()
#Read-Host -Prompt "Operation failed..! Press any key to close this and re run the script -Sub"
}
}
function Get-ListItemInfo
{
param ($sCSOMPath,$sSiteUrl,$sUserName,$sPassword,$sWeburl,$FileName,$ImportFile)
try
{
Write-Host "Getting All Documents from All Document Libraries in a Site Collection"
Write-Host "-----------------------------------------------------------"
#$ListBaseTemplate =@("110","111","112","113","114","116","121","122","123")
$DocumentList =@("DocumentLibrary")
$ListInfo = import-csv ($sCSOMPath+"\"+$ImportFile+".csv") | Where {$DocumentList -contains $_.BaseType -And $_.ItemCount -ne 0}# -And $_.ListViewUrl -notcontains $DefaultLists }
write-host "List Info Cont -$($ListInfo.Count)"
for ($i = 0; $i -lt $ListInfo.Count; $i++)
{
$List=$ListInfo[$i]
Write-Host "$($i). Library Title : $($List.ListTitle) :: Site: $($List.SiteUrl)"
Get-ListItemInfo-ByList -sCSOMPath $sCSOMPath -sSiteID $List.SiteID -sSiteName $List.SiteName -sSiteUrl $List.SiteUrl -ListID $List.ListID -ListTitle $List.ListTitle -ListViewUrl $List.ListViewUrl -BaseTemplate $List.BaseTemplate -BaseType $List.BaseType -ItemCount $List.ItemCount -sUserName $sUserName -sPassword $sPassword -sWeburl $sWeburl -FileName $FileName
}
Read-Host -Prompt "file created Successfull..! in the following path $sCSOMPath, Press any key to close this window"
}
catch [System.Exception]
{
Write-Host -ForegroundColor Red $_.Exception.ToString()
Read-Host -Prompt "Operation failed..! Press any key to close this and re run the script -main"
}
}
$FileName = "Source_Document_Collection" #Read-Host -Prompt "Enter File Name to write (eg: XXXXX)"
$ImportFileName= "Source_ListCollection" #Read-Host -Prompt "Enter existing File Name to import file extension is csv (eg: YYYYY)"
$sWeburl = "https://tarun.sharepoint.com/" #Read-Host -Prompt "Enter Root Site URL (eg: http://Server:port/)"
$sSiteUrl = "https://tarun.sharepoint.com/sites/sharepointmates/" #Read-Host -Prompt "Enter Site Collection URL (eg: http://Server:port/Sites/Dev)"
$sUserName = "admin@tarun.onmicrosoft.com" #Read-Host -Prompt "Enter User Name"
$sPassword = ConvertTo-SecureString "xxx" -AsPlainText -Force #Read-Host -Prompt "Enter your password" -AsSecureString
$scriptpath = $MyInvocation.MyCommand.Path
$dir = Split-Path $scriptpath
Get-ListItemInfo -sCSOMPath $dir -sSiteUrl $sSiteUrl -sUserName $sUserName -sPassword $sPassword -sWeburl $sWeburl -FileName $FileName -ImportFile $ImportFileName
Note:
- Please update the $sWeburl,$sUserName,$sPassword and $sSiteUrl variables with your SharePoint online environment.
- In ImportFileName= “Source_ListCollection”, Source_ListCollectionis the name of the excel which contains the details of the all list details that was generated in the second post . If you want to give the your custom file as an input to the above script please create the excel with the name Source_ListCollection and the excel sheet name Source_ListCollection structure as below
Or else you can use the second post to generate the excel
2) Now Right click on the Get all Document Info from all Sites.ps1 Power shell script file and select Run with Power Shell
3) After Successful execution of the scripts we will get the below message
4) Now go to the folder, we will find the excel file With name Source_Document_Collection as below
On Premises:
For On premises we need to slightly change $spoCredentials and $sUserName variables in the above script.
$spoCredentials = New-Object System.Net.NetworkCredential($sUserName, $sPassword)
We need to give the user name along with domain name as below
$sUserName = "2013dev\spadmin"
Please change the on premises site collection url , web url and password as well.
In the Next post we will how to get the Checked out documents from all the sub sites in a site collection in SharePoint Online
SharepointMates 
[…] Get All Document details from all sites in a site collection Using Power shell in SharePoint Online […]
LikeLike