Using on-premises data gateway with SharePoint 2019 and Microsoft Flow

I wanted to create a working environment with the on-premises data gateway in combination with SharePoint 2019. The scenario I configured is a simple one as it only sends a mail when a new item has been added to a library in my SharePoint 2019 farm. This scenario would present more possibilities for customers who are using SharePoint on-premises and are looking for a Workflow or PowerApp solution without having to use for example SharePoint Designer.

This post will first describe the installation of the on-premises data gateway and then the creation of the Microsoft Flow using this gateway.

Installing the on-premises data gateway

Information can be found at and you can download the gateway using at
I’ve downloaded the on-premises data gateway and started the installation on a server which also hosts my Azure AD Connect tool.

Click on Next

Install to the default location (or specify a location), accept the terms and use and click on Install

Click on Yes

You will be asked the same question again and also click on Yes

Enter an Email address to use with this gateway and click on Sign in

Connect to you Office 365 Tenant and click on Sign In (Aanmelden)

Give your on-premises data gateway a name and enter a recovery key.
Click on Configure

The gateway should be created successfully and it will register itself as a gateway for your Office 365 tenant.
You can disable helping Microsoft if you like and go to Diagnostics

Start a network ports test by clicking on Start new test

It may take a while but should give the above results.
In my case it completed but failed.
Open the last completed test result.

Everything was successful except the two server names and IP.
This can be at Microsoft’s end or at my end but I decided to not troubleshoot this as it has 31 successful server names.
Now go to

Configuring Microsoft Flow Connections

The Gateway should be automatically created for your tenant where you only need to create a connection.
Please go to to verify the available plans and if you can add Gateway Connections as it does not work with the free version or E1 license.

Click on the “Gear” icon and select Connections

Create a new connection

Select SharePoint but you can also use SQL Server or any of the available connections which work with the On-Premises Data Gateway

Select connecting using the on-premises data gateway and scroll down

Enter the credentials for your on-premises environment and select the created gateway.
Note that you can add multiple gateways depending on your flow plan.
Go back to the flows homepage

Creating the Microsoft Flow

Click on My Flows

Create a new Microsoft Flow

Click on Create from blank

Select When a file is created in a folder from the SharePoint available triggers

First select the connection which you are going to use

Enter the Site Address and Folder Id (you can also use the folder viewer to select a folder from your site).
Click on + New step

I’m just going to send an email using the Office 365 Outlook action

It will be sent to my personal mail account with metadata from the SharePoint 2019 library.
First click on Save and then on Test

Save & Test and add a file to your SharePoint 2019 library and wait for the Microsoft Flow to continue

The flow executed successfully and an email has been sent to my mailbox

I have added a new .txt document to verify if it also triggers without testing and this test was also successful

This “simple” scenario shows that you can communicate with your SharePoint On-Premises environment using the on-premises data gateway from Microsoft.
Microsoft Flows are being triggered by the On-Premises environment.
You can now build your complex workflows not using SharePoint Designer but using Microsoft Flow.

The logging from the on-premises data gateway also provides good information regarding your gateway.
I have verified the logs for my environment and can see that Microsoft Flow is actively polling my SharePoint on-premises library for new content.

DM.EnterpriseGateway Information: 0 : 2018-10-02T19:22:55.9955178Z DM.EnterpriseGateway 6d3ce9bd-66b1-4af9-8140-38d010ebc9a1        2e3ae2b3-44d1-49a1-89db-f33e83245d52             MGPP   983aa058-7427-4c1b-97d2-08090368600b    46EFAADE [DM.GatewayCore] Deserialized GatewayHttpWebRequest, executing

DM.EnterpriseGateway Information: 0 : 2018-10-02T19:22:55.9955178Z DM.EnterpriseGateway 4b2ee4e3-e434-4e4b-9f7e-f1e6c837a391              2e3ae2b3-44d1-49a1-89db-f33e83245d52             MWPR  983aa058-7427-4c1b-97d2-08090368600b    2BD360D1 [DM.GatewayCore] Processing http(s) request with URL:$filter=TimeCreated%20ge%20datetime’2018-10-02T19:00:09Z’&$orderby=TimeCreated,Name&@p=’%2fShared+Documents’

DM.EnterpriseGateway Information: 0 : 2018-10-02T19:22:55.9955178Z DM.EnterpriseGateway 4b2ee4e3-e434-4e4b-9f7e-f1e6c837a391              2e3ae2b3-44d1-49a1-89db-f33e83245d52             MWPR  983aa058-7427-4c1b-97d2-08090368600b    BB3A41D3 [DM.GatewayCore] Processing https request

DM.EnterpriseGateway Information: 0 : 2018-10-02T19:22:55.9955178Z DM.EnterpriseGateway 4b2ee4e3-e434-4e4b-9f7e-f1e6c837a391              2e3ae2b3-44d1-49a1-89db-f33e83245d52             MWPR  983aa058-7427-4c1b-97d2-08090368600b    DFE1F964 [DM.GatewayCore] Http(s) request with windows authentication

SharePoint Online site provisioning using Microsoft Flow, Azure Functions and Azure Storage Queue

A while back I created a provider hosted app using CSOM in C# for creating project sites but this required the users to have sufficient permissions to create a site. Using Microsoft Flow, Azure Function, Azure Storage Queue, PowerShell and SharePoint Online I created a proof of concept with the latest techniques and using the AppId/AppSecret so the user doesn’t need additional permissions. This solution isn’t free as it needs an Azure Subscription but the costs are minimal. Please find references to Microsoft in the summary at the end.

This article describes the following scenario:

  1. The user creates an item in a SharePoint list.
  2. Microsoft Flow will be triggered on item creation.
  3. Microsoft Flow will add a message on the Azure Storage Queue.
  4. The Azure Function will monitor the Azure Storage Queue and create the subsite based on the values entered in the SharePoint list using PowerShell.

This article has the following chapters:

  • Create SharePoint List
  • Get and register AppId and AppSecret in SharePoint Online
  • Create Azure Storage Queue
  • Create Azure Function
  • Create PowerShell Script
  • Test Azure Storage Queue
  • Create Microsoft Flow

Create SharePoint List

First we are going to create a list in SharePoint which we are going to use for our site metadata.

Add an App

Custom List


Add the below columns:

  • SiteURL –> Single line of Text
  • SiteTemplate –> Choice
  • SiteLanguage –> Choice


The list has been created which we are going to use for our site provisioning.

Get AppId and AppSecret in SharePoint Online

It is possible to use a username and password for the Azure Function but it is also possible to use an AppId and AppSecret for impersonation.
In this scenario we are going to use an AppId and AppSecret.

Go to the site collection where you want to register the app by appending the url with “_layouts/15/appregnew.aspx”

Fill in the above information and click on create


Save the Client Id and Secret as we are going to need it for our Azure Function.
Next append /_layouts/appinv.aspx to the url

With the below Permission Request XML we allow the app access to the site collection. You can specify different levels which are explained at .

<AppPermissionRequests AllowAppOnlyPolicy=”true”>

<AppPermissionRequest Scope=”http://sharepoint/content/sitecollection” Right=”FullControl” />


and click on Create

Trust It

Create Azure Storage Queue

We are going to setup the Azure Storage Queue which will handle all our messages which have been sent using Microsoft Flow.
Please note that this can also be achieved without the Azure Storage Queue as you can directly sent the message to the Azure Function using an Azure HttpTrigger function.

First go to your Azure Dashboard

Storage accounts



Open the newly created storage account

Click on Queues

+ Queue


The Azure Storage Queue has now been created which we use within our Microsoft Flow and Azure Function.

Create Azure Function

The next thing we will build is the Azure Function. The Azure Function will be created based on PowerShell and the SharePointPnPPowerShellOnline module.
We are going to start from the Azure Dashboard.

Go to the App Services


Function App


We are going to use the existing resource group and storage which we created during the Azure Storage Account. Click on Create

Open the newly created Azure Function

New function

Enable Experimental Language Support and navigate to Queue trigger

Click on PowerShell

Enter the queue name we created earlier. And click on New

Select the Azure Storage Account

Create and navigate back to the Platform features

Go to Platform features

Open Advanced tools (Kudu)

Click on Debug Console and then on PowerShell

Navigate to Site –> wwwroot –> QueueTriggerPowerShell

Create a new folder called “modules”

We are going to upload the PowerShell DLL’s which we are going to use here as it is not possible to import-modules from within the Azure Function. You can drag and drop the files to this folder.
The files we need are by default installed in the following location: C:\Program Files\WindowsPowerShell\Modules\SharePointPnPPowerShellOnline

Copy the contents from this folder to the Azure Function.
If you are missing this folder; Install this using PowerShell on the workstation with the command: Install-Module SharePointPnPPowerShellOnline


Also copy the items from the following locations:

  • C:\Windows\assembly\GAC_MSIL\Microsoft.IdentityModel
  • C:\Windows\Microsoft.NET\assembly\GAC_MSIL\Microsoft.IdentityModel.Extensions

Go back to the function

Go to the application settings

Select 64-bit and scroll down

Add the AppId and AppSecret with the key to the application settings as we can reference to these settings from the Azure Function.
Save the modification and in the next chapter we will create the PowerShell script.

Create PowerShell Script

Go to the QueueTriggerPowerShell in the Azure Function


Add the below PowerShell code

$requestBody = Get-Content $triggerInput -Raw | ConvertFrom-Json

$ParentSiteUrl = ""

$WebTemplate = $requestBody.WebTemplate

$SiteTitle = $requestBody.SiteTitle

$SiteDescription = "Site with PowerShell"

$SiteURL = $requestBody.SiteURL

$SiteLanguage = $requestBody.SiteLanguage

$AppId = $env:AppId

$AppSecret = $env:AppSecret

connect-PnPOnline -AppId $AppId -AppSecret $AppSecret -url $ParentSiteUrl

New-PnPWeb -Title $SiteTitle -url $SiteURL -Locale $SiteLanguage -Template $WebTemplate -Description $SiteDescription

Write-Output "PowerShell script processed queue message '$requestBody'"

Click on test in the right corner

Enter the below Request body

“WebTemplate”: “STS#0”,
“SiteTitle”: “TestCreation1”,
“SiteURL”: “TestCreation1”,
“SiteLanguage”: 1033

and click on Save and run

You can verify the log for success and navigate to the created site

We now know that the PowerShell code is successful.

Test Azure Storage Queue

Go to the Azure Storage Queue to test if adding a message is being successfully processed by the Azure Function.

Add message

OK and you can verify if the Azure function picked up the message if you still have the log open

Or go to the newly created site

We confirmed the Azure Storage Queue with the Azure Function is working correctly.

Create Microsoft Flow

We can now create a Microsoft Flow that will add an message in the Azure Storage Queue which will be picked up by our Azure Function.
Go to

Create from blank

When an item is created

And add a new step

Put a message on a queue

Add a new connection if you already had one like me

The Connection Name can be anything where the Storage Account Name and Shared Storage Key can be found in Azure



Save the flow and create a new item in the previous created SharePoint List


Save and first verify the Microsoft Flow

Next verify the Azure Function Log if still open

And last verify if the site has been created


The site has been created successfully.


We have now created a working site provisioning solution based on a SharePoint list.
This solution uses multiple techniques such as Microsoft Flow, Azure Storage Queues, Azure Functions and SharePoint Online.
This is just an example of working with these techniques but you can for example do more after the site creation such as adding extra permissions and set default columns.
It is possible to do more with Microsoft Flow as for example send an email after creation or update the status during the creation

You can find more information at regarding for example an app ID and app secret with administrative rights on your tenant, Microsoft Flow and an Azure function. Costs for an Azure Function are mentioned in and queue costs at

Information about the SharePoint PnP PowerShell CmdLets can be found at and

Please let me know your use case for Azure Functions and if there are any questions.

PowerShell Office 365 Inventory tool

I’ve thought of creating a different tool after creating the PowerShell Office 365 tool a couple of days ago to improve my PowerShell coding and to make my work and that of my colleagues a little bit easier. The PowerShell Office 365 inventory tool lists a lot of information you would like to see when troubleshooting or getting information from a tenant you never connected to.

You can follow and download the PowerShell Office 365 Inventory Tool at GitHub:

This PowerShell Office 365 Inventory tool will retrieve the following information:

  • Azure Active Directory Users
  • Azure Active Directory Deleted Users
  • Azure Active Directory External Users
  • Azure Active Directory Contacts
  • Azure Active Directory Groups
  • Azure Active Directory Licenses
  • Azure Active DIrectory Domains
  • Exchange Mailboxes
  • Exchange Archives
  • Exchange Groups
  • SharePoint Sites
  • SharePoint Webs

Please note that you will need a few pre-requisites before fully able to run this tool:

Please let me know which information you would like to see added to this tool and I’ll add this is as soon as possible.


[How to] PowerShell Office 365 Inventory Tool


Run ‘Start-Office365Inventory.ps1’. You will automatically be asked to run as administrator if you ran this as a normal user.


First Connect to Office 365 by clicking on the credential logo, by pressing f4 or via the menu

Fill in your credentials and press OK


You are connecting successfully when the icons are green.

There are 2 possible actions now:

  • Navigate to a tab and run only this action
  • Press the run all button to run all available actions


Only the Azure Active Directory Licenses have been returned. You can verify which actions have been run on the home tab.


Now click on the run all actions button. You can see the progress on the Home tab.

Please note that you will need site collections permissions for the SharePoint Webs option as this uses CSOM to connect to the different webs.

You will receive the following message but it will continue looking for more webs


After all actions everything should be green


Run the action individually if you encounter an error as the exception will be shown in the below message center.

You can create a .CSV or .HTML file based on the items visible at any time in the view by clicking on the button next to the CSV Report.
The report will be saved to the report folder located in the script root.

The .CSV report will look like:

The .HTML report will look like:

It is also possible to create a .HTML file for all the available tabs in a nice format. Please note that this is still a work in progress but it will lists everything at the moment.


Error log
Some error information is displayed on the background PowerShell window as other information is only readable using the errorlog.
Please send me this information and the message in the below message box if you encounter an error.