Monday 27 June 2016

Useful file handling commands in the SharePoint PnP PowerShell module

Last week I got a request from one of our customers to help them to move some of the files from one SharePoint library to another in different web. Sounds an easy and quick task, but the catch was that the library had ~ 12000 documents and 1500 folders and the customer also wanted to keep the Created, Created by, Modified and Modified by column values. The number of items that had to be moved was ~ 3500. Pointless to say that with such number of items the Explorer view is not working, the new OneDrive client does not support sync from SharePoint libraries yet and I still had to figure out how to effectively copy the metadata. The way to accomplish this is with PowerShell or with 3rd party migration tool. Since the customer had only this requirements and not migration of the version history for example, my weapon of choice was powershell.
In this post I wont to share a couple of SharePoint PnP PowerShell cmdlets that greatly helped me in writing my migration script. Bluesource is also a contributor to the PnP project, thanks to my colleague Pieter Veenstra.
The first thing I want to share is a new feature that came with the June 2016 release. It is the option to map SharePoint site as PSDrive. This is done at the begging using Connect-SPOnline with CreateDrive parameter. You will then get a PSDrive called SPO and a PSProvider also called SPO. See how it looks below.

As mentioned above you will connect to the site and you will be looking at the root web. The sub webs will be shown as folders. You can do many standard things in this PSDrive like listing items, copy, move and more. One thing I was unable to do is listing the items in lists with 5000+ items, it seems that the view threshold limitation is kicking in. The other thing is copy items from SPO drive to the local file system, this is because you cannot copy items from one drive to another if the PSProviders are different. Also it would have been nice if this SPO drive is persistent and you can access it in Windows Explorer, this is not available and I am not sure if it is possible. 
I did not used this in my script, but I think that it is nice to have and you can learn more about this and other improvements in the June 2016 Community Call.

Get-SPOFile - This is one very useful command that will help you to download a file by supplying the server relative url to it. This was easy task after I retrieved all items and their FileLeafRef and FileDirRef fields using the technique from my previous post

Ensure-SPOFolder - With this command you can get a folder by giving a web relative url to the folder. If the folder does not exist it will create it even if the folder is nested in other non-existing folders, they will be created as well.

Add-SPOFile - With this command you can upload a file by supplying web relative url of the folder, the file will be uploaded with the same name. If the folder does not exist this command will create it before uploading the file. Really nice command!

I hope that this was helpful!

Monday 20 June 2016

Get All Items in 5000+ large list with CSOM in PowerShell

Last week I had to write a script that needed to take all items in large SharePoint Online list.
By large I mean above 5000 items. This means that the list is above the list view threshold in SharePoint Online, which is 5000 and we cannot change that. The way to get all items in SharePoint Online is to use CAML query. However if it is just an empty query without any filtering it will fail, if you use unindexed column for filtering or ordering the query will fail, if you filter/order by indexed column and the query returns more than 5000 items it will fail again. The error in this and other scenarios is similar to the one below.

Exception calling "ExecuteQuery" with "0" argument(s): "The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator."

The way to workaround this is pagination of the view. This means that we will have a row limit of the result that the query can return that should be less or equal to 5000. Once we get the first 5000 items we can do another query for the next 5000 starting from the position where the first result(page) ends. This is the same with what we do in the UI scrolling foreword in the list view. Below is an example PowerShell snippet that will take all items from a list using 5000 items page size ordering the items by ID.

$list = $ctx.Web.Lists.GetByTitle($DocLibName)
$ctx.Load($list)
$ctx.ExecuteQuery()
## View XML
$qCommand = @"
<View Scope="RecursiveAll">
    <Query>
        <OrderBy><FieldRef Name='ID' Ascending='TRUE'/></OrderBy>
    </Query>
    <RowLimit Paged="TRUE">5000</RowLimit>
</View>
"@
## Page Position
$position = $null
 
## All Items
$allItems = @()
Do{
    $camlQuery = New-Object Microsoft.SharePoint.Client.CamlQuery
    $camlQuery.ListItemCollectionPosition = $position
    $camlQuery.ViewXml = $qCommand
 ## Executing the query
    $currentCollection = $list.GetItems($camlQuery)
    $ctx.Load($currentCollection)
    $ctx.ExecuteQuery()
 
 ## Getting the position of the previous page
    $position = $currentCollection.ListItemCollectionPosition
 
 # Adding current collection to the allItems collection
    $allItems += $currentCollection
}
# the position of the last page will be Null
Until($position -eq $null) 

Few word about the query, I am using RecursiveAll because I used it against library and I wanted to get all items in all folders, the size of the page is 5000, just on the edge of the threshold and I am ordering the result by ID because this column is always indexed.
I am using Do-Until loop to get all pages and setting the position to be the position of the last item collection that was retrieved.
This is really a powerful and quick way to workaround the annoying 5000 list view threshold. I hope you find it useful!

Sunday 12 June 2016

Disable SharePoint Event Firing in PowerShell process

Last week I worked with a customer that is using SharePoint 2010 as part of their enterprise DMS solution. Only a small part of the users are accessing the SharePoint sites directly, but they are accessing and adding documents using 3rd party Outlook integration product and in-house legacy LOB system integrations with SharePoint. If you have dealt with such DMS solutions(which is not uncommon in some industries) you will know that during the exploitation you might end up with a complex folder structures created based on the document metadata. You might also end up with large numbers of empty folders.
The empty folders are an issue for my customer and they reached me with a request to write a PowerShell cleanup script that will run on schedule and will delete the empty folders.
The catch is that there is an ItemDeleting event receiver that is preventing the deletion of any item including folders. You can see in my dev. machine a similar event receiver in action. It is also stopping the operation when I call Recycle() on item in PowerShell.


If you are a developer, most probably you know how to disable the event firing inside an event receiver in order to prevent the firing of other event receivers. This is done by setting the value of property SPItemEventReceiver.EventFiringEnabled. We can do the same thing in powershell with below code and prevent any events from being fired.

$assembly = [Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint");
$type = $assembly.GetType("Microsoft.SharePoint.SPEventManager");
$prop = $type.GetProperty([string]"EventFiringDisabled",`
[System.Reflection.BindingFlags] `
([System.Reflection.BindingFlags]::NonPublic -bor [System.Reflection.BindingFlags]::Static)); 
#SET EVENT FIRING DISABLED.
$prop.SetValue($null, $true, $null); 
 
<#
 DO WHAT YOU NEED TO DO
#>
 
#SET EVENT FIRING ENABLED.
$prop.SetValue($null, $false, $null); 

This code will disable the event firing in the current PowerShell thread and I am able to delete/recycle any item without executing the event handler. I have tested this with SharePoint 2010 and 2013, haven't tested it on SharePoint 2016, but I assume that it will work there too.
This is very powerful technique use it carefully on your own responsibility. I hope that this was helpfull!