SharePoint Online: Get all checked-out files using Powershell
The article below is using SharePoint Module for managing lists, items and files as an extension to SharePoint Online Management Shell. Install the module to proceed.
Why should you?
The files with no checked in version are visible only to the user who checked them out. Other users may report "missing files".
The checked out files may not synchronize.
A file that was forgotten to be checked in for a long time may lead to workflow issues.
A user that checked out the files is no longer part of the project.
For business reasons and reporting purposes.
Prerequisites
Import the SPOMod module:
Import-Module C:\SpoMod2.psm1 -Verbose
Connect to the site:
Connect-SPOCSOM -Username admin@tenant.onmicrosoft.com -Url https://tenant.sharepoint.com/sites/zxy0
Replace admin@tenant.onmicrosoft.com with your admin account and https://tenant.sharepoint.com/sites/zxy0 with the actual URL on your tenant
For One List
After connecting using Connect-SPOCSOM cmdlet, you can verify the available lists' titles using Get-SPOList:
Get-SPOList
1. Use the Get-SPOListItems to retrieve the items and their server-relative URLs:
$items=(Get-SPOListItems Documents -IncludeAllProperties $true -Recursive | where {$_.FSObjType -eq 0}).FileRef
Documents is the name of the library. Remember to change it. If your library name contains a space, use "", e.g. "Project Docs"
-Recursive parameter sets the CamlQuery to search through all folders and sub-folders. In vernacular, the items returned will be ALL the items, not only from the first level.
FSObjType -eq 0 ensures that no folders are returned. It selects only files.
FileRef is a property that contains server-relative URL. You can check all the available properties by entering Get-SPOListItems NameOfTheLibrary
2. Check if the items you got are the items you wanted (optional step):
$items
3. Declare an array. We will need it to retrieve the files based on the server-relative URLs from the items.
$arr=@()
4. Get the files.
for($i=0;$i -lt $items.Count; $i++){$arr+=(Get-SPOFileByServerRelativeUrl -ServerRelativeUrl $items[$i])}
5. Verify if there are any checked-out files:
foreach($ar in $arr){ if($ar.CheckedOutByUser.LoginName -ne $null) {$ar.ServerRelativeUrl}}
6. Verify if there are any checked-out files for a specific user:
foreach($ar in $arr){ if($ar.CheckedOutByUser.LoginName -eq "i:0#.f|membership|uss1@tenant.onmicrosoft.com"{$ar.ServerRelativeUrl}}
7. Export the results to a CSV file:
foreach($ar in $arr){ if($ar.CheckedOutByUser.LoginName -ne $null) {$ar | Export-Csv -Append -Path C:\QWCE.csv}}
For All Lists
1. Get all the list titles. You can include all the lists or choose e.g. only document libraries.
$listOfLists=(Get-SPOList).Title
only Document Libraries:
$lists=(Get-SPOList -IncludeAllProperties | where {$_.BaseTemplate -eq 101}).Title
or
$lists=(Get-SPOList -IncludeAllProperties | where {$_.BaseType -eq "DocumentLibrary"}).Title
Difference:
2. Declare an empty array into which items from the libraries will be pulled:
$emptyitems=@()
3. Retrieve the items and their server-relative URLs:
foreach($list in $listOfLists){$emptyitems+=((Get-SPOListItems $list -IncludeAllProperties $true -Recursive | where {$_.FSObjType -eq 0}).FileRef)}
4. Optionally you can check if the items retrieved are correct:
$emptyitems
5. Declare an empty array for files:
$files=@()
6. Get the files based on their server-relative URLs:
for($i=0;$i -lt $emptyitems.Count; $i++){$files+=(Get-SPOFileByServerRelativeUrl -ServerRelativeUrl $emptyitems[$i])}
7. Display the paths for all checked out files:
foreach($file in $files){ if($file.CheckedOutByUser.LoginName -ne $null) {$file.ServerRelativeUrl}}
8. Display the paths only for files checked out by a specific user:
foreach($file in $files){ if($file.CheckedOutByUser.LoginName -eq "i:0#.f|membership|uss1@tenant.onmicrosoft.com") {$file.ServerRelativeUrl}}
9. Export files checked out by a specific user to a CSV file:
foreach($file in $files){ if($file.CheckedOutByUser.LoginName -eq "i:0#.f|membership|uss1@tenant.onmicrosoft.com") {$file | Export-Csv c:\qwecheckout.csv -Append}}
Check them in
Once you have an array of files called $arr or $files, you can use their properties to check them in:
foreach($file in $files){ if($file.CheckedOutByUser.LoginName -ne $null) {Set-SPOFileCheckin -ServerRelativeUrl $file.ServerRelativeUrl -CheckInType MajorCheckIn -CheckinComment "Some comment. Checked in automatically"}}
Summary
To get all checked out files from a single library use the following code:
Connect-SPOCSOM -Username admin@tenant.onmicrosoft.com -Url https://tenant.sharepoint.com/sites/zxy0 $items=(Get-SPOListItems LIbraryName -IncludeAllProperties $true -Recursive | where {$_.FSObjType -eq 0}).FileRef
$arr=@()
for($i=0;$i -lt $items.Count; $i++){$arr+=(Get-SPOFileByServerRelativeUrl -ServerRelativeUrl $items[$i])}
foreach($ar in $arr){ if($ar.CheckedOutByUser.LoginName -ne $null) {$ar.ServerRelativeUrl}}
To get files checked out by a single user from a** single library** use the following code:
Connect-SPOCSOM -Username admin@tenant.onmicrosoft.com -Url https://tenant.sharepoint.com/sites/zxy0 $items=(Get-SPOListItems LibraryName -IncludeAllProperties $true -Recursive | where {$_.FSObjType -eq 0}).FileRef
$arr=@()
for($i=0;$i -lt $items.Count; $i++){$arr+=(Get-SPOFileByServerRelativeUrl -ServerRelativeUrl $items[$i])}
foreach($ar in $arr){ if($ar.CheckedOutByUser.LoginName -eq "i:0#.f|membership|username@tenant.onmicrosoft.com"{$ar.ServerRelativeUrl}}
To get all files checked out from all libraries use the following code:
Connect-SPOCSOM -Username admin@tenant.onmicrosoft.com -Url https://tenant.sharepoint.com/sites/zxy0
$lists=(Get-SPOList -IncludeAllProperties | where {$_.BaseTemplate -eq 101}).Title
$emptyitems=@()
foreach($list in $listOfLists){$emptyitems+=((Get-SPOListItems $list -IncludeAllProperties $true -Recursive | where {$_.FSObjType -eq 0}).FileRef)}
$files=@()
for($i=0;$i -lt $emptyitems.Count; $i++){$files+=(Get-SPOFileByServerRelativeUrl -ServerRelativeUrl $emptyitems[$i])}
foreach($file in $files){ if($file.CheckedOutByUser.LoginName -ne $null) {$file.ServerRelativeUrl}}
To get files checked out by a single user from all libraries use the following code:
Connect-SPOCSOM -Username admin@tenant.onmicrosoft.com -Url https://tenant.sharepoint.com/sites/zxy0
$lists=(Get-SPOList -IncludeAllProperties | where {$_.BaseTemplate -eq 101}).Title
$emptyitems=@()
foreach($list in $listOfLists){$emptyitems+=((Get-SPOListItems $list -IncludeAllProperties $true -Recursive | where {$_.FSObjType -eq 0}).FileRef)}
$files=@()
for($i=0;$i -lt $emptyitems.Count; $i++){$files+=(Get-SPOFileByServerRelativeUrl -ServerRelativeUrl $emptyitems[$i])}
foreach($file in $files){ if($file.CheckedOutByUser.LoginName -eq "i:0#.f|membership|user@tenant.onmicrosoft.com") {$file.ServerRelativeUrl}}