Wednesday 30 January 2019

End-to-end guide to SharePoint Site Designs and PnP templates using a C# Azure Function - part 2

This article is the continuation of my mini-series on creating a custom template for a SharePoint site or Microsoft Team using Site Designs and a PnP template. The focus is on use of a C# function, since Microsoft's documentation shows use of PowerShell which is unlikely to be an option in future since it is not supported by Azure Functions v2. In this series:

  1. Part 1 - Azure queue, app registration, Flow, Site Design and Site Script
  2. Part 2 - the Azure Function, Key Vault, enabling Managed Service Identity, and the PnP template [this article]
Configuring the Azure Function for authentication
So we created the Function app in the previous article, but there is no code there yet - and we still have some config to do. It's still in preview here in early 2019, but I'm choosing to use the integration between Azure Key Vault and Azure Functions app settings for storing credentials (the app ID and app secret in this case). This is the thing where the credentials are stored in Key Vault, but you don't need to write any special code to fetch them (or indeed, modify any Azure Function code you already have) - instead, you use tokens in app settings which point over to the items in key vault. See the "Key Vault references for Application Settings" section of Simplifying security for serverless and web apps with Azure Functions and App Service. In general, this is a far better way of dealing with credentials than storing them in App Settings, because you can control exactly who can access them – in terms of users working with Azure and THINGS in Azure (e.g. particular App Services, or anything else with a Managed Service Identity).

Step 1 – enable Managed Service Identity for your Function app
  1. In the Azure Portal, go to the “Platform features” area for your Function app and find the “Identity” item:
  2. Enable the “system assigned” managed identity:
  3. Click “Save” and then acknowledge the message which appears:
  4. Your Function app’s managed identity will now be registered:
Step 2 - add credentials to Key Vault and grant access
I’m going to skim over this bit slightly, but the full documentation is at if you need it.
  1. First, add the Client ID and Client Secret for your SharePoint app registration as secrets in the Key Vault. (N.B. If you don’t already have a Key Vault instance in your Azure subscription, you’ll need to create one). Choose any names you like, but mine look like this:
  3. For each of these, I need to obtain the full identifier. Do this by clicking into the current version of each secret once you’ve created it, and finding the “Secret Identifier” property:
  4. Copy these URI values as we’ll need them in a second.
  5. Next, grant access to these secrets to your Azure Function. Still in the Key Vault area, go into the “Access policies” area:
  6. Add a new policy:
  7. First, select the principal – here, you are searching for your Azure Function app by name:

  8. Click the “Select” button once you’ve found it.
  9. Now we amend the “Secret permissions”:
  10. Check the “Get” option:
  11. Finally, click “OK” to confirm the permissions:
Step 3 - add App Settings to point to Key Vault entries
In the App Settings for my Function, I need to use the convention of @Microsoft.KeyVault(SecretUri=secret_uri_with_version) to point to my credentials stored securely in Key Vault. So having obtained the URI for each of my secrets in earlier steps, I add entries like these with the App Settings key names used in my code. For each one, value of secret_uri_with_version is the URI for the appropriate secret:

The Azure Function
The final piece is the Azure Function - the core code for this is below, but you can also download the full project from the Github project linked at the end. Some notes about the code:
  • As noted in the previous article, we’re using SharePoint app-only authentication to talk to SharePoint - and the auth code reflects that. You'll need to use alternative PnP AuthenticationManager methods if you choose to use AAD app-only authentication with a certificate
  • I use a separate class to slightly abstract away the Client ID and Client Secret, but it's simply a class with a property for each one. Feel free to create one, or just use local variables instead
Overall there's nothing too complex here, but I find always need a reference for how to pick up the location of the PnP template file when everything is running in Azure. The paths in the code line up with what's in my Visual Studio project that gets published to the Azure Function app:

The PnP template
For completeness, here's a simple PnP template to illustrate the integration - it provisions a modern home page with specific web parts, a content type and a document library, and also adds an entry to the web property bag:

The result

Once everything is in place, you have a great self-service Teams or SharePoint site provisioning process for your organization - you can configure the sites in just about any way possible between Site Designs and PnP templates.
For use with Teams/Office 365 Groups, you can set the default Site Design to be applied for team sites, and your templates will be invoked. For standalone SharePoint sites, users can request a new site from the SharePoint home page:

Your custom Site Design will appear in the list:

..and then the details of the site are collected:

Once the user hits the “Create site” button, the site will be created and Office 365 will indicate that the Site Design and associated templating is taking place:

After a few seconds the site will be available, and both your Site Design and PnP template will have been applied. In my case, I have:
  • From my Site Design:
    • A content type, document library and navigation links
    • Application of my “COB purple” modern theme
  • From my PnP template:
    • A custom modern home page with specific web parts in specific zones
    • Regional settings applied to the site
    • Another custom document library, for good measure
    • A property bag entry
    • Request access settings applied
It’s slightly eye-watering to look at, but that’s great for testing :)


Integrating PnP templating with a SharePoint site design is slightly involved, but the power it brings in terms of being able to template both sites for Microsoft Teams and standard SharePoint means it's an important technique. Importantly, the use of PnP means that you can configure the sites in ways which aren't currently possible with Site Designs alone. Hopefully this guide is some use towards understanding the mechanisms. As I mentioned previously, all the files used here can be downloaded from:

End-to-end guide to SharePoint Site Designs and PnP templates using a C# Azure Function - part 1

A pattern that I think will become increasingly useful in 2019 and beyond is creating a custom template for a SharePoint site or Microsoft Team using Site Designs and a PnP template. It’s a topic that I and many others have already covered in some way, but at this time most of the samples (including the official Microsoft documentation) show using PowerShell in an Azure Function – but this now feels strange since Azure Functions V2 has been released and there is no support for PowerShell. As you might know, it was only ever “experimental” in v1, and it remains to be seen whether Microsoft will resolve the blocking issues they’ve cited (related to V2 function bindings and the PowerShell language). So in this mini-series, let’s walk through what an implementation would look like using an Azure Function written in C#:

  1. Part 1 - Azure queue, app registration, Flow, Site Design and Site Script [this article]
  2. Part 2 - the Azure Function, Key Vault, enabling Managed Service Identity, and the PnP template

NOTE: all of the files and code I'm referencing are available for download from this Github repo:

The recipe

Overall the solution elements are:
  • A SharePoint Site Design (and corresponding Site Script)
  • A Flow which adds an item to an Azure Queue
  • A SharePoint app registration which allows the code to authenticate to SharePoint and modify the site
  • An Azure Function (queue-triggered) which authenticates to SharePoint (using the app registration) and applies a PnP template to the site
A simplified representation of this sequence might be:

The implementation

Phase 1 – Azure things and SharePoint app registration

We have some pre-requisites to set up first – the Azure function app, queue and the SharePoint app registration. I recommend creating these first as you’ll need references from these things to put into later steps (your Flow needs details of the queue for example).
So let’s start there..
Creating the queue in Azure
  1. To do this, navigate to the Azure portal, and then go into an appropriate Storage Account that you have. If you don’t have one already, just create one (it’s a container for Azure storage things such as queues, tables and BLOBs ).
  2. Once there, go into the “Queues” area and create a new one:
  3. Also grab the storage key at this point. Go into the area for the storage account in general (not the specific queue we just created), and go into the “Access keys” area:

You’ll need both the queue name and access key later when we come to configure the flow, so keep these details somewhere handy but secure. Your Flow will need to know them so it can put a message on the queue :)
Creating the Azure function app
I won’t go into detail on this step as I’ve covered it elsewhere (e.g. 3 ways to create and debug Azure Functions) – but fundamentally we want to create the function app upfront to reserve the URL in Azure. We don’t have to do anything else at this stage. But it’s a good idea to do it early, as it would be annoying to specify the URL in one of the steps (e.g. registering the SharePoint app) only to find out that out someone else has taken it and you need to re-do lots of the config.

Of course, creating the function app in the Azure portal is probably the simplest way. I tend to just click the big “Create” button and search for “function app”:

Click the “Create” button when you’ve found the item.

This will then take you to the place where you can supply the details. The important things really are to:
  • Use a URL that makes sense for what you’re doing
  • Ensure you’re on the Consumption Plan
  • Associate the function app with a storage account related to this solution
For example:

You’ll need to decide whether you’re creating a new Resource Group for this stuff, or whether it fits with one you already have. Again, this is just standard Azure app service stuff and there are lots of documentation and articles to help with these decisions if needed.

Click “Create” when you’re ready. Ensure the function app gets created successfully, and make a note of the URL you used – you can get it back from listing the App Services you have in Azure if needed.

Remember! Your Function App should use Azure Functions V1 for now!
As of early 2019, you'll need to switch your Function App to use V1 of the functions runtime for now (due to .NET dependency issues with SharePoint CSOM and PnP). You'll need to do this before any actual functions exist in the app. To make the switch, go into the Function app settings and find the "Runtime version" setting. Change this to "v1" and save the config.

Creating the SharePoint app registration
To do this, we use SharePoint’s AppRegNew.aspx page to register our app. Remember this authentication approach results in a token that can only be used against SharePoint (as opposed to the Graph or other areas of Office 365), but that’s all we need when templating SharePoint sites – even if it happens to be a site which belongs to a Microsoft Team.
  1. Navigate to the AppRegNew.aspx page in the root site collection of your tenant – so
  2. Fill in the details for the app:
    1. Client ID and Client Secret can be new identifiers from the “Generate” button.
    2. The Title should be something you’ll recognise as belonging to this solution e.g. “Site Design Provisioning” or similar.
    3. The App Domain and Redirect URI should reflect the URL you used earlier for the function app – the former has the domain, the latter is qualified with “https://” at the beginning:
    4. Ensure you see the confirmation message – when you do, keep these details safe:

Authentication flavours
The alternative to SharePoint add-in authentication is to use an AAD app registration and AAD auth. However, for app-only auth this approach is more involved as it requires you to create and upload a certificate (possibly self-signed) and select appropriate AAD scopes etc. If your code only needs to talk to SharePoint (e.g. to apply site templates) rather than talk to other Office 365 services via the Graph, then SharePoint add-in authentication is simpler. Just understand that you might need to use the other approach if you also want to configure the Microsoft Team or take some other actions.
Grant admin consent for the app
Anyway, assuming that we'll stick with SharePoint app authentication, the next step is to trust the app, so that it has the appropriate access to your tenant:
  1. Go to https://[yourtenant] (notice the -admin in the URL).
  2. In the App Id field, paste the Client ID that you copied, and choose Lookup.
  3. In the Permission Request XML field, paste the following XML:

    <AppPermissionRequests AllowAppOnlyPolicy="true" >
       <AppPermissionRequest Scope="http://sharepoint/content/tenant" Right="FullControl" />

  4. Click Create:

  5. To confirm that you want to trust this app, choose Trust It:

We’re now done with the prerequisites – so we’re ready for the Site Design, Flow and code for the Azure Function.
The Flow
The Flow itself is extremely simple. All you need is:
  • A HTTP trigger
  • A “put a message on a queue” action
The steps are:
  1. Go to the Flow portal in Office 365 and in the “My Flows" are click New > Create from blank:
  2. Add a “When a HTTP request is received” trigger. To do this, search for “http” using the search box:

  3. Find the “When a HTTP request is received” trigger and select it to add it to the Flow:

  4. Enter the following as the request body:

  5. Click “New step”, and type “queue” into the search box. Find and select the “Azure Queues” item:

  6. From there, select the “Put a message on a queue” item:
  7. Assuming you don’t already have a connection to your Azure storage account defined, click the ellipses (…) on the action in the flow and find the “Add new connection” item:
  8. Enter the details of the storage account name and key in the boxes shown (we’ll select the actual queue within this storage account in a second). Note that the connection name can be anything meaningful – but the connection itself is to the overall storage account, so it’s a good idea to include that in the name perhaps:
  9. Click “Create”.
  10. The Flow action should now let you select the specific queue, and also what details to put on it. Select the queue you created from the dropdown, and for the "Message” value, select “webUrl” from the HTTP request action:
  11. You should end up looking something like this:
  12. Click in the Flow name near the top left of the screen, and rename your Flow to something sensible:
  13. Finally, click “Save” to save the Flow:
Obtain the URL for your Flow
You’ll need the URL of your Flow to put into your site script. To obtain it, follow these steps:
  1. Go back in to edit your Flow.
  2. Edit the “when a HTTP request is received” activity, and grab the HTTP POST URL that’s specified (there’s a ‘copy to clipboard’ button to the right):

  3. Store the URL somewhere safe, ready for the next step.
The Site Design and Site Script
As you’ll remember, a Site Design is effectively a “registration” of sorts which is made up of one or more Site Scripts (JSON definitions of what should be provisioning into the site). So you can effectively share site scripts amongst many site designs if needed. Here’s a sample site script which:
  • Applies a theme named ‘COB purple’ which I’ve previously defined in my tenant – see for more details on modern SharePoint theming
  • Calls my Flow using it’s URL – I’ve edited the site script to paste in my Flow URL from the previous step
  • Provisions a couple of custom lists (one with a custom column) and adds them to the site navigation
  • Configures JSON column formatting on some columns

So that’s a site script. You’ll need to register both the site script and the site design using some PowerShell like the below.

Step 1 – register the site script:

You'll get an output something like this:

Id          : be7c02a8-9134-4961-921a-cffc4772e97c
Title       : COB project site script
Description :
Content     :
Version     : 0

Copy the ID that was returned as we'll need it in the next step. When we register a site design, we need to point to one or more site scripts that comprise the provisioning steps - so paste it into the next bit of PowerShell you need, as I've done below:

You'll now get an output something like this:

Id                  : ef49c695-b5e3-49ef-afba-ef053ca61414
Title               : COB project site
WebTemplate         : 64
SiteScriptIds       : {be7c02a8-9134-4961-921a-cffc4772e97c}
Description         : Creates a COB project site with a theme applied
PreviewImageUrl     :
PreviewImageAltText :
IsDefault           : False
Version             : 1

Success! Your site design and site script have now been registered, and it's hooked up to the Flow. However, we're not done yet - we still need our C# Azure Function which will actually do the work of applying the PnP template :) We'll do that in the next post..

Part 2 - the Azure Function, Key Vault, enabling Managed Service Identity, and the PnP template

Wednesday 2 January 2019

PowerApps – sample formulas to implement offline support in your app

Implementing offline support in a PowerApp isn’t necessarily the simplest thing. Currently there is no magic setting which enables this (unfortunately). Instead, you have to write the behaviour you need into your formulas – but it isn’t the most complex thing either. I linked to some sample formulas in a previous post, but today I want to go through them in a bit more detail and walk through the offline behaviour I chose and the corresponding formulas.

I'm speaking about this at the SharePoint Conference 2019!
If you want to hear more about this, I have a session at SPC 2019 on the topic. The conference happens 21-23 May in Las Vegas - see for more details. And use code 'OBRIEN' while you're at it! ;)

The upside of having to implement offline support yourself is that you’re in full control of the user experience. I chose to implement a couple of simple screens in the app to show appropriate messages, with the following behaviour (excuse the quick and dirty Visio here):

PowerApps offline process

So that’s the behaviour. You might also decide to make it very clear to the user whether they currently have a connection – I added a “connection bar” to the home screen of my app for this, which is really just a Label control with the text and fill colour set appropriately:

PowerApps online - Copy 2 PowerApps offline - Copy

The formulas are simply:



If the user is offline when they submit the form, they are taken to the “pending” screen with this message:

 Generic - PowerApp - pending screen

..but if the user is online when they submit, OR he/she returns to the app later after initially being offline, then the record is submitted and they are navigated to the confirmation screen:

Generic - PowerApp - confirmation screen

As you can see from the messages, we’re telling the user that they must return to the app if they initially submit their request at a time when they don’t have a connection. PowerApps cannot talk to the data source at this point, but we can store the data locally until a connection is re-established.

If you didn’t want to redirect the user to entirely different screens in the app, you could do something else like use the Notify() function:


In my case, however, I felt that the thin notification bar at the top wasn’t obvious enough - but it’s up to you.


Here are the formulas I used for the behaviour to save data locally/navigate to the appropriate screens:

Formula 1:
  • Where: submit button of my form
  • Description: submits the record to SharePoint if there is a connection, otherwise saves it locally to the device – this is done by creating a local collection with ClearCollect() and then calling SaveData().The user is then taken to the "pending" screen.
Formula 2:
  • Where: timer control added to pending screen
  • Description: looks to see if a saved collection named 'LocalRecord' exists, and if so submits the record to SharePoint. The collection is then cleared, and the user is then taken to the "confirmation" screen.

When using this approach, I decided to add a timer control to both the "pending" screen and the home screen of my app - this seems to work well in terms of ensuring the data is submitted as soon as the user comes back to the app with a connection, regardless of whether they resume where they left off (usually the pending screen), or the app has to restart (perhaps because the device was restarted). Importantly, it *is* necessary that the user comes back into the app once a connection has been re-established though - with PowerApps, it isn't possible to "fire and forget" a request to a data source unfortunately (i.e. for the record to be auto-submitted when the device comes back online *without* coming back into the app). I'm assuming only certain apps with low-level OS integration which can perform tasks like this whilst running in the background are able to do this, and PowerApps and the Nintex mobile app (for example) are the same in this regard.


Offline support in PowerApps isn’t a simple flick of a switch, but once you know the recipe it’s not too hard. Essentially it’s a question of detecting if the device has a connection, and if not using Collect() or ClearCollect() to create a local collection and then calling SaveData() to ensure it is properly saved to the device. A PowerApps Timer control can be useful way of “auto-submitting” the data, although equally you could choose to provide users a button to explicitly click – in either case, you pick up the data from the local collection and then submit it to the data source using something like Patch() to add a database record or SharePoint list item, or Update() to update. Happy app building!