Return SPListItems using CSOM and PowerShell without writing CAML


I recently tweeted about my triumph when trying to accomplish returning all SharePoint List Items without the use of CAML or LINQ. The reason I wanted to do this might be strange, but I’ll try to explain my process and methodology and why I got to a point of wanting to make SharePoint do things it didn’t want to do. 🙂

As a very advanced SharePoint Scripter, I do a TON of PowerShell. If you’ve read this blog, follow me on Twitter or talk to me in person you probably knew that. Having said that, there are times when I want to iterate through all list items – either looking for a match or just to return all values. It’s not super common but it happens. Well it’s a very easy thing to do with server side code (read: PowerShell and the Microsoft.SharePoint.PowerShell snap-in).

Getting List Items using standard PowerShell

$web = Get-SPWeb http://someSiteUrl
$list = $web.Lists["SomeList"]
$list.Items #do something with them here

As you see on line 3, there is an Items collection property on the List object. This is great, end of story – IF you’re using server-side PowerShell code. However, I’ve been focusing a lot lately on integrating PowerShell into the CSOM space – specifically to manage SharePoint Online with PowerShell. If you’ve written any CSOM code, you know that things are done a little differently. Here’s an example of how to return a list item using PowerShell on the client-side – note that I’ve omitted about 12 lines of code that must take place before this to load the Microsoft.SharePoint.Client* assemblies, create ClientContext, get the SPWeb, etc.:

Getting a List Item using PowerShell Client-Side Code

$list = $GLOBAL:Web.Lists.GetByTitle("BigListLotsOfItems")
$item = $list.GetItemById(5)
$GLOBAL:Context.Load($item)
$GLOBAL:Context.ExecuteQuery()
$item["Title"]

That’s great, but if I wanted to return all items – the Client Object Model won’t give me List.Items. In reading MSDN, there are C# examples of how to write CAML queries to return all list items – but the whole point is that I don’t want to write CAML. Here’s the C# example – which I could have certainly translated to PowerShell if I wanted to do so:

Using C# to get all List Items

// Starting with ClientContext, the constructor requires a URL to the
// server running SharePoint.
ClientContext context = new ClientContext("http://SiteUrl");

// Assume the web has a list named "Announcements".
List announcementsList = context.Web.Lists.GetByTitle("Announcements");

// This creates a CamlQuery that has a RowLimit of 100, and also specifies Scope="RecursiveAll"
// so that it grabs all list items, regardless of the folder they are in.
CamlQuery query = CamlQuery.CreateAllItemsQuery(100);
ListItemCollection items = announcementsList.GetItems(query);

// Retrieve all items in the ListItemCollection from List.GetItems(Query).
context.Load(items);
context.ExecuteQuery();
foreach (ListItem listItem in items)
{
    // We have all the list item data. For example, Title.
    label1.Text = label1.Text + ", " + listItem["Title"];
}

So that’s great, but here’s how I went about getting all list items without writing CAML! I’ll break it down section by section, but if you’re impatient the whole solution is at the bottom. 🙂

Creating our Context

The first thing we need to do when working with SharePoint from the Client Side is create ClientContext. To do so using PowerShell, here is some example code:

$GLOBAL:Context = New-Object Microsoft.SharePoint.Client.ClientContext("http://someWebUrl")
$GLOBAL:Credentials = Get-Credential -UserName $EmailAddress -Message "Please enter your Office 365 Password"
$Context.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Credentials.UserName,$Credentials.Password)
$GLOBAL:Web = $GLOBAL:Context.Web
$GLOBAL:Context.Load($GLOBAL:Web)
$GLOBAL:Context.ExecuteQuery()

Getting our SPWeb

Once we’ve created our ClientContext variable, we’ve already got the SPWeb. But just for transparency, here’s how you would/could access methods and properties of the SPWeb object:

$GLOBAL:Web.Title #returns Web.Title
$GLOBAL:Web.Description #returns Web.Description

Getting our SPList

Now, to get the List object – we’ll do the following:

$SPList = $GLOBAL:Web.Lists.GetByTitle("BigListLotsOfItems") #Note that BigListLotsOfItems is my List Title

Using the For loop to iterate through all items

And this is where the magic happens as they say. For anyone who has done any level of development or scripting, you know that the best way to loop while also allowing for modification of the array with which you’re looping is to use a For loop.

For ($i=0; $i -le $SPList.ItemCount; $i++)
{
}

This is a pretty standard implementation of the For loop, what I want to call out though is the use of Try/Catch/Finally. As a dabbling developer, I’m not sure if this is the intended use of Try/Catch/Finally – so if this is bad practice forgive me. 🙂

Essentially I start out by creating the ListItem variable, and then calling the Load method of the ClientContext variable. I do that before entering the Try/Catch/Finally. I then use Try {} to run the Context.ExecuteQuery method, and if that fails the loop will go into the Catch {} block – where I will increment the array by 1 ($i++) and then run the GetItemById(), Load() and ExecuteQuery() methods against the next $i in the loop.

$ListItem = $SPList.GetItemById($i)
$GLOBAL:Context.Load($ListItem)
    Try
    {
        $GLOBAL:Context.ExecuteQuery()
    }
    Catch
    {
        $i++
        $ListItem = $SPList.GetItemById($i)
        $GLOBAL:Context.Load($ListItem)
        $GLOBAL:Context.ExecuteQuery()
    }

Why did I do this? Well it’s really pretty simple. Since I have to use the GetItemById() method, I have to know the ListItemID of each ListItem. If someone has deleted an item, the ItemCount property won’t match the highest ID in the list – right? So if there are 5 items but someone deleted a few at one point or another – the highest ID might be 7 or 8, without incrementing by 1 on a failure, we’ll never get to 7 or 8…

And so now that we’ve discussed all of that, here’s the final example script.

The Final Solution

$GLOBAL:Context = New-Object Microsoft.SharePoint.Client.ClientContext("http://someWebUrl")
$GLOBAL:Credentials = Get-Credential -UserName $EmailAddress -Message "Please enter your Office 365 Password"
$Context.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Credentials.UserName,$Credentials.Password)
$GLOBAL:Web = $GLOBAL:Context.Web
$GLOBAL:Context.Load($GLOBAL:Web)
$GLOBAL:Context.ExecuteQuery()
$SPList = $GLOBAL:Web.Lists.GetByTitle("BigListLotsOfItems")
For ($i=0; $i -le $SPList.ItemCount; $i++)
{
    $ListItem = $SPList.GetItemById($i)
    $GLOBAL:Context.Load($ListItem)
    Try
    {
        $GLOBAL:Context.ExecuteQuery()
    }
    Catch
    {
        $i++
        $ListItem = $SPList.GetItemById($i)
        $GLOBAL:Context.Load($ListItem)
        $GLOBAL:Context.ExecuteQuery()
    }
    Finally
    {
        #Return item here and do something with it.
    }
}

And that – my friends – is how you can get all List Items from a SharePoint list using PowerShell and CSOM. Hopefully this is helpful for someone else.

Advertisements

Delete all SharePoint List Items using PowerShell


Sometimes when I’m working on something related to SharePoint, I come across scenarios where I’m constantly building up test data in Lists. Frequently when doing this, I have a need to almost constantly delete and recreate list items.

Rather than using the UI or writing nice PowerShell one-liners (which for the record I love doing), I decided to throw together a little script for this.

This script takes two mandatory parameters, one for URL and one for List Name. Assuming you give it the correct values, it will quickly use Object Model code to call the Delete() method against every item in the list (or library).

Here is the script, hope it works for you! Note: I did not add any kind of confirmation, so if you run this – it WILL literally delete all items, it won’t first make sure you’re serious about it – so make sure you know what you’re doing! 🙂

RD

<#
.Synopsis
	Use this PowerShell Script to delete all list items, very quickly!
.Description
	This advanced script uses SharePoint Cmdlets and the SP Object Model to bulk 
	delete SharePoint list items from a specified SPList.
.Example
	C:\PS>.\Delete-SPListItems.ps1 -Url http://intranet -ListName "Test List"
	This example deletes list items from a list called Test List in the
	http://intranet web.
.Notes
	Name: Delete-SPListItems
	Author: Ryan Dennis
	Last Edit: 8/2/2012
	Keywords: Delete List Items
.Link
	http://www.sharepointryan.com
 	http://twitter.com/SharePointRyan
.Inputs
	None
.Outputs
	None
#Requires -Version 2.0
#>
[CmdletBinding()]
Param(
[Parameter(Mandatory=$true)][System.String]$Url,
[Parameter(Mandatory=$true)][System.String]$ListName
)
$Web = Get-SPWeb $Url
$List = $Web.lists[$ListName]
if($List -eq $null){
	Write-Error "The List cannot be found";return
}
Write-Warning "Deleting all list items from $($ListName)"
$Items = $List.GetItems()
$Items | ForEach-Object{
$List.GetItemById($_.Id).Delete()
}
$List.Update()
$Web.Dispose()

Workflow failed on start (retrying) when anonymous users start workflows


Here is an interesting issue and subsequent workaround – anonymous users can create list items (if granted the appropriate permissions), but workflows will not start for anonymous users.

If you do some research on this topic, you’ll see lots of people trying to allow this, so that emails can be sent for example – but the answer is almost always “here is some code you can deploy.” That’s an unfortunate answer, as not all of us have the ability or skillset to write and deploy Visual Studio code to our environments or our client’s environments.

However, after lots of trial and error I came up with a solution using PowerShell.

Essentially, we need to do the following:

  1. Grant anonymous users the ability to add items
  2. Setup a SharePoint Designer workflow to do the e-mailing, or what have you – set the workflow to start when items are changed, not when items are created…
  3. Configure a PowerShell script to run against the site or sites so that the items get updated if they have not yet had their Workflow Status updated to “Complete”

The script is currently configured to iterate through a single site collection and all of it’s subwebs, looking for any “GenericList” (Custom List) that has items which have not yet been marked as “Completed” by the workflow engine…

Here is my PowerShell Script to update items:

Start-SPAssignment -Global
$Site = Get-SPSite http://siteurl
$Webs = $Site.AllWebs
	foreach ($Web in $Webs) {
		foreach ($list in $Web.Lists | where {$_.BaseType -eq "GenericList"}){
			foreach ($item in $list.Items | where {$_.Workflows.InternalState -ne "Completed"}) {
			$item.Update()
			}
		$list.Update()	
		}
	$Web.Dispose()		
	}
$Site.Dispose()
Stop-SPAssignment -Global

Setup a Windows Scheduled Task to run this every half-hour or so, and it will iterate through your site – kicking off any yet-to-be-started workflows under the context of whatever account you use in the Scheduled Task.

Disclaimer: This may cause heavy utilization of server resources if you have a large site collection, lots of lists, big lists, or if you run this against multiple web applications. I’m sure there are other (perhaps better) solutions, but this works for me. No warranty or guarantee is implied. 🙂

Enjoy!
RD