Woman In Tech: NAVIGATING THE DIGITAL WORLD

Necessity is the mother of invention AND research. Its also the mother of all workflow history/list row deletions using all powerful CSOM and POWERSHELL!

So, I was working on an application and just when I thought I had it all done to perfection(this is very relative isn’t it!) I found that electronic signature fields that I was capturing from workflow back to my list was NOT being updated right. I do what any good logger(yes! I am one) does, and check Workflow History list for the first time in a month to see this beautiful message that everyone dreads…i do alteast and have been putting it off with manual deletes for a while.

I am done with this..so combining a bunch of powershell scripts from a previous app and adding on list item delete function, I created a script that you can use to run in your SharePoint online environment to delete ANY number of rows YOU choose from ANY list in your site. (For performance reasons, batch deletes are better for larger number of rows, but thats not the focus of the post here)

Dreaded Message:

Steps taken to delete:

Create a powershell script from an editor of your choice(I switch between visual studio code and 2015)

You can checkout the script at GitHub: https://github.com/SwethaSan/DeleteRowsWithPowerShellCSOM

Once you save your powershell script in your SharePoint site assets > scripts or which every folder you choose to save it to.

You can run it from SharePoint Online Management4 Shell and enter required details

cls
#Import SharePoint Online Management Shell
Import-Module Microsoft.Online.SharePoint.Powershell -ErrorAction SilentlyContinue

Add-PSSnapIn Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

#region Input Variables

$SiteUrl = Read-Host -Prompt “Enter the site name in which your list resides”
$UserName = Read-Host -Prompt “Enter User Name”
$SecurePassword = Read-Host -Prompt “Enter password” -AsSecureString

#endregion

#region Connect to SharePoint Online tenant and Create Context using CSOM

Try
{
#region Load SharePoint Client Assemblies

Add-Type -Path “C:\Program Files\Common Files\microsoft shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll”
Add-Type -Path “C:\Program Files\Common Files\microsoft shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll”
Add-Type -Path “C:\Program Files\Common Files\microsoft shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.WorkflowServices.dll”

#endregion
#region connect/authenticate to SharePoint Online and get ClientContext object..

$clientContext = New-Object Microsoft.SharePoint.Client.ClientContext($SiteUrl)
$credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($UserName, $SecurePassword)
$clientContext.Credentials = $credentials

Write-Host “Connected to SharePoint Online site: ” $SiteUrl -ForegroundColor Green
Write-Host “”

#endregion
#region Load SharePoint List
if(!$clientContext.ServerObjectIsNull.Value)
{
$web = $clientContext.Site.RootWeb
Write-Host $web

$clientContext.Load($web)
$clientContext.ExecuteQuery()

$listName=Read-Host -Prompt “Enter the name of the list you wish to delete rows from”
Write-Host “List Name” $listName -ForegroundColor Green

$list=$clientContext.Web.Lists.GetByTitle($listName)

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

$itemsCountToDelete = Read-Host “Enter number of items to delete from your “$listName ” list”
Write-Host $itemsCountToDelete “rows to be deleted from: ” $SiteUrl -ForegroundColor Green

$query = New-Object Microsoft.SharePoint.Client.CamlQuery
$query.ViewXml=”<View><RowLimit>”+ $itemsCountToDelete +”</RowLimit></View>”

$items=$list.GetItems($query)

$clientContext.Load($items)
$clientContext.ExecuteQuery()
Write-Host “Items count ” $items.Count
if ($items.Count -gt 0)
{
for ($i = $items.Count-1; $i -ge 0; $i–)
{
$items[$i].DeleteObject()
}
$clientContext.ExecuteQuery()
}
}
Write-Host $itemsCountToDelete “rows deleted from: ” $SiteUrl -ForegroundColor Red

#endregion

}
Catch
{
$SPOConnectionException = $_.Exception.Message
Write-Host “”
Write-Host “Error:” $SPOConnectionException -ForegroundColor Red
Write-Host “”
Break
}

#endregion

References:

http://www.enjoysharepoint.com/Articles/Details/how-to-delete-list-items-in-sharepoint-online-using-client-21587.aspx

http://www.sharepointnutsandbolts.com/2013/12/Using-CSOM-in-PowerShell-scripts-with-Office365.html