0

I have poured over a ton of posts and code and none seem to work with checked out files and really large lists. I am newish to sharepoint, especially online. I moved a doc repo up there for testing and no search results because everything is checked out. My lists have 780k files and 190k files, so well over the 5k crippling limitations. On the sharepoint side, the views work but no search as I am using the document center. Anyways, already loaded 2 different sub sites with meta data. I have required fields. I turned off versioning and went to classic view on 1 site, which is a clone of the other site but with tiff files bundled into PDFs.

I am trying to use some powershell (or c# via command line app) to check in the documents. The program works fine using CAML and RowLimit and paging the results, but I can't get just the checked out files because of the stuipid 5k limits. I can get the whole list and iterate through fine, but if the app hangs, which it hasn't, I have to start the whole process over. Would be great to just have the still checked out files.

I will post all the code, because I found it all over the Internets anyway.

function Get-SPOFolderFiles
{
param (
    [Parameter(Mandatory=$true,Position=1)]
    [string]$Username,
    [Parameter(Mandatory=$true,Position=2)]
    [string]$Url,
    [Parameter(Mandatory=$true,Position=3)]
    $password,
    [Parameter(Mandatory=$true,Position=4)]
    [string]$ListTitle
    )


  $ctx=New-Object Microsoft.SharePoint.Client.ClientContext($Url)
  $ctx.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Username, $password)
  $ctx.Load($ctx.Web)
  $ctx.ExecuteQuery()

  Write-Host "Logged into Sharepoint"
  $ll=$ctx.Web.Lists.GetByTitle( $ListTitle)
  $ctx.Load($ll)
  $ctx.ExecuteQuery()

  ## Page Position
  $page = $null
  ## All Items

#$spqQuery.ViewXml="<View Scope='FilesOnly'><RowLimit>1000</RowLimit>     </View>"  
      $qCommand = @"
<View Scope="RecursiveAll">
 <Query>

  <OrderBy>
  <FieldRef Name='ID' Ascending='TRUE'/>
  </OrderBy>
 </Query>
 <RowLimit Paged="TRUE">1000</RowLimit>
</View>
"@
$caml = @"
<OrderBy>
  <FieldRef Name='ID' Ascending='TRUE'/>
</OrderBy>
"@


#    <Where>
#    <Geq>
#      <FieldRef Name="CheckoutUser" LookupId="TRUE" />
#              <Value Type="int">0</Value>
#    </Geq>
#    </Where>

#<FieldRef Name='Deal_x0023_' Ascending='TRUE'/>
#  <Where>
#    <IsNotNull><FieldRef Name='CheckoutUser' /></IsNotNull>
#  </Where>
#<OrderBy><FieldRef Name='ID' Ascending='TRUE'/></OrderBy>

#                          <IsNotNull><FieldRef Name='ID' /></IsNotNull>
#                         <IsNotNull><FieldRef Name='CheckoutUser' /></IsNotNull>
 #                        <Eq><FieldRef Name='CheckoutUser' /><Value Type='Integer'><UserID Type='Integer' /></Value></Eq>
#             <Where>                    
#                        <Lt><FieldRef Name="Deal_x0023_" /><Value>"a"</Value></Lt>
#             </Where>

#<And><IsNotNull><FieldRef Name="CheckoutUser"/></IsNotNull></And>
  Do{
      $spqQuery = New-Object Microsoft.SharePoint.Client.CamlQuery

      $spqQuery.ListItemCollectionPosition = $page
      #$spqQuery.ViewAttributes = "Scope='Recursive'";
      #$spqQuery.RowLimit = 100
      #$spqQuery.Query = $caml

      $spqQuery.ViewXml = $qCommand
      $itemki=$ll.GetItems( $spqQuery)
      $ctx.Load($itemki)
      $ctx.ExecuteQuery()

      ## Getting the position of the previous page
      $page = $itemki.ListItemCollectionPosition

      Write-Host "################## PAGE " $page.PagingInfo " #########################"
      Write-Host "processing query results. Recs: " + $itemki.Count

      $Counter = 0;
      foreach($item in $itemki)
      {
          $Counter++

          Write-Host $Counter - $item["FileRef"] $item.ElementType

          $file = $ctx.Web.GetFileByServerRelativeUrl($item["FileRef"]);
            $ctx.Load($file)
            $ctx.Load($file.Versions)     

            $ctx.Load($file.ListItemAllFields)
            $Author=$file.Author
            $CheckedOutByUser=$file.CheckedOutByUser
            $LockedByUser=$file.LockedByUser
            $ModifiedBy=$file.ModifiedBy
            $ctx.Load($Author)
            $ctx.Load($CheckedOutByUser)
            $ctx.Load($LockedByUser)
            $ctx.Load($ModifiedBy)

            #$ctx.Load($file.EffectiveInformationRightsManagementSettings)

            #$ctx.Load($file.Properties)
            #$ctx.Load($file.VersionEvents)

            try
            {
                $ctx.ExecuteQuery()
            }
            catch
            {}

           if($CheckedOutByUser.LoginName -ne $null){
              Write-Host "File Name" $file.Name
              Write-Host "Checked out by" $CheckedOutByUser.LoginName
              $file.CheckIn('Checked in automatically', 'MajorCheckIn')
              $ctx.Load($file)

              try
              {
                $ctx.ExecuteQuery()        
                Write-Host $file.Name " has been checked in"     -ForegroundColor DarkGreen 
              }
              catch [Net.WebException]
              { 
                Write-Host $_.Exception.ToString()
              }

            }



     }
 }   
 Until($page -eq $null) 
}

and gets called like:

#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")
 [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint.Client.UserProfiles")


#Enter the data
$spPassword ="a password that is super duper secure"
$secureStringPwd = ConvertTo-SecureString -string $spPassword –asplaintext –force 

#$AdminPassword=Read-Host -Prompt "Enter password" -AsSecureString
$username="userUserton@aCompany.onmicrosoft.com"
$Url="https://aCompany.sharepoint.com/DocManage/PDFRepo/"
#$Url="https://aCompany.sharepoint.com/DocManage/TiffRepo/"
$ListTitle="Documents"


Get-sPOFolderFiles -Username $username -Url $Url -password $secureStringPwd -ListTitle $ListTitle 
  • I have also added a workflow using sharepoint designer 2013 to check in files after a 2 minute pause if they are checked out; works on new or on change only. But, I have 90% already uploaded and need to fix them or sharepoint will be useless if we can't search it. Can't search checked out files....so fun – Philmee95 May 30 '18 at 17:24
  • Also, sharepoint built in view for working with checked out files does not work with the large lists. Doc Library -> settings -> Manage files which have no checked in version -> Fail. Either runs with no return page, or gives the 500 error citing the list size. Contacted MS support and they escalated and then said tough luck more or less. I followed the latest steps but my items remained checked out. – Philmee95 May 30 '18 at 17:34
  • 1. Navigate to Document Library and then click on return to classic mode 2. After that click on library at the top left corner 3. Then click on library settings 4. Click on Versioning settings->Required Check out->NO->Ok 5. It will remove the automatic check out of the files and it will be searchable as well – Philmee95 May 30 '18 at 17:34
  • I did this step and the originally uploaded files are still checked out. It may help future uploads. I also changed my migration app to check in files during their upload and meta data population – Philmee95 May 30 '18 at 17:50
  • Oh yeah, checkout user CAML fails to pull a view, probably because of the 5k item limit. Field cannot be indexed right (I have 6 indexed fields in this sharepoint librabry, 2 say auto and 4 indexes I made)? I tried the # <Where> <IsNotNull><FieldRef Name='CheckoutUser' /></IsNotNull> </Where> and I get the query can't be run with 0 parameters – Philmee95 May 30 '18 at 21:46
0

Still no RowLimit and checked out user solution, and MS help gave up on me. Found I could start the query at a determined point if I say wrote a checkfile to hold the last record processed in case of a crash of the script. While this does not have the checkfile, it does have the Paging set last record in paged group set, so I can now restart the script. 1 instance processed 145k records over a weekend and completed. I had 4 files left checked out and the Sharepoint online work with checked out files page worked and gave me the 4 files I still had checked out. The other Document Library poweshell check in script did not finish, and crashed somewhere over the weekend running in ASC and DESC sort at the same time so the middle section was not checked in. Now I can finish it.

The whole script if someone else has to mess with this:

# Read more: http://www.sharepointdiary.com/2015/05/check-in-all-documents-in-sharepoint-library-using-powershell.html#ixzz5GYBU6e4M

function Get-SPOFolderFiles
{
param (
        [Parameter(Mandatory=$true,Position=1)]
        [string]$Username,
        [Parameter(Mandatory=$true,Position=2)]
        [string]$Url,
        [Parameter(Mandatory=$true,Position=3)]
        $password,
        [Parameter(Mandatory=$true,Position=4)]
        [string]$ListTitle
        )

#move inside do loop because of memory issues over a few days, may resolve
#      $ctx=New-Object Microsoft.SharePoint.Client.ClientContext($Url)
#      $ctx.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Username, $password)
#      $ctx.Load($ctx.Web)
#      $ctx.ExecuteQuery()

#      Write-Host "Logged into Sharepoint"
#      $ll=$ctx.Web.Lists.GetByTitle( $ListTitle)
#      $ctx.Load($ll)
#      $ctx.ExecuteQuery()

      ## Page Position
      #$page = $null
      # skip some maybe as rowlimit does not work
      #$page = "Paged=TRUE&p_ID=80000"
      # http://www.karthikscorner.com/sharepoint/listitemcollectionposition-and-pageinfo/ good stuff right here
      # https://sharepoint.stackexchange.com/questions/38848/caml-query-to-retrieve-the-second-set-of-three-list-items-from-the-announcements
      $startAt = "80000"
      $page = New-Object Microsoft.SharePoint.Client.ListItemCollectionPosition
      $page.PagingInfo = "Paged=TRUE&p_ID=$startAt"
      ## All Items
      $Counter = 0;

    $qCommand = @"
 <View Scope="RecursiveAll">
  <Query>

    <OrderBy>
      <FieldRef Name='ID' Ascending='TRUE'/>
    </OrderBy>
  </Query>
  <RowLimit Paged="TRUE">4000</RowLimit>
</View>
"@

      $qCommand1 = @"
<View Scope="RecursiveAll">
  <Query>
    <Where>
        <Gt><FieldRef Name="ID" /><Value Type='Integer'>1000</Value></Gt>
    </Where>
    <OrderBy>
      <FieldRef Name='ID' Ascending='TRUE'/>
    </OrderBy>
  </Query>  
  <RowLimit Paged="TRUE">1000</RowLimit>

</View>
"@
    $qCommand2 = @"
    <View Scope="RecursiveAll">  
            <Query> 
               <OrderBy><FieldRef Name='ID' /></OrderBy> 
            </Query> 
             <ViewFields><FieldRef Name='Deal_x0023_' /><FieldRef Name='ID' /></ViewFields> 
            <RowLimit Paged="TRUE">1000</RowLimit> 
      </View>  
"@
    $qCommand3 = @"
 <View Scope="RecursiveAll">
  <Query>
   <Where>
    <IsNotNull><FieldRef Name='CheckoutUser' /></IsNotNull>
  </Where>
    <OrderBy>
      <FieldRef Name='ID' Ascending='TRUE'/>
    </OrderBy>
  </Query>
  <RowLimit Paged="TRUE">1000</RowLimit>
</View>
"@
    $qCommand4 = @"
<View Scope="RecursiveAll">
  <Query>
    <Where>
       <Eq><FieldRef Name='ID' /><Value Type='Counter'>2</Value></Eq>
    </Where>
    <OrderBy><FieldRef Name='ID' Ascending='TRUE'/></OrderBy>
  </Query>
  <RowLimit Paged="TRUE">1000</RowLimit>
</View>
"@
    $qCommand5 = @"
<View Scope="RecursiveAll">
  <Query>
    <Where>
      <And>
        <IsNotNull><FieldRef Name='ID' /></IsNotNull>
        <IsNotNull><FieldRef Name='CheckoutUser' /></IsNotNull>
        <Eq><FieldRef Name='CheckoutUser' /><Value Type='Integer'><UserID Type='Integer' /></Value></Eq>
      </And>
    </Where>
    <OrderBy><FieldRef Name='ID' Ascending='TRUE'/></OrderBy>
  </Query>
  <RowLimit Paged="TRUE">1000</RowLimit>
</View>
"@
#    <Where>
#    <Geq>
#      <FieldRef Name="CheckoutUser" LookupId="TRUE" />
#              <Value Type="int">0</Value>
#    </Geq>
#    </Where>

#<FieldRef Name='Deal_x0023_' Ascending='TRUE'/>
#  <Where>
#    <IsNotNull><FieldRef Name='CheckoutUser' /></IsNotNull>
#  </Where>
#<OrderBy><FieldRef Name='ID' Ascending='TRUE'/></OrderBy>

#                          <IsNotNull><FieldRef Name='ID' /></IsNotNull>
 #                         <IsNotNull><FieldRef Name='CheckoutUser' /></IsNotNull>
  #                        <Eq><FieldRef Name='CheckoutUser' /><Value Type='Integer'><UserID Type='Integer' /></Value></Eq>
#             <Where>                    
#                        <Lt><FieldRef Name="Deal_x0023_" /><Value>"a"</Value></Lt>
#             </Where>

#<And><IsNotNull><FieldRef Name="CheckoutUser"/></IsNotNull></And>

      Do{
        # log in to sharepoint
        $ctx=New-Object Microsoft.SharePoint.Client.ClientContext($Url)
        $ctx.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Username, $password)
        $ctx.Load($ctx.Web)
        $ctx.ExecuteQuery()

        Write-Host "Logged into Sharepoint"
        $ll=$ctx.Web.Lists.GetByTitle( $ListTitle)
        $ctx.Load($ll)
        $ctx.ExecuteQuery()

          $spqQuery = New-Object Microsoft.SharePoint.Client.CamlQuery

          $spqQuery.ListItemCollectionPosition = $page

          $spqQuery.ViewXml = $qCommand
          $itemki=$ll.GetItems( $spqQuery)
          $ctx.Load($itemki)
          $ctx.ExecuteQuery()

          ## Getting the position of the previous page
          $page = $itemki.ListItemCollectionPosition

          Write-Host "################## PAGE " $page.PagingInfo " #########################"
          Write-Host "processing query results. Recs: " + $itemki.Count


          foreach($item in $itemki)
          {
              $Counter++

              Write-Host $Counter - $item["ID"] $item["FileRef"] $item.ElementType

              $file = $ctx.Web.GetFileByServerRelativeUrl($item["FileRef"]);
                $ctx.Load($file)
                $ctx.Load($file.Versions)     

                $ctx.Load($file.ListItemAllFields)
                $Author=$file.Author
                $CheckedOutByUser=$file.CheckedOutByUser
                $LockedByUser=$file.LockedByUser
                $ModifiedBy=$file.ModifiedBy
                $ctx.Load($Author)
                $ctx.Load($CheckedOutByUser)
                $ctx.Load($LockedByUser)
                $ctx.Load($ModifiedBy)

                #$ctx.Load($file.EffectiveInformationRightsManagementSettings)

                #$ctx.Load($file.Properties)
                #$ctx.Load($file.VersionEvents)

                try
                {
                    $ctx.ExecuteQuery()
                }
                catch
                {}

                # do the checkin if needed
               if($CheckedOutByUser.LoginName -ne $null){
                  Write-Host "File Name" $file.Name
                  Write-Host "Checked out by" $CheckedOutByUser.LoginName
                  $file.CheckIn('Checked in automatically', 'MajorCheckIn')
                  $ctx.Load($file)

                  try
                  {
                    $ctx.ExecuteQuery()        
                    Write-Host $file.Name " has been checked in" -ForegroundColor DarkGreen 
                  }
                  catch [Net.WebException]
                  { 
                    Write-Host $_.Exception.ToString()
                  }

                }

         }
     }   
     Until($page -eq $null) 
}

#Enter the data
$spPassword ="a password that is super duper secure"
$secureStringPwd = ConvertTo-SecureString -string $spPassword –asplaintext –force 

#$AdminPassword=Read-Host -Prompt "Enter password" -AsSecureString
$username="userUserton@aCompany.onmicrosoft.com"
$Url="https://aCompany.sharepoint.com/DocManage/PDFRepo/"
#$Url="https://aCompany.sharepoint.com/DocManage/TiffRepo/"
$ListTitle="Documents"


Get-sPOFolderFiles -Username $username -Url $Url -password $secureStringPwd -ListTitle $ListTitle

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.