Use Azure function as remote event receiver for Sharepoint Online list and debug it locally with ngrok

If you worked with Sharepoint on-prem you probably know what are event receivers: custom handlers which you may subscribe on different types of events. They are available for different levels (web, list, etc). In this article we will talk about list event receivers.

In Sharepoint Online we can't use old event receivers because they should be installed as farm solutions which are not available in SPO. Instead we have to use remote event receivers. The concept is very similar but instead of class and assembly names we should provide end point url where SPO will send HTTP POST request when event will happen.

Let's see how it works on practice. For event receiver end point I will use Azure function and will run it locally. For debugging it I will use ngrok tunneling (btw ngrok is great service which makes developers life much easier. If you are not familiar with it yet I hardly suggest you to do that :) ). But let's go step by step.

First of all we need to implement our Azure function:

[FunctionName("ItemUpdated")]
public static async Task<HttpResponseMessage> Run([HttpTrigger(AuthorizationLevel.Anonymous, "post", Route = null)]HttpRequestMessage req, TraceWriter log)
{
    log.Info("Start ItemUpdated.Run");
    var request = req.Content.ReadAsStringAsync().Result;
    return req.CreateResponse(HttpStatusCode.OK);
}

It doesn't do anything except reading body payload as string - we will examine it later. Then we run it locally - by default it will use http://localhost:7071/api/ItemUpdated url.

Next step is to create ngrok tunnel so we will get public https end point which can be used by SPO. It is done by the following command:

ngrok http -host-header=localhost 7071

After this command you should see something like that:

Now everything is ready for attaching remote event receiver to our list. It can be done by using Add-PnPEventReceiver cmdlet from PnP.Powershell. Note however that beforehand is it important to connect to target site with Connect-PnPOnline with UseWebLogin parameter:

Connect-PnPOnline -Url https://{tenant}.sharepoint.com/sites/{url} -UseWebLogin

Without UseWebLogin remote event receiver won't be triggered. Here is the issue on github which explains why: RemoteEventReceivers are not fired when added via PnP.Powershell.

When ngrok is running we need to copy forwarding url: those which uses https and looks like https://{randomId}.ngrok.io (see image above). We will use this url when will attach event receiver to target list:

Connect-PnPOnline -Url https://mytenant.sharepoint.com/sites/Test -UseWebLogin
$list = Get-PnPList TestList
Add-PnPEventReceiver -List $list.Id -Name TestEventReceiver -Url https://{...}.ngrok.io/api/ItemUpdated -EventReceiverType ItemUpdated -Synchronization Synchronous

Here I attached remote event receiver to TestList on site Test and subscribed it to ItemUpdated event. For end point I specified url of our Azure function using ngrok host. Also I created it as synchronous event receiver so it will be triggered immediately when item got updated in the target list. If everything went Ok you should see your event receiver attached to the target list using Sharepoint Online Client Browser:

Note that ReceiverUrl property will contain ngrok end point url which we passed to Add-PnPEventReceiver.

Now all pieces are set and we may test our remote event receiver: go to Test list and try to edit list item there. After saving changes event receiver should be triggered immediately. If you will check body payload you will see that it contains information about properties which have been changed and id of list item which we just modified:

<s:Envelope xmlns:s="http://schemas.xmlsoap.org/soap/envelope/">
	<s:Body>
		<ProcessEvent xmlns="http://schemas.microsoft.com/sharepoint/remoteapp/">
			<properties xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
				<AppEventProperties i:nil="true"/>
				<ContextToken/>
				<CorrelationId>c462dd9f-604a-2000-ea96-f4bacc80aa84</CorrelationId>
				<CultureLCID>1033</CultureLCID>
				<EntityInstanceEventProperties i:nil="true"/>
				<ErrorCode/>
				<ErrorMessage/>
				<EventType>ItemUpdated</EventType>
				<ItemEventProperties>
					<AfterProperties xmlns:a="http://schemas.microsoft.com/2003/10/Serialization/Arrays">
						<a:KeyValueOfstringanyType>
							<a:Key>TimesInUTC</a:Key>
							<a:Value i:type="b:string" xmlns:b="http://www.w3.org/2001/XMLSchema">TRUE</a:Value>
						</a:KeyValueOfstringanyType>
						<a:KeyValueOfstringanyType>
							<a:Key>Title</a:Key>
							<a:Value i:type="b:string" xmlns:b="http://www.w3.org/2001/XMLSchema">item01</a:Value>
						</a:KeyValueOfstringanyType>
						<a:KeyValueOfstringanyType>
							<a:Key>ContentTypeId</a:Key>
							<a:Value i:type="b:string" xmlns:b="http://www.w3.org/2001/XMLSchema">...</a:Value>
						</a:KeyValueOfstringanyType>
					</AfterProperties>
					<AfterUrl i:nil="true"/>
					<BeforeProperties xmlns:a="http://schemas.microsoft.com/2003/10/Serialization/Arrays"/>
					<BeforeUrl/>
					<CurrentUserId>6</CurrentUserId>
					<ExternalNotificationMessage i:nil="true"/>
					<IsBackgroundSave>false</IsBackgroundSave>
					<ListId>96c8d1c1-de22-47bf-9f70-aeeefa349856</ListId>
					<ListItemId>1</ListItemId>
					<ListTitle>TestList</ListTitle>
					<UserDisplayName>...</UserDisplayName>
					<UserLoginName>...</UserLoginName>
					<Versionless>false</Versionless>
					<WebUrl>https://mytenant.sharepoint.com/sites/Test</WebUrl>
				</ItemEventProperties>
				<ListEventProperties i:nil="true"/>
				<SecurityEventProperties i:nil="true"/>
				<UICultureLCID>1033</UICultureLCID>
				<WebEventProperties i:nil="true"/>
			</properties>
		</ProcessEvent>
	</s:Body>
</s:Envelope>

This technique allows to use Azure function as remote event receiver and debug it locally. Hope it will help someone.

Update 2021-11-22: see also one strange problem about using of remote event receivers in SPO sites: Strange problem with remote event receivers not firing in Sharepoint Online sites which urls/titles ends with digits.

How to edit properties of SPFx web part using PnP.Framework

In my previous post I showed how to add SPFx web part on modern page using PnP.Framework (see How to add SPFx web part on modern page using PnP.Framework). In this post I will continue to familiarize readers of my blog with this topic and will show how to edit web part properties of SPFx web part using PnP.Framework.

For editing web part property we need to know 3 things:

• web part id
• property name
• property value
  

You may get web part id and property name from manifest of your web part. Having these values you may set SPFx web part property using the following code:

var ctx = ...;
var page = ctx.Web.LoadClientSidePage(pageName);

IPageWebPart webpart = null;
foreach (var control in page.Controls)
{
    if (control is IPageWebPart && (control as IPageWebPart).WebPartId == webPartId)
    {
        webpart = control as IPageWebPart;
        break;
    }
}

if (webpart != null)
{
    var propertiesObj = JsonConvert.DeserializeObject<JObject>(webpart.PropertiesJson);
    propertiesObj[propertyName] = propertyValue;
    webpart.PropertiesJson = propertiesObj.ToString();
    page.Save();
    page.Publish();
}

At first we get instance of modern page. Then find needed SPFx web part on the page using web part id. For found web part we deserialize its PropertiesJson property to JObject and set its property to passed value. After that we serialized it back to string and store to webPart.PropertiesJson property. Finally we save and publish parent page. After that our SPFx web part will have new property set.

How to add SPFx web part on modern page using PnP.Framework

If you migrated from OfficeDevPnP (SharePointPnPCoreOnline nuget package) to PnP.Framework you will need to rewrite code which adds SPFx web parts on modern pages. Old code which used OfficeDevPnP looked like that:

var page = ctx.Web.LoadClientSidePage(pageName);

var groupInfoComponent = new ClientSideComponent();
groupInfoComponent.Id = webPartId;

groupInfoComponent.Manifest = webPartManifest;
page.AddSection(CanvasSectionTemplate.OneColumn, 1);

var groupInfiWP  = new ClientSideWebPart(groupInfoComponent);
page.AddControl(groupInfiWP, page.Sections[page.Sections.Count - 1].Columns[0]);

page.Save();
page.Publish();

But it won't compile with PnP.Framework because ClientSideWebPart became IPageWebPart. Also ClientSideComponent means something different and doesn't have the same properties. In order to add SPFx web part to the modern page with PnP.Framework the following code can be used:

var page = ctx.Web.LoadClientSidePage(pageName);

var groupInfoComponent = page.AvailablePageComponents().FirstOrDefault(c => string.Compare(c.Id, webPartId, true) == 0);
var groupInfoWP = page.NewWebPart(groupInfoComponent);
page.AddSection(CanvasSectionTemplate.OneColumn, 1);
page.AddControl(groupInfoWP, page.Sections[page.Sections.Count - 1].Columns[0]);

page.Save();
page.Publish();

Here we first get web part reference from page.AvailablePageComponents() and then create new web part using page.NewWebPart() method call. After that we add web part on a page using page.AddControl() as before. Hope it will help someone.

Use assemblies aliases when perform migration from SharePointPnPCoreOnline to PnP.Framework

Some time ago PnP team announced that SharePointPnPCoreOnline nuget package became retired and we should use PnP.Framework now. Latest available version of SharePointPnPCoreOnline is 3.28.2012 and it won't be developed further. Based on that recommendation we performed migration from SharePointPnPCoreOnline to PnP.Framework. During migration there were several interesting issues and I'm going to write series of posts about these issues.

One of the issue was that one class SharePointPnP.IdentityModel.Extensions.S2S.Protocols.OAuth2.OAuth2AccessTokenResponse was defined in 2 different assemblies in the same namespace:

• SharePointPnP.IdentityModel.Extensions.dll
• PnP.Framework
  

Compiler couldn't resolve this ambiguity and showed an error:

Since class name is the same and namespace is the same - in order to resolve this issue we need to use  quite rarely used technique called extern aliases. At first in VS on referenced assembly's properties window we need to specify assembly alias. By default all referenced assemblies have "global" alias - so we need to change it on custom one:

Then in cs file which has ambiguous class name on the top of the file we need to define our alias and then add "using" with this alias:

SharePointPnPIdentityModelExtensions;
...
using SharePointPnPIdentityModelExtensions::SharePointPnP.IdentityModel.Extensions.S2S.Tokens;

After these steps compilation error has gone and solution has been successfully built.

Get Sql Server database file's max size limit with db_reader permissions

Some time ago I wrote how to get current db size in Sql Server with limited db_reader permissions (see Calculate Sql Server database size having only db_reader permissions on target database). In this post I will show how you can also get max size limit of db file also having only db_reader permissions.

As you probably know in Sql Server we may set limit on db file size (and on db transaction log file size) using the following commands (in example below we limit both files sizes to 100Mb):

ALTER DATABASE {db}
MODIFY FILE (NAME = {filename}, MAXSIZE = 100MB);
GO

ALTER DATABASE {db}
MODIFY FILE (NAME = [{filename}.Log], MAXSIZE = 100MB);
GO

As result if we will check Database properties > Files - we will see these limits on both files:

In order to get these limits programmatically using db_reader permissions we should use another system stored procedure sp_helpdb and provider database name as parameter:

exec sp_helpdb N'{databaseName}'

This stored procedure returns 2 result sets. In 2nd result set it returns field maxsize which returns max size limit for db file. Here is the code which reads maxsize field from result of sp_helpdb proc:

using (var connection = new SqlConnection(connectionString))
{
	connection.Open();
	using (var cmd = connection.CreateCommand())
	{
		cmd.CommandText = $"exec sp_helpdb N'{connection.Database}'";
		using (var reader = cmd.ExecuteReader())
		{
			if (reader.NextResult())
			{
				if (reader.Read())
				{
					return reader["maxsize"] as string;
				}
			}
		}
	}
}

For database used in example above it will return string "100 Mb". Hope that it will help someone.

How to get specific subfolder from source control repository in Azure DevOps pipeline

Often in Azure DevOps pipeline we only need some specific subfolder instead of whole repository branch. If we will check "Get sources" task we will notice that there is no way to choose subfolder: we may only select project, repository and branch but there is no field for selecting specific folder:

However it is not that hard to achieve our goal. After "Get sources" we need to add "Delete files from" task:

and in this task enumerate all subfolders which are not needed:

As result all subfolders enumerated there will be deleted (in example above these are subfolder1, subfolder2 and subfolder3) from the root folder with copied sources before to continue pipeline execution. This trick will allow to get only specific subfolder from source control for Azure DevOps pipeline.

Sharepoint MVP 2021

Today I've got email from MS that my MVP status has been renewaed. This is my 11th award and I'm very excited and proud that got recognized for my community contribution for the last year. This year was challenging and probably was most different from previous years. Pandemic added own corrections to our lives. We had to change our daily routines to adopt for the new normal. Many people went to online and start working remotely. From other side it added new requirements to digital tools which help people do their work in this not very easy time. Applications like MS Teams, Zoom, etc started to play important role in our lives and growing amount of users of these apps confirm that. In our work we pay more attention to scalability and performance nowadays which will allow to handle massive users grow. All of that was difficult and challenging but at the same time very interesting from professional point of view. I've got familiar with many new technologies from O365, Azure, Sharepoint Online world which help with these goals. And tried to share my experience with other developers in my technical blog, forums, webinars, etc. That's why I'm especially glad that my contribution was recognized by MS for this challenging year. Thank you and let's continue our discovery in IT world.