O365 Saturday Melbourne

Last weekend, I presented a session at the Office 365 Saturday held in Melbourne.   I didn’t get there until lunch was being served, as I was coaching my son’s basketball team – but there was a great buzz & lots of chatter when I got there !

My session was entitled “VS, PS, CS, JS and no BS”.

If you don’t know the acronyms, it was a session about :

  • VS = Visual Studio 2015
  • PS = PowerShell
  • CS = C#
  • JS = JavaScript
  • BS = well, there was none, right !?    


Using Visual Studio 2015 as my IDE, I covered three main areas, with corresponding demos.

Provision a Site Collection – using PowerShell

  • Connect to SPO
  • Create a new Site Collection
  • Regional Settings
  • Activate features
  • Upload files
  • Create lists & pages
  • Set Alt Css Url
  • Welcome page
  • Add JavaScript reference (JS injection)

This last point was a topic that I’ll cover in detail in another post – a way to get some good functionality into your SharePoint O365 site, without needing to change the Master Page – otherwise known by some work colleagues as ‘dentistry via the belly button’ – LOL !

Provider hosted app in Azure – using C#

Using CSOM, users can provision a sub-site (SPWeb), and have it configured with a variety of features and layout.   My code even provisions a Yammer group if required.

  • Activate features
  • Add lists
  • Create webparts
  • Set security
  • Create Yammer group
  • Add Yammer webpart to page
  • Add blog site
  • Add audience targetted content editor webparts

Additional functionality – using JavaScript

Lastly, I showed some of the functions we developed using CSOM and the JSOM :

  • Add footer – without Master Page update (JavaScript injection)
  • Waffle
  • Show dialog
  • Delete sub-sites
  • Get AppTiles

Much of the demo’s were fairly fast paced, and “you had to be there to see it” – but I’ve loaded the slides for your viewing – and will blog some of the items above in depth.

Let me know if you would like to see/hear more – and thanks for attending my session – if you were there on the day.    (no BS, was there ??   right !!?!?)

Convert IP Address to numeric (JS)

I’ve been using some fancy functions in SQL to convert to/from an IP Address and Mask – but have needed to do the same within JavaScript.

This is a requirement based on some MySQL functions – eg. INET_NTOA and INET_ATON.

This first one will take an input value – eg. – and return a number (BIGINT / INT64 for calculations – or storing in a database – as we’re needing to do.

    function convertIpToNumeric(ipAddress) {
        var arrIp = ipAddress.split(".");

        var segment1 = parseInt(arrIp[0]);
        var segment2 = parseInt(arrIp[1]);
        var segment3 = parseInt(arrIp[2]);
        var segment4 = parseInt(arrIp[3]);

        //reverse order calc 
        //eg.     <-- start at the end
        // 0 + (240*256) + (255*65536) + (255*16777216)
        var calc = segment4 + (segment3 * 256) + (segment2 * 65536) + (segment1 * 16777216);

        return calc;


And – if needing to do the opposite – you can use the following function.

This will take an input value – eg. 167772161 – and return the resultant IP Address :

function convertNumericToIp(bigNumber) {

    var Octet1 = Math.floor(bigNumber / 16777216)
    var RestOfIP = bigNumber - (Octet1 * 16777216)

    var Octet2 = Math.floor(RestOfIP / 65536)
    var RestOfIP = RestOfIP - (Octet2 * 65536)

    var Octet3 = Math.floor(RestOfIP / 256)
    var Octet4 = RestOfIP - (Octet3 * 256)

    var returnValue = Octet1 + "." + Octet2 + "." + Octet3 + "." + Octet4; 

    return returnValue;



We have a set of publishing pages that are not correctly showing if/when the following flag is set to ON :


This is to do with the SEO and vanity URL’s stuff – but we want this “OFF”.

Looking at the field via the REST URL, you can see the default value.    But, the field/column is actually a SEALED column, and so you can’t update it via the UI.



So – it’s some fancy PowerShell to force this value (after connecting to O365) :

#get the fields at the root site level
$rootWeb = $clientContext.Web
$fields = $rootWeb.Fields

#grab the field 
$fld = $fields.GetByInternalNameOrTitle("PublishingIsFurlPage")


$fld.DefaultValue = $false


After running this PowerShell, the field is SET when viewing the column via REST :


And now, when users create a page – regardless of PAGE LAYOUT – this value will be ticked to OFF by default – and standard URL’s will be used – too easy.

The main issue we had was with a Content Search WebPart that was NOT showing the pages that had the checkbox ON.   We needed users to make sure to set it off – making it DEFAULT to off was the best option.


SQL Server INET_ATON IP Address + CIDR

Most IT folk would understand that an IP Address is a 4-octset set – eg.

There’s another piece that is the subnet mask – eg.

These are usually managed as numerical values – BIGINT values (Int64).

Within MySQL, you can determine the IP Address to/from a number using the functions INET_ATON and INET_NTOA.

For an equivalent in SQL Server – you can use the following UDF’s :

Integer to IP Address :

CREATE FUNCTION [dbo].[IntegerToIPAddress] (@IP AS bigint)
RETURNS varchar(15)
 DECLARE @Octet1 bigint
 DECLARE @Octet2 tinyint
 DECLARE @Octet3 tinyint
 DECLARE @Octet4 tinyint
 DECLARE @RestOfIP bigint
 SET @Octet1 = @IP / 16777216
 SET @RestOfIP = @IP - (@Octet1 * 16777216)
 SET @Octet2 = @RestOfIP / 65536
 SET @RestOfIP = @RestOfIP - (@Octet2 * 65536)
 SET @Octet3 = @RestOfIP / 256
 SET @Octet4 = @RestOfIP - (@Octet3 * 256)
 RETURN(CONVERT(varchar, @Octet1) + '.' +
        CONVERT(varchar, @Octet2) + '.' +
        CONVERT(varchar, @Octet3) + '.' +
        CONVERT(varchar, @Octet4))

IP Address to Integer

CREATE FUNCTION [dbo].[IPAddressToInteger] (@IP AS varchar(15))
RETURNS bigint
         CONVERT(bigint, PARSENAME(@IP,2)) * 256 +
         CONVERT(bigint, PARSENAME(@IP,3)) * 65536 +
         CONVERT(bigint, PARSENAME(@IP,4)) * 16777216)

Yet another piece of the puzzle is related to a CIDR – which looks like this :

It’s an acronym for “Classless Inter-Domain Routing” – I had to look it up too…!

I’ve created another function that determines the “/18” for a given numeric sequence (in T-SQL).


	 SELECT @maskCalc = dbo.IPAddressToInteger('') - @mask + 1

	 DECLARE @logCalc int
	 SELECT @logCalc = (32 - LOG(@maskCalc, 2))

	 RETURN '/' + CAST(@logCalc AS VARCHAR(5))


This helped me with the retrieval of an IP Address – and CIDR – fairly complex, but easy to use in the end – eg.

select dbo.IntegerToIPAddress('4294966272') + dbo.CIDRFromMask('4294966272')


A Web Part with this ID has already been added to this page.

Within a SharePoint site that we’ve upgraded, we have an error showing when users navigate to a page :


Using some PowerShell, I was able to list the specific webparts on the page :

$web = Get-SPWeb http://intranet/Services/Finance 
$list = $web.Lists["Pages"] 
#get the page (listitem) 
$item = $list.Items[0] 
#get the webpart manager 
$wpm = $item.File.GetLimitedWebPartManager('Shared') 
$wpm.WebParts | select id, title 

And somehow, there is a duplicate GUID for a certain WebPart :


The next step is to remove one of those webparts from the page – following on from the PowerShell shown above :

#get the webpart using #3
$wp = $wpm.WebParts[3] 

#check which one we've got
$wp | select id, title
#delete from the page - and update 


That outta do it – and your users will be able to browse to the page.

NB.  You might have to checkout the page also – and then verify which webpart you need to remove – be careful !

Quick connect to O365 using PowerShell

I’ve been doing lots of posts about connecting to Office365, and a bunch of scripts, so I thought I’d share my easy script – just gotta update the URL, and PASSWORD.

** NB.  You need a folder with the DLL’s – this is my set of files :


This is the basic script – to add a reference to the necessary DLL’s (above) – and then define the user/password – and make a connection – and then load the WEB object.

$path =  'C:\o365';

$urlSite = 'https://[tenant].sharepoint.com/sites/[SITECOLLECTION]'

$user = '[USER]@[TENANT].onmicrosoft.com';
$password = 'Password1234';


add-type -Path $path'\Libraries\Microsoft.SharePoint.Client.dll'
add-type -Path $path'\Libraries\Microsoft.SharePoint.Client.Runtime.dll'

$passwordSecureString = ConvertTo-SecureString -string $password -AsPlainText -Force
$credential = New-Object -TypeName System.Management.Automation.PSCredential -argumentlist $user, $passwordSecureString

$spoCtx = New-Object Microsoft.SharePoint.Client.ClientContext($urlSite)
$spoCredentials = New-Object -TypeName Microsoft.SharePoint.Client.SharePointOnlineCredentials -argumentlist $user, $passwordSecureString
$spoCtx.Credentials = $spoCredentials
$spoCtx.RequestTimeout = '500000'

$web = $spoCtx.web

Please let me know if that’s helpful – feel free to copy+use – I’ve used this a LOT in quick testing scripts…


SetDefaultPageLayout CSOM

As part of my new Office 365 site collection provisioning (via PowerShell), I’d like to be able to set the ‘default page layout’ :


BUT – within the object model for SharePoint.Client.Publishing, there are only a handful of methods for the PublishingWebClass – and so I can’t use CSOM or POWERSHELL to set the default page layout.

Click here to see on MSDN :


But – after a quick poke into the DLL for SharePoint.Publishing.DLL (using ILSPY), I could see that the functionality is just to add a property – which is EASY via CSOM…>!



And – sure enough, if you look using the REST endpoint, there it is :



It looks like the value that I need – for the Page Layout of “ArticleLeft” is :

<layout guid=”8520f570-356b-462d-8976-1e58b936c65e” url=”_catalogs/masterpage/ArticleLeft.aspx” />

Unfortunately – that GUID is different, as you move to another site collection – even for the same page layout – doh !

Another look at ILSPY shows that the GUID is the ID of the listitem :


So – the PowerShell needs to determine the specific item for the page layout I want – and then construct the little XML chunk – and update the property for __DefaultPageLayout.

There’s a REST call that shows these guys :


And – you can actually grab the specific file :



But – from a POWERSHELL command line, this is what you need – once you’ve established a context to the specific site collection :

$rootWeb = $spoCtx.web


$defaultPageLayout = '_catalogs/masterpage/ArticleLeft.aspx'

#get the page item - and grab the GUID of it
$urlRelative = $rootWeb.ServerRelativeUrl + "/" + $defaultPageLayout
$pageLayoutFile = $rootWeb.GetFileByServerRelativeUrl($urlRelative) 

$pageGuid = $pageLayoutFile.UniqueId;

#set the xmlchunk for the property
$xmlPageLayout = "<layout guid='{0}' url='{1}' />"
$xmlPageLayout = $xmlPageLayout.Replace("{0}", $pageGuid);
$xmlPageLayout = $xmlPageLayout.Replace("{1}", $defaultPageLayout);

$rootWeb.AllProperties["__DefaultPageLayout"] = $xmlPageLayout;

That works nicely – phew !     🙂

Add SharePoint Group to Library (Break Permissions)

I’ve had some back and forth hiccups when getting some security provisioning for a SharePoint library.

The basic premise was to break-inheritance – and only allow the “OWNERS” group to have permission (full control).

Seems easy enough – but I was getting errors like :

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

Or this one, which was annoying, and tripping me up :

Cannot add a role assignment with empty role definition binding collection.

It turns out that I was actually doing “too many” of the ExecuteQuery statements – which I didn’t actually need.   

So – I had success, using this code – and through I’d share it in case you have the same problem – or for my own future reference :

// ==================================================================
// set the OWNERS group to have permissions to the [submitted work] library
// ==================================================================
List submittedWorkLib = clientCtx.Web.Lists.GetByTitle('Submitted Work');

//break security/permissions for the library
submittedWorkLib.BreakRoleInheritance(false, false);

//add the owners group
string workspaceOwnersGroup = web.Title + &quot; Owners&quot;;
LogHelper.WriteToLog(log, &quot;Add the workspace owners group to library : &quot; + workspaceOwnersGroup);


var role = web.RoleDefinitions.FirstOrDefault(r =&gt; r.Name == &quot;Full Control&quot;);


var group = web.SiteGroups.FirstOrDefault(g =&gt; g.Title == workspaceOwnersGroup);

var roleDefBinding = new RoleDefinitionBindingCollection(clientCtx);
submittedWorkLib.RoleAssignments.Add(group, roleDefBinding);


Let me know if that helps – thanks !

Other references :

403 Forbidden when deleting SPWeb (JSOM) REST call Office365

When attempting to DELETE a SharePoint sub web within some included JavaScript (as opposed to a SharePoint App), you can simply call a REST endpoint, and pass the ‘DELETE’ verb as a HTTP header.

This is using the $.ajax method of jQuery.

You just need to use the URL of the subweb you want to delete – with the “_api/web” suffix.

var urlToDelete = 'http://tenant.sharepoint.com/sites/corp/web/subweb1/_api/web';

And – to then do a call to the REST endpoint, you just construct JavaScript like this :

        url: urlToDelete,
        method: 'POST',
        headers: {
            'Accept': 'application/json; odata=verbose', 
            'X-HTTP-Method': 'DELETE'
        success: function (data) {
        error: function (err) {

BUT – and this is what caught me out – you might get a “403 Forbidden” error.    If you check using F12 developer tools – you’ll see the error.

The trick is to include the content of the page (hidden variable) – and use as another header :


And so – when you add it all together – you get this – and it works !    and no 403 error…

        url: urlToDelete,
        method: 'POST',
        headers: {
            'Accept': 'application/json; odata=verbose', 
            'X-HTTP-Method': 'DELETE',
            'X-RequestDigest': $('#__REQUESTDIGEST').val()
        success: function (data) {
        error: function (err) {

If you get a 500 error – it maybe that there are “subwebs” – and you need to delete those first.

Get SharePoint Apps for current web using JavaScript CSOM (Office 365)

We have a requirement for a right hand pane set-of-links, which is essentially a set of shortcuts to the “APPS” that are within the SITE CONTENTS page.   This will be shown on the home page of a SharePoint SPWeb, in a short list, rather than the user viewing all the big tiles, etc.

The basic steps are :

  • Get the current SPContext for the web
  • Get the AppTiles for the current web
  • Cycle through, and get the TITLE and TARGET (URL) for each tile
  • Inject the HTML in using jQuery

I simply saved this as a file called “allApps.htm” and then added a Content Editor WebPart.   There are only a few lines of HTML – just a placeholder :


And then, there’s a stack of JavaScript – yay !


Here’s what it looks like when displayed in the CEWP, inside a Page – needs some CSS magic – but it works !


And – if you want to use the above technique – here’s the code :

var ctxCurrent;
var ctxWeb;

//get the SharePoint context
SP.SOD.executeFunc('sp.js', 'SP.ClientContext', loadContext);

function loadContext() {
    ctxCurrent = new SP.ClientContext.get_current();
    ctxWeb = ctxCurrent.get_web();

function loadAppTiles() {
    //get the appTiles
    var appTiles = ctxWeb.get_appTiles();

    //go through them all - and find the app for 'class workspace provisioning';
        function onQuerySucceeded(sender, args) {
            if (appTiles.get_count() &gt; 0) {
                //go through the appTiles - get the TITLE and TARGET - and jQuery them into the UL
                for (var i = 0; i &lt; appTiles.get_count() ; i++) {
                    //get the tile at the &quot;i&quot; number
                    appTile = appTiles.getItemAtIndex(i);

                    //grab the title and url
                    appTitle = appTile.get_title();
                    appUrl = appTile.get_target();

                    //append to the UL - using jQuery
                    var newLi = &quot;&lt;li&gt;&lt;a href='&quot; + appUrl + &quot;' &gt;&quot; + appTitle + &quot;&lt;/a&gt;&lt;/li&gt;&quot;;
                    $jq('#currentApps ul').append(newLi);
        function onQueryFailed(sender, args) {
            console.error('getAppTile. Request failed. ' + args.get_message() + '\n' + args.get_stackTrace());

Let me know if this was helpful – cheers !