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.

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.

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.

Change the original Owner of a Power App & Flow

Has there been a requirement or a need to change the owner/creator of the PowerApps or a Flow built by your organizational users? There could be various reasons for this request

  • App/flow creator would have left the organization
  • App/flow creator would have changed role within the organization
  • Handing over the app to the operations team…

By the time I am writing this post there are no Powershell command or actions available in Flow/PowerApp to change the original Owner of the flow but still you would be able to assign a Owner for the flow created by an user who has left the Organization from the Flow Admin center, I will cover the steps on this post. The good news is Microsoft has plans to release this feature as per this user voice request.

Prerequisite: Environment Admin or Power Platform Admin

Change the Owner of a Power App:

There are different ways to change the Owner of Power Apps using

  1. Power Shell
  2. Flow
  3. Power App

PowerShell cmdlets for PowerApps:

There is a PowerApps cmdlet for Administrators Set-AdminPowerAppOwner which allows you change the Owner of the App

Prerequisite: The following modules should be installed. It requires Administrator access on the workstation to install the modules

Install-Module -Name Microsoft.PowerApps.Administration.PowerShell
Install-Module -Name Microsoft.PowerApps.PowerShell -AllowClobber

If you don’t have admin access, then you can import the modules to your workstation using the following commands

Save-Module -Name Microsoft.PowerApps.Administration.PowerShell -Path
Import-Module -Name Microsoft.PowerApps.Administration.PowerShell
Save-Module -Name Microsoft.PowerApps.PowerShell -Path
Import-Module -Name Microsoft.PowerApps.PowerShell

Power Shell cmdlet for changing the Owner:

# This call opens prompt to collect credentials (Azure Active Directory account and password) used by the commands 
Add-PowerAppsAccount
Set-AdminPowerAppOwner -AppName '6aac46a2-a0f3-43f3-a2fb-51111785437c' -AppOwner '4cea7f11-c013-4bee-a6d1-ae3381a7f386' -EnvironmentName 'Default-2r6e8761-108d-417e-9bb4-e7c4e3ba2e23'
  1. EnvironmentName is the environment of the PowerApp you would like to change the Owner. To get the environment name, the powershell command will help Get-PowerAppEnvironment
  2. App Name is the App ID of the PowerApp. To get this information run the command Get-PowerApp ‘Name of the powerapp’
  3. AppOwner is the Azure Active directory object id of the new Owner. It is the Unique id of the user in the tenant, you can get this information in multiple ways. To get it from flow, the following action would help. The outputs of this action should have the attribute Id which is the id of the user to be passed on the Powershell command.

The old owner will get viewer access to the app but you can get it changed if required. For other Powershell cmdlets for PowerApps & flow refer this article from Microsoft.

PowerShell Tip:

To get help on any Power shell cmdlet, type Get-Help cmdletname (e.g get-help Set-AdminPowerAppOwner). To get some examples type get-help Set-AdminPowerAppOwner -examples

PowerApps for Admin Connector in Flow:

There is a preview action by the name “Set App Owner” under the connector PowerApps for admin which also helps you to change the owner of the PowerApp

PowerApps for Admin Connector in PowerApp:

The same connector used in the flow can also be used in PowerApp to change the owner for the powerapp. There is a Powerapps tool Connector Browser Tool from Microsoft to test the PowerApps for Admin connector which can be used to change the Owner of the app. The app is available as a package for download from this link, the link to the blogpost from Microsoft. You can select any actions, after entering values for the parameters click Submit.

You can test connector for Flow as well on this tool.

Assign an Owner for a Flow created by an user who has left Organization:

This can be done by connecting to the Flow Admin center, click the environment which has the flow

Click resources & then click Flows

Then look for the flow which needs the update, click the flow & click Manage sharing to add Owner

You can also export the flow as a package & then recreate it to have a new Owner. Follow this blogpost from Microsoft.

Summary: On this post, I’ve covered different ways to update the owner for PowerApps & Flow using Powershell & Admin connector in Flow & PowerApps. Hope you find this post useful & informational. Let me know if there is any comments or feedback below.

Automate the provision of Azure AD Account & License assignment – Part 1

A decade back I was part of a team to automate the On & Offboarding process of employees for a customer using .NET framework, it had a module to provision user accounts in an on-premise environment. I still remember having used couple of dll’s for Active directory 2003 & exchange 2007 to create AD & Email account. It was not easy but nowadays with the Office 365 in place its so easy to create account & enable different Office 365 services (Exchange, SharePoint, Yammer etc) for a user in Azure Active directory. This example will be applicable for the Organization which does not have On-premise Active directory. Organizations having On-premise active directory, the user account’s will be synchronized from On-premise AD to Azure AD. On this post I am going show you how to

  1. Create Azure AD account & assign license using Power Automate
  2. Assign License using Graph Endpoint

Create Azure AD account & assign license using Power Automate:

There is a Power Automate action Create user under the connector Azure AD which helps us to create account in Azure AD but there is no action as of now to assign individual license to a user but we can overcome this by adding the user to the AD security group which has a license assigned to it.

There is a flow action Add user to group under the same connector for adding the user to the security group, all the members of the group will get the license assigned on that group. The Azure AD connector does not return custom attributes of Azure AD. For e.g you can’t assign a value to a custom AD attribute with the Create user action, if you want to assign a custom attribute or an attribute which is not exposed in the Create User action then the account has to be created using PowerShell. There are ways to call a PowerShell script from Azure Automation Runbooks with the help of a flow action.

Other Azure AD actions apart from the above screenshot which could be of use are

  • Create group
  • Get group members
  • Get groups of a user
  • Get user
  • Remove Member from Group
  • Update user

There are templates available in Power automate template section which helps you create account based on the information from the SharePoint List, based on HTTP request etc

Prerequisite:

  • Permissions on Azure AD:
    • Group.ReadWrite.All
    • User.ReadWrite.All
    • Directory.ReadWrite.All
  • Security group with license assigned

For assigning a license to Security group, go to Azure AD Admin center. Follow this documentation from Microsoft to assign license to a group.

You can also turn off certain services from the license to the group, for e.g Turning off the Power App service for the user

You can also use dynamic groups for assigning license to a user, if you have dynamic group based license assignment to a user then you could ignore the step on the flow to add user to the security group. Dynamic groups works based on rules to determine group membership, for e.g if a user has an AD attribute set for Department. In this case the AD user created with certain department will get automatically added to the group which will in turn assign a license to the user.

Let’s now create the flow, I have used an Instant flow with trigger Manually Trigger a flow. Add the action Create user from the connector Azure AD

Now add the action Add user to group, the Group Id should be for the Security group which has a license assigned to it. The User Id field should be dynamic value Id from the previous action Create user.

To get the group Id, go to Azure AD

Run the flow. Once the flow runs successful the user account will be provisioned on Azure Ad with a license.

Assign License using Graph Endpoint:

There is a beta graph endpoint to assign license to a user. Find the Microsoft documentation for more information

All types of license (E5, E3, PowerApps, Power etc) has a Service Plan id also called as SKU id. Find the list of SKU id’s on this link if your tenant has procured the license for the service

 To get the available service plan or SKU ID, make a GET call to the endpoint https://graph.microsoft.com/v1.0/subscribedSkus & also from the beta endpoint of the user https://graph.microsoft.com/beta/me

Once the sku id are available based on the type of license to be assigned, you will have to make a POST call to

Endpoint URL: https://graph.microsoft.com/beta /users/testuser10@mydevashiq.onmicrosoft.com/assignLicense

Request Body:

{
  "addLicenses": [
        {
            "disabledPlans": [],
            "skuId": "b05e124f-c7cc-45a0-a6aa-8cf78c946968"
        },
        {
            "disabledPlans": [],
            "skuId": "a403ebcc-fae0-4ca2-8c8c-7a907fd6c235"
        }
  ],
  "removeLicenses": []
}

The first SKU id is for Enterprise Mobility & Power BI (Free)

To remove the license for a user, use the collection removeLicenses. This graph endpoint to assign license can also be called from a Flow.

Summary: You can also use a HTTP request trigger in the Flow for integrating with other applications. On next post I will write about creating account in On-premise Active Directory. Hope you find this post useful & informational. Let me know if there is any comments or feedback 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.

Accessing SharePoint modern page Likes & Comments using Power Automate

In a SharePoint modern page, you as an author would be able to turn on or turn off comments while creating a page. Everyone with minimum Read access on a SharePoint site would be able to Post, Like, reply to a comment at a single level, @mention a person while commenting in a SharePoint page.

When someone comments on a page or news post, SharePoint notifies the author or the person mentioned in the header of the page via an email. The SharePoint mobile app also alerts the author whenever a comment is made. If there are multiple comments, it will be batched so that the authors receive a single email when several comments have been made within a short period of time. Additionally, author gets email notices when users reply to comments or leave a “like”.  Author or a user can unsubscribe from notifications by clicking the “Unsubscribe” link in the footer of the email.

UnSubscribe

User with contribute access on a site would be able to delete any comments posted on the page which is quite an issue & not yet addressed by Microsoft. User with Read access to the site would be able to delete only the comments they have posted. Do you know where the comments are stored when ever you make a comment, they are stored in a separate data store with references to the Site Pages library guid & the item id of the page. The comments & likes are accessible via Rest API of the site. Find the different endpoints available for executing below actions

  • Getting comments & likes of a page item
  • Posting a comment on a page
  • Deleting a existing comment

There are couple of nice blog posts covering this topic with the API details. Find the links below:

On this blog post I am going to show you how to access the comments details of a SharePoint page using Power Automate.

Let’s create an automated flow with trigger “Manually Trigger a Flow”, add an action “Send an Http request to SharePoint” since the API to retrieve the comments is an SharePoint rest API.

The API for getting the comments of a SharePoint page is

https://tenantname.sharepoint.com/sites/sitename/_api/web/lists/GetByTitle(‘Site Pages’)/GetItemById(pageitemid)/Comments

Method: Get

Header Information: accept: application/json;odata.metadata=none

The odata.metadata=none option reduces the size of the payload significantly, and for many scenarios this is all that you need when working with list items

Run the flow, the output of this action would have all the data related to the comments posted by users on the page in a JSON format. To get the required information we will have to parse the JSON with the help of the Parse JSON action. Add the Parse to JSON action as below with content to the Body from Outputs of the action “Send an Http request to SharePoint”

Click on the button “Generate from sample” which loads a popup “Insert a Sample JSON payload” paste the run data of the flow for generating the schema automatically. To get the run data, go to the run history and click the run and go to outputs of the action “Send an Http request to SharePoint” copy everything inside the Body

Find the information of the body in the JSON online viewer to decide what information you need & what is available on JSON output

Add the compose action to see all the information available from the Output of the parse action, I’ve added only text which has the comment text & email has the email address of the user posted the comment

Summary: There are different Rest API endpoints available for comments & likes in Modern SharePoint page, you can select the any of them based on the requirement. The api’s can also be used on SPFx solutions for customizing Comments feature. Hope you find this post useful. Let me know if there is any comments or feedback by posting a comment below.

Actionable Outlook Message using Adaptive Card connected to SharePoint using Power Automate

Adaptive Cards a new way for developers to display & exchange content in a common and consistent way across different applications. It can be hosted on

  • Bot Framework Webchat
  • Cortona Skills
  • Outlook Actionable Messages
  • Windows Timeline
  • Microsoft Teams
  • Windows Notifications

SDKs (.NET, JavaScript, Android, React, iOS etc) are available for authoring & rendering cards inside your own apps. Microsoft has got a very great documentation on this. The schema explorer has got information about all the list of available Card elements, containers & actions sets to use. Adaptive card templating features which is now available in preview mode to help create, reuse & share the cards you develop & enable you to separate the data from the layout in an Adaptive card. There is also a plugin available called as Adaptive Card Viewer in Visual studio code for visualizing the card & a Team app called as App Studio.

Develop a card using the Adaptive Card Designer from the scratch or you can start with some available samples. Card Authors describe their content as a simple JSON object with the help of the designer which could then be rendered natively inside a Host Application as shown above, automatically adapting to the look and feel of the Host.

On this blog post, I am going to show you how to create an Outlook actionable message using a sample (Input Form) available in Adaptive card designer, this collects information (Name, Email) from the user & storing it in a SharePoint list using two flows. On submission of the form, a simple response message will be shown using a response Adaptive card. For the outlook version requirements for actionable message, click on this link. Find the flows to be created below

  1. Instant flow with the manual trigger for sending the adaptive card in an email
  2. Instant Flow using trigger When a Request is received for sending information to SharePoint list

Actionable messages can be via Email which I am focusing on this blog post or using connectors. Check here the supported scenarios via an Email.

Steps Involved to create actionable message via an Email:

  • Creating the Adaptive card for the host application (Outlook)
  • Creating the Flows

Let’s start with creating the card using the Adaptive Card designer

Adaptive Card Creation:

There is going to be two adaptive cards

  • First one is the initial form to be sent on email using the sample Input form
  • Second one is a response card which will be shown after the input form is submitted

First Adaptive Card:

Go the designer & click on Open Sample, select Input Form as shown below

Adaptive Card Designer

Then change the host app from the default Bot Framework Webchat to Outlook Actionable Messages. Remove the Phone number Text Block [Element] & the corresponding Text.Input [Inputs] field to keep it simple. Remove the Submit action set, add the action set of type Action.Http for us call the flow with method POST for us to interact with SharePoint

Update the following attributes of the just added element action set

  1. Rename the Title of the element to Submit from Action.Http
  2. Add the Id submit
  3. Change the Style to Positive from Default
  4. Select the Method to POST
  5. Url to be set to our second FLOW (When a Http request is received) HTTP POST url later once we have it ready with us
  6. Add the following to the Body
{
"inputName":"{{myName.value}}",
"inputEmail":"{{myEmail.value}}"
}
  1. Set the HTTP Headers
    • Click the Add New header, enter “Authorization” to Name and leave the Value blank. If this is not done, you will receive an HTTP 401 unauthorized message while clicking the button on the email. The HTTP request is received flow does not have any authentication its anonymous so be careful with the URL and have some steps to validate on the flow so check if its triggered from valid source
    • Add one more header, enter “Content-type” to Name and Value should be “application/json”. This is required to make the POST request from the email

The first card is ready, lets us go ahead and create the second one which is the response card. This will be shown once the response is submitted

Second Adaptive Card:

This is going to be a very simple card, lets start from the scratch. Go to the designer and select New Card

  1. Add a Container
  2. Add a TextBlock with text “Your Response has been submitted successfully” on the container

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

{
  "type": "AdaptiveCard",
  "version": "1.0",
  "body": [
    {
      "type": "Container",
      "items": [
        {
          "type": "TextBlock",
          "text": "Your response has been submitted successfully",
          "id": "response text"
        }
      ]
    }
  ],
  "$schema": "http://adaptivecards.io/schemas/adaptive-card.json"
}

Instant Flow using trigger When a Request is received:

This flow is an Instant flow with trigger “When a HTTP request is received”, this is going to be called from email for submitting the Input form 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 email.Create the flow with the trigger as said above. The POST url will be generated after the flow is saved with an action. Click Generate from sample on the trigger and copy and paste below information which would automatically generate the schema for you. If you have more advanced JSON schema with, try using this tool.

{
	"inputName": "",
	"inputEmail": ""
}

Click Advanced options to select the method POST. Add the action create item to store the user form response to SharePoint list created above with the request body information mapped to Title (Name – inputName) & Email (inputEmail) using the dynamic content

Add the Compose action, paste the JSON of the second adaptive card to the Inputs.

Compose action

Add the Response action, a premium one with the header key CARD-UPDATE-IN-BODY and the value as true. The body parameter should be the outputs of the compose action JSON

Response Action – Premium Action

Save the flow, the POST url will now be generated copy it and go to the first adaptive card and paste it on the url attribute for the Submit action set. After this copy the JSON from ribbon, we are now ready for creating the next flow

Instant flow with the manual trigger for sending the Adaptive Card

This flow is an instant flow with trigger Manually Trigger a flow for sending the adaptive card an outlook actionable message in an Email. You can have a different type of trigger based on the requirement. Add the compose & send an email (v2) action, paste the JSON for the first adaptive card to the inputs field of compose action. Make sure JSON also has the POST Url of the first flow on the Action set. On the compose action, also include the script tags as given below

<script type=”application/adaptivecard+json”>

— JSON of First Adaptive Card—

</script>

<script type="application/adaptivecard+json">
{
    "$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://upload.wikimedia.org/wikipedia/commons/b/b2/Diver_Silhouette%2C_Great_Barrier_Reef.jpg",
                            "size": "auto"
                        }
                    ]
                }
            ]
        }
    ],
    "actions": [
        {
            "type": "Action.Http",
            "title": "Submit",
            "url": "HTTP POST FLOW URL",
            "id": "submit",
            "style": "positive",
            "method": "POST",
            "body": "{\"inputName\":\"{{myName.value}}\",\"inputEmail\":\"{{myEmail.value}}\"}",
            "headers": [
                {
                    "name": "Authorization",
                    "value": ""
                },
                {
                    "name": "Content-type",
                    "value": "application/json"
                }
            ]
        }
    ]
}
</script>
Instant Flow for sending the actionable message

On the Send an email (V2) action, click the html view and select the outputs of the compose JSON which has the first adaptive card JSON. The flow is ready, click the Test link on the right corner of your flow to trigger it. The email will be received to the sender as below

Email – Input for collection information (Name & Email)

Once the information is Submitted, you would see the response as below

Email response after the form is submitted

If you would like to test with the tenant or global users, then register your adaptive card actionable message on the Actionable Email Developer Dashboard to enable this service. Enter the Flow URL (HTTP Request response), scope & sender email address. For more information on the developer dashboard refer here. Regarding security of the actionable message, refer here.

New Provider Registration

Once your provider is approved. Add the Provider Id (originator) field to the originator attribute in your JSON of the adaptive cards next to the type key, as follows:

“originator”: “ProviderId”,

Summary: You now have an actionable message adaptive card on outlook for collecting information from users on email. It provides you more functionality than the available Out of the box actions Approvals or Emails with options. To know the future road map, click here to know. 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.

Post a Teams Conversation on a Channel using API call/HTTP Request

On this blog post, I am going to cover different options to post a Teams conversation in a Teams channel using API call.

  • Using a Flow with HTTP Request trigger & Flow action
  • Create an Incoming Webhook in Teams
  • Graph Endpoint to create a Teams conversation

I do now see more requirements from customer to integrate Teams with different applications. These options for posting a message in a Teams channel could be to used on an External application or from a SharePoint Site using SPFx.

Create a Flow with HTTP Request trigger

Create an Instant flow with Trigger “When a HTTP request is received” a premium trigger which gets triggered to a HTTP request. This is a responsive trigger as it responds to an HTTP Request. The structure of the requests/responses that Microsoft Flow uses is a RESTful API web service known as REST. The API or HTTP post URL will be generated only after the flow is saved with at least one action.

Let’s say I would like to post a Teams conversation with @mention to a specific user and some message. In this case I will have to pass the information either in Parameters or on the body of the call. On this example we will be passing the information on the request Body

{
    "To":"ashiqf@####.onmicrosoft.com",
    "Message": "Hello from HTTP Request"
}

Copy the above sample and paste it on the popup you get after clicking on Generate Schema. The tool will automatically generate the JSON schema for you. Also jsonschema.net could be used to generate the schema

Find the generated schema below for the information email address & message which would be passed on the request body while making the POST call

Add the action “Post a message as the Flow bot to a channel”, this will create a Teams conversation on a specific channel in a Team. On the below screenshot, look at the way I am doing @mention to a user, leaving a New line & making a text appear Bold

After you save the flow, the HTTP Post URL will be generated for us to use in an external application or where ever we want. The API is not secured its anonymous. Lets now use Postman client to trigger. Don’t forget to set the headers for content-type to application/json, Information on the body & method to POST else the trigger will fail

After the Post button is clicked on Postman client which would then trigger the flow. The message on Teams channel will appear as shown below

In Postman client, there is an option to generate the code to call the API for different programming languages

As already said, the HTTP post URL is anonymous. If you would like to secure the flow actions, you could do it after the flow is triggered with some validations. If you look at the output from the Flow run for the Trigger, there is information on the field “User-Agent”, with this information you would able to add some validations

Create an Incoming Webhook in Teams for a Specific channel

Incoming webhooks could be used to create Teams Conversations on a specific channel on a team. It’s special type of Connector in Teams that provides a simple way for an external app to share content in team channels

To create a Webhook, go to the Teams channel where you would like to have the conversation posted. Click connectors

Create Webhook

Look for Incoming Webhook from the list of connectors then click Add

Provide a Name for the webhook and click Create. Now the URL will be generated, click Done

The generated URL will be on this format, get this copied

I will now use post man to create a conversation in Teams, it has to be Post request with the information passed on the Body. The header information is not required for this POST call

{"text":"<b>Post using Incoming Webhook</b><br>Message from External Application!"}
Postman Client
Teams Channel conversation

Ref: https://docs.microsoft.com/en-us/microsoftteams/platform/webhooks-and-connectors/how-to/add-incoming-webhook

Refer the below Microsoft documentation which has some example to create an Adaptive card in teams

https://docs.microsoft.com/en-us/microsoftteams/platform/webhooks-and-connectors/how-to/connectors-using

Graph Endpoint to create a Teams conversation

There is also endpoint (REST) available in MS-Graph to post/create a Teams conversation. The time I am writing this post, its a Beta endpoint and its not recommended for production use

POST /teams/{id}/channels/{id}/messages

The ID of the Teams and the Channel ID must be passed along with the bearer Token to create a team’s conversation. To get the bearer token create a Azure AD app with API permissions for Graph to create a message. The permission could be either Delegated or Application permissions based on the requirement. There are lot of articles which covers the steps to create an App in Azure so am not going to cover those in this post.

Ref: https://docs.microsoft.com/en-us/graph/api/channel-post-messages?view=graph-rest-beta&tabs=http

Summary:

I’ve described different ways to create team’s conversation using API calls. Hope you find this post informative & useful. If you have any comments or questions, let me know on the comment section.