Change Site Policy Deletion notification email template in SharePoint

The Site Policies in SharePoint are information management tool that helps you implement some site life cycle management. Whether this is dictated by internal house keeping rules or some external regulations that apply to your organisation, the Site Policy is the out of the box way to go if you want to "close" a site, delete it or both, automaticaly after certain period of time.

With site policies you have the option to notify the site owners in advance before the site is deleted. The mail looks like the one below.

The information in this email might not be suitable for your organization.

Fortunately there is a way to change the default Site Policy notification email body template and the email subject. This is not done in some XML template file like the Alerts template, maybe there is one, but I have not found it. There is SSOM and CSOM API that you can use to set custom email body template per policy.

The documentation of this is very poor and the best resource on this is the article Site Policy in SharePoint.

Unfortunately I have not managed to make this work server side or using PowerShell. I have tried with SharePoint 2013, SharePoint 2016 and SharePoint Online ssom and csom as well.

The only way I found it working is from console application using the CSOM approach.

The site policy post above is good and the code should work as it is, but it has some gaps.

There are three placeholders that we can use, placeholders for Site Url, Deletion Date and Mailbox Id.
However the placeholders with curly braces that are demonstrated in the post will not work.

I would like to save you some time testing especially if you are targeting SharePoint Online, as there you cannot manually run the "Expiration Policy" timer job.

The correct placeholders are below, without curly braces or any spaces.

---

SiteUrl: <!--SiteUrl-->
Deletion Date: <!--SiteDeleteDate-->
Mailbox ID: <!--TeamMailboxID-->

---

I hope you found this helpful!

Set Managed Metadata field value with PowerShell and CSOM

In the previous post I demonstrated an easy way to migrate managed metadata term store objects to SharePoint Online with PowerShell.

Now when you have migrated the terms you might need to migrate some documents and set the metadata fields in SharePoint Online. In the same project I had to migrate around 600 documents to SPO including the metadata which had 6 managed metadata fields, 4 of them were multi-valued.

In this post I will share a powershell snipped to make TaxonomyFieldValueCollection and use it as value for field of type Managed Metadata.

I am showing this method because I got some mixed results when I used simple string as value. It is hard for me to explain why simply updating with taxonomy string did not worked in all cases.
For example, if the document was created in Office Web Apps I was unable to set the fields using a simple string. You can try using the string method and then cross-check  if everithing is set, because if you feed only metadata string(multi-valued) or just guid(for single-valued) you might not get any error, but the field will be left blank.

The challenge for me in the "TaxonomyFieldValueCollection" approach was to create TaxonomyField object instance, because I had to use the generic client context method CastTo and PowerShell don't work well with generic methods. This is why I decided it is worth sharing this example. You can see the code below.

Now a couple of words about the string that is used. In the example above I am setting multi-valued MM field with collection of two terms. The string is in format "<int>;#<label>|<guid> " with ;# delimiter between the terms. The integer is the item id of the term in the Taxonomy Hidden List, if you are using the term for first time or you do not know this id you can use the default value "-1".
The label part speaks for itself, this is the label of the term and the most important part is the guid of the term. If something is wrong with the format of the string you will see below error message.

---

"The given value for a taxonomy field was not formatted in the required <int>;#<label>|<guid> format."

---

This method is working every time for all items. I hope that this was helpful!

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!

Write and Get User Profile Properties in SharePoint Online with CSOM in PowerShell. The Scripts!

Last year (1st December) I published an article called Write and Get User Profile Properties in SharePoint Online with CSOM in PowerShell . In this article I demonstrated the latest capabilities that were introduced with the latest CSOM SDK. There were new APIs that made possible to work with User Profile properties in SharePoint Online(for now). I published PowerShell snippets for writing user profile properties and retrieving them. At that time I was unable to figure out how to write MultiValue properties, the snippets were just for the demo, with hard coded property and they were not very reusable.

Since this article became very popular and people were happy that they can do this from PowerShell, I decided to write generalized script for writing any Single or Multi value user profile property.

The Name(not the DisplayName) of the property to be edited is provided as script parameter. As I said the script is generalized, not tested with all available out of the box properties in SharePoint Online. Some of the properties have specific requirement for the value. For example the About Me property should be html(<br> instead new line), the Birthday should be some parsable datetime format and so on. If the format is not correct in most cases you will receive some meaningful exception from CSOM.

The script I will provide requires SharePoint Server 2013 Client Components SDK(x64) or SharePoint Online Client Components SDK(x64) that are released September 2014 or newer.

The script is written like native cmdlet, it accepts pipeline values for the AccountName, you can combine it with a CSV or simple text file that contains a list with account names, include it in your own scripts and modify it as you find for useful. Test the script to account that is not C-level manager :-) and if it works use it for bulk property updates for "live" accounts for example. 

To demonstrate how the script for writing user profile properties is working I am going to change the profile of  Anne Wallace (AnneW@spyankulov.onmicrosoft.com). She is the President of Contoso. I am going to change her Work Phone(Single Value), her Skills(Multi Value) and her Birthday(short date Single Value). See how her profile page looks like before changes.

Below are the PowerShell lines I have used to change this properties, I have made it in couple of lines in order to fit it better in the blog. Fore more examples see the Help section of the scripts.

---

########################## Change Work Phone ###############
.\Set-SPOUserProfileProperty.ps1 -PropertyName WorkPhone `

-AccountName 'AnneW@spyankulov.onmicrosoft.com' -Value "+44 555 555 55" `

-SPOAdminPortalUrl 'https://spyankulov-admin.sharepoint.com' `

-UserName ****@spyankulov.onmicrosoft.com -Password *******

########################## Change The Skills ###############

.\Set-SPOUserProfileProperty.ps1 -PropertyName 'SPS-Skills' `

-AccountName 'AnneW@spyankulov.onmicrosoft.com' -Value "SharePoint","PowerShell","Tech Support"-SPOAdminPortalUrl 'https://spyankulov-admin.sharepoint.com' `

-UserName ****@spyankulov.onmicrosoft.com -Password ******* -MultiValue

######################### Change The Birthday ###############                                         
.\Set-SPOUserProfileProperty.ps1 -PropertyName 'SPS-Birthday' `

-AccountName 'AnneW@spyankulov.onmicrosoft.com' -Value "5/9/1988" `

-SPOAdminPortalUrl 'https://spyankulov-admin.sharepoint.com' `

-UserName ****@spyankulov.onmicrosoft.com -Password *****

---

Lets see how Anne's profile looks like below, after the change.

We can see that the properties are changed accordingly. How cool is that :).

In the package you can download below I also included a script that can retrieve the user profile properties in PowerShell. Please, download the script,test,use and rate!

I am writing a fair amount of Help to my scripts, please read it.

 Download the script from: TechNet Gallery

(You may experience issue with Chrome, use Firefox/IE to open the link)

Write and Get User Profile Properties in SharePoint Online with CSOM in PowerShell

A couple of weeks ago Vesa Juvonen wrote a post about the new capability in CSOM that is letting us to write user profile properties. This feature is already enabled in all Office 365 tenants.
The features are available in Microsoft.SharePoint.Client.UserProfiles.dll assembly version 16. Here is the moment to mention that if you download the latest CSOM package you will receive client dlls version 15 and version 16. In my earlier post I wrote about using CSOM in powershell I mentioned that the client dlls are located in "C:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\ISAPI", well there are version 15 dlls that can be used against SharePoint 2013 on-premise. But if you want to use the latest and greatest features against SharePoint Online you should load the version 16 dlls located in "C:\Program Files\Common Files\microsoft shared\Web Server Extensions\16\ISAPI".
The new features are coming  from two new methods in PeopleManager class, SetSingleValueProfileProperty() and SetMultiValuedProfileProperty().
In his post Vesa gives example code that demonstrates the capability in SharePoint app. I found this interesting and since I am not a developer I will show you how this works in PowerShell. Unfortunately, I was unable to get SetMultiValuedProfileProperty() working from powershell, I think that this is because of the issue with the generic lists in powershell. However I will show you a function that edits the AboutMe user profile property and function that will get all user properties. I will authenticate against the admin portal of SharePoint online and I will be able to play with the properties of other users.
Below you can see both function(for editing AboutMe and gettin profile properties) and everything I do befor that.

[*UPDATE*]: I have published "universal" scripts for editing any User Profile property read more HERE

---

$cDLLS = Get-ChildItem -Path "C:\Program Files\Common Files\microsoft shared\Web Server Extensions\16\ISAPI" | Where {$_.Name -notlike "*.Portable.dll"}
ForEach ($dll in $cDLLS)
{
    Add-Type -Path $dll.FullName
}


$username = "******@yankulovdemo.onmicrosoft.com" 
$password = "*******" 
$url = "https://yankulovdemo-admin.sharepoint.com"
$securePassword = ConvertTo-SecureString $Password -AsPlainText -Force

$credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($username, $securePassword)
$Context = New-Object Microsoft.SharePoint.Client.ClientContext($url)
$Context.Credentials = $credentials
$Global:oContext = $Context

Function Update-AboutText{
Param(
    [string]$AboutText,
    [string]$UserLogin
)
Process{
    $logIn = ("i:0#.f|membership|" + $UserLogin)
    $ctx = $Global:oContext
    $aboutTextHtml = $AboutText.Replace(([System.Environment]::NewLine), "<br />")
    $peopleManager = New-Object Microsoft.SharePoint.Client.UserProfiles.PeopleManager($ctx)
    $Properties = $peopleManager.GetPropertiesFor($logIn)
    $ctx.Load($Properties)
    $ctx.ExecuteQuery()
    $peopleManager.SetSingleValueProfileProperty($logIn, "AboutMe", $AboutText)
    $ctx.ExecuteQuery()
}
}

Function Get-UserProperties{
Param(
    [string]$UserLogin
)
Process{
    $logIn = ("i:0#.f|membership|" + $UserLogin)
    $ctx = $Global:oContext
    $peopleManager = New-Object Microsoft.SharePoint.Client.UserProfiles.PeopleManager($ctx)
    $user = $peopleManager.GetPropertiesFor($logIn)
    $ctx.Load($user)
    $ctx.ExecuteQuery()
    Write-Output $user.UserProfileProperties

}
}

---

The first thing I do is to load the client dlls. Second, I am getting the client context, as I said against the admin portal and putting it as global variable for further reuse.
After we are ready we can start editing the properties and here is the outcome:

It seems that this worked out. The updated About Me property is shown in Gencho's profile as well.

Display CSOM objects in PowerShell

Microsoft strategy for SharePoint 2013 and their offer for Hosted SharePoint as a service SharePoint Online is to bring the power of the full trust code on the client side. Since we are unable to deploy full trust code in SharePoint Online via the good old solution packages, Microsoft came up with the App model in SharePoint 2013 in order to make the customisation of SharePoint less dependent on the full trust code that is executed on the server. The underlying supporting technologies that are allowing SharePoint Apps and clients to interact with the SharePoint platform "remotely" are client-side object model (CSOM), JavaScript object model (JSOM) and REST. With this technologies you don't need to be on the SharePoint server to read properties or execute methods on SharePoint objects like Webs,Sites,Lists,Users and so on. You can work with this objects from your browser, from a compiled application,  or in my case from the PowerShell of my Windows 8.1 workstation for example.

As SharePoint Administrator I had a few interaction with SharePoint Online and I was disappointed by the limited functionality and control I had with the few commands that the native SharePoint Online Management Shell has. The answer for me to automate some repetitive tasks or to get clear view on the objects, in order to create some reports for example, was to utilize some client-side technology.

So, as Administrator the JavaScript object model is not my primary interest, I can invoke REST requests from PowerShell, but the most similar to the server-side code(that I am used to) is the client-side object model or CSOM.

In this post I am not going to do an in depth guide on how to write PowerShell scripts with CSOM, but I am going to show you a significant difference between CSOM and the server-side code in PowerShell, that can demotivate you to dig further into CSOM if you are new to it. Also I am going to show you how to correct this in certain extension and make CSOM less "abstract" when you work with it in PowerShell.

For the demonstration I am going to use on-premises installation of SharePoint 2013 and the sample function below that can list all webs(the subwebs) under the root web.

---

Function Get-SPClientSiteSubWebs{
 [CmdletBinding()]
 Param(
    [string]$SiteUrl
 )
 PROCESS{
    $clientContext = New-Object Microsoft.SharePoint.Client.ClientContext($SiteUrl)

    $rootWeb = $clientContext.Web
    $clientContext.Load($rootWeb)
    $clientContext.ExecuteQuery()

    $webs = $rootWeb.Webs
    $clientContext.Load($webs)
    $clientContext.ExecuteQuery()

    Write-Output $webs
 }
}

---

I will be running the function from client VM that is joined in the domain of my test SharePoint 2013 environment. So in order to use CSOM from computer that is not a SharePoint server you will need the Client Assemblies. You can find the DLLs in every SharePoint 2013 server in the folder "C:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\ISAPI", they all start with Microsoft.SharePoint.Client*.dll. Or you can download and install the SharePoint 2013 Client Components SDK, this way you will have the dlls in \15\ISAPI folder, also I think most of them will be registered in GAC. After you get the client DLLs you will need them loaded into the PowerShell session where you will run your scripts, now I already have done this and lets see what will be the result from my sample function. I will need to give a site collection as parameter and since we are running the function against on-premise SharePoint with sitecollection hosted in Web Application that is using Windows Integrated authentication we do not need additional credentials or change the authentication mode for the client context, this however will not be the case if we write functions for SharePoint Online or if we are using different from Windows authentication in on-premises. So it seems that we are ready to test.

Error:

---

format-default : The collection has not been initialized. It has not been requested or the request has not been executed. It may need to be explicitly requested.

---

We are receiving no useful result and a couple of red lines, which almost everytime means fail in powershell.
But when I pipe the command to Measure-Object the number of object is different than 0, and here is one of the big differences with CSOM.
Lets see how the function works. First we are getting the Client context from the URL we have passed. Then we are getting the Root Web which is the property Web, then we should get all the subwebs of the root web by getting the property Webs. But in CSOM you need to use the generic method Load() and then to call ExecuteQuery(). We cannot get objects from other objects on the fly as it is with the client-side code. Apparently our functions is working fine, but the Web objects are returned with only part of their properties and for the rest we need to load them with Load() and ExecuteQuery(). You can see this by piping to Select-Object and select Title and Url properties.

So our function is working fine, but first time we ran it the PowerShell failed to display the webs. This is because PowerShell used the default type display template to display Microsoft.SharePoint.Client.Web Type including some properties that are not loaded. For example SiteUsers, if we want to see the users we will need to load them. If we try to get this property from one of the webs we are going to receive below error:

---

An error occurred while enumerating through a collection: The collection has not been initialized. It has not been requested or the request has not been executed. It may need to be explicitly requested..

---

The PowerShell is using the default template because there are no display templates for Microsoft.SharePoint.Client types.
PowerShell display templates are XML files with extension .ps1xml. The display templates for the different types can be configured as list or table. Some of the files are stored in "C:\Windows\System32\WindowsPowerShell\v1.0" directory. If you open one of this files you are going to see warning that this file should not be edited but we have reference to the cmdlet Update-FormatData.
With this command we can load additional display templates for the Types that we want.
I have created such .ps1xml with templates for the most popular client objects. I can load it with below command.

---

Update-FormatData -AppendPath "C:\SPO\SPClient.Format.ps1xml"

---

Now when we have loaded our custom display templates lets see what will be the output from our function.

I think it is looking better now. You can download the template file below. You can change it and expand to suit your needs. The display templates will be loaded only for the current session. If you close your PowerShell. Next time when you or another user try to use CSOM you will need to load the templates again.

Download Here