Connect to SharePoint Online using PnP framework

Microsoft is constantly improving and updating their systems, therefore is a permanent challenge to keep your up to date. Now, as  ACS app-only access token is deprecated, you need to change authentication methods to use Azure AD only access. One of the way is to use a certificate, which seems to work fine from an MVC application.

Create Certificates

Basically you need to create a certificate file (.cer extension) and a personal information file (.pfx extension). You can create them with PowerShell script below (I cannot take the credits for it, is not mine, but is tested and worked).

$cert = New-SelfSignedCertificate -DnsName localhost -CertStoreLocation cert:\CurrentUser\My

$password = ConvertTo-SecureString -String "password" -Force -AsPlainText

Export-PfxCertificate -Cert $cert -FilePath .\my.pfx -Password $password<br>Export-Certificate -Cert $cert -FilePath .\my.cer

First file needs to be uploaded in Azure application registration, in certificates and secrets section.

The second one, personal information file, should be stored locally to be used by the code. 

 PnP.Framework.AuthenticationManager manager = new PnP.Framework.AuthenticationManager("clientapplicationid", "C:\folder\my.pfx", "pfxFilePassword", "tenantid");
 ClientContext ctx = await manager.GetContextAsync("https://4q234m.sharepoint.com/sites/BMS");
 Microsoft.SharePoint.Client.Web oWeb = ctx.Web;
 Microsoft.SharePoint.Client.List oList = oWeb.Lists.GetByTitle("AccessLogs");
 CamlQuery query = new CamlQuery();
 query.ViewXml = @"<View><Query><Where><IsNull><FieldRef Name='DisabledOn' /></IsNull></Where></Query></View>";
 ListItemCollection oItems = oList.GetItems(query);
 ctx.Load(oItems);
 ctx.ExecuteQuery();

I am using GetContextAsync method, because, for some reasons, GetContext methods freeze the solution.

Adding link in quick launch with target audience (PnP)

There is simple way to add a link in quick launch with PnP including target audience. Replace the client id, site URL, link title, link URL and azure AD group name with values and run the script below

Import-Module PnP.PowerShell
Connect-PnPOnline -Interactive -ClientId "clientid" -Url "nysitemurl"

$title = "link tile"
$link = "link url"
$azureADGroupName = "azure active directory group name"

$ADGroup = Get-PnPAzureADGroup -Identity $azureADGroupName
$audienceList = New-Object System.Collections.Generic.List[guid] 
$audienceList.add([System.guid]::New($ADGroup.Id))

Add-PnPNavigationNode -Location QuickLaunch -Title $title -Url $link -External -AudienceIds  $audienceList

Clear folder permission with PnP PowerShell

Office 365 and SharePoint Online development is constantly changing. PnP is pushed on the market by Microsoft, but this doesn’t mean things are easy. Recently I had a challenge to clear all permission on a folder before applying new ones. Here is the function I created for this:

Import-Module PnP.PowerShell

function RemoveFolderPermisions {
    param($libraryName, $folderSiteRelativeUrl, $siteUrl, $clientId)

    $connection = Connect-PnPOnline -Url $siteUrl  -Interactive -ClientId $clientId -ReturnConnection
    $folder = Get-PnPFolder -Url $folderSiteRelativeUrl -Connection  $connection

    # Return false if folder doesn't exist
    if($null -eq $folder) {
        return $false
    }

    $folderAsListItem = Get-PnPFolder -Url $folderSiteRelativeUrl -AsListItem -Connection  $connection
    $folderPermissions = Get-PnPListItemPermission -List $libraryName -Identity $folderAsListItem.ID -Connection  $connection

    foreach($permissionObj in $folderPermissions) {
        foreach($permissionLevel in $permissionObj.PermissionLevels) {
            if($permissionObj.PrincipalType -eq "SharePointGroup") {
                Set-PnPFolderPermission -List $libraryName  -Identity $folder -RemoveRole $permissionLevel -Group $permissionObj.PrincipalName -Connection $connection
            }
            elseif ($permissionObj.PrincipalType -eq "User") {
                Set-PnPFolderPermission -List $libraryName   -Identity $folder -RemoveRole $permissionLevel -User $permissionObj.PrincipalName -Connection $connection
            }
        }
    }

    # Return true if operation is completed
    return $true

}

Show weather reports on SharePoint page

Recently, I received a task to show weather reports for 2 cities on a SharePoint page, Miami and Atlantic City. It was supposed to be done in SharePoint Online environment, so nothing on the server side has to written. So, I had to challenges. One is how to get the data and the second is how to do it properly to get the reports for 2 cities, as in general APIs are provided information for one city in a single call.

Let’s start with first point. After some investigation, I discovered the best way for me is to OpenWeather API. I could choose the free version, as I did not have so many calls to require a paid account, and it was enabled for cross domains JavaScript calls also. Using GET method of Ajax calls, to get the data for a city you only need to set the right URL; https://api.openweathermap.org/data/2.5/weather?q=Miami&appid=myapplicaionid

So far I was good, but I needed to get the data for 2 cities and, with some help of JavaScript promises, this was easily done. 

(function(objOwner) {

    objOwner.Weather = objOwner.Weather || {};

    objOwner.Weather.getWeather = function(city)  {
        var promise = new Promise(function(resolve, reject) {
            var url = 'https://api.openweathermap.org/data/2.5/weather?q=' + city + '&appid=myapplicationid';
            jQuery.ajax({
                method: "GET",
                url: url,
                success: function(data) {
                    resolve(data);
                },
                fail: function(err) {
                    reject(err);
                }
            });

        });
        return promise;
    }

})(window);

Sys.Application.add_load(function() {

    var miamiWeather = Weather.getWeather('Miami');
    var acWeather = Weather.getWeather('Atlantic City');
    Promise.all([miamiWeather, acWeather]).then(function(results) {

        var html = '';
        for(var i = 0; i < results.length; i++) {
            var city = results[i];
            html += '<div class="weather-location">' +
            '<div class="weather-location-icon"><img src="https://openweathermap.org/img/w/' + city.weather[0].icon + '.png" /></div>' +            
            '<div class="weather-location-city">City ' + city.name + '</div>' +
            '<div class="weather-location-temp">Temp ' + city.main.temp + '</div>' +
            '<div class="weather-location-mintemp">Min ' + city.main.temp_min + '</div>' +
            '<div class="weather-location-maxtemp">Max ' + city.main.temp_max + '</div>' +
            '<div class="weather-location-wind">Wind ' + city.wind.speed + '</div>' +
            '</div>';
        }
        jQuery('div.weather-container').html(html);

    }).catch(function(err) {
        alert(err);
    });
});

I have register code to be executed with Sys.Application.add_load method, with the purpose of creating 2 calls to the API, one for each city, and show the data in div element when both calls are completed. I placed entire code in JavaScript file and register in the SharePoint page using script editor web part.

<div class="weather-container"></div>
<script type="text/javascript" src="https://mytenant.sharepoint.com/sites/portal/Scripts/weather/weather.js"></script>

The styling of the results is not something I can write about. I am really not into design area, but I am sure some nice widgets can outcome from this.

Get list items created in current month

DateRangesOvelap might work when you are working with calendar list, but for sure is not working with custom lists. For this, you need to write your own CAML query and get the data based on it. Below I have a created a function to get the items and returns a promise object.

    function GetListItems(listName, caml) {
        let promise = new Promise(function (resolve, reject) {
            let ctx = SP.ClientContext.get_current();
            let oList = ctx.get_web().get_lists().getByTitle(listName);
            let camlQuery = new SP.CamlQuery();
            camlQuery.set_viewXml(caml);
            let oListItems = oList.getItems(camlQuery);
            ctx.load(oListItems);
            ctx.executeQueryAsync(function () {
                resolve(oListItems);
            }, function (sender, args) {
                reject(args.get_message());
            });
        });
        return promise;
    }

Now let’s built the CAML query and call the function.

function getDaysInMonth(year, month) {
    // Get numbers of days in a month
    return new Date(year, month + 1, 0).getDate();
}

let currentDate = new Date();
let firstDate = new Date(currentDate.getFullYear(), currentDate.getMonth(), 1);
let lastDate = new Date(currentDate.getFullYear(), currentDate.getMonth(), getDaysInMonth(currentDate.getFullYear(), currentDate.getMonth()));

let caml = '<view>' +
    '<Query>' +
    '<Where>' +
    '<And>' +
    '<Geq><FieldRef Name="Created" /><Value Type="DateTime">' + firstDate.format('yyyy-MM-dd') + '</Value></Geq>' +
    '<Leq><FieldRef Name="Created" /><Value Type="DateTime">' + lastDate.format('yyyy-MM-dd') + '</Value></Leq>' +
    '</And>' +
    '</Where>' +
    '</Query>' +
    '</View > ';

GetListItems('List Name', caml).then(function (items) {
    // Do something with the items
}, function (err) {
    // Do something with error message
});

You can notice, is not complicated to achieve this using JS CSOM. Of course you can apply the filter on different date and time fields. The next article will be probably how to get the items for a specified week, as is more probably to be a goal in many projects.

Get data from multiple lists using Promise.all

I always had this question in mind, how can I optimize data operations using JavaScript CSOM. With some help from Promise object, this is possible. Below, I have written a small portion of code for getting data from lists and returning a promise object.

class $SPData {

    constructor() {
        throw '$SPList is a static class and cannot be instantiated';
    }

    /**
     * Get list items
     * @param {string} listName
     * @param {string} camlQuery
     * @param {string} webUrl
     */
    static GetListItems(listName, caml, webUrl) {
        let e = Function.validateParameters(arguments, [{ name: 'listName', type: String, mayBeNull: false, optional: false },
        { name: 'caml', type: String, mayBeNull: false, optional: false },
        { name: 'webUrl', type: String, mayBeNull: false, optional: true }], true);
        if (e) throw e;

        let promise = new Promise(function (resolve, reject) {
            let ctx = (arguments.length === 2) ? SP.ClientContext.get_current() : new SP.ClientContext(webUrl);
            let oList = ctx.get_web().get_lists().getByTitle(listName);
            let camlQuery = new SP.CamlQuery();
            camlQuery.set_viewXml(caml);
            let oListItems = oList.getItems(camlQuery);
            ctx.load(oListItems);
            ctx.executeQueryAsync(function () {
                resolve(oListItems);
            }, function (sender, args) {
                reject(args.get_message());
            });
        });
        return promise;
    }
}

Now, let’s see how you can use this in your code.

let getItemsFromListOne = $SPData.GetListItems('List One', '<View></View>');
let getItemsFromListTwo = $SPData.GetListItems('List Two', '<View></View>');
Promise.all([getItemsFromListOne, getItemsFromListTwo]).then(function (values) {
    let itemsFromListOne = values[0];
    let itemsFromListTwo = values[1];
});

This should simplify the way you are working with SharePoint data. You do not need to have wait for a call to finish to start another one. I have tested only to get data from the lists, but it can used also to insert and update list items also.

Copy current Access database with VBA

Access is still being in used by some companies, even is not perfect choice anymore. So, today I was requested to create a sort of back-up functionality for an Access database which contains linked tables to SharePoint. I have tried some methods, but all them are failed, including FileCopy method from VBA. Still VBA was solving my problem, but code was a little bit different. 

Dim fDialog As Office.FileDialog
Set fDialog = Application.FileDialog(msoFileDialogSaveAs)

With fDialog

    If fDialog.Show() <> 0 Then
    
        Dim Sourcefile As String
        Dim Destinationfile As String
        Sourcefile = Application.CurrentProject.Path & "\" & Application.CurrentProject.Name
        Destinationfile = fDialog.SelectedItems(1)
    
        With CreateObject("Scripting.FileSystemObject")
            .copyfile Sourcefile, Destinationfile
        End With
       
      
    End If
End With

Code is also opening a file save dialog and you can choose the location where you want to copy the database.

CDN issues in SharePoint apps

When I am developing SharePoint apps, in general, I create my custom master page and place it inside the application. I would not say I had too much problems with this approach, until I needed to deploy the application on a SharePoint Online site where I could not disabled CDN due to insufficient rights on tentant. JQuery loaded from CDN simply makes some Bootstrap functionality not to work anymore.

My scripts were loaded using ScriptManager control, which is the classic way to load javascript files in web forms sites. As SharePoint tried to load additional files, the salvation was again JavaScript.


function pageLoad(sender, args) {



    let scripts = document.querySelectorAll('script')
    for (let k = 0; k < scripts.length; k++) {
        if (scripts[k].src.includes('ajax.aspnetcdn.com/ajax/jquery') === true) {
            document.body.removeChild(scripts[k]);
            jQuery.getScript(_spPageContextInfo.webAbsoluteUrl + '/Scripts/Framework/jQuery/jquery.js', function () {
                jQuery.getScript(_spPageContextInfo.webAbsoluteUrl + '/Scripts/Framework/bootstrap/bootstrap.js', function () {
                    // Do something here if needed
                });
            });
        }
    }

}

Don’t judge me if the code is not optimized as I found the solution about 10 minutes ago and this article is only written to give you an idea about how to remove jQuery from CDN. You can remove of course old Bootstrap and jQuery files before adding new ones or try to find a better loader, like require.js.

Adding image in master page (SharePoint apps model)

When I developing SharePoint apps, I prefer to use my custom master page, as the idea is to create responsive design. One of the issues I had was to how to add a logo or an image in the master page. The solution is still classic asp.net control called image. 

<asp:Image runat="server" ImageUrl="../../Images/sp_logo.png" />

You can apply style on the image by setting CssClass property of the control, as well as you can use all other properties. See full documentation for this tag here: http:// https://docs.microsoft.com/en-us/dotnet/api/system.web.ui.webcontrols.image?view=netframework-4.7.2

Create folder with properties using JavaScript CSOM

JavaScript CSOM is already on the market for a while. Introduces on version 2010, it can cover today a lot of scenarios. One of them is to create a folder in a document library with some other associated fields populated. This was one of my recent tasks. Code below should to do everything for you:

Type.registerNamespace('Shp');
        
        
        Shp.DocLib = function() {
            throw 'Cannot initiate Shp.DocLib static class';        
        }
        
        Shp.DocLib.createFolder = function(listName, folderName, fields, webUrl, success, fail) {
            var e = Function.validateParameters(arguments, [{ name: 'listName', type: String, optional: false, mayBeNull: false },
                                                            { name: 'folderName', type: String, optional: false, mayBeNull: false },
                                                            { name: 'fields', type: Object, optional: false, mayBeNull: false },
                                                            { name: 'webUrl', optional: false, mayBeNull: true },
                                                            { name: 'success', type: Function, optional: false, mayBeNull: false },
                                                            { name: 'fail', type: Function, optional: true, mayBeNull: false }], true);
           if(e) throw e;

   		  var fail = fail || function (err) { alert(err); };
          var ctx = (webUrl === null) ? SP.ClientContext.get_current() : new SP.ClientContext(webUrl);          
          Shp.DocLib._createFolder(listName, folderName, fields, ctx, success, fail);
        }
        
        Shp.DocLib._createFolder = function(listName, folderName, fields, ctx, success, fail) {
        
        	var oLib = ctx.get_web().get_lists().getByTitle(listName); 
        	var itemCreateInfo = new SP.ListItemCreationInformation();
        	itemCreateInfo.set_underlyingObjectType(SP.FileSystemObjectType.folder); 
        	itemCreateInfo.set_leafName(folderName);
        	var oFolder = oLib.addItem(itemCreateInfo);
        	for (var field in fields) {
                if (fields.hasOwnProperty(field) === true) {
                    oFolder.set_item(field, fields[field]);
                }
            };
            
     		oFolder.update();
            ctx.load(oFolder);     
            
			ctx.executeQueryAsync(function () {
                success(oFolder);
            }, function (sender, args) {
                fail(args.get_message());

            });			  
        }
        
        Shp.DocLib.registerClass('Shp.DocLib');

In order to use, you just need to provide right types of parameters, as a validation is performed.

Shp.DocLib.createFolder("List Name", "Folder Name", { "Field1": "some value", "Field2": "some value" }, "path to sharepoint",
    function (folder) {
        // Do something with the created folder
    }, function (err) {
        // Do something with the error message
    });

Web URL parameter can be null, if you want to use the client context for current web. Also, if you do not provide an error callback method, a simple alert with an error is displayed.

Design a site like this with WordPress.com
Get started