Tag: PowerAutomate

Call Microsoft Graph API as a signed in user with delegated permission in Power Automate or Azure Logic apps using HTTP Connector

If you have a requirement to access graph endpoint as a signed in user/account on an instant/automated/scheduled flow, this blog post will help you with instructions and steps to access the Microsoft graph API with delegated permissions using the

  1. HTTP connector
  2. Invoke an HTTP request connector

There are resources (Presence information, Planner etc) in Microsoft graph which is available only as delegated permissions and not as application permission. Application permissions can be granted only by an administrator but users can register an application with delegated permission (Except All permission) unless the IT team has restricted the app registration by users.

Access Graph API using HTTP connector:

I have used the HTTP connector to generate a token for accessing the Graph API using the OAuth resource owner Password Credentials grant authentication flow supported by Microsoft Identity platform with the User ID and Password. Once we have the access token, the request to the Graph API endpoint will be made. To follow along this post be ready with the following

Pre-Requisites:

  1. Access to HTTP Premium Connector in Power Automate
  2. Access to register Azure AD Application in Azure AD Portal
  3. A service account without MFA enabled
    1. User ID
    1. Password

If you have an account with MFA enabled, then you should be creating a Custom connector. I have written a blog post on creating a custom connector to call Microsoft Graph API for Power Apps and Power Automate.

Azure Active Directory Application:

Register an application in Azure AD and obtain the client id, client secret & tenant id for the registered application. In this example I have added the delegated permission Presence.Read to get the presence information of the service account.

Add the redirect URI for the web http://localhost as shown on the screenshot below.

The Web redirect URI http://localhost/ is required to provide consent for the Azure AD application for the permission scope by the service account. The consent can be provided by an admin to use this application in flow by all users or the consent has to be provided by an individual user. To provide consent by an individual user in this case by the service account, construct the following url using the tenant ID, Client ID and the scope (ex. Presence.Read)

Individual User Consent URL:

https://login.microsoftonline.com/yourtenantID/oauth2/v2.0/authorize?
client_id=azureadappclientid
&response_type=code
&redirect_uri=http://localhost/
&response_mode=query
&scope=https://graph.microsoft.com/Presence.Read

If there are multiple delegated permissions, the scope should be separated by a space (%20)

scope=https://graph.microsoft.com/Presence.Read%20
https://graph.microsoft.com/Sites.Read.all

Now login to Office.com with the service account and enter the above User Consent url on a separate tab for the consent which will bring up a screen similar to the one shown below

Now Click the Accept button to provide consent for the requested permission for the service account. After the Accept button is clicked there will be a message stating that this site cannot be reached or something similar with the url like below on the browser address bar

http://localhost/?code=xxxxxxxxxxxxxxxxxx&session_state=xxxx-xxx-xxx-xx-xxxxx

The consent is provided, to validate the consent login to My Applications link url and the select the Azure AD application from the list and then click Manage your application as shown below

Find below screenshot with consent for Presence.Read permission. To revoke the permission, click Revoke permissions

To provide Admin consent for all the users to use this app in the flow, the URL is

https://login.microsoftonline.com/yourtenantID/adminconsent?client_id=azureadappclientid

Power Automate Flow:

Now we are ready to generate the graph token using the HTTP connector in flow which is a pre-requisite to call the Graph API endpoint. To generate a token in Flow

  1. Store the Client Secret on a String variable
  2. Make the following HTTP request using the HTTP connector

HTTP Method: POST

URI: https://login.microsoftonline.com/yourtenantID/oauth2/v2.0/token

Headers: Content-Type: application/x-www-form-urlencoded

Body:

Replace the client id, service account username and password

client_id=azureadappclientid&username=serviceaccount@yourdomain.com&password=serviceaccountpassword&grant_type=password&client_secret=azureadappclientsecret&scope=Presence.Read%20offline_access

For the client secret and password (only if there is special character), make sure to URL encode using the expression encodeUriComponent(variables(‘clientSecret’)) else the request will fail due to the presence of special characters.

If there is no consent provided by the user/service account for the Azure AD application then the above HTTP request will generate the following error

{“error”:”invalid_grant”,”error_description”:”AADSTS65001: The user or administrator has not consented to use the application with ID ‘xxxxxxx-65xx-47e0-xxxx-xxxxx0bb22′ named AzureADAppName’.

To extract the token from the above request, add the parse JSON action with Content from the HTTP request body and the following schema

{
    "type": "object",
    "properties": {
        "token_type": {
            "type": "string"
        },
        "scope": {
            "type": "string"
        },
        "expires_in": {
            "type": "integer"
        },
        "ext_expires_in": {
            "type": "integer"
        },
        "access_token": {
            "type": "string"
        },
        "refresh_token": {
            "type": "string"
        }
    }
}

Add the Body from the dynamic content from the HTTP – GET Token action to the content of the Parse JSON action

Include the access token when calling the Microsoft Graph API on the Headers sections as shown below. The access_token is from the output of the Parse JSON action

If you run the flow, you can now see the response with the presence information of the service account as shown below

Use Azure Key vault connector to secure the Client Secret & Password information in the flow.

Invoke a HTTP Request connector:

This connector can be used to fetch resources from various web services authenticated by Azure AD including Microsoft Graph in more easier way. Look for the action with the keyword invoke an HTTP request

If it is accessed for the first time, enter https://graph.microsoft.com on both Base and Azure AD resource URI and then click Sign In

Enter the Graph API endpoint on the Url of the request and select the Method

The API is executed in the context of the action’s connection as shown below. In this example it gets the profile information of the serviceaccount

If you get an error similar to { “error”: { “code”: “Forbidden”, “message”: “” } }, then it could be because the connector has a limited set of scopes. Getting Presence information is not supported with this connector as of now. If your scenario requires something more advanced or not currently supported by the connector, please use the �HTTP� connector as shown above or create a custom connector.

Reference:

https://docs.microsoft.com/en-us/graph/auth/auth-concepts#delegated-and-application-permissions

https://ashiqf.com/2021/03/16/call-microsoft-graph-api-in-power-apps-and-power-automate-using-a-custom-connector/

Summary: There are many endpoints available with Microsoft graph which can be leveraged for different use cases. Keep in mind the HTTP connector in Power Automate is Premium, you can also consider using this approach in Azure Logic apps. The access token is valid only for an hour, if you have to call a graph api after an hour from the initial token generation time the token has to be obtained again. Hope you have found this informational & thanks for reading. If you are visiting my blog for the first time, please do take a look at my other blogposts.

Trigger an Azure Webjob from Power Automate

On this post let us see how to trigger or run a WebJob from Power Automate. WebJob is a powerful service in Azure keeping in mind the supported file types or programs it can run. Before proceeding with the instructions to call a WebJob in Power Automate, let us see some basics of an Azure WebJob. WebJobs is a feature of Azure App Service that enables you to run a program or script in the same instance of the Azure web app with no additional cost. As of now it is not supported in App service plan for Linux. There are two types of WebJobs

  1. Continuous WebJob
    • Starts immediately when the WebJob is created. To keep the job from ending, the program or script typically does its work inside an endless loop.
    • Runs on all instances that the web app runs on. You can optionally restrict the WebJob to a single instance.
  2. Triggered WebJob
    • Starts only when triggered manually or on a schedule based on CRON expression.
    • Runs on a single instance that Azure selects for load balancing.

Supported file types for scripts or programs:

The following file types are supported:

  • .cmd, .bat, .exe (using Windows cmd)
  • .ps1 (using PowerShell)
  • .sh (using Bash)
  • .php (using PHP)
  • .py (using Python)
  • .js (using Node.js)
  • .jar (using Java)

Check here the documentation from Microsoft to choose between Flow, Logic Apps, Functions & Webjobs for your automation services with comparisons against each other. If you are using a Function app with a Consumption plan your function can run only to a max of 10 mins. If you have a long running task on a webjob, set this property in the App service Application setting from the Configuration blade as shown below

The above setting is to avoid idling out if there is no CPU activity. The IDLE timeout setting is set to 1 hour in the above screenshot.

Azure WebJobs SDK:

There is a Powerful Azure WebJobs SDK which simplifies the task of writing background processing code that runs in WebJobs. It makes it easier to write code that reads or writes from Azure Storage account and it also facilitates to trigger the WebJob if there is any new data on the queue, blob, table, service bus for an event driven architecture. Azure functions is built on the WebJobs SDK. If you set your web app to run continuous or scheduled (timer-trigger) WebJobs, enable the Always on setting on your web app’s Azure Configuration page to ensure that the WebJobs run reliably. This feature is available only in the Basic, Standard, and Premium tiers of the App service plan.

Create and Deploy a WebJob:

To call a WebJob from Power Automate, let us create a Triggered WebJob (.Net Framework) from Visual Studio. There is a also support for .NET Core console apps as WebJobs. Refer this documentation from Microsoft to create a WebJob from Visual Studio. In Visual studio there is a template to create a WebJob project as shown below

This is how the VS project looks like

The Program.cs has the code to ensure that the Job will be running continuously, for this case it is not required comment or remove the code which is highlighted. The Functions.cs has the code to pick up the message from the Storage Queue (Event-Driven) through the WebJobs SDK runtime, the WebApp must set to Always on to make it work. For this example, it is not required since it is going to be a triggered Job so the file Functions.cs can be deleted.

If you have any arguments to be passed from Power Automate, you can access it on your code as shown below

To deploy the WebJob, right click the project and select Publish. If there is no publish profile yet, create one or export it from Azure WebApp and then Publish. To know more about the Publish settings In the Publish tab, choose Edit as shown below

The WebJob will be now in Azure. Go to your Azure WebApp or App Service and click WebJobs under the settings blade to find the WebJob deployed from Visual Studio. Find the WebJob in the Azure portal

WebJobs API endpoint for the WebJob:

There are API endpoints available for the Azure WebJob which will be used for triggering the WebJob from Power Automate. Go through the following documentation for more details on the list of available endpoints:

https://github.com/projectkudu/kudu/wiki/WebJobs-API

To Trigger or Start a WebJob, you should have the Webhook URL from the Azure Portal. To get the URL, click Properties after selecting the WebJob as shown below

Copy the Web hook URL, User Name and Password to be later used in Power Automate. Let us trigger the WebJob from Postman client using the above information

Method: Post

URL: https://yourappname.scm.azurewebsites.net/api/triggeredwebjobs/webjobname/run

Authorization Type: Basic Auth

User Name and Password copied from the Portal

This will trigger the Job.

If there are parameters to be passed, the API would be like

https://youwebappname.scm.azurewebsites.net/api/triggeredwebjobs/youwebjobname/run?arguments={arg1} {arg2}

Trigger from Power Automate:

Till now we have the WebJob published in Azure, can we call an API in Power Automate. Yes, it is possible with the help of the Premium action HTTP as shown below

Voila! The WebJob has been triggered from Power Automate.

Summary: On this post we have seen how to call a WebJob using PowerAutomate. There is also a trigger to calla  Flow from a PowerApp, which could be used to start the WebJob. Hope you have found this informational & helpful. Let me know any feedback or comments on the comment section below

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

Create Tile view card for custom List item image attachments using PowerAutomate & JSON row view formatting

In Modern SharePoint lists you can display list item content in a more modern way using the Tiles view layout. If you have very big list with multiple columns along with picture columns you get a horizontal scroll bar on the list view, the Tiles view can solve this issue since the content will be displayed on the tile card where you can design the layout of the tile card to display the different list column values.

There are many blog posts & PnP Samples which will help you to create a Tiles view using JSON row formatting. If you are new to JSON row formatting, I recommend you to go through this link from Microsoft. Microsoft has recently brought in interface to format the list item row & do conditional formatting by creating rules based on column values

On this blog post, lets see how to create Tiles view as shown above for the Images stored as attachments in the list item. If you add an attachment to list item in SharePoint list, the attachments are stored in the following path

https://domain.sharepoint.com/sites/SiteName/Lists/ListName/Attachments/ItemID/attachmentName.extension

Components used in this blog post

  1. Power Automate Flow: To get the path of the attached file (Image file in this case), we will be creating an automated Flow which gets triggered on List item creation to get the path of the image & update it to the custom hyperlink list column (ProductPhotoHL).
  2. JSON: To create a Tile view layout using list row view formatting.

Pre-Requisites:

  • Create a SP List by the name ProductInformation with the following columns
    1. Title: Single line of text
    2. ProductPhotoHL: Hyperlink (to the image)
    3. ProductPhotoPic: Picture (to the image)
    4. ProductPrice: Number
    5. Features: Multiple lines of text
  • Couple of list items with Images as attachments after the Power automate flow is created
    1. Only images as attachments
    2. Not more than one image as an attachment

Power Automate to get the path of the Image attachment URL:

Create an automated flow with Trigger When an item is created and configure the trigger to the ProductInformation list. Add the Get Attachments action connected to the Product Information list & for Id parameter it should the List item Id (ID) selected using the dynamic content from the trigger When an item is created.

Now with the above action we have the attachment URL of the image, this must be updated to the list column ProductPhotoHL & ProductPhotoPic of the ProductInformation list in order to be displayed in the Tile view. To create the above shown Tile view ProductPhotoPic (Picture) is not required but I’ve used it show you that we can create a Thumnail of the image on the default list view using the Picture column ProductPhotoPic. By the time I am writing this post the Power Automate action Update item is not capable to update a column with Picture as a DataType but it can update a HyperLink column. Action Send an HTTP request to SharePoint to make HTTP requests to any SharePoint Rest endpoints, I’ve used this action to update the ProductPhotoPic (Picture) column as below

I’ve said this on the pre-requisite section that there should not be more than one attachment. In the Body of the HTTP request, the Url parameter for the ProductPhotoHL & ProductPhotoPic gets only the first attachment URL from the previous action “Get attachments” AbsoluteUri as dynamic content. To get the first attachment URL you can use any of the following formula from the expression

  • first(body(‘Get_attachments’))?[‘AbsoluteUri’]
  • body(‘Get_attachments’)?[0]?[‘AbsoluteUri’]

I’ve used the function first() to get the first item from the array. The flow is ready, add couple of items to the list by filling in information only for Title, ProductPrice, Features & a Image as an attachment. The flow gets triggered which will update the ProductPhotoHL & ProductPhotoPic with the image attachment url. You can download the flow template from the following GitHub repo link.

Create Tiles View layout using JSON:

I’ve used the sample from PnP List view formatting samples to create items in tile layout for images. On the sample JSON I’ve updated the column ProductPhoto to ProductPhotoHL. The updated JSON is available here for download. Now copy the JSON & go to the List view & click on the down arrow (All Items)>Format current view>Advanced mode as shown below

The Apply formatting to should be set to Entire Row & paste the JSON to box as shown on the picture and then Save it.

Now you will have another layout by the name Tiles added to the existing layouts List & Compact List as shown below, select it

Now its time to see the need for the column ProductPhotoPic of datatype Picture, with the default layout you can see the thumbnail of the image added as an attachment

Summary: There are many samples available in PnP Github repo for List Row View & Column view formatting. In document & picture libraries the Tiles view layout are added by default, there is also a Column by the name Thumbnail in a Picture library. You can display a Thumbnail view of Images in PowerApps gallery for the Images stored in Document library, go through this link for more information. If you are storing images on a seperate document library & not as an attachment, the url of the image can be added on the HyperLink column. Hope you find this interesting & helpful.

Convert Outlook Email with embedded images to PDF using PowerAutomate

Recently I’ve came across a business case with need to automate the conversion of Outlook email messages with embedded images to PDF document. This could be done manually on Outlook client using Microsoft Print to PDF or browser Print if opened using Outlook on the Web. This process can be automated with the help of PowerAutomate trigger When a new email arrives and actions Export Email, Convert File, Create file but if an email has an embedded image or HTML content it will not work as of now. There are Third party connectors in Power Automate from Muhimbi, Plumsail which might have this functionality but I’ve not tested those yet. PowerAutomate action Export Email converts the email to .eml file.

An EML file is an email message containing the content of the message, along with the subject, sender, recipient(s), and date of the message in plain text format. Once you have the .eml file change the file extension from .eml to .txt where you can see the content. If there is any embedded image it will stored in the Base64 format. You can also change the .eml file extension to .mht and open it directly in Internet Explorer

For this blogpost I’ve used third party API service from ConvertAPI to convert Email message to PDF, they have REST API endpoints to convert Word, Excel, PowerPoint, HTML, PDF and Image formats. There is also a Free Plan with ConvertAPI where you get 1500 seconds API execution time if you sign up.

You can also create your own API service hosted in Azure for conversion with the .NET libraries like iTextSharp, GroupDocs, PDFSharp etc. Let’s go ahead & create flow to

  1. Convert Email to PDF – Without Embedded image
  2. Convert Email to PDF – With Embedded image

The above two flows packages can be downloaded from Github repo.

Convert Email to PDF – Without Embedded image:

Power Automate connector OneDrive for Business has an action Convert file (preview) converts files to different formats such as PDF, HTML, JPG etc. This connector can be used to convert a simple email with out an embedded image.

Step 1: Create a flow with Automated trigger When a new email arrives & configure the trigger parameters by clicking Show advanced options.

Step 2: Add the action Export email with Message Id from the output of the previous action. This action creates the .eml file

Step 3: Add the action Create file from the connector OneDrive for Business. Select the Folder path from your One drive, Enter the File Name for the .eml file & the File Content should be Body from the output of the action Export email (Previous). Find the screenshot below

Step 4: Add the action Convert file from the connector OneDrive for Business with Id from the output of the previous action Create File.

Step 5: Add the action Create file from the connector OneDrive for Business. This step is for storing the PDF file back to the OneDrive. Select the Folder path from your One drive to store the PDF file, Enter the File Name for the PDF file & the File Content should be File content from the output of the action Convert file. Find screenshot below

Note: The storage location I’ve chosen is Onedrive, you can choose SharePoint, Azure blob etc. Based on the need you can choose to delete the .eml files after the file conversion is done.

Convert Email to PDF – With Embedded image:

As already said the previous flow will not convert an email with embedded image as expected. Be ready with the API endpoint from ConvertAPI to convert email to PDF. The endpoint will have the secret as a query string shown as below

https://v2.convertapi.com/convert/eml/to/pdf?Secret=yoursecretkeyfromconvertapi

Note: On this flow I will be using the .eml file generated from the previous flow.

Step 1: Create a flow with Instant trigger Manually trigger a flow.

Step 2: Add the action Get file content from the connector OneDrive for Business. Select the .eml file which has the embedded image from the storage location i.e the file from OneDrive.

Step 3: Add the action Compose from the connector Data Operation. This step is to convert in to base64 representation a requirement for the convert API to work. On the Inputs file go to the expression editor and add the function base64(file content from the previous action get file) for converting .eml to base64.

Step 4: Add the action HTTP (Premium) from the connector HTTP to make a POST request to the API convert API endpoint.

Method: POST

URI: https://v2.convertapi.com/convert/eml/to/pdf?Secret=yoursecretkeyfromconvertapi

Headers:

Key: Content-Type

Value: application/json

Body: You can generate this from the ConvertAPI site by uploading a .eml file on the site. Once this data is added to the HTTP action Body parameter change the Data parameter should be the Output of the previous action Compose – Convert to Base64

{
  "Parameters": [
    {
      "Name": "File",
      "FileValue": {
        "Name": "myemailfile.eml",
        "Data": "@{outputs('Compose_-_Convert_to_Base64')}"
      }
    }
  ]
}

Step 5: Add the action Parse JSON from the connector Data Operation. This step is to parse the response of the HTTP POST action to the ConverAPI endpoint. You can generate the scheme by copying from the Flow run history for the HTTP action output. The schema will be look like

{
    "type": "object",
    "properties": {
        "ConversionCost": {
            "type": "integer"
        },
        "Files": {
            "type": "array",
            "items": {
                "type": "object",
                "properties": {
                    "FileName": {
                        "type": "string"
                    },
                    "FileExt": {
                        "type": "string"
                    },
                    "FileSize": {
                        "type": "integer"
                    },
                    "FileData": {
                        "type": "string"
                    }
                },
                "required": [
                    "FileName",
                    "FileExt",
                    "FileSize",
                    "FileData"
                ]
            }
        }
    }
}

Step 6: Add the Compose action to convert the base64 data to binary to create the PDF from the HTTP request response. Select the filedata from the Output of the Parse JSON action which will automatically create a Apply to each since the Files is an array. Then add the following to the inputs of the of the compose action

base64toBinary(items(‘Apply_to_each’)?[‘FileData’]).

Now add the Create file action from the connector OneDrive for Business as shown below. The parameter File content should be output of the Compose action. PFB the screenshot of the flow actions

Now its time to test the flow, run the flow & check your OneDrive for the PDF file. PFB the screenshot of the PDF file with embedded image

Summary: I am not vouching to use the ConvertAPI service for converting the email to PDF. Just a sample for a use case where you get some knowledge on different actions usage & some information on the .eml file which Microsoft has used for storing email content. If its going to be heavily used or if the data is secure, then I advise you to create a REST API endpoint of your own hosted in Azure for the conversion. Hope you find this post useful & informational. Let me know if there is any comments or feedback by posting a comment 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.

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.

Execute SharePoint Online PowerShell scripts using Power Automate

Most of us would have used PowerShell for SharePoint to manage SharePoint settings at the organization level and site collection level. SharePoint Online PowerShell commands are very efficient for batch operations for e.g creating multiple sites, list items etc. To use the SharePoint Online PowerShell commands

  • You must have the SharePoint Admin role or Global Administrator role in Office 365
  • Install the SharePoint Online Management Shell module

As you know you must be administrator to install a PowerShell module on your workstation which not everyone will have in corporate environments.

I often use a PowerShell script to enable App Catalog at a site collection level to test the PnP webparts & extensions before deploying at the tenant level app catalog based on requirement. If you are not an SPO admin then the dependency is with the SPO admin. In this blogpost I am going to show you how to automate this process by executing PowerShell script to enable App catlog in Azure using Power Automate.

Pre-requisite & permissions:

  • SPO Admin
  • Azure Subscription to create Automation account
  • Access to Premium connector (Azure Automation) in Power Automate
  • SharePoint List to collect details about the site which needs to have App catalog enabled

To complete this automation process, create the following two components

  1. Automation account in Azure with a Run Book to execute PowerShell script for enabling App Catalog in SP site
  2. Power automate flow to call the Run Book

Automation account in Azure with a Run Book to execute PowerShell script for enabling App Catalog in SP siteAutomation service in Azure is a cloud-based automation and configuration service that supports consistent management across your Azure and non-Azure environments. Go through the documentation from Microsoft to know about this powerfull service in Azure. Let’s use the service in Azure to create a simple Runbook with PowerShell code to enable App catalog in SPO site, you can do much more than this using this service. Refer to this link for the pricing details for the automation service in Azure.

Step 1: Go the Azure portal & create a resource Automation

Enter the name of the automation account, select the Subscription & resource group & click Create

Step 2: After the resource is created, go to the resource & click Modules Gallery under the section Shared Resources as shown below to add the PS SPO module

Search with the keyword “SharePoint” & click “Microsoft.Onlie.SharePoint.PowerShell” and then click Import. This step will the add the SharePoint online PowerShell module for us to use the available PS SPO cmdlets in Runbook.

Now click modules & verify if the SPO PowerShell is added & available.

Step 3: The next step is to add the user credentials (Username & Password) of the SPO admin which is safe & secure by not hardcoding the password on the Runbook. You can also use certificates or AppID AppSecret in PnP online Powershell for creating connection to SPO.

Step 4: Now we are good to create the Runbook, to create it click Runbooks under the section Process Automation and then click Create a runbook. Enter the Name of the Runbook, select the Runbook type to PowerShell and click Create.

Now let’s add the code by editing the runbook to enable app catalog. The section Dynamic Parameters on the code will be passed from flow. To connect to SharePoint Online we are using the SPO admin credentials created in the previous step. Find the code below

# Dynamic Parameters
param(
  [parameter(Mandatory=$true)]
  [string]$SiteURL = "https://domain.sharepoint.com/sites/contosoportal",
  [parameter(Mandatory=$false)]
  [boolean]$enableAppCatalog = 1
)
# Credentials
$myCred = Get-AutomationPSCredential -Name "SPOAdminCred" 
# Parameters
$AdminSiteURL = "https://domain-admin.sharepoint.com"
# Connect to SharePoint Online
Connect-SPOService -Url $AdminSiteURL -Credential $myCred 
# Get the Site Collection
$Site = Get-SPOSite -Identity $SiteURL 
# Enable App catalog
if($enableAppCatalog)
{Add-SPOSiteCollectionAppCatalog -Site $Site}
# Disable App catalog if false
else{Remove-SPOSiteCollectionAppCatalog -Site $Site}
# Get Site Collection Title
Write-Output $Site.Title

The runbook is now created, you can test the script by clicking on Test Pane & pass parameters (Site URL etc) to test it. Click Publish button as shown below to publish so that it can be called from Power Automate. It’s now time to create the flow

Power automate flow to call the Run Book

You can now create a flow with automated trigger from a SharePoint list to get the site url & Boolean value either to enable or disable the app catalog on the site. Here I will be using an Instant flow with trigger “Manually trigger a Flow”

Once the flow is created, add the action “Create Job” under the connector “Azure Automation” which is a premium connector.

Select the Azure Subscription which has the Automation account resource with runbook>Select Resource Group>Select Automation Account>Select the Runbook name which has PS script to enable app catalog. If there is a need to wait until the automation job completes then select Yes on the field “Wait for Job”. For the dynamic parameter, write a JSON to pass the mandatory & optional parameters to the runbook script. On this example I will be passing the Site URL & Boolean value to either enable or disable app catalog using JSON as below

{
  "SiteURL": "https://domain.sharepoint.com/sites/MyFirstTeam",
  "enableAppCatalog": 1
}

If using a SharePoint list, construct the above JSON dynamically with the URL

For the runbook parameters, you might also get an interface as shown below to pass the values (Site Url & enableAppcatalogbooleanvalue).

The flow is ready, run it to test now with parameters.

 Summary: The use case I’ve chosen is a simple one but azure automation can be a more powerful service to perform various automation tasks. Find few below

  • Write Python script in the Runbook
  • Many samples are available within the Runbook gallery (Create AD user, Display All provisioned site collections etc) under the section Process automation.
  • With the PowerShell type Runbook all the PS modules (Azure AD for automating AD account creation, PNP SP Online etc) are readily available for us to import easily.
  • There is a feature by name “Hybrid Runbook Worker Feature” available within Azure Automation account for us to connect Onpremise resources in Azure (e.g SharePoint Onpremise, Onpremise AD etc).
  • Create a Webhook to call the runbook from an External application by making a POST call
  • Call a custom built dll by importing them in to the Modules section
  • Create graphical Runbook with GUI to add cmdlets & to configure the steps
  • Create schedule linking a runbook

Hope you have enjoyed reading this post and find it useful. If you have any comments or feedback, please provide it on the comments section below.

Collect response from a user with Adaptive Card in Teams using Power Automate

This is in continuation to my earlier post using Adaptive card for collecting information in Outlook also known as Outlook actionable message. On this post I am going to show you how to collect information from a user in Teams and storing the values back in a SharePoint list. The following Power Automate actions under Microsoft Teams connector are now available in preview mode which helps us to capture data back from a Teams adaptive card meaning you would be able to make POST calls back to the flow by click of a button (Action.Submit) on the Adaptive card

  1. Post an adaptive card as the Flow bot to a Teams user, and wait for a response
  2. Post an adaptive card as the Flow bot to a Teams channel, and wait for a response

Once an Adaptive card is posted in Teams using the above actions, the flow run will not continue until the recipient or someone in the channel (if sent to channel) responds to inputs that are required within the card till then the flow is put on wait for maximum period (Async calls) of 30 days as per the documentation. Post that period the flow will time out if no one responds to the card. There can be use case to collect responses from users in Teams & post it to Azure services like SQL etc, this avoids the users to have access to premium services or license since the card is sent using Power automate. The use case I’ve chosen for this post is to collect Name & Email address of a teams user by sending them an input form which stores the responses in a SharePoint list after the user responds. Find the resources I’ve used for this example

  • Adaptive Card Designer for creating JSON
  • Automated Flow with action to post an JSON Adaptive card using the connector MS Teams
  • SharePoint custom list with columns Name & Email
  • Microsoft Teams with the Flow App installed

Adaptive Card Creation:

Let’s start by designing the card using the Adaptive card designer. Click on Open Sample, select Input Form as shown below

Then change the host app from the default Bot Framework Webchat to Microsoft Teams – Light (Optional Step). Remove the Phone number Text Block [Element] & the corresponding Text.Input [Inputs] field to keep it simple & I’ve also changed the Adaptive card image URL on the right column to the following URL since the image default image on the sample has got some issues rendering on teams. Find some information on image size & resolutions limits here.

Click Copy Card JSON from ribbon for this card to be used on the flow. We now have the adaptive cards JSON ready with us, let’s go ahead and the create the flow using Power Automate. Find the generated JSON below

{
    "$schema": "http://adaptivecards.io/schemas/adaptive-card.json",
    "type": "AdaptiveCard",
    "version": "1.0",
    "body": [
        {
            "type": "ColumnSet",
            "columns": [
                {
                    "type": "Column",
                    "width": 2,
                    "items": [
                        {
                            "type": "TextBlock",
                            "text": "Tell us about yourself",
                            "weight": "Bolder",
                            "size": "Medium"
                        },
                        {
                            "type": "TextBlock",
                            "text": "We just need a few more details to get you booked for the trip of a lifetime!",
                            "isSubtle": true,
                            "wrap": true
                        },
                        {
                            "type": "TextBlock",
                            "text": "Don't worry, we'll never share or sell your information.",
                            "isSubtle": true,
                            "wrap": true,
                            "size": "Small"
                        },
                        {
                            "type": "TextBlock",
                            "text": "Your name",
                            "wrap": true
                        },
                        {
                            "type": "Input.Text",
                            "id": "myName",
                            "placeholder": "Last, First"
                        },
                        {
                            "type": "TextBlock",
                            "text": "Your email",
                            "wrap": true
                        },
                        {
                            "type": "Input.Text",
                            "id": "myEmail",
                            "placeholder": "youremail@example.com",
                            "style": "Email"
                        }
                    ]
                },
                {
                    "type": "Column",
                    "width": 1,
                    "items": [
                        {
                            "type": "Image",
                            "url": "https://download-ssl.msgamestudios.com/content/mgs/ce/production/SolitaireWin10/dev/adapative_card_assets/v1/tile_spider.png",
                            "size": "auto"
                        }
                    ]
                }
            ]
        }
    ],
    "actions": [
        {
            "type": "Action.Submit",
            "title": "Submit"
        }
    ]
}

Flow Creation:

Create an Instant flow with trigger “Manually trigger a Flow”, this will post an Adaptive card to a Teams user with the Input form which collects response to a SharePoint list. Create a SharePoint list with two columns for us to store the Name and Email submitted from the adaptive card on Teams.

Add the flow action “Post an adaptive card as the Flow bot to a Teams user, and wait for a response”, on the action

  1. Enter the email address of the user in the Recipient field
  2. Paste the JSON copied from the card designer in the Message field
  3. Enter information to be shown to the user on the field Update message after the Submit button is clicked
  4. Field Should update card to be set as Yes

Now add the action “Create item” to store the form response in the SharePoint list created above with the request body information mapped to Name (myName) & Email (myEmail) using the dynamic content. The dynamic content has also information about the user (Email, Display Name, Response time etc) responded in Teams

The flow is ready, Run the flow to test it. The recipient would have received the card in Teams as below

After the user keys in the Name & Email address on Teams and clicking Submit button will complete the flow till then the flow will be in wait state for a period of 30 days maximum. The data will be submitted to the SharePoint list and the card will be updated with the update message as below

There is an Adaptive card designer in Power Automate which is an experimental feature currently with which you would able to design/update Adaptive card in the Power Automate action. To enable it, click the cog wheel on your flow environment and click  “View All Power Automate settings”. On the popup toggle the Experimental Features to On and click Save button.

Go back to the flow in Edit mode, the Teams action will now have an Adaptive card designer as shown below

Senior Program Manager for Power Automate Audrie Gordon has a great video on Adaptive cards for Power Automate which has lot of information.

If you run in to an error while submitting the form or triggering the flow, look at the Troubleshooting tips for Adaptive cards. There are few known issues documented here with regards to using this action on Power Automate.

Reference: https://docs.microsoft.com/en-us/power-automate/overview-adaptive-cards

Collecting responses from Multiple team users:

If you want to collect from responses from multiple users, refer the following blog post

https://ashiqf.com/2020/09/07/collect-response-from-multiple-users-with-adaptive-card-in-teams-using-power-automate/

Summary: You now have created an input form for collecting information from a user in teams. To know the future road map for Adaptive cards, click here to know. There are couple of amazing templates available in the Flow environment, just search for Adaptive card in templates where you get template for different use cases. Hope you have enjoyed reading this post and find it useful. If you have any comments or feedback, please provide it on the comments section below.