Delete First N Records From SharePoint List Online Using Power Shell

In today's world, PowerShell is a main concept for every SharePoint developer. Using PowerShell, we can do SharePoint tasks easily and very fast. So, in this article, I am sharing on of my experience task. I want to delete first n records from the SharePoint list (Office 365).

Here, I have a custom list with few views. In particular view display, I am getting list threshold problem. Because in that view, am displaying total records from the list, so my requirements is that when the items in list is crossing 4990, then immediately I need to delete first 100 records from the list.

First, I am getting the total records in the list. Then, if the count is greater than 4990, I am getting first 100 records. Then, I am looping each record for deletion. So, I will not get list threshold problem for my view. Here, I am using SharePoint client object model to perform this operation for SharePoint online. I think this same code will work for on premises also.

 

So, for logging into SharePoint online, it will not accept direct password. We need to convert our password as secure string.

My code

#
first we need to include the sharepoint client dll and client runtime dll 's

Add - Type - Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.dll"
Add - Type - Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"
$0 = $MyInvocation.MyCommand.Definition
$dp0 = [System.IO.Path]::GetDirectoryName($0)

$url = "https://sharepoint.com/usa/"
$username = "Username"
$password = "*******"

# in below line am converting my sharepoint online password to secure string.

$securePassword = ConvertTo - SecureString $password - AsPlainText - Force# connect / authenticate to sharepoint online and get ClientContext object...
$clientContext = New - Object Microsoft.SharePoint.Client.ClientContext($url)
$credentials = New - Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($username, $securePassword)
$clientContext.Credentials = $credentials

if (!$clientContext.ServerObjectIsNull.Value) {
    $web = $clientContext.Site.RootWeb
    $clientContext.Load($web)
    $clientContext.ExecuteQuery()

    $list = $clientContext.Web.Lists.GetByTitle('MyList')


    $clientContext.Load($list)
    $clientContext.ExecuteQuery()


    $query = New - Object Microsoft.SharePoint.Client.CamlQuery
    $query.ViewXml = "<View><RowLimit>5000</RowLimit></View>"
    $items = $list.GetItems($query)
    $clientContext.Load($items)
    $clientContext.ExecuteQuery()
    Write - Host "Total number of ListItems: "
    $items.Count

    # check the count of the sharepoint list

    if ($items.Count - gt 4990) {
        $query1 = New - Object Microsoft.SharePoint.Client.CamlQuery

        # in below code am passing 100 as rowlimit means it will get first 100 records from the sharepoint list

        $query1.ViewXml = "<View><RowLimit>100</RowLimit></View>"
        $items1 = $list.GetItems($query1)
        $clientContext.Load($items1)
        $clientContext.ExecuteQuery()
        if ($items1.Count - gt 0) {
            for ($i = $items1.Count - 1; $i - ge 0; $i--) {
                $items1[$i].DeleteObject()
            }
            $clientContext.ExecuteQuery()
        }
    }
}

Happy coding.