Simple Template for Sharepoint Online Command Line Tool

For testing purposes in SharePoint Online (and even SharePoint on-prem) I often use a simple command line tool. Following a simple template as a good starting point.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Security;
using Microsoft.SharePoint.Client;

namespace AnyDummy
{
    class Program
    {
        static void Main(string[] args)
        {
            var url = "{add your url here}";
            var user = "{add the username here}";
            var pw = "{add the user password here}";

            var password = new SecureString();
            foreach (char c in pw.ToCharArray()) password.AppendChar(c);

            using (var ctx = new ClientContext(url))
            {
                ctx.Credentials = new SharePointOnlineCredentials(user, password);
				// comment the previous line and use the following line, when running on-premise
				// ctx.Credentials = new System.Net.NetworkCredential(user, password);
				
                Web web = ctx.Web;
                ctx.Load(web);
                ctx.ExecuteQueryRetry();
            }
        }
    }
}

 

Step by step: Line Coloring in SharePoint View

In a blog post by Wes Preston we can see how to implement line coloring for a SharePoint view. In this article, I will show the steps to do, to see the results.

First, I have a simple custom list with just to columns: the Title and a field called “Prio”. Depending on the value of the field “Prio”, the line in the view should be colored in red.

For the line coloring, I use the following script (yes, it’s copied from the post by Wes Preston) and I store the file with the script in a library called “Scripts” in my SharePoint site.

(function () {
	var overrideCtx = {};
	overrideCtx.Templates = {};

	overrideCtx.OnPostRender = [
		HighlightRowOverride
	];

	SPClientTemplates.TemplateManager.RegisterTemplateOverrides(overrideCtx);
})();

function HighlightRowOverride(inCtx) {
	for (var i = 0; i < inCtx.ListData.Row.length; ++i) {
		var listItem = inCtx.ListData.Row[i];

		var iid = GenerateIIDForListItem(inCtx, listItem);
		var row = document.getElementById(iid);

		// in this example, the internal name of the field is "Prio". Change the next
		// line depending on the structure of the list
		if (listItem.Prio == "1") {
			if (row != null) {
				row.style.backgroundColor = "rgba(255, 0, 0, 0.5)"; //red
			}
		}
	}

	inCtx.skipNextAnimation = true;
}

Next, I open the default view in my list. It is important that the field needed for coloring is available in this view (in my case the field “Prio”). When the view is opened, I start editing the page. Open the properties of the webpart and enter the JSLink in the properties pane.

It is important to add the ~site token, when the SharePoint site is hosted in SharePoint Online. Apply the changes to the webpart, end editing the page and reload the page. Depending on the data in the list, we will see some of the lines colored.

That’s all. We can use the same script for several lists or views, we only need to change the line, where the value of the field is checked.

How to switch the display language in Office 365

Office 365 and SharePoint Online are multilingual. When a SharePoint Online site is configured for different languages, the user could select his display language in the profile settings. No configuration needed in the browser settings.

When you are in any SharePoint Online site, click the user symbol on the top left and click the “About me” link.

This will open the Delve page for the user. On the Delve page click the “Update Profile” button.

On the update profile page in the bottom you can see the link “How can I change language and regional settings?”. Clicking this link will display a short hint. In this hint click the “here” link.

A new tab will open in the browser displaying the details page from the user profile. Click the “…” on top and choose “Language and Region”.

On top of the page you find the section for the language preferences. Using the “Pick a new language” combobox you can select the preferred language. You can also remove the actually selected language.

When finished, click the “Save all and close” button at the bottom of the page.

That’s all. It might take some time, until the new setting is active.

Prepare Deployment of Azure Web Jobs

An Azure Web Job could be a simple command line program. Creating such a piece of software in Visual Studio is more than simple. To deploy the program as a Web Job in a Web App in Azure, we just need a zip-file with the content of the bin\release-folder. To automate the creation of this step to prepare the deployment package, we just need a simple PowerShell script and a post-build event in Visual Studio.

The PowerShell script will look like this:

param (
	$Path,
	$DestinationPath,
	$ZipFile
)

Write-Host "Path: $Path"
Write-Host "DestinationPath: $DestinationPath"
Write-Host "ZipFile: $ZipFile"

If ((Test-Path $DestinationPath) -eq $false)
{
	Write-Host "Create path $DestinationPath"

	New-Item -ItemType Directory -Path $DestinationPath | Out-Null
}

Compress-Archive -Path $Path -DestinationPath $DestinationPath\$ZipFile -Force

Write-Host -ForegroundColor Green "Done."

Place the PowerShell script to the root folder of the project in Visual Studio.

The post-build command line will just call this script to create the package and store it in a folder in the project.

To make copy & paste easier, the post-build command line (do not copy the hash sign, just the single line of text):

#
if $(ConfigurationName)==Release (PowerShell -Command "..\..\CreateDeploymentPackage.ps1 -Path '$(TargetDir)*.*' -DestinationPath '$(ProjectDir)ZipPackage' -ZipFile $(TargetName).zip" )
#

The zip-file for the deployment to the Web Job in Azure will be stored in a sub-folder of the project. Just grab this file and use another PowerShell script or the manual way to deploy the Web Job.

That’s it.

Recreate Site Collection in SharePoint Online

During development I often need to recreate a site collection in a SharePoint Online tenant. Usually this is simply deleting the old site and creating the site again. The following script would automate these steps, using the Office 365 PnP PowerShell extensions.

But be careful, the existing site collection will be removed without any confirmation.

When the site collection was created, the Connect-PnPOnline cmdlet is called to connect to the new site collection.

param (
	[Parameter(Mandatory=$true)]
    [string]$Tenant,
	[Parameter(Mandatory=$true)]
    [string]$WebUrl,
	[Parameter(Mandatory=$true)]
	[string]$Title
)

$url = "https://$Tenant.sharepoint.com/sites/$WebUrl"
$owner = "admin@$Tenant.onmicrosoft.com"
$lcid = 1033
$template = "STS#0"
$timezone = 4
$resourceQuota = 10
$storageQuota = 1

Try
{
	Write-Host -ForegroundColor Yellow "Checking, if site already exists..."
	
    $site = Get-PnPTenantSite -Url $url -ErrorAction SilentlyContinue
}
Catch
{
}

if ($site -ne $null)
{
    Write-Host -ForegroundColor Yellow "Site already exist, removing current site..."

    Remove-PnPTenantSite -Url $url -Force
	
	Write-Host
}

Write-Host -ForegroundColor Yellow "Creating new site..."

New-PnPTenantSite -Title $Title -Url $url -Owner $owner -Lcid $lcid -Template $template -TimeZone $timezone -ResourceQuota $resourceQuota -StorageQuota $storageQuota -Wait -RemoveDeletedSite

Write-Host

Connect-PnPOnline -Url $url

Write-Host -ForegroundColor Green "Done."

Create lookup field with additional columns

When we use lookup fields in SharePoint, we are able to configure the field to show additional fields from the list that is used as the target of the lookup as shown in the following image:

Technically SharePoint will create two fields for this lookup column (or more, depending how many additional columns are selected). When we want to create such a field using the Office 365 PnP PowerShell extensions, we can use the Add-PnPFieldFromXml cmdlet. For each field, we need one xml-definition as shown in the following example:

Main field

<Field 
	Type="Lookup" 
	ID="{890a17aa-8062-4e25-a5f5-d14357b8a704}" 
	Name="MyColor" 
	DisplayName="MyColor" 
	StaticName="MyColor" 
	List="{6a49e685-22d1-4f2f-bca3-eb01efd69613}" 
	WebId="81297a18-0e89-4c75-90f1-91981ee15d5d" 
	SourceID="{81297a18-0e89-4c75-90f1-91981ee15d5d}" 
	ShowField="Title" 
	UnlimitedLengthInDocumentLibrary="FALSE" 
	Required="FALSE" 
	EnforceUniqueValues="FALSE" 
	Group="Tester" 
/>

Secondary field

<Field 
	Type="Lookup" 
	ID="{b2ae0377-9b77-44c2-8b0f-765dda207fbc}" 
	Name="MyColor_x003A_Value" 
	DisplayName="MyColor:Value" 
	StaticName="MyColor_x003a_Value" 
	List="{6a49e685-22d1-4f2f-bca3-eb01efd69613}" 
	WebId="81297a18-0e89-4c75-90f1-91981ee15d5d" 
	SourceID="{81297a18-0e89-4c75-90f1-91981ee15d5d}" 
	FieldRef="890a17aa-8062-4e25-a5f5-d14357b8a704" 
	ShowField="Value" 
	ReadOnly="TRUE" 
	UnlimitedLengthInDocumentLibrary="FALSE" 
	Group="Tester" 
/>

There is a relationship between the attributes in the xml-definitions, as shown in the following image (ignore and do not use the attribute “Version”, as you can see it in the image):

To create the fields, we can use the following script:


function AddFieldToWeb([string]$Path)
{
	$content = Get-Content $Path

	$string = [string]$content

	Add-PnPFieldFromXml -FieldXml $string 
}

AddFieldToWeb .\Fields\MyColor.xml
AddFieldToWeb .\Fields\MyColor.Value.xml

Remember, the xml-definition includes the guid of the list and the web. So it’s necessary to patch these xml-definitions, before they could be used in a deployment scenario.

Create fields from XML for SharePoint Online

With the Office 365 PnP PowerShell extensions we have a very simple way to create new columns with the cmdlet Add-PnPFieldFromXml. The xml to use with this cmdlet could be taken from the Field-Element schema: https://msdn.microsoft.com/de-de/library/office/aa979575.aspx. So, to add new fields we just need to build the xml for the new column. Next some examples, tested as site columns:

Text field

<Field 
	Type="Text" 
	DisplayName="Document Title English"
	Description="Document Title in English"
	Required="TRUE" 
	EnforceUniqueValues="FALSE" 
	Indexed="FALSE" 
	MaxLength="255" 
	Group="Demo" 
	ID="{161ef8f6-e73c-4c56-8a5f-c6a8900f2fc8}"
	StaticName="TitleEN" 
	Name="TitleEN">
</Field>

Multitext field

<Field 
	ID="{B76B58EC-0549-4f00-9575-2FD28BD55010}" 
	Name="DemoDescription" 
	DisplayName="Description" 
	Description="A summary of the asset" 
	StaticName="DemoDescription" 
	Group="Demo" 
	Type="Note" 
	NumLines="5" 
	UnlimitedLengthInDocumentLibrary="TRUE" 
	Hidden="FALSE" 
	ShowInEditForm="TRUE" 
	SourceID="http://schemas.microsoft.com/sharepoint/v3" 
/>

Number field

<Field
	ID="{f9e31c80-7de8-4577-8df6-d7821aff4bce}"
	Type="Number"
	Name="DSStatus"
	DisplayName="DS Status"
	StaticName="DSStatus"
	Group="Demo"
	Description=""
	Hidden="false"
	ShowInNewForm="FALSE"
	ShowInEditForm="FALSE"
	Min="0"
	Max="2"
	Decimals="0">
	<Default>0</Default>
</Field>

Currency field

<Field 
	Type="Currency" 
	DisplayName="Price" 
	Required="FALSE" 
	EnforceUniqueValues="FALSE" 
	Indexed="FALSE" 
	Decimals="2" 
	LCID="1033" 
	ID="{57717312-4640-4408-93bb-1cb8eabb8f9a}" 
	SourceID="{269f1ad3-0434-4032-8677-99e06e3e6a01}" 
	StaticName="Price" 
	Name="Price" 
	CustomFormatter="" 
/>

Choice field

<Field 
	Type="Choice" 
	DisplayName="Process Group"
	Description="Process Group" 
	Required="TRUE" 
	EnforceUniqueValues="FALSE" 
	Indexed="FALSE" 
	Format="Dropdown" 
	FillInChoice="FALSE" 
	Group="Demo" 
	ID="{cdc426f9-4db3-4d03-936c-e9986ade7254}" 
	StaticName="ProcessGroup" 
	Name="ProcessGroup">
	<Default>Process group #1</Default>
	<CHOICES>
		<CHOICE>Process group #1</CHOICE>
		<CHOICE>Process group #2</CHOICE>
		<CHOICE>Process group #3</CHOICE>
		<CHOICE>Process group #4</CHOICE>
	</CHOICES>
</Field>

Yes/No field (Boolean)

<Field 
	Type="Boolean" 
	DisplayName="ReminderEmailSent" 
	Required="FALSE" 
	EnforceUniqueValues="FALSE" 
	Indexed="FALSE" 
	Hidden="TRUE"
	Group="Demo" 
	ID="{CE1411F7-7B47-4EE3-AD4B-E70AE5FC21F9}" 
	StaticName="ReminderEmailSent" 
	ShowInEditForm="FALSE"
	ShowInNewForm="FALSE"
	ShowInViewForms="FALSE"
	ShowInDisplayForm="FALSE"
	Name="ReminderEmailSent">
    <Default>FALSE</Default>
</Field>

DateTime field

<Field 
	Type="DateTime" 
	DisplayName="Approved" 
	Required="FALSE" 
	EnforceUniqueValues="FALSE" 
	Indexed="FALSE" 
	Format="DateTime" 
	Group="Demo" 
	FriendlyDisplayFormat="Disabled" 
	ID="{2eb9160b-7f84-4f35-9834-5253ee84c292}" 
	StaticName="Approved"
	ShowInEditForm="FALSE"
	ShowInNewForm="FALSE"
	Name="Approved">
</Field>

Person field

<Field 
  Type="User" 
  DisplayName="Approved by" 
  List="UserInfo" 
  Required="FALSE" 
  EnforceUniqueValues="FALSE" 
  ShowField="ImnName" 
  UserSelectionMode="PeopleOnly" 
  UserSelectionScope="0" 
  Group="Demo" 
  ID="{c4994fe6-24fe-458e-b06a-b9ed9fcb5c39}"
  StaticName="Approvedby"
  ShowInEditForm="FALSE"
  ShowInNewForm="FALSE"
  Name="Approvedby"> 
</Field>

Hyperlink field

<Field
	Type="URL" 
	DisplayName="Review" 
	Required="TRUE" 
	EnforceUniqueValues="FALSE" 
	Indexed="FALSE" 
	ShowInEditForm="FALSE"
	ShowInNewForm="FALSE"
	Format="Hyperlink" 
	Group="Demo" 
	ID="{b30b37aa-c6ea-4e9f-a848-7e251a7571b1}" 
	StaticName="Review" 
	Name="Review">
</Field>

Calculated field

<Field
	ID="{111b0f4c-3135-4a8a-9d7d-d53daaf1931a}"
	Type="Calculated"
	ResultType="Number"
	Name="WorkflowStatus"
	DisplayName="Workflow Status"
	StaticName="WorkflowStatus"
	Group="Demo"
	Description=""
	Hidden="false"
	JSLink="https://ajax.googleapis.com/ajax/libs/jquery/1.11.2/jquery.min.js|~site/Scripts/Fields.JSLink.js">
	<FieldRefs>
		<FieldRef Name="IMStatus" />
		<FieldRef Name="ISStatus" />
		<FieldRef Name="ITStatus" />
		<FieldRef Name="FBStatus" />
		<FieldRef Name="DSStatus" />
	</FieldRefs>
	<Formula>=[IMStatus]+[ISStatus]+[ITStatus]+[FBStatus]+[DSStatus]</Formula>
</Field>

Lookup field

<Field 
	ID="{8e99027f-501b-4902-8ee4-97020d04df74}"
	DisplayName="Company"
	Name="DemoCompany"
	StaticName="DemoCompany"
	Group="Demo"
	Type="Lookup"
	Required="FALSE"
	EnforceUniqueValues="FALSE"
	List="Companies"
	ShowField="Title" 
/>

When defining a lookup field from xml, you must replace the listname (“Companies” in the example) by the guid of the list, before the xml could be used.

To create taxonomy fields, use the cmdlet Add-PnPTaxonomyField instead of the Add-PnPField cmdlet.

When you are not sure, which attributes you need in the xml, simply create the field in a library using the web interface, then execute the Get-PnPField cmdlet as shown in this example:

That’s it, really simple.