Update 10/25/2017: This article originally appeared on ITUnity.com but as that site has been decommissioned I’ve republished it here.

In the Connecting to SharePoint Online using the SharePoint CSOM API with Windows PowerShell article you learned how to get started using the SharePoint CSOM API to connect to your SharePoint Online Site Collection with Windows PowerShell. That article introduced a utility function named Connect-SPOSite. In this article you’ll learn how to actually work with the Site Collection by loading and executing queries.

Loading and Executing Queries

Let’s look again at the example from the end of the aforementioned article. Figure 1 shows how to load the Connect-SPOSite function into memory and then use it to connect to a Site Collection:

Figure 1: Load the Connect-SPOSite function into memory and execute it by passing in the URL of the Site Collection you want to connect to.

In figure 1 you might have noticed that the ClientContext object actually contains both Site and Web properties. You’ll use these properties as your primary entry points into your Site Collection data. Figure 2 shows what happens when we try to inspect these properties:

Figure 2: The Site and Web properties of the ClientContext object have not been initialized.

As you can see from the error message, things aren’t quite ready for you to use yet. The way the CSOM objects work is that no data is loaded automatically – you have to tell the objects what properties you want populated and what methods you want executed, and none of that actually happens until you tell it to execute the commands you provided. So when we’re talking about working with properties you need to use the Load method of the ClientContext object and then once you’ve specified the properties you want loaded, you can then run the ExecuteQuery method of the ClientContext object (figure 3):

Figure 3: Loading the Web and Site properties of the ClientContext object and then running its ExecuteQuery method.

This batches these two load operations up so that only one request is made to the server. You could have called the ExecuteQuery method after each call to the Load method but it’s generally a good idea to batch calls like this up so you reduce the chatter and therefore improve the performance of the operations. Now that you’ve loaded the properties they should now be available for you to inspect (figure 4):

Figure 4: Whoops. The Site and Web properties are populated, but child properties are not.

If you’re following along, you’re probably thinking, “What the heck! This is the same error as before!” Well there’s a reason for this – the Site and Web properties were populated, but they contain lots of child properties that themselves need to be loaded. If the entire object tree were populated then the performance of such an operation would be less than ideal so only a sub-set of the properties are loaded. The problem here is with how PowerShell wants to render the view of the object – it doesn’t know what properties have been loaded and what hasn’t, it just wants to load everything it can see and as soon as it attempts to load one of the non-initialized properties it throws this error and you don’t get to see any of the properties that were initialized. So when you saw this error earlier (in figure 2, when you attempted to view the properties before loading them) the reason you got the error you did was not because the Web and Site properties weren’t loaded but because PowerShell was trying to show one of the child properties that hasn’t been initialized (the objects still needed to be loaded but the error itself is due to trying to render one of the collection properties that isn’t directly loaded).

So how do you look at what data was actually populated? There’s a couple of ways, but the simplest is to use the Get-Member cmdlet to see what properties are available to you (Get-Member -InputObject $web), and then either test each property individually or use the Select-Object cmdlet (aliased as select in the examples below) to view the properties you want to view, as shown in figure 5.

Figure 5: Using the Select-Object cmdlet to view properties.

Note that in the example, the Lists collection object has not been initialized so attempting to view it, either directly or by including it in the Select-Object cmdlet call, results in an error. Obviously, this is a bit of a pain, which makes working with the CSOM objects in PowerShell slightly annoying, but you eventually get used to it. (It’s issues like this that prompted me to create my custom SharePoint Online cmdlets, which I’ll cover in a later article.) Another way you can see what properties are initialized would be to use a tool such as Telerik’s HTTP Fiddler to decrypt and inspect the traffic to and from SharePoint; however, that’s outside the scope of this article.

As you can see, working with properties is pretty simple, if not slightly annoying, and we’ll look at some other examples of how to drill down into various objects in the Loading Specific Values Using Lambda Expressions and the SharePoint CSOM API with Windows PowerShell article.

So what about executing methods? It’s actually exactly the same – just execute the method like you normally would and then use the ExecuteQuery method to have it actually execute the method (remember, just calling the method merely batches it for execution when you call the ExecuteQuery method).

In the example in Figure 6, I’m using the GetCatalog method of the Web object to return the Master Page Gallery list. In this case, I’m retrieving a complex type that, like before, will need to be populated so I then call the Load method followed by the ExecuteQuery method and then output some properties of interest.

Figure 6: Using the GetCatalog method of the Web object to return the Master Page Gallery list.

The example in Figure 7 uses the GetUserEffectivePermissions method to return back an object that contains information about all the permissions a specific user has. In this case, calling the Load method is not necessary and we can simply use the Has method of the Value property to see if the user has a specific permission.

Figure 7: Using the GetUserEffectivePermissions method.


In this article you learned how the SharePoint CSOM API loads and executes queries using the Load and ExecuteQuery methods. If you wish to learn more about using the SharePoint CSOM API with PowerShell then you should look at the Loading Specific Values Using Lambda Expressions and the SharePoint CSOM API with Windows PowerShell article which explains how to load specific values of an object by using a custom function that will replicate a common C# language construct known as lambda expressions. And finally, to tie what you have learned in this article and that all together, you should read the Completing Basic Operations using the SharePoint CSOM API and Windows PowerShell article. That article will take some common C# examples and show you how to convert them to a PowerShell equivalent. Hopefully these articles will give you the foundation you need to successfully manipulate your SharePoint Online tenant using the SharePoint CSOM API with Windows PowerShell.