Tag: SharePoint Online

Add-PNPField: Add SharePoint columns with PowerShell
Sometimes it’s hard enough to map the exact business requirements on the intranet. Implementing them manually afterwards can lead to the requirements not being implemented exactly in the further course. Also doing it manually is very time consuming and frustrating, when mistakes are made. This is where automation comes in handy. If you are looking forward to automate your intranet, it is crucial to design lists by adding them programtically. When you add SharePoint columns with PowerShell, you can be sure, that mistakes are not made like choosing the wrong column type or wrong internal name. You basically ensure that your intranet follows the standards, which have been defined in the beginning by your stakeholders. Thus I thought it might be interesting to share my experiences with you. All my descriptions have been tested and can be used on lists and libraries.

Prerequisites

You have to have access to the sites, where you want to add the SharePoint columns with PowerShell. Check out the article of Microsoft, if you are not sure, how to customize the permissions: Customize permissions for a SharePoint list or library – SharePoint (microsoft.com).

To have a understanding, how lists / libraries can be designed, will help you to automate the procedures. If you are not familiar with this, try out creating and defining a list manually, to know what options are available.

Scenario

Lets assume, we are looking forward, to create a list with various column types.

The beginning will be this plank list:

Description

In the following, I will show you step by step how to add the SharePoint columns with PowerShell.

Step 1 – Connect to SharePoint

In order to add SharePoint Columns with PowerShell, you have to connect to SharePoint Online with PNP. PowerShell. If you are not sure about how to, check out: Connect to SharePoint Online with PowerShell (workplace-automation.com/)
```
$Credential = Get-Credential
Connect-PnPOnline -Url "https://devmodernworkplace.sharepoint.com/sites/sales" -Credential $Credential
```
Step 2 – Get the List

Get the SharePoint list or library with Get-PNPList
```
$List = Get-PnPList -Identity "ID of the list"
```
```
$List = Get-PnPList -Identity "978f0ca5-7cc9-4151-8ba0-2fc45d736723"
```
You can find the internal name of the list by running Get-PNPList.

Step 3 – Add SharePoint Columns

After we have connected to the site and got the list/ library, we can add the desired fields. If you don’t want to add the fields to the default view, remove the paramter -AddToDefaultView.

I have used $DisplayName also for the paremter InternalName, since we cannot ensure, that the name for the internal name will be set, as you can see in the screenshot:

Add String Column
```
$DisplayName = "Delivery address (Customer)"

Add-PnPField -List $List -DisplayName $DisplayName -InternalName $DisplayName -Type Text -AddToDefaultView
```
The result of the string column looks like this:

Add Date Colum with Time

If you want to add the date column, without time, you can do it like this:
```
$DisplayName = "Date"
Add-PnPField -List $List -DisplayName $DisplayName -InternalName $DisplayName -Type DateTime -AddToDefaultView
```
The result of the date column with time looks like this:

Add Date Column without Time

If you want to add the date column without time, you can do it like this:
```
$DisplayName = "Date without time"
$PNPField = Add-PnPField -List $List -DisplayName $DisplayName -InternalName $DisplayName -Type DateTime -AddToDefaultView
[XML]$SchemaXml = $PNPField.SchemaXml
$SchemaXml.Field.SetAttribute("Format","DateOnly")

Set-PnPField -List $List -Identity $PNPField.Id -Values @{SchemaXml =$SchemaXml.OuterXml} -UpdateExistingLists
```
The result of the date column looks like this:

Add Lookup Column

If you want to add lookup columns, you have to know the ID of the list and the Internal Name of the column, which you want to look up.

Let’s assume, that we will lookup the email address from the list Contacts.

The ID of the list contact is: a66ff40f-ceca-4f6b-a523-7fe32a97ea11

For the email column, you other can do it with GUI: Determine internal name of SharePoint Columns with GUI – SPO Scripts or with PowerShell:
```
Get-PnPField -List $List

#Or

Get-PNPField -List "INTERNALNAME OFLIST"
```
Now you can add the column email address to the opportunity list.
```
$DisplayName = "Email"
$PNPField = Add-PnPField -List $List -DisplayName $DisplayName -InternalName $DisplayName -Type Lookup  -AddToDefaultView
Set-PnPField -List $List -Identity $PNPField.Id -Values @{LookupList= "a66ff40f-ceca-4f6b-a523-7fe32a97ea11"; LookupField="Email"}
```
The result of the lookup column looks like this:

Add Choice Column

First you define the choices, than you add the column.
```
$Choices = (
"Choice 1",
"Choice 2",
"Choice 3"
)

$DisplayName = "Choice"
$PNPField = Add-PnPField -List $List -DisplayName $DisplayName -InternalName $DisplayName -Type Choice -AddToDefaultView -Choices $Choices
```
Result:

Add Boolean Column
```
$DisplayName = "Win"
$PNPField = Add-PnPField -List $List -DisplayName $DisplayName -InternalName $DisplayName -Type Boolean -AddToDefaultView
```
The result of the boolean column looks like this:

Add Number Value
```
$DisplayName = "Number of Stakeholder"
$PNPField = Add-PnPField -List $List -DisplayName $DisplayName -InternalName $DisplayName -Type Number -AddToDefaultView
```
The result of the number column looks like this:

Add Currency Column
```
$DisplayName = "Deal Size"
$PNPField = Add-PnPField -List $List -DisplayName $DisplayName -InternalName $DisplayName -Type Currency -AddToDefaultView
```
The result of the currency column looks like this:

Add Notes Column
```
$DisplayName = "Notes"
$PNPField = Add-PnPField -List $List -DisplayName $DisplayName -InternalName $DisplayName -Type Note -AddToDefaultView
```
Result:

Add Location Column
```
$DisplayName = "Location"
$PNPField = Add-PnPField -List $List -DisplayName $DisplayName -InternalName $DisplayName -Type Location -AddToDefaultView
```
The result of the location column looks like this:

Add Person / User Column (single person)
```
$DisplayName = "Sales Manager"
$PNPField = Add-PnPField -List $List -DisplayName $DisplayName -InternalName $DisplayName -Type User -AddToDefaultView
```
The result of the single people column looks like this:

Add Person / User Column (multiple persons)
```
$DisplayName = "MultiPerson"
$PNPField = Add-PnPField -List $List -InternalName $DisplayName -DisplayName $DisplayName -Type User -AddToDefaultView 
[XML]$SchemaXml = $PNPField.SchemaXml

$SchemaXml.Field.SetAttribute("Mult","TRUE")
$OuterXML = $SchemaXml.OuterXml.Replace('Field Type="User"','Field Type="UserMulti"')
Set-PnPField -List $List -Identity $PNPField.Id -Values @{SchemaXml =$OuterXML} -UpdateExistingLists
```
The result of the multi people column looks like this:

Add Hyperlink Column
```
$DisplayName = "Url to Invoice"
$PNPField = Add-PnPField -List $List -DisplayName $DisplayName -InternalName $DisplayName -Type  -AddToDefaultView
```
The result of the hyperlink column looks like this:

Add Managed Metadata Column

Retrieve the Unique Identifier of the term set, which you want to add as a metadata column.

Visit admin center url and follow the numbers in the screenshot:

https://tenant-admin.sharepoint.com

Now add it to TaxononmyItemID and run the cmdlets
```
$DisplayName = "Departments"
Add-PnPTaxonomyField -List $List -DisplayName $DisplayName -InternalName $DisplayName -TaxonomyItemId f076462d-bde7-4fa4-aa7b-4409a769fcd1
```
The result is, that the department column, pops up as a managed metadata column.

Add Calculated Column

You can add a calculated column by defining a formular per each column. Check the reference, which shows you, how to define a formular: Examples of common formulas in lists – SharePoint (microsoft.com)

Note: When you define the formular, you have to take the displayname. Internal names do not work currently.

Otherwise, you see this error:
```
$DisplayName = "Revenue per stakeholder"
$PNPField = Add-PnPField -List $List -DisplayName $DisplayName -InternalName $DisplayName -Type Calculated -AddToDefaultView -Formula ="[Deal Size]/[Number of Stakeholder]"
```
The result of the calculated column looks like this:

Add Image Column
```
$DisplayName = "Logo of customer"
$PNPField = Add-PnPField -List $List -DisplayName $DisplayName -InternalName $DisplayName -Type Thumbnail -AddToDefaultView
```
The result of the image column, looks like this:

Add Task Outcome Column

The task outcome column is designed to define the outcome of tasks like approvals. So basically it is a choice column, but you cannot allow fill-in choices and it is a single selection choice.

I will define following choices:

approved
rejected
to be reviewed by supervisor

The default is empty. I want the people to make conscious decisions.
```
[array]$Choices = (
"approved",
"rejected",
"to be reviewed by supervisor"
)
$DefaultChoice = ""

$Choices = $Choices |ConvertTo-Xml -NoTypeInformation
$Choices = $Choices.Objects.OuterXml
$Choices =  $Choices -replace "Objects", "CHOICES"
$Choices =  $Choices -replace "Object", "CHOICE"



$FieldXML = @"
<Field RowOrdinal="0" ColName="nvarchar15" Name="$DisplayName" StaticName="$DisplayName" SourceID="{$($List.Id)}" ID="{$([guid]::NewGuid())}" Indexed="FALSE" Viewable="TRUE" EnforceUniqueValues="FALSE" Required="FALSE" DisplayName="$DisplayName" Type="OutcomeChoice">
    <Default>$DefaultChoice</Default>
    $($Choices)
</Field>
"@
$PNPField = Add-PnPFieldFromXml -List $List -FieldXml $FieldXML 
```
If you want to make the field visible in the default view, you have to run following cmdlets:
```
$PNPView = Get-PnPView -List $List | Where-Object {$_.DefaultView -eq $true}
$PNPView.ViewFields.Add($PNPField.InternalName)
$PNPView.Update()
```
The result of our task outcome column looks like this:

Bonus: Complete Script

Like everytime, I have provided you the whole script to add a column:
```
$Credential = Get-Credential
Connect-PnPOnline -Url "https://devmodernworkplace.sharepoint.com/sites/sales" -Credential $Credential
$List = Get-PnPList -Identity 978f0ca5-7cc9-4151-8ba0-2fc45d736723


$DisplayName = "Departments"
$PNPField = Add-PnPField -List $List -DisplayName $DisplayName -InternalName $DisplayName -Type  -AddToDefaultView
```
Conclusio

As you can see, adding SharePoint Columns with PowerShell can save you a lot time, especially if you have to do it on multiple lists/ libraries and sites. I hope to save you a ton of work with this cheat sheet.

References

GitHub – pnp/powershell: PnP PowerShell

FieldCollection.AddFieldAsXml-Methode (Microsoft.SharePoint.Client) | Microsoft Docs

Image by StartupStockPhotos from Pixabay
July 25, 2021

Add Items to SharePoint Online lists with PowerShell

When you want to integrate 3rd party systems to SharePoint, you might need to add items to SharePoint Online lists with PowerShell. In this article, you learn, how to do it for each data type.

Use Case

I want to add new oppurtunities as items to my sales list. The aim is to add all values for this various items.

Step 1 – Connect to SharePoint Online

In order to add items to SharePoint Online lists with PowerShell, you have to connect to SharePoint with PNP.PowerShell. If you never herad of it, read this article: Connect to SharePoint Online with PowerShell (workplace-automation.com/)

Since I want to use it for automation purposes, I am using a credential object.

param
(
    $SiteUrl = "https://devmodernworkplace.sharepoint.com/sites/Sales"
)

$Credential = Get-Credential

Connect-PnPOnline -Url $SiteUrl -Credential $Credential

You’ll be prompted for credentials. If you want to prevent prompting, make use of following method:

Step 2 – Get your list

I want to add the items to the opportunites list, and since I know the internal name of the list, I can get it like this.

$ListName = "Opportunities"
$List = Get-PnPList -Identity $ListName

If you are not sure about your list name, you can also get your list interactively name like this.

Get-PnPList | Out-GridView -PassThru

Take the ID or Name of the list and put it in the script.

If you didn’t know about the interactive way of getting items, checkout the post: How to filter for PowerShell objects easily – SPO Scripts

Step 3 – Get the internal field values of your list

In order to code according to the right syntax, you have to know the internal name of the columns. To do this, checkout the post: Getting FieldValues of Items – SPO Scripts

So in this case I am getting the last item and retrieving the field values.

$Item = Get-PnPListItem -List $List  | Select-Object -Last 1 
$Item.Fieldvalues

You have to create a hashtable of the desired columns you want to fill.

$Values = @{
"KEY"= "VALUE"
}

In my example it is

$Values = @{
"Title"= "Automation"
"Stakeholder" = 2
}

Step 4 – Add your Item

So now it gets exciting. You can add your items like this.

$Values = @{
"Title"= "Automation"
"Stakeholder" = 2
}

Add-PnPListItem -List $List -Values $Values

In the following I will show you how to add the values for each column type.

Add String value

Adding string is the simplest way. You have to do it like this:

$Values = @{
"Title"= "Automation"
"Stakeholder" = 2
}

Add-PnPListItem -List $List -Values $Values

The result:

Add Integer value

You have to put the value without quotes.

$Values = @{
"Stakeholder" = 2
}

Add-PnPListItem -List $List -Values $Values

The result:

Add Multi line text value

When a new line starts, you just can make use of enter to create a new line.

$Values = @{
"Notes"= "Customer was very glad,

Hopefully he closes the deal"
}

Add-PnPListItem -List $List -Values $Values

The result:

Add Boolean Value

You can use the variables $true and $false for describing the value

$Values = @{
"Win"= $true
}

Add-PnPListItem -List $List -Values $Values

The result:

Add Link value

$Values = @{
"Linktocontract"= "https://workplace-automation.com/"
}

Add-PnPListItem -List $List -Values $Values

The result:

Add Date Value

Caution: You have to consider that the date, you have entered is your timezone. The date will be parsed and transfered to universal time. If you want it in your time, you have to add the amount of hours depending on your timezone offset.

I live in Germany, so I add a offset of 2 hours (summer time).

Add Date Value (no time)

$Date = get-date 10/07/2021
$Date = $Date.AddHours(2)

$Values = @{
"Date" = $Date
}

Add-PnPListItem -List $List -Values $Values

If you live in universal timezone, you can even add it like this. Checkout your timezone here: Default Time Zones | Microsoft Docs

$Values = @{
"Date" = "10/07/2021"
}

Add-PnPListItem -List $List -Values $Values

The result:

Add Date value (with time)

$Date = Get-Date "05:01 10/07/2021"

$Values = @{
"Date" = $Date
}

Add-PnPListItem -List $List -Values $Values

The result:

Add Currency Value

It is very similar to integer values

$Values = @{
"DealSize" = 8000
}

Add-PnPListItem -List $List -Values $Values

The result:

Add Metadata Value

We assume following Term store:

Add Metadata Value (single value)

We have to get the term, add it to the array departments and add the array to the hash table values.

$Values = @{}
$Departments = @()

Get-PnPTerm -TermGroup 'Company Terms' -TermSet 'Departments' -Identity 'HR' |
 Select-Object id -ExpandProperty id  |
 ForEach-Object {$Departments += ($_.guid).tostring() }

$Values.Add('Customer_x0020_Department', $Departments)

Add-PnPListItem -List $List -Values $Values

The result:

Add Metadata Value (multiple values)

We have to get the terms, add them to the array departments and add the array to the hash table values.

$Values = @{}
$Departments = @()

Get-PnPTerm -TermGroup 'Company Terms' -TermSet 'Departments' -Identity 'HR' |
 Select-Object id -ExpandProperty id  |
 ForEach-Object {$Departments += ($_.guid).tostring() }

Get-PnPTerm -TermGroup 'Company Terms' -TermSet 'Departments' -Identity 'Legal' |
 Select-Object id -ExpandProperty id  |
 ForEach-Object {$Departments += ($_.guid).tostring() }

$Values.Add('Customer_x0020_Department', $Departments)

Add-PnPListItem -List $List -Values $Values

The result:

Add Person Field Value

Add Person Field Value (single person)

You can achieve adding persons to a person field by the mail address

$Values = @{
"SalesManager" = "Serkar@devmodernworkplace.onmicrosoft.com"
}

Add-PnPListItem -List $List -Values $Values

The result:

Add Person Field Value (multiple persons)

You can achieve adding persons to a person field by the mail adress Syntax Highlighter

$Values = @{}

$Persons = @("Serkar@devmodernworkplace.onmicrosoft.com", "aaa@devmodernworkplace.onmicrosoft.com")
$Values.Add('SalesManager', $Persons)

Add-PnPListItem -List $List -Values $Values

The result:

Add Lookup value

First you got to determine the ID of the value, which you want to lookup in the other list. After determining it, you can add the looked up attribute to your item.

$Account= Get-PnPListItem -List "Accounts" | Where-Object {$_.Fieldvalues.Title -eq "Füller AG"}

$Values = @{
"Account"= $Account.id
}

Add-PnPListItem -List $List -Values $Values

The result:

Add Location value

This one is the trickiest. You have to know the complete adress and coordinates.

$Address = @{
"Street" = "Westenhellweg 36"
"City" = "Bergkamen"
"State" =  "Nordrhein-Westfalen"
"CountryOrRegion" = "Germany"
"PostalCode" = "59192"
}

$Coordinates = @{
"Longitude" = [double]"7.6405439376831055"
"Latitude" = [double]"51.646881103515625"
}

$DisplayNameLocation = $Adress["Street"]+ ", " + $Adress["Postalcode"] + " " +$Adress["City"]

$Location = @{
"DisplayName" = $DisplayNameLocation
"Address"= $Address
"Coordinates" = $Coordinates
}

$Location = $Location | ConvertTo-Json

$Values = @{
"LocationofDeal"= $Location
}

Add-PnPListItem -List $List -Values $Values

The result:

Bonus: Adding all types of values in one item

param
(
    $SiteUrl = "https://devmodernworkplace.sharepoint.com/sites/Sales"
)

$Credential = Get-Credential

Connect-PnPOnline -Url $SiteUrl -Credential $Credential

$ListName = "Opportunities"
$List = Get-PnPList -Identity $ListName

<# Determine internal names of columns

$Item = Get-PnPListItem -List $List -Id 4
$Item.Fieldvalues
#>

$Date = Get-Date "05:01 10/07/2021"

$Values = @{
"Title" = "Automation"
"Stakeholder" = 2
"Notes"= "Customer was very glad,

Hopefully he closes the deal"
"Win"= $true
"Linktocontract"= "https://workplace-automation.com/"
"Date" = $Date
"DealSize" = 4000
}

$Account= Get-PnPListItem -List "Accounts" | Where-Object {$_.Fieldvalues.Title -eq "Füller AG"}
$Persons = @("Serkar@devmodernworkplace.onmicrosoft.com", "aaa@devmodernworkplace.onmicrosoft.com")

#region define departments
$Departments = @()

Get-PnPTerm -TermGroup 'Company Terms' -TermSet 'Departments' -Identity 'HR' |
 Select-Object id -ExpandProperty id  |
 ForEach-Object {$Departments += ($_.guid).tostring() }

Get-PnPTerm -TermGroup 'Company Terms' -TermSet 'Departments' -Identity 'Legal' |
 Select-Object id -ExpandProperty id  |
 ForEach-Object {$Departments += ($_.guid).tostring() }

#endregion

#region define location 
$Address = @{
"Street" = "Westenhellweg 36"
"City" = "Bergkamen"
"State" =  "Nordrhein-Westfalen"
"CountryOrRegion" = "Germany"
"PostalCode" = "59192"
}

$Coordinates = @{
"Longitude" = [double]"7.6405439376831055"
"Latitude" = [double]"51.646881103515625"
}

$DisplayNameLocation = $Adress["Street"]+ ", " + $Adress["Postalcode"] + " " +$Adress["City"]

$Location = @{
"DisplayName" = $DisplayNameLocation
"Address"= $Address
"Coordinates" = $Coordinates
}

$Location = $Location | ConvertTo-Json
#endregion

$Values.Add('Customer_x0020_Department', $Departments)
$Values.Add('SalesManager', $Persons)
$Values.Add('Account',$Account.Id)
$Values.Add('LocationofDeal',$Location)

Add-PnPListItem -List $List -Values $Values

Conclusio

As you can see, you can add item to SharePoint Online lists with PowerShell with the scripts I provided. I hope I have saved you a ton of work :).

Photo by Conscious Design on Unsplash

July 22, 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

Tag: SharePoint Online

Add-PNPField: Add SharePoint columns with PowerShell

Prerequisites

Scenario

Description

Step 1 – Connect to SharePoint

Step 2 – Get the List

Step 3 – Add SharePoint Columns

Add String Column

Add Date Colum with Time

Add Date Column without Time

Add Lookup Column

Add Choice Column

Add Boolean Column

Add Number Value

Add Currency Column

Add Notes Column

Add Location Column

Add Person / User Column (single person)

Add Person / User Column (multiple persons)

Add Hyperlink Column

Add Managed Metadata Column

Add Calculated Column

Add Image Column

Add Task Outcome Column

Bonus: Complete Script

Conclusio

References

Add Items to SharePoint Online lists with PowerShell

Use Case

Step 1 – Connect to SharePoint Online

Step 2 – Get your list

Step 3 – Get the internal field values of your list

Step 4 – Add your Item

Add String value

Add Integer value

Add Multi line text value

Add Boolean Value

Add Link value

Add Date Value

Add Date Value (no time)

Add Date value (with time)

Add Currency Value

Add Metadata Value

Add Metadata Value (single value)

Add Metadata Value (multiple values)

Add Person Field Value

Add Person Field Value (single person)

Add Person Field Value (multiple persons)

Add Lookup value

Add Location value

Bonus: Adding all types of values in one item

Conclusio

Term label propagation in SharePoint Online

Problem

How terms are propagated to sites

Workaround

Conclusio

Further Documentation

Dealing with existing SharePoint connections

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

Filtering for SharePoint items with CAML Queries

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