Author: Serkar

How to use Word and Excel templates in SharePoint

If you are using SharePoint and want to ensure, that your end users make use of your templates in SharePoint, you can make use of content types, to use word and excel templates in SharePoint.

Prerequisites

If you want to create use templates in your across multiple SharePoint sites, you need access to the SharePoint Admin center otherwise you just can create it for single sites.

You can use templates for word and excel files, which don’t have macros. If you have macros in your templates, you cannot use them as template for your content type.

What are content types?

You can imagine a content type as an object with meta data, which describes an object.

Example: invoice.

Meta data for an invoice are e.g. invoice date and due date.

I will show you how to configure content types in your content type hub, so you can make use of word and excel templates in SharePoint!

Create a template

Before we start to use templates in SharePoint, we have to define a template. For our invoice, I took a template of Microsoft.

Invoice template

Create content type in SharePoint Admin Center

To create a content type visit the admin center

https://yourdomain-admin.sharepoint.com

for my domain it is

https://devmodernworkplace-admin.sharepoint.com/

Click on Content services -> Content type gallery

Click on create a content type

Give it a name, optionally a description define a category and set document content types as parent category and document as content type.

I chose to locate my content type invoice in the new category “DevModernWorkplace” category. You can also use the exisiting category document content types category. I would recommend a new category, so you see all your custom content types at one place.

After your content type is created, I would recommend to add site columns. These site columns will show up in the library, where you will use this content type.

Add site columns to content type

I have added the meta data due date from the existing site columns and added invoice date as new site columns and added the new site column to the new dedicated category “Invoice Columns”.

Add existing column

Create new site column

Add template to content type

Now you can add your previously created template to your content type. Click on Settings and then on Advanced settings.

Click on upload a new document template and browse to your template. After selecting it, save it.

Publish content type

After adding the template, publish your content type.

Republish content type

If you already published your content type, click on Publish.

Click on Republish

Switch to the site, where you have added the content type and visit the site settings. I am switching back to the sales site.

Click on Content type publishing.

Check Refresh all published content types on next update and click on OK.

Add content type to your list

I have added a list invoices to the demo site and as you can see the default content types are configured.

In order to add the invoice template, click on the gear -> Library settings.

Click on Advanced settings

Set Allow management of content types? to Yes, scroll down to “Ok” to save your settings.

Add your content type by clicking on Add from existing site content types

If you don’t see your content type, ensure that you have published the content type and if you have published it, wait 5-10 minutes.

After confirming with OK, you can see the invoice content type

Bonus: Adding Metadata to your template

If you want to add metadata to your Word/ Excel template, create a document with your configured content type.

Click on editing -> Open in the Desktop App

Mark the area, where you want to add the metadata.

Click on tab Insert -> Quick Parts -> Document Property -> “Your Metadata”

I chose Invoice date

I have added invoice date and due date

Save the file locally.

Now republish the template in your content type.

there we are!

If you change the metadata in SharePoint, the document gets updated automatically.

Before:

After:

Date fields in the Library and in Word app are different
Check out, the regional setting of your site. You might have different time zones configured for your client and your site.

Conclusio

Ensuring that Word and Excel templates are used in SharePoint online can be achieved by content types, published in the content type hub.

July 17, 2021
Use credentials in PowerShell
Credentials are necessary, if you want to access systems or APIs. Credentials can be used interactively and within a script. If you want to use credentials in PowerShell to automate processes, you might have to export your credentials and import them within your automation solution.

In this article I will show you a few ways to make use of credentials, how to export and import credential objects and security considerations.

Functionality

You export the credentials with PowerShell the context the user whith which you are running the cmdlets, also the machine will be considered. So if you copy the credential file to another machine, it won’t work with the same user and you have to re-export the credential file.

Security considerations

Every initialization of credentials (interactive or automated) will lead to a prompt like this. When you export credentials with PowerShell consider following advice:

Be sure, that you do this close to your authentication and do not enter your credentials on client/ servers, which are not trusted.

Why?

You might think this is safe. If we try to read the password, it looks like this:

But there is still a way to read the password:

The risk will be there, when you enter your credentials to a shell, which you are not owner of.
- Recommendation
  1. If you have to enter it in a Shell, which you do not controll, ensure, that the credential object will be initialized like this $Credential = $null
  2. If possible, try to run your scripts within windows authentication. In PNP.PowerShell it looks like this:
```
Connect-PNPOnline -Url $Url -UseCurrentCredentials
```
Using credentials interactively

This will lead to a prompt.
```
$UserName = "John.Doe@Contoso.com"
$Credential = Get-Credential ($UPN)
```
You can than make use of the credentials like this:
```
Connect-PnPOnline -Url "https://devmodernworkplace.sharepoint.com/sites/sales" -Credentials $Credential
```
Using credentials non-interactively

For this use case I wrote two ready-to-use functions.
```
Function Export-CredentialFile 
{
    param(
    [Parameter(Mandatory=$true,Position=0)]
    $Username,
    [Parameter(Mandatory=$true,Position=1)] 
    $Path
    )
    
    
    While ($Path -eq "")
    {
        $Path = Read-Host "The path does not exist. Where should the credentials be exported to?"
    }

    $ParentPath = Split-Path $Path
    If ((Test-Path $ParentPath) -eq $false)
    {
        New-Item -ItemType Directory -Path $ParentPath
    }

    $Credential = Get-Credential($Username)
    $Credential | Export-Clixml -Path $Path
    Return $Credential
}

Function Import-CredentialFile ($Path)
{
    if (! (Test-Path $Path))
    {
        Write-Host "Could not find the credential object at $Path. Please export your credentials first"
        Export-CredentialFile
    }
    Import-Clixml -Path $Path
}
```
You can call the functions like this:
```
$Path = "C:\keys\serkar.key"
$UserName = "Serkar@devmodernworkplace.onmicrosoft.com"

Export-CredentialFile -Username $UserName -Path $Path
$Credential = Import-CredentialFile -Path $Path
```
And afterwards you can connect to SharePoint. If you feel unsure about it, check out this post: Connect to SharePoint Online with PowerShell (workplace-automation.com/)
```
Connect-PnPOnline -Url https://devmodernworkplace.sharepoint.com/sites/sales -Credentials $Credential
```
But be sure to initialize the $Credential afterwards, so the potential security vector is as small as possible when you export credentials with PowerShell.

Additional links

Get-Credential (Microsoft.PowerShell.Security) – PowerShell | Microsoft Docs
July 13, 2021
How to use LINQ in PowerShell to compare arrays
LINQ stands for Language Integrated Query and if you use LINQ in PowerShell it might boost the performance of your scripts. In this article, I’ll show you how to use LINQ in PowerShell for comparing arrays.

In this article, I will show you except and intersect of LINQ. There are many more methods. If you are interested in other methods, check out the official documentation of Microsoft.

Concept of using LINQ in PowerShell

We have three amounts red, orange and yellow. The aim is to only get a specific amount e.g. only the red portion.
```
$Red = @( "A", "B", "C")
$Yellow = @("C","D","E")
```
We also can do this for integer values.
```
$Red = @( 1..5)
$Yellow = @(4..10)
```
Using LINQ for Strings in arrays

I will show you how to use LINQ for string in this chapter. We will cover the methods except and intersect.

String – Get the red amount

In order to get the red amount, you have to do following:
```
$Red = @( "A", "B", "C")
$Yellow = @("C","D","E")

$Left  = [string[]]$Red
$Right = [string[]]$Yellow

[string[]][Linq.Enumerable]::Except($Left, $Right)
```
By doing this, you’ll get only A and B:

String – Get the yellow amount

In order to get the red amount, you have to change Right and Left in the LINQ cmdlet:
```
$Red = @( "A", "B", "C")
$Yellow = @("C","D","E")

$Left  = [string[]]$Red
$Right = [string[]]$Yellow

[string[]][Linq.Enumerable]::Except($Right, $Left)
```
By doing this, you’ll get only D and E:

String – Get the orange amount

If we want to get the orange amount, we have to make use of intersect
```
$Red = @( "A", "B", "C")
$Yellow = @("C","D","E")

$Left  = [string[]]$Red
$Right = [string[]]$Yellow

[string[]][Linq.Enumerable]::Intersect($Left, $Right)
```
String – Get everything except orange
```
$Red = @( "A", "B", "C")
$Yellow = @("C","D","E")

$Left  = [string[]]$Red
$Right = [string[]]$Yellow

[string[]]([Linq.Enumerable]::Except($Left, $Right) + [Linq.Enumerable]::Except($Right, $Left))
```
By doing this, you’ll everything but not C:

Using LINQ for integers in arrays

I will show you how to use LINQ for integers in this chapter. We will cover the methods except and intersect.

Integer – Get the red amount

In order to get the red amount, you have to do following:
```
$Red = @( 1..5)
$Yellow = @(4..10)

$Left  = [int[]]$Red
$Right = [int[]]$Yellow

[int[]][Linq.Enumerable]::Except($Left, $Right)
```
By doing this, you’ll get only 1, 2, 3:

Integer – Get the yellow amount

In order to get the red amount, you have to change Right and Left in the LINQ cmdlet:
```
$Red = @( 1..5)
$Yellow = @(4..10)

$Left  = [int[]]$Red
$Right = [int[]]$Yellow

[int[]][Linq.Enumerable]::Except($Right, $Left)
```
By doing this, you’ll get only 6, 7, 8, 9, 10:

Integer – Get the orange amount

If we want to get the orange amount, we have to make use of intersect
```
$Red = @( 1..5)
$Yellow = @(4..10)

$Left  = [int[]]$Red
$Right = [int[]]$Yellow

[int[]][Linq.Enumerable]::Intersect($Right, $Left)
```
Integer – Get everything except orange
```
$Red = @( 1..5)
$Yellow = @(4..10)

$Left  = [int[]]$Red
$Right = [int[]]$Yellow

[int[]]([Linq.Enumerable]::Except($Left, $Right) + [Linq.Enumerable]::Except($Right, $Left))
```
By doing this, you’ll everything but not 5:

Read more
July 8, 2021

Remove SharePoint Sites fastly

Howdy guys, sometimes we create ton of sites just for testing. A clean tenant is a must for an efficient management. Have you ever removed sites by hand? It took me at least 18 seconds to remove ONE site restless by hand. Come on, there MUST be a way to do it faster. In this article I want to show you how you can remove SharePoint sites fastly by using Out-GridView.

Step 1 – Connect to SharePoint Online with PowerShell

In order to remove SharePoint Sites fastly, we connect to our admin Site with PNP PowerShell. Basically we make use of cmdlets handled in this article:

Connect to SharePoint Online with PowerShell (workplace-automation.com/)

Connect-PnPOnline https://devmodernworkplace-admin.sharepoint.com/ -Interactive

Step 2 – Remove all sites, which you don’t need

After connecting, I basically make use of Out-GridView -PassThru, to pass my selected sites for deletion. If you are not familiar with Out-GridView check following article. It will help definitelly:

Get Items / Files interactively – SPO Scripts

Get-PnPTenantSite -IncludeOneDriveSites -Detailed  | Out-GridView -PassThru | ForEach-Object { 

    if ($_.Template -eq "GROUP#0") 
    {
        try
        {
            Remove-PnPMicrosoft365Group -Identity $_.GroupID 
            Write-Host "Removed M365 Group $($_.GroupID)" -ForegroundColor Green
        }
        catch
        {
            Write-Error "Could not remove M365 Group $($_.GroupID) $($Error[0].ErrorDetails.Message)"
        }
        
    }
    else
    {
        try
        {
            Remove-PnPTenantSite -Url $_.Url  -Force -ErrorAction Stop
            Write-Host "Removed Site $($_.URL)" -ForegroundColor Green
        }
        catch
        {
            Write-Error "Could not remove Site $($Error[0].ErrorDetails.Message)"
        } 
    }
}

So what will happen now? A popup will show up, where you can select the sites, which you don’t want to use. As you can see, I marked 3 sites. After clicking okay, it will be removed, but you still we see them in the recycle bin – so no need for panic ;).

Choice of sites, which have to be deleted

If you want to remove the sites without residue, you have to make use of following cmdlets. Sites, which belong to the a M365 Group will be put in recycle bin anyways.

Get-PnPTenantSite -IncludeOneDriveSites -Detailed  | Out-GridView -PassThru | ForEach-Object { 

    if ($_.Template -eq "GROUP#0") 
    {
        try
        {
            Remove-PnPMicrosoft365Group -Identity $_.GroupID 
            Write-Host "Removed M365 Group $($_.GroupID)" -ForegroundColor Green
        }
        catch
        {
            Write-Error "Could not remove M365 Group $($_.GroupID) $($Error[0].ErrorDetails.Message)"
        }
        
    }
    else
    {
        try
        {
            Remove-PnPTenantSite -Url $_.Url  -Force -ErrorAction Stop -SkipRecycleBin
            Write-Host "Removed Site $($_.URL)" -ForegroundColor Green
        }
        catch
        {
            Write-Error "Could not remove Site $($Error[0].ErrorDetails.Message)"
        } 
    }
}

Thats it! The three sites I have marked, are deleted now. In my case even restless.

Step 3 – Controll your Action

Controlling this, can be done by following cmdlet:

Get-PnPTenantSite -IncludeOneDriveSites  | Out-GridView -PassThru

You might see, that there are still sites, which are related to M365 groups. This sites wil be removed by a job afterwards, so no need to worry.

BONUS 1: Wrapped all in functions

I wrapped all the stuff in functions, so you don’t have to do it and proceed to remove SharePoint Sites fastly.

Function Invoke-SPOSiteRemoval
{
    Param
    (
        $Site,
        [Switch]$SkipRecycleBin
    )

    if ($_.Template -eq "GROUP#0") 
    {
        try
        {
            Remove-PnPMicrosoft365Group -Identity $_.GroupID 
            Write-Host "Removed M365 Group $($_.GroupID)" -ForegroundColor Green
        }
        catch
        {
            Write-Error "Could not remove M365 Group $($_.GroupID) $($Error[0].ErrorDetails.Message)"
        }
        
    }
    else
    {
        try
        {
            if ($SkipRecycleBin)
            {
                Remove-PnPTenantSite -Url $_.Url  -Force -ErrorAction Stop -SkipRecycleBin
            }
            else
            {
                Remove-PnPTenantSite -Url $_.Url  -Force -ErrorAction Stop
            }
            
            Write-Host "Removed Site $($_.URL)" -ForegroundColor Green
        }
        catch
        {
            Write-Error "Could not remove Site $($Error[0].ErrorDetails.Message)"
        } 
    }

}

Get-PnPTenantSite -IncludeOneDriveSites -Detailed  | Out-GridView -PassThru | ForEach-Object { Invoke-SPOSiteRemoval -Site $_ -SkipRecycleBin }

BONUS 2: Ready-To-Use Script

$TenantAdminUrl = "https://devmodernworkplace-admin.sharepoint.com/"
Connect-PnPOnline $TenantAdminUrl -Interactive

Function Invoke-SPOSiteRemoval
{
    Param
    (
        $Site,
        [Switch]$SkipRecycleBin
    )

    if ($_.Template -eq "GROUP#0") 
    {
        try
        {
            Remove-PnPMicrosoft365Group -Identity $_.GroupID 
            Write-Host "Removed M365 Group $($_.GroupID)" -ForegroundColor Green
        }
        catch
        {
            Write-Error "Could not remove M365 Group $($_.GroupID) $($Error[0].ErrorDetails.Message)"
        }
        
    }
    else
    {
        try
        {
            if ($SkipRecycleBin)
            {
                Remove-PnPTenantSite -Url $_.Url  -Force -ErrorAction Stop -SkipRecycleBin
            }
            else
            {
                Remove-PnPTenantSite -Url $_.Url  -Force -ErrorAction Stop
            }
            
            Write-Host "Removed Site $($_.URL)" -ForegroundColor Green
        }
        catch
        {
            Write-Error "Could not remove Site $($Error[0].ErrorDetails.Message)"
        } 
    }

}

Get-PnPTenantSite -IncludeOneDriveSites -Detailed  | Out-GridView -PassThru | ForEach-Object { Invoke-SPOSiteRemoval -Site $_ -SkipRecycleBin }

#Controlling
Get-PnPTenantSite -IncludeOneDriveSites -Detailed  | Out-GridView -PassThru

Conclusio

Using Out-GridView saves you a ton of time, when you want to remove sites in SharePoint fastly.

Further links

Microsoft Docs for the removal cmdlet Remove-PnPTenantSite (PnP.Powershell) | Microsoft Docs

Microsoft Docs for Out-Gridview Out-GridView (Microsoft.PowerShell.Utility) – PowerShell | Microsoft Docs

July 7, 2021

Term label propagation in SharePoint Online
Rome was not build on a day so isn‘t our taxonomy. Business requirements change frequently, so we have to adapt our systems to support these changes. This article will show you how to change term labels ins SharePoint Online and what to consider.

You can change term labels in SharePoint Online easily. You just have to consider the term set propagation procedure. I‘ve made some interesting experience, which I would share with you.

Problem

Following situation occurs: the term label of a customer is changing from “Quality Assurance” to “Quality Management Systems”. I changed the term label in the term store, but noticed, that the items and file metadata did not change in the SharePoint List.

I changed the term “Quality Assurance” to “Quality Management Systems”.

As you can see, the term is not updating in the list ‘Organization’

Even when editing the item, the term label stays the old one. Neither my customer, nor did understand, why it did not change.

How terms are propagated to sites

Everytime you using terms by adding a termset to a column, the terms are getting stored in the hidden taxonomy list of a site. You can find the list under following URL:

https://yourtenant.SharePoint/lists/TaxonomyHiddenList/AllItems.aspx

In my case it is

https://devmodernworkplace.sharepoint.com/sites/Sales/Lists/TaxonomyHiddenList/AllItems.aspx

As you can see, the term is still “Quality Assurance”. The Taxonomy Update Scheduler timer job will update the taxonomy list and also the term within one hour.

If you change one term label, the procedure is like this:
1. You change the term in the term store
2. The Taxonomy Update Scheduler timer job will update the taxonomy list and also the term within one hour.
3. Your items and files are getting are up to date with the newest term label
Workaround

Caution: This workaround should only be used if the change have to be fulfilled urgently.

If you have to change the term label urgently, you can remove the item from the TaxonomyHiddenList.

After removing the item, in the taxonomy hidden list, you can see the change of the term label took effect in the list:

Conclusio

When you change term labels in SharePoint, you have to be patient. The timer job will do it’s work and if not, make use of the workaround.

Further Documentation

If you don’t know what terms are, consider reading the doc of Microsoft: Introduction to managed metadata – SharePoint in Microsoft 365 | Microsoft Docs
July 4, 2021

Dealing with existing SharePoint connections

We all can Imagine the scenario. You create sites in sharepoint and now you want to edit multiple sites afterwards with PowerShell. In order to be safe, we have to check, wether an connection exists and if yes to disconnect the current connection to have a clean processing of the sites. In this article I want to show you how can achieve dealing with existing SharePoint connections. If you don’t know how to connect to SharePoint Online, check the article.

Symptoms – How I tried it first

I tried to check the connection by a normal if query, but as you can see it throws everytime an error, so the script will be halted under normal circumstances. Changing the ErroActionPreference is something you could do for sure, but I would not recommend it, if you want to handle other upcomming potential errors of the API. So as you can see dealing with existing SharePoint connections in terms of checking, wether an connection exists, is not that easy.

if ((Get-PnPConnection) )
{
    Write-Host "Connection found"
}
Get-PnPConnection : The current connection holds no SharePoint context. Please use one of the Connect-PnPOnline commands which uses the -Url argument to connect.
In Zeile:2 Zeichen:6
+ if ((Get-PnPConnection) )
+      ~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [Get-PnPConnection], InvalidOperationException
    + FullyQualifiedErrorId : System.InvalidOperationException,PnP.PowerShell.Commands.Base.GetPnPConnection
PS H:>> 
if ((Get-PnPConnection) -ne $Null )
{
    Write-Host "Connection found"
}
Get-PnPConnection : The current connection holds no SharePoint context. Please use one of the Connect-PnPOnline commands which uses the -Url argument to connect.
In Zeile:2 Zeichen:6
+ if ((Get-PnPConnection) -ne $Null )
+      ~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [Get-PnPConnection], InvalidOperationException
    + FullyQualifiedErrorId : System.InvalidOperationException,PnP.PowerShell.Commands.Base.GetPnPConnection
PS H:>> 
if ((Get-PnPConnection|out-null) -ne $Null )
{
    Write-Host "Connection found"
}
Get-PnPConnection : The current connection holds no SharePoint context. Please use one of the Connect-PnPOnline commands which uses the -Url argument to connect.
In Zeile:2 Zeichen:6
+ if ((Get-PnPConnection|out-null) -ne $Null )
+      ~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [Get-PnPConnection], InvalidOperationException
    + FullyQualifiedErrorId : System.InvalidOperationException,PnP.PowerShell.Commands.Base.GetPnPConnection
PS H:>> 
if ((Get-PnPConnection -ErrorAction SilentlyContinue) -ne $Null )
{
    Write-Host "Connection found"
}
Get-PnPConnection : The current connection holds no SharePoint context. Please use one of the Connect-PnPOnline commands which uses the -Url argument to connect.
In Zeile:2 Zeichen:6
+ if ((Get-PnPConnection -ErrorAction SilentlyContinue) -ne $Null )
+      ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [Get-PnPConnection], InvalidOperationException
    + FullyQualifiedErrorId : System.InvalidOperationException,PnP.PowerShell.Commands.Base.GetPnPConnection

Solutions

Try Catch Solution

In order to handle this situation, you have to catchup the error.

The snippet tries to check wether there is an connection and if there is one, it will proceed and disconnect it. After disconnecting it I have set the variable $connection to $null, so I can process it later on.

try 
{
    Get-PnPConnection -ErrorAction Stop
    Disconnect-PnPOnline
    $Connection = $null
}
catch 
{
    $Connection = $null
}

BONUS 1: Invoke-PNPConnection with Credential object (No MFA enforcement)

A function which handles the whole procedure of cutting of the connection and reconnecting, makes the handling easier. In this case I have added an additional check of the contents of lists, because sometimes you do connect, but experience that the webserver is not ready yet – basically you get an 403 FORBIDDEN message in PowerShell.

NOTE: This will only work If your user has no MFA enforcement. If you have MFA enabled, I have another function for you.

Function Invoke-PNPConnection ($Url, $Cred)
{
    try 
    {
        Get-PnPConnection -ErrorAction Stop
        Disconnect-PnPOnline
        $Connection  = $null
    }
    catch
    {
        $Connection  = $null
    }
    $i = 1
    while ($null -eq $Connection -and $i -le 6 -and $null -eq $Lists)
    {
        Write-Verbose "Trying to connect to $Url for the $i time" 
        $Lists = $null
        Connect-PnPOnline -Url $Url -Credentials $Cred
        $Lists = Get-PnPList -ErrorAction SilentlyContinue
        $i++
        if ($i -ne 1 -and $null -eq $Lists)
        {
            Start-Sleep -Seconds 30
            Write-Verbose "Wait 30 Seconds"
        }
    }
    Write-Verbose "Connection to $Url established"
}

You can call the function like this

$Cred = get-credential
$Url = "https://contoso.sharepoint.com/sites/Sales"
Invoke-PNPConnection -Url $Url -Cred $Cred

Bonus 2: Invoke-PNPConnection interactively (MFA enforced)

So if you use the scripts interactively (with MFA enforced users), you can make use of this function

Function Invoke-PNPConnection ($Url)
{
    try 
    {
        Get-PnPConnection -ErrorAction Stop
        Disconnect-PnPOnline
        $Connection  = $null
    }
    catch
    {
        $Connection  = $null
    }
    $i = 1
    while ($null -eq $Connection -and $i -le 6 -and $null -eq $Lists)
    {
        Write-Verbose"Trying to connect to $Url for the $i time" 
        $Lists = $null
        Connect-PnPOnline -Url $Url -Interactive
        $Lists = Get-PnPList -ErrorAction SilentlyContinue
        $i++
        if ($i -ne 1 -and $null -eq $Lists)
        {
            Start-Sleep -Seconds 30
            Write-Verbose "Wait 30 Seconds"
        }
    }
    Write-Verbose "Connection to $Url established"
}

Start the function like this

$Url = "https://contoso.sharepoint.com/sites/Sales"
Invoke-PNPConnection -Url $Url

Conclusio

There are ways to deal with the connections, you just have to think a bit OOTB 🙂

You might find intersting

If you are not familiar with connecting to SharePoint, check out this Post: Connect to SharePoint Online with PowerShell

Original article of PNP Connecting with PnP PowerShell | PnP PowerShell

Images:
Bild von Meine Reise geht hier leider zu Ende. Märchen beginnen mit auf Pixabay

Bild von vargazs auf Pixabay

Bild von Ina Hoekstra auf Pixabay

Bild von saulhm auf Pixabay

June 25, 2021

Filtering for SharePoint items with CAML Queries

Most of our times, we just need just a bunch of items, to export them or to change their values. This post should help you to show, how to handle filtering for SharePoint items. Besides filtering for SharePoint items with Where-Object, you can also make use of CAML (Collaborative Application Markup Language), which lets you get only the items, you need. It might increase the performance of your queries, when you are dealing with large amounts of data.

Where are the items, which I am looking for?

Preqrequistes

If we want to achieve filtering for SharePoint items, with a CAML query, we have to fulfill following prerequisites:

Permissions to access the list
Installed Module PNP.Powershell. If you don’t know how to, check the post .
Connection to the site via PNP.PowerShell. If you don’t know how to, check the post .

Considerations

You should take care of the case sensitivity of operands and column names
You should take care of the <view> part. Sometimes it is needed, sometimes not – so I would rely on the examples.

Query Schema

A query is structured like this

 "<View><Query><Where><LOGICAL OPERATOR><FieldRef Name='INTERNAL NAME OF COLUMN'/><Value Type='VALUE TYPE'>VALUE</Value></LOGICAL OPERATOR></Where></Query></View>"

You can find the internal name of columns in two ways:

PowerShell or GUI.

Explanation for PowerShell: Getting FieldValues of Items | SPO Scripts
Explanation for GUI: Determine internal name of SharePoint Columns with GUI (workplace-automation.com/)

Value types

Type	Meaning	Examples
Boolean	It means true or false. You can find this in yes/no checkboxes	true, false true reflects 1 false reflects 0
Choice	It reflects the choices in your sharepoint list	apple, banana
Currency	It reflects the amount of an defined currency	5$
DateTime	It reflects a timestamp	23.06.2021 15:30
GUID	Globally Unique Identifier (GUID)	6154ff96-8209-457b-86dd-ee7dcd80b584
Integer	It reflects a number as an integer	10
Lookup	Links to another list: for example an Orders list may have a lookup field that links to customers in a Customer list;	Füller AG
Note	Reflects a multi line text field. Not sortable or groupable	Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Donec odio. Quisque volutpat mattis eros. Nullam malesuada erat ut turpis. Suspendisse urna nibh, viverra non, semper suscipit, posuere a, pede.
Text	Reflects a single line text field. Sortable and groupable. Corresponds to the nvarchar SQL data type and represented by the SPFieldText class.	Lorem ipsum dolor sit amet, consectetuer adipiscing elit.
User	A Lookup field that references the UserInfo database table.	Email TypeId LookupId LookupValue —– —— ——– ———– Serkar@devmodernworkplace.onmicrosoft.com {c956ab54-16bd-4c18-89d2-996f57282a6f} 11 Serkar Aydin

Source: Field element (Field) | Microsoft Docs

Logical Comparison Operators

In this case X means your entry

Operator	Meaning
BeginsWith	The existing value begins with X
Contains	The existing value contains x
DateRangesOverlap	The existing date overlaps the date range defined in x `\|---- 01.01.-07.01 ------\|` `\|---02.01-09.01 -----\|`
Eq	The existing value equals x
Geq	The existing value is greater or equal x
Gt	The existing value is greater than x
In	X is one of the existing values
Includes	checks, whether x is in the defined values
NotIncluses	checks, whether x is not in the defined values
IsNotNull	Checks wheter the existing value is not null
IsNull	Checks wheter the existing value is null
Leq	The existing value is lower equal x
Lt	The existing value is lower than x

Source: Query schema in CAML | Microsoft Docs

In order to filter by query paramter, you have to define a filter query, depending on your datatype (string, integer, boolean..) you have to choose a different query value type.

Logical Joins

Operator	Meaning
And	Both query operations have to be fulfilled
Or	Only one query operation have to be fulfilled

Source: Query schema in CAML | Microsoft Docs

Query Examples

My blog would not keep it’s promise, If you would not find examples, which give you a fast way to adapt the scripts, so here we go!

In my example, I am using my demo opportunities list. I have marked the names of the columns, the value types, the operands and the actual values bold. Mostly I am using the logical operator “eq”, but I think if you got the basic concept of this, you can adapt it to your solution easily and if not, we will find a way together.

Example for boolean

If you want to find items with TRUE values, you have to enter 1. For FALSE values, you have to make use of 0.

If boolean should be true:

Get-PnPListItem -List "Opportunities" -Query "<View><Query><Where><Eq><FieldRef Name='Win'/><Value Type='Boolean'>1</Value></Eq></Where></Query></View>"

If boolean should be false:

Get-PnPListItem -List "Opportunities" -Query "<View><Query><Where><Eq><FieldRef Name='Win'/><Value Type='Boolean'>0</Value></Eq></Where></Query></View>"

Example for choice

If you want to filter for values choice values, you have to make use of a query like this:

Get-PnPListItem -List "Opportunities" -Query "<View><Query><Where><Eq><FieldRef Name='Product'/><Value Type='Choice'>SAP</Value></Eq></Where></Query></View>"

Example for currency

You have to enter the value of the amount without the currency sign.

Get-PnPListItem -List "Opportunities" -Query "<View><Query><Where><Eq><FieldRef Name='DealSize'/><Value Type='Currency'>40000</Value></Eq></Where></Query></View>"

Example for DateTime

You have to format date times according to this format (ISO8601).

yyyy-MM-ddTHH:mm:ssZ

You can do this by appending -Format s, when creating the variable

$CreationDate = Get-Date "16.06.2021 20:04" -Format s

If DateTime should exactly match a specific date

$CreationDate = Get-Date "16.06.2021 20:04" -Format s
Get-PnPListItem -List "Opportunities" -Query "<View><Query><Where><Eq><FieldRef Name='Created'/><Value Type='DateTime' IncludeTimeValue='FALSE'>$CreationDate</Value></Eq></Where></Query></View>"

If DateTime should be after a specific date

Example: I want to find all items, created after 15.06.2021.

$CreationDate = Get-Date "15.06.2021" -Format s
Get-PnPListItem -List "Opportunities" -Query "<View><Query><Where><Gt><FieldRef Name='Created'/><Value Type='DateTime' IncludeTimeValue='FALSE'>$CreationDate</Value></Gt></Where></Query></View>"

If DateTime should be before a specific date

Example: I want to find all items, created before 15.06.2021.

Get-PnPListItem -List "Opportunities" -Query "<View><Query><Where><Lt><FieldRef Name='Created'/><Value Type='DateTime' IncludeTimeValue='FALSE'>$CreationDate</Value></Lt></Where></Query></View>"

Example for GUID

[GUID]$UniqueID= "b4ae9e9f-7103-459a-acb2-73573d035b36"
Get-PnPListItem -List "Opportunities" -Query "<View><Query><Where><Eq><FieldRef Name='UniqueId'/><Value Type='GUID'>$UniqueID</Value></Eq></Where></Query></View>"

Example for integer

In this case I want to find all opportunites with 2 stakeholders.

Get-PnPListItem -List "Opportunities" -Query "<View><Query><Where><Eq><FieldRef Name='Stakeholder'/><Value Type='Integer'>2</Value></Eq></Where></Query></View>"

Example for lookup

Get-PnPListItem -List "Opportunities" -Query "<Query><Where><Eq><FieldRef Name='Contact'/><Value Type='Lookup'>Sus Spicious</Value></Eq></Where></Query>"

Example for Note aka multi line text

Get-PnPListItem -List "Opportunities" -Query "<Query><Where><Eq><FieldRef Name='Notes'/><Value Type='Note'>He was really curious.</Value></Eq></Where></Query>"

Example for text aka string

In this Query, I am looking for items, where the title equals Opp 3.

Get-PnPListItem -List "Opportunities" -Query "<Query><Where><Eq><FieldRef Name='Title'/><Value Type='Text'>Opp 3</Value></Eq></Where></Query>"

Example for user

In this query, I am looking for items, where the authors UPN is Serkar@devmodernworkplace.onmicrosoft.com.

Get-PnPListItem -List "Opportunities" -Query "<Query><Where><Eq><FieldRef Name='Author' /><Value Type='User'>Serkar@devmodernworkplace.onmicrosoft.com</Value></Eq></Where></Query>"

Example for OR

In this query, I am looking for items, where the value for Stakeholder is 1 or the value Win is yes.

Get-PnPListItem -List $ListName -Query "<View><Query><Where><Or><Eq><FieldRef Name='Stakeholder'/><Value Type='Integer'>1</Value></Eq><Eq><FieldRef Name='Win'/><Value Type='Boolean'>1</Value></Eq></Or></Where></Query></View>"

Example for AND

In this query, I am looking for items, where the value for Stakeholder is 1 and the value Win is yes.

Get-PnPListItem -List $ListName -Query "<View><Query><Where><And><Eq><FieldRef Name='Stakeholder'/><Value Type='Integer'>1</Value></Eq><Eq><FieldRef Name='Win'/><Value Type='Boolean'>1</Value></Eq></And></Where></Query></View>"

Complete example

$Url = "https://devmodernworkplace.sharepoint.com/sites/Sales" 
$ListName = "Opportunities"

Connect-PnPOnline -Url $Url -Interactive

$AmountOfStakeholders = 2
$ColumName = "Stakeholder"

Get-PnPListItem -List $ListName -Query "<View><Query><Where><Eq><FieldRef Name='$ColumName'/><Value Type='Integer'>$AmountOfStakeholders</Value></Eq></Where></Query></View>"

Troubleshooting

I am getting to many items

Error

You get nearly every item in the list, but you are filtering for specific SharePoint items

Cause

Maybe you forgot the <View> part?

Resolution

Without view:

With view:

Get-PnPListItem -List "Opportunities" -Query "<View><Query><Where><Eq><FieldRef Name='Stakeholder'/><Value Type='Integer'>2</Value></Eq></Where></Query></View>"

Exception from HRESULT: 0x80131904

Error message:

Get-PnPListItem : Exception from HRESULT: 0x80131904
In Zeile:1 Zeichen:1
+ Get-PnPListItem -List "Opportunities" -Query "<View><Query><Where><gt ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : WriteError: (:) [Get-PnPListItem], ServerException
    + FullyQualifiedErrorId : EXCEPTION,PnP.PowerShell.Commands.Lists.GetListItem

Cause:

You did not care about the case sensitivity of the logical operands

Resolution:

Wrong:

Get-PnPListItem -List "Opportunities" -Query "<View><Query><Where><gt><FieldRef Name='Created'/><Value Type='DateTime' IncludeTimeValue='FALSE'>$CreationDate</Value></gt></Where></Query></View>"

Right:

Get-PnPListItem -List "Opportunities" -Query "<View><Query><Where><Gt><FieldRef Name='Created'/><Value Type='DateTime' IncludeTimeValue='FALSE'>$CreationDate</Value></Gt></Where></Query></View>"

Field types are not installed properly

Error message in german

Get-PnPListItem : Mindestens ein Feld ist nicht richtig installiert. Wechseln Sie zur Listeneinstellungsseite, um diese Felder zu löschen.
In Zeile:1 Zeichen:1
+ Get-PnPListItem -List "Opportunities" -Query "<View><Query><Where><Gt ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : WriteError: (:) [Get-PnPListItem], ServerException
    + FullyQualifiedErrorId : EXCEPTION,PnP.PowerShell.Commands.Lists.GetListItem

Error message in english

Get-PnPListItem : One or more field types are not installed properly. Go to the list settings page to delete these fields.
In Zeile:1 Zeichen:1
+ Get-PnPListItem -List "Opportunities" -Query "<View><Query><Where><Gt ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : WriteError: (:) [Get-PnPListItem], ServerException
    + FullyQualifiedErrorId : EXCEPTION,PnP.PowerShell.Commands.Lists.GetListItem

Cause

You did not care of the case sensitivity of the column name

Resolution

Wrong:

Get-PnPListItem -List "Opportunities" -Query "<View><Query><Where><Gt><FieldRef Name='created'/><Value Type='DateTime' IncludeTimeValue='FALSE'>$CreationDate</Value></Gt></Where></Query></View>"

Right:

Get-PnPListItem -List "Opportunities" -Query "<View><Query><Where><Gt><FieldRef Name='Created'/><Value Type='DateTime' IncludeTimeValue='FALSE'>$CreationDate</Value></Gt></Where></Query></View>"

Bild von Deedee86 auf Pixabay

June 20, 2021

Determine internal name of SharePoint Columns with GUI

You have to determine the internal name of SharePoint columns, when you want to get the values of the items programatically or develop some apps with PowerApps. If you make use of display names of columns, you might not get the values of the columns, that’s why you should use the internal name of the columns, In this article I will show you how to do it by a step-by-step description.

Description

Browse to the list, where you want to get field internal name and open the list settings.

Scroll down to the SharePoint columns and click on the column, which you want to get the internal name of. In my example I want to check it for the SharePoint Column Deal Size.

After clicking on Deal Size, I am checking the URL in the adress bar. The part after Field= is the internal name for the column. In my case it is DealSize.

If you use special characters, you will notice, that it won’t be that easy to determine the internal name of the SharePoint columns. Let me show it to you for the column Delivery address (Customer). As you can see, it got _x0020 and some other placeholder, which are not guessable easily.

You might find intersting

If you want to create a column, consider the official article of microsoft: Create a column in a list or library – SharePoint (microsoft.com)

You can deterimine the internal name of a SharePoint Column programatically like this: Getting FieldValues of Items – SPO Scripts

June 20, 2021
Getting Fieldvalues of Items
Getting all values of all items

If you want to retrieve all fieldvalues of an item, you can use following steps. I am getting the opportunities of the sales department
- Connect to SharePoint Online with PNP. If you don’t know how to, check the post. I am connecting with the sales site
  
  Connect-PnPOnline -Url "https://devmodernworkplace.sharepoint.com/sites/Sales" -Interactive
- After connecting, get the list.
  
  $Items =Get-PnPListItem -List "YourList"
  
  I am using the list opportunities:
  
  $Items =Get-PnPListItem -List "Opportunities"
  
  If you don’t know what the internal name of your list is, you can make use of the cmdlet
  
  $Items = Get-PNPListItem | Out-Gridview -Passthru
After getting the items, you can get all fieldvalues like this:
$Items.Fieldvalues

Filter items by specific value

You can filter items like this. You have to use the key, which you get with $Items. FieldValues

Filter by Where Object

$Item = $Items | Where-Object {$_.Fieldvalues["KEY"] -eq "VALUE"}

I filter for items with the title Opp 2

$Item = $Items | Where-Object {$_.Fieldvalues["Title"] -eq "Opp 2"}

Filter by -Query Parameter

I have created a separate post for this. Check it out.

Getting only specific amount of Items

It might happen, that your list contains many items. In order to get only 1000 Items, make use of the parameter -PageSize

Get-PnPListItem -List "Opportunities" -PageSize 1000

Getting a specific value for specific item

First get one single item, either by filtering, or by using the index

You can use indexing with following method

$Item= $Items[INDEX]

For the first Item: $Item = $Items[0]

For the second Item: $Item = $Items[1] etc,

If you got a single item, you can get the fieldvalues like this:

$item.FieldValues.KEY

In my case I use the key “Product”

$item.FieldValues.Product

Or “Contact”

Example script to get all fieldvalues for all items
```
$Url = "https://devmodernworkplace.sharepoint.com/sites/Sales" 
$ListName = "Opportunities"

Connect-PnPOnline -Url $Url -Interactive
$Items = Get-PnPListItem -List $ListName

$Items.Fieldvalues
```
Example script to get all fieldvalues for a specific item
```
$Url = "https://devmodernworkplace.sharepoint.com/sites/Sales" 
$ListName = "Opportunities"

Connect-PnPOnline -Url $Url -Interactive
$Items = Get-PnPListItem -List $ListName

$Item =  $Items | Where-Object {$_.Fieldvalues["Title"] -eq "Opp 2"}

$item.FieldValues
```
June 16, 2021

Author: Serkar

Prerequisites

What are content types?

Create a template

Create content type in SharePoint Admin Center

Add site columns to content type

Add template to content type

Publish content type

Republish content type

Add content type to your list

Bonus: Adding Metadata to your template

Date fields in the Library and in Word app are different

Conclusio

Functionality

Security considerations

Using credentials interactively

Using credentials non-interactively

Additional links

Concept of using LINQ in PowerShell

Using LINQ for Strings in arrays

String – Get the red amount

String – Get the yellow amount

String – Get the orange amount

String – Get everything except orange

Using LINQ for integers in arrays

Integer – Get the red amount

Integer – Get the yellow amount

Integer – Get the orange amount

Integer – Get everything except orange

Step 1 – Connect to SharePoint Online with PowerShell

Step 2 – Remove all sites, which you don’t need

Step 3 – Controll your Action

BONUS 1: Wrapped all in functions

BONUS 2: Ready-To-Use Script

Conclusio

Further links

Problem

How terms are propagated to sites

Workaround

Conclusio

Further Documentation

Symptoms – How I tried it first

Solutions

Try Catch Solution

BONUS 1: Invoke-PNPConnection with Credential object (No MFA enforcement)

Bonus 2: Invoke-PNPConnection interactively (MFA enforced)

Conclusio

You might find intersting

Preqrequistes

Considerations

Query Schema

Value types

Logical Comparison Operators

Logical Joins

Query Examples

Example for boolean

If boolean should be true:

If boolean should be false:

Example for choice

Example for currency

Example for DateTime

If DateTime should exactly match a specific date

If DateTime should be after a specific date

If DateTime should be before a specific date

Example for GUID

Example for integer

Example for lookup

Example for Note aka multi line text

Example for text aka string

Example for user

Example for OR

Example for AND

Complete example

Troubleshooting

I am getting to many items

Exception from HRESULT: 0x80131904

Field types are not installed properly

Description

You might find intersting

Getting all values of all items