Conditional Power Automate flow triggers for SharePoint Online Pages and NEWS Post

SharePoint Online Pages library is a container for different type of pages (News post, Page, Space, News Link) created in a Communication or Team site. There can be various scenarios to have a Power Automate Flow associated to a SharePoint Site pages library to handle additional processes after a Page or a News post is published. In this blog post, let us see how to

  1. Trigger the flow if a News post is published
  2. Trigger the flow only for Major versions
  3. Trigger the flow for a specific Content Type
  4. Avoid infinite trigger loop on an Item Created/Modified trigger if a page/list item is updated by the flow

using Trigger Conditions. Trigger conditions can be used on a trigger to stop your Automated Flow from running if the conditions are not met. Unnecessary flow runs can spend your quota limits based on the license types without providing any value. To begin with, create an automated cloud flow with the SharePoint trigger When an item is created or modified and configurations for the Site Pages Library. Once you provide the Site URL where your Site Pages library exists, you will notice the Site Pages library doesn’t show in the drop-down. In the List Name property, just provide the guid of the library instead.

To get the guid, browse to the Site Pages library on the SharePoint site, go to Library settings and select the value after the List= parameter on the URL after decoding.

Trigger the flow if a News post is published

There can be scenarios to trigger the Flow when a News post is created or modified. A SharePoint property PromotedState can help identify if the SharePoint page is a News post or a normal page since all the different types of pages are stored in the same library.

LabelValueWhat it means
NotPromoted0Regular Page
PromoteOnPublish1News post in draft mode
Promoted2Published News post

The trigger condition will make sure the trigger is fired only when ever there is a News Post is published or Saved as draft (All Major and Minor versions).

@equals(triggerOutputs()?['body/PromotedState'],2)

Now add the above trigger condition in the settings of the trigger as shown below

The above trigger condition will have the flow triggered for all major versions (1.0, 1.1 .. 2.0, 2.1, ..).

There can be multiple trigger conditions which accepts Boolean value (True or False), all conditions must be True for the trigger to fire.

To trigger the flow only on first Published version of the flow, add the following trigger condition.

@and(equals(triggerOutputs()?['body/PromotedState'],2),equals(triggerOutputs()?['body/{VersionNumber}'],'1.0'))

To trigger the flow only on major versions and on News post, add the following trigger condition

@and(equals(triggerOutputs()?['body/PromotedState'],2),contains(triggerOutputs()?['body/{VersionNumber}'],'.0'))

Trigger the flow only for Major versions

The following trigger condition will make sure to fire only for Major versions (1.0, 2.0, 3.0 etc) and not for minor versions aka draft version (0.1, 0.2 etc)

@contains(triggerBody()?['{VersionNumber}'],'.0')

Trigger the flow for a specific Content Type

Content types in SharePoint are a set of columns that are grouped together to serve a specific type of content (Crisis News, Marketing News etc). A Page or a News post in a SharePoint site can be associated with content types. The trigger condition for the flow to be triggered only for a specific content type is

@equals(triggerOutputs()?['body/{ContentType}/Name'], 'Name of the Content Type')

Avoid infinite trigger loop on an Item Created/Modified trigger if a page/list item is updated by the flow

In your Automated cloud flow, if you have the Created or Modified trigger with an action to update the same item then there will be an infinite trigger loop.

The Flow checker will provide you a warning Actions in this flow may result in an infinite trigger loop. To overcome the above warning, trigger condition to the rescue.

How it will be done

The update item action on the flow should use a different connection (Service Account) in the flow, other than the user who will be using the site to create or update pages. The trigger condition will make sure the flow run will not happen if the update to the Page or News post is done by the service account using the Update item action. SharePoint Library and List has the out of the box column Modified By which holds the information on who has recently updated the item be it from the SharePoint UI or through program. The trigger condition will be written based on this column Modified By, if the column value has a different value other than the service account then the flow will be triggered.

Step 1: Create a service account with password never set to expire. Licenses are not required for this account if the flow connection is going to be used only on SharePoint connectors. Password setting Never Expires will make sure the connection is not invalidated due to a password change on the account.

Step 2: Grant edit access for the service account to the SharePoint site. This step allows the account to updates to the List or Library item.Step 3: Add a new connection to the service account

Step 4: Add the following trigger condition to the SharePoint trigger if the service account does not have an Exchange Email License

@not(equals(triggerOutputs()?['body/Editor/Claims'],'i:0#.f|membership|serviceaccountupn@domain.com'))

Replace the serviceaccountupn@domain.com with actual UPN of the service account.

If the service account has email address or a license to email service, then the trigger condition should be

@not(equals(triggerOutputs()?['body/Editor/Email'],'serviceaccountemail@domain.com '))

Tip to write the trigger condition:

Before adding the condition to the trigger, evaluate the condition on a compose action using expressions and data fields selected from Dynamic content.

After the condition is added on the compose action, click Peek code

Copy the expression from the inputs parameter

The condition to be added on the trigger must be True for the trigger to fire.

Summary:

Trigger conditions are powerful if used wisely to avoid unnecessary runs. I’ve shown some examples from the SharePoint pages library but it can be used on List trigger as well. The trigger can be written based on any data available on the trigger output. Hope you have found this informational & thanks for reading. If you are visiting my blog for the first time, please do look at my other blogposts.

Do you like this article?

Subscribe to my blog with your email address using the widget on the right side or on the bottom of this page to have new articles sent directly to your inbox the moment I publish them.

Call a SharePoint REST API as an Application in Power Automate HTTP Connector

SharePoint connector in Power Automate is very rich with various actions that can make the developers or makers life simple when it comes to interacting with SharePoint data. There might be some actions like

  • Breaking permission to a list item
  • Creating a site
  • Adding user to a SharePoint group etc

which is not possible through the SharePoint standard connector or MS Graph API as of the time I am writing this article, SharePoint REST API to rescue. The SharePoint online REST API enables developers to remotely interact with SharePoint data. There is an action Send an HTTP request to SharePoint which could come handy in many scenarios, the point to note here is the action uses the context of user aka flow creator while executing the API. In this blogpost, let us see how to call a SharePoint REST API to create a Modern SharePoint communication site as an application in a Power Automate cloud flow using the HTTP connector with the help of a Self-Signed certificate. Find below the list of steps to enable calling the SharePoint REST API using certificate credentials

  1. Creation of Self-Signed certificate
  2. Application Registration in Azure AD Portal
  3. Creation of Power Automate cloud flow with the HTTP Connector
    • Method 1: Without using Azure Key Vault
    • Method 2: Azure Key Vault to store Certificate

Pre-Requisites:

Creation of Self-Signed certificate:

The first step is to create a certificate. Refer to this blog post for instructions creating a self signed certificate using the PnP utility

https://ashiqf.com/2021/07/05/call-microsoft-graph-api-using-a-certificate-in-a-power-automate-http-connector#self-signed-certificate

Application Registration in Azure AD Portal:

Register an application in Azure AD and obtain the client id & tenant id for the registered application. In this example I have added the Sites.Read.All Application permission with Admin Consent to create the SharePoint communication site, this permission is more than enough to create the site as an Application. Grant appropriate permission based on the requirements, for e.g to break permission on list items grant Sites.Manage.All. Find below screenshot for your reference for granting permissions

To add the above created self-signed certificate, click Certificates & secrets under the Manage blade. Click Upload certificate > Select the certificate file MSFlow.cer > Add

Creation of Power Automate cloud flow with the HTTP Connector:

Let us see below how to access the SharePoint REST API to create a SharePoint site with & without using the Azure Key Vault.

  1. Method 1: Without using Azure Key Vault
  2. Method 2: Azure Key Vault to store Certificate

Method 1: Without using Azure Key Vault

In the cloud flow, add a Compose action to store the PfxBase64 value copied during the creation of the certificate. Now add the HTTP action to create a Modern Communication site

Request Type: POST

URL: https://tenantname.sharepoint.com/_api/SPSiteManager/create

Headers:

Key: accept

Value: application/json

Body:

{
  "request": {
    "Title": "Communication Site from Cloud Flow",
    "Url": "https://tenantname.sharepoint.com/sites/commsitefromPA",
    "Lcid": 1033,
    "ShareByEmailEnabled": false,
    "Description": "Description",
    "WebTemplate": "SITEPAGEPUBLISHING#0",
    "SiteDesignId": "6142d2a0-63a5-4ba0-aede-d9fefca2c767",
    "Owner": "UPNoftheSiteAdministrator@domain.com",
    "WebTemplateExtensionId": "00000000-0000-0000-0000-000000000000"
  }
}

Change the SiteDesignId for the different site teamplate Topic, Showcase, Blank

Authentication: Active Directory OAuth

  • Tenant: TenantId
  • Audience: https://tenantname.microsoft.com
  • Client ID: Azure AD Client Id
  • Pfx: Output of the compose action
  • Password: Certificate password given during the creation

Find below screenshot for your reference

Run the flow, it should be able to create the Site. Find below screenshot of the flow run

Method 2: Azure Key Vault to store Certificate

Azure Key Vault is a cloud service for storing and accessing secrets enabling your applications accessing it in a secure manner. Follow my blog article which I have written to call a Microsoft Graph API with Certificate using a Azure Key Vault to store the certificate

https://ashiqf.com/2021/07/05/call-microsoft-graph-api-using-a-certificate-in-a-power-automate-http-connector/#azure-key-vault

Summary:

Custom Connector can be used to call a SharePoint REST api in the context of the user. If you are visiting my blog for the first time, please do look at my other blogposts.

Do you like this article?

Subscribe to my blog with your email address using the widget on the right side or on the bottom of this page to have new articles sent directly to your inbox the moment I publish them.

Handle SharePoint site creation response – The site address is unavailable

There is a possibility to change the SharePoint online site address of an existing site by a SharePoint admin or Global admin. There are different options to change the site URL from

  1. SharePoint Admin center
  2. SharePoint online management shell

I’ve recently faced a scenario recently, after renaming a site URL https://tenant.sharepoint.com/sites/site1 to https://tenant.sharepoint.com/sites/site2 from the Admin center I was still not able to use the url https://tenant.sharepoint.com/sites/site1 while trying to create another site though the url has to be available. I’ve tried to create the site from the SharePoint start page & SharePoint admin center, I was getting one of the following message

  • The site address is unavailable
  • Couldn’t create the site, please go back and try again.

SharePoint Online PowerShell to the rescue, run the following command as an Administrator after creating a site with some address from the start page or admin center to rename the URL

Connect-SPOService -Url https://tenant-admin.sharepoint.com/
Start-SPOSiteRename -Identity https://tenant.sharepoint.com/sites/someaddress -NewSiteUrl https://m365pal.sharepoint.com/sites/site1

Hope you have found this informational & thanks for reading. If you are visiting my blog for the first time, please do look at my other blogposts.

Collect response from multiple users with Adaptive Card in Teams using Power Automate

This post is in response to a comment in one of the most viewed article from my blogsite to post an Adaptive card to an user in Teams using PowerAutomate. Assume we have a use case for using Adaptive card for collecting response from n number of users based on the data from an Excel, SQL database etc. The response must be unique for users so there has to be separate instance of Adaptive card flow to each user since the flow has to wait till it gets response from the user.

To handle this scenario, we are going to create two flows

  1. Flow 1 – Send Adaptive card to collect response: This flow creates an adaptive card to collect response from each user
  2. Flow 2 – Microsoft Teams User Details: The main flow which has the user details

For this example, I will be storing the user details on an Array variable but you can dynamically generate user details or based on the data from various datasources like Excel, Database etc. Let us go ahead and create the flows

Flow 1 – Send Adaptive card to collect response

This flow will be called from flow 2 to create the Adaptive card for the team user to collect response.

Step 1: Create an Instant flow with trigger type “When a HTTP request is received” and select the method type to Post by clicking Show advanced options. Now click Use sample payload to generate schema under the section Request Body JSON Schema and the enter the following data for the team user email address and click Done to generate the schema

{
“Email”:”user@domain.onmicrosoft.com”
}

The email address of the Teams user will be passed from Flow 2 on the request body.

Step 2: Add the action Post an Adaptive card to a Teams user and wait for a response. The only change is for the field Recipient which should be Email (request body json schema) from the dynamic content of the trigger When a HTTP request is received.

Step 3: Add Create item for collecting the Team user response to the SharePoint list. Refer to the blogpost Adaptive card to an user in Teams using PowerAutomate for detailed explanation.

Step 4: Saving the flow automatically generates the HTTP POST URL, the URL will be used in the Flow 2. The complete flow should be looking like the below

We are now good to create the second flow from where the Adaptive card collect response flow will be triggered from.

Flow 2 – Microsoft Teams User Details:

This flow is the primary flow which triggers the Flow 1 for the posting the adaptive card to multiple team users.

Step 1: Create an Instant flow with the trigger type “Manually trigger a flow” and add a Array variable to store the user email address for sending the Adaptive card to collect response from multiple users.

Step 2: Add the Parse JSON action to parse the email address from the array variable and then click Generate from sample

Paste the array data as given below and click Done to automatically generate the schema for us. Then for the Content parameter in the action, select Teams Users (array variable) from the dynamic content.

[
{
“Email”: “user1@domain.onmicrosoft.com”
},
{
“Email”: “user2@domain.onmicrosoft.com”
}
]

Step 3: Add a compose action and the select the email attribute from the Parse JSON output to automatically generate a Apply to each loop as below

Step 4: Add the HTTP action to make a Post request to the HTTP url created from the first flow to post an Adaptive Card to the teams user. Find the parameters below

Method: Post

URI: HTTP Request flow URL (when a HTTP request is received) copied from the Flow 1

Headers: Key: Content-Type Value: application/json

Body:

{

  “Email”: Output of JSON Parse action (Email)-to be replaced

}

Authentication: None

This should now create Adaptive card to collect responses from multiple users irrespective of the users response to the Adaptive card.

Summary: On this post we have seen how to send adaptive card to multiple teams users using Power automate. There should be a question? Why cannot we use a Child flow concept to call the Adaptive card from the parent flow using the action Run a Child Flow available in Power platform solutions. Since we are using a For Each loop in Flow 2 Step 3 it will go to the next loop only if the first user responds to the adaptive card since there will be an action Respond to a PowerApp or flow at the end of a child flow (must have in child flow). We will have to keep in mind about the action (HTTP) and triggers (When a HTTP request is received) used in this flow are Premium. Let me know any feedback or comments on the comment section below

Batch SharePoint requests [GET, POST, PATCH, DELETE] in PowerAutomate and MS Graph

Batching helps you in optimizing the performance of your application by combining multiple requests into a single request. SharePoint Online & MS Graph APIs supports the OData batch query option. Batch requests MUST be submitted as a single HTTP POST request to the batch endpoint of a service as below for

The request body of the above POST request must be made up of an ordered series of query operations [GET] and/or ChangeSets [POST or PATCH or DELETE]. You can have different combination of change sets.

In this blog post, I am going to show you how to batch multiple SharePoint requests for Creating, Reading, Updating & Deleting List items in

  1. PowerAutomate
  2. MS Graph

Pre-Requisites:

Have the following items ready to follow along this post

  1. SharePoint Site
    1. Site Id [GUID of the Site]
    2. Create a SharePoint List by the Name EmployeeInformation with the schema
      1. Title [Default]
      2. Location [Custom: Single Line of Text]
    3. List Id [GUID of the above list]
  2. Graph Explorer to test the Graph batching

Batch SharePoint requests in PowerAutomate:

If there is a requirement for multiple requests to be performed in SharePoint from your flow, the batch request with SharePoint Online REST API helps in reducing the execution time of your flow by combining many operations into a single request to SharePoint. Create an Instant Flow with trigger “Manually trigger a Flow” and the action Send an HTTP request to SharePoint to send the batch requests.

Lets now prepare the parameters to be passed for the Send an HTTP request to SharePoint action:

Site Address: https://mydevashiq.sharepoint.com/sites/test77

Method: POST

Headers:

  • Key: accept Value: application/json;odata=verbose
  • Key: content-type Value: multipart/mixed; boundary=batch_cd329ee8-ca72-4acf-b3bf-6699986af544

The boundary specification with batch_guid used on the content type header can be any random guid. In the request body the batch_guid will be used. To understand more about the OData batch operation, go through this documentation.

Body:

The request body given below is for reading all the items [GET], creating a list item, deleting an existing item & updating an existing item on the EmployeeInformation List using REST API endpoints. A ChangeSet (random guid) is used to group one or more of the insert/update/delete operations and MUST NOT contain query operations [GET]. For the query operation there must be separate batch as per the example below

--batch_cd329ee8-ca72-4acf-b3bf-6699986af544
Content-Type: application/http
Content-Transfer-Encoding: binary

GET https://domain.sharepoint.com/sites/sitename/_api/web/lists/GetByTitle('EmployeeInformation')/items?$select=Title,Location HTTP/1.1
Accept: application/json;odata=nometadata

--batch_cd329ee8-ca72-4acf-b3bf-6699986af544
Content-Type: multipart/mixed; boundary="changeset_64c72699-6e7c-49c4-8d9b-6b16be92f7fc"
Content-Transfer-Encoding: binary

--changeset_64c72699-6e7c-49c4-8d9b-6b16be92f7fc
Content-Type: application/http
Content-Transfer-Encoding: binary

POST https://domain.sharepoint.com/sites/sitename/_api/web/lists/GetByTitle('EmployeeInformation')/items HTTP/1.1
Content-Type: application/json;odata=verbose

{
    "__metadata": {
      "type": "SP.Data.EmployeeInformationListItem"
    },
    "Title": "Mohamed Shaahid Faleel",
    "Location": "England"
}

--changeset_64c72699-6e7c-49c4-8d9b-6b16be92f7fc
Content-Type: application/http
Content-Transfer-Encoding: binary

DELETE https://domain.sharepoint.com/sites/sitename/_api/web/lists/GetByTitle('EmployeeInformation')/items(37) HTTP/1.1
If-Match: *

--changeset_64c72699-6e7c-49c4-8d9b-6b16be92f7fc
Content-Type: application/http
Content-Transfer-Encoding: binary

PATCH https://domain.sharepoint.com/sites/sitename/_api/web/lists/GetByTitle('EmployeeInformation')/items(30) HTTP/1.1
Content-Type: application/json;odata=nometadata
If-Match: *

{
    "Title": "Mohamed Faleel",
    "Location": "USA
}

--changeset_64c72699-6e7c-49c4-8d9b-6b16be92f7fc--

--batch_cd329ee8-ca72-4acf-b3bf-6699986af544--

Once the above action is executed the response can be parsed to get the required information if you’ve used a GET request as per this documentation from Microsoft. PFB the screenshot of the action

The request body can be generated dynamically based on the requirement.

Batch SharePoint requests in MS Graph:

As we have done batching using the SharePoint REST APIs, in a similar manner you can combine multiple requests in one HTTP call using JSON batching for MS Graph. Here I will use the MS Graph explorer to test the batch request. Find the request parameters

Endpoint URL: https://graph.microsoft.com/v1.0/$batch

Method: POST

Body:

I’ve used the Site Id and List Id for the EmployeeInformation list to construct the SP endpoint URL’s as per the documentation for Creating, Reading, Updating & Deleting SP list items.

{
    "requests": [
      {
        "id": "1",
        "method": "POST",
        "url": "/sites/{77b3a8c8-549f-4848-b82c-8bb6f4864918}/lists/{2f923934-d474-4473-8fc0-3486bd0c15c5}/items",
         "body": {
          "fields":{"Title":"Test from Graph","Location":"Oslo"}
        },
        "headers": {
          "Content-Type": "application/json"
        }
      },
      {
        "id": "2",
        "method": "GET",
        "url": "/sites/{77b3a8c8-549f-4848-b82c-8bb6f4864918}/lists/{2f923934-d474-4473-8fc0-3486bd0c15c5}/items"
      },
      {
        "id": "3",
        "url": "/sites/{77b3a8c8-549f-4848-b82c-8bb6f4864918}/lists/{2f923934-d474-4473-8fc0-3486bd0c15c5}/items/44",
        "method": "PATCH",
        "body": {
            "fields":{"Title":"Mohamed Ashiq Faleel","Location":"Stockholm"}
        },
        "headers": {
          "Content-Type": "application/json"
        }
      },
      {
        "id": "4",
        "url": "/sites/{77b3a8c8-549f-4848-b82c-8bb6f4864918}/lists/{2f923934-d474-4473-8fc0-3486bd0c15c5}/items/50",
        "method": "DELETE"
      }
    ]
  }

On a same way you can batch different APIs endpoint from MS Graph. JSON batching also allows you to sequence the requests. Find below the screenshot from Graph explorer

Graph explorer also generates code snippets for the different programming languages

JavaScript Code snippet

Summary: On this post we have seen how to batch SharePoint requests using PowerAutomate & MS Graph. Microsoft has used request batching on many first party features. Hope you have found this informational & helpful in some way. Let me know any feedback or comments on the comment section below

Create/Delete a SharePoint custom theme using PowerAutomate

In a modern SharePoint site you can create custom themes using PowerShell, REST API & CSOM. In this blogpost I will show you how to create themes using PowerAutomate. The following REST endpoints are available

There is an online Theme Generator tool that you can use to define new custom themes. At the time of writing this post, the endpoints are open to everybody & not just to the SharePoint tenant admins which seems to be quite buggy. Laura Kokkarinen has written a very detailed blog post about this topic. I’ve got the inspiration to write about this topic from John Liu who has recently recorded a video about this. Find screenshot from the Theme generator tool:

Once you have defined the theme from the tool, click on the Export theme button on the Right top corner of the tool to export the theme as a code block in JS, JSON & PowerShell. In this case, click JSON & Copy the generated block

{
  "themePrimary": "#50AFC6",
  "themeLighterAlt": "#f7fcfd",
  "themeLighter": "#def1f6",
  "themeLight": "#c3e6ee",
  "themeTertiary": "#8ecddd",
  "themeSecondary": "#61b8ce",
  "themeDarkAlt": "#489eb3",
  "themeDark": "#3c8597",
  "themeDarker": "#2d626f",
  "neutralLighterAlt": "#faf9f8",
  "neutralLighter": "#f3f2f1",
  "neutralLight": "#edebe9",
  "neutralQuaternaryAlt": "#e1dfdd",
  "neutralQuaternary": "#d0d0d0",
  "neutralTertiaryAlt": "#c8c6c4",
  "neutralTertiary": "#d9d9d9",
  "neutralSecondary": "#b3b3b3",
  "neutralPrimaryAlt": "#8f8f8f",
  "neutralPrimary": "gray",
  "neutralDark": "#616161",
  "black": "#474747",
  "white": "#ffffff"
}

Flow for Creating or adding the Theme to the tenant:

Let’s create an instant flow with trigger Manually trigger a flow to add a theme to the tenant. Add two Compose actions as shown below

The first compose action is the actual definition copied from the theme generator tool

{
  "palette" : 
JSON block copied from the Theme generator tool
}

The second compose action has the name of the theme & its stringified JSON from the output of the previous compose action. To convert the JSON to string add a string expression on the dynamic content pane

{
"name":"My first Custom theme created using FLOW", 
"themeJson": @{string(outputs('Compose_-_Custom_Theme_Pallete'))}
}

Now add the action Send an HTTP request to SharePoint with the following parameters

Site Address: https://domain.sharepoint.com/sites/sitename

Method: POST

URI: /_api/thememanager/AddTenantTheme

Headers:

Key: Accept

Value: application/json;odata.metadata=minimal

Body: Output of the Second compose action (Compose – Theme Name)

Now you are ready to test the flow. Once its successful you can apply the custom theme to the site

Click cog wheel on the site to select the theme by selecting the Change the look link

For deleting the theme, add the action Send a HTTP request to SharePoint with the following parameters

Site Address: https://domain.sharepoint.com/sites/sitename

Method: POST

URI: /_api/thememanager/DeleteTenantTheme

Headers:

Key: Accept

Value: application/json;odata.metadata=minimal

Body: { “name”:”the name of your custom theme” }

Summary: Hope you find this post useful & informational. Let me know if there is any comments or feedback below.

Multiple ways to access your On-premise data in Microsoft 365 and Azure

If your organization is using a hybrid cloud environment, this post will shed some light to integrate on-premise resources with Microsoft 365 & Azure services. Hybrid integration platforms allows enterprises to better integrate services and applications in hybrid environments (on-premise and cloud). In this blog post, I will write about the different services & tools available with in Microsoft Cloud which allows you to connect or expose your On-premises data or application in Office 365. There are still many enterprise organizations on Hybrid mode due to various factors. It can be a challenging task to integrate your on-premises network but with right tools & services in Office 365 & Azure it can be easier. Find below the high-level overview & some references on how to

  1. Access your on-premise data in Power Platform & Azure Apps (Logic Apps, Analysis Services & Azure Data factory)
  2. Programmatically access your on-premise resources in your Azure Function app
  3. Access on-premise resources in Azure automation account
  4. Expose your on-premise Application or an existing WEB API in Office 365 cloud

Access on-premise data in Power Platform & Azure Apps (Logic Apps, Analysis Services & Azure Data factory):

The on-premises data gateway allows you to connect to your on-premises data (data that isn’t in the cloud) with several Microsoft cloud services like Power BI, Power Apps, Power Automate, Azure Analysis Services, and Azure Logic Apps. A single gateway can be used to connect multiple on premise applications with different Office 365 applications at the same time.

At the time of writing, with a gateway you can connect to the following on-premises data over these connections:

  • SharePoint
  • SQL Server
  • Oracle
  • Informix
  • Filesystem
  • DB2

To install a gateway, follow the steps outlined in MS documentation Install an on-premises data gateway. Install the gateway in standard mode because the on-premises data gateway (personal mode) is available only for Power BI.

Once the data gateway is installed & configured its ready to be used in the Power platform applications.

  1. PowerApps
  2. PowerAutomate
  3. PowerBI

The other catch the gateway is not available for the users with Power Automate/Apps use rights within Office 365 licenses as per the Licensing overview documentation for the Power Platform. Data gateways can be managed from the Power Platform Admin center.

Shane Young has recorded some excellent videos on this topic for PowerApps & PowerBI.

To use in

  1. Azure Logic Apps
  2. Azure Analysis service
  3. Azure Data Factory

create a Data Gateway resource in Azure.

High Availability data gateway setup:

You can use data gateway clusters (multiple gateway installations) using the standard mode of installation to setup a high availability environment, to avoid single points of failure and to load balance traffic across gateways in the group.

No need to worry about the security of the date since all the data which travels through the gateway is encrypted.

Data gateway architecture:

Find below the architecture diagram from Microsoft on how the gateway works

I recommend you to go through On-premises data gateway FAQ.

Integration Service Environment:

As per the definition from Microsoft an integration service environment is a fully isolated and dedicated environment for all enterprise-scale integration needs. When you create a new integration service environment, it’s injected into your Azure Virtual Network allowing you to deploy Logic Apps as a service in your VNET. The private instance uses dedicated resources such as storage and runs separately from the public global Logic Apps service. Once this logic apps instance is deployed on to your Azure VNET, you can access your On-premise data resources in the private instance of your Logic Apps using

  • HTTP action
  • ISE-labeled connector for that system
  • Custom connector

For the pricing of ISE, refer this link.

Programmatically access your on-premise resources in your Azure Function app

As you all know Azure Functions helps in building functions in the cloud using serverless architecture with the consumption-based plan. This model lets the developer focus on the functionality rather than on infrastructure provisioning and maintenance. Okay let’s not more talk about what a Function app can do but let us see on how to connect to your on-premise resources (SQL, Biztalk etc) within your function.

During the creation of a Function app in Azure, you can choose the hosting plan type to be

  • Consumption (Serverless)
  • Premium
  • App Service plan

Consumption based plan is not supported for the on-premise integration so while creating the app the hosting plan has to either premium or app service based plan & the Operating system has to to be Windows. On-premise resources can be accessed using

  1. Hybrid Connections
  2. VNet Integration

Hybrid Connections:

Hybrid Connections can be used to access application resources in private networks which can be on-premise. Once the Function app resource is created in Azure, go to Networking section of the App service to setup & configure. Go through the documentation from Microsoft for the detailed instructions to set this up.

How it works:

The Azure Hybrid Connection represents a connection between Azure App Service and TCP endpoint (host and port) of an on-premise system. On the diagram below Azure Service Bus Relay receives two encrypted outbound connections. One from the side of Azure App Service (Web App in our case) and another from the Hybrid Connection Manager (HCM). HCM is a program that must be installed on your on-premise system. It takes care of the integrations between the on-premise service (SQL in this case) with Azure Service Bus Relay.

Once the setup is done, you can create a connection string in Appsettings.json file or from Azure function app interface of your function app. After this you can access the data in your function app code.

I’ve found a couple of interesting blogs about this setup.

VNet Integration:

In the Networking features of the App service, you can add an existing VNET. An Azure Virtual Network (VNet) is a representation of your own network (private) in the cloud. It is a logical isolation of the Azure cloud dedicated to your subscription.

In Azure Vnet you can connect an on-premise network to a Microsoft VNet, this has been documented from Microsoft here. Once there is integration between your Azure Vnet & on-premise network and the VNet is setup on your function app you are set to access on-premise resources in your function app.

Access on-premise resources in Azure automation account:

Azure Automation is a service in Azure that allows you to automate your Azure management tasks and to orchestrate actions across external systems from right within Azure. Hybrid runbook worker feature allows you to access on-premise resources easily. The following diagram from Microsoft explains on how this feature works

I’ve written a blogpost recently about this feature for automating on-premise active directory.

Expose your on-premise Application or an existing WEB API in Office 365 cloud:

Azure Active Directory’s Application Proxy provides secure remote access to on-premises web applications (SharePoint, intranet website etc). Besides secure remote access, you have the option of configuring single sign-on. It allows the users to access on-premise applications the same way they access M365 applications like SharePoint Online, PowerApp, Outlook etc. To use Azure AD Application Proxy, you must have an Azure AD Premium P1 or P2 license.

How it works:

The following diagram from Microsoft documentation shows how Azure AD and Application Proxy works

Find below documentations on how to

  1. Add an on-premises application for remote access through Application Proxy in Azure Active Directory
  2. Secure access to on-premises APIs with Azure AD Application Proxy
  3. Use Azure AD Application Proxy to publish on-premises apps for remote users
  4. Deploy Azure AD Application Proxy for secure access to internal applications in an Azure AD Domain Services managed domain

Once the connector service is installed from your Azure AD application proxy, you can add an on-premise app as shown below

The above step will register an application with App registrations.

Summary: I’ve given some overview about the different services & tools to connect & integrate on-premise resources with Microsoft cloud. Hope you like this post & find it useful. Let me know any feedback or comments on the comment section below

Automated trigger recurrence frequency – Power Automate

Have you ever noticed on your Automated flow with trigger for e.g Item created or modified on a SharePoint list will not run immediately as & when there was an item either created or modified in the list? The reason is all the automated triggers has a recurrent frequency schedule which is set to 3 mins, it means it looks for the changes in the SharePoint list every 3 mins. To check this, go to Peek Code on the trigger to check the interval frequency

For the When an Item is created trigger

This setting cannot be changed in Power Automate but with Azure Logic Apps you can adjust this setting. For more details on the pricing, refer to this link

If there is further delay in the trigger to get fired, check your flow plan since it has a dependency. As per information gathered from the Flow community forum

The maximum flow frequency for User based or App based plans is 1 minute, however if you are using Free plan it will be 15 minutes. And if it is Flow for Office 365 (Plan from your Enterprise license E3, E5 etc) and Flow for Dynamics 365 it will be 5 minutes.

From the FAQ page in the Microsoft site for Flow, it says

Your plan determines how often your flows run. For example, your flows may run every 15 minutes if you’re on the free plan. If a flow is triggered less than 15 minutes after its last run, it’s queued until 15 minutes have elapsed.

The same trigger with Logic apps which has options to update the recurrent frequency interval

If you are new to Logic Apps, follow this article from Microsoft to get started. The other advantage with Logic apps is there is a code view to update & Save which is not the case with Power Automate. In Power Automate, you can only view the code & not update

Hope this information was useful in some way. If you have any comments, let me know on the comments section.