Get All Document details from all sites in a site collection Using Power shell in SharePoint Online

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

resultscreenshotOr 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

DocumentListItemsExecutionScreen

4) Now go to the folder, we will find the excel file With name Source_Document_Collection as below

DocumentListItemsexcelResultScreen

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

One thought on “Get All Document details from all sites in a site collection Using Power shell in SharePoint Online

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s