Convert Speech to Text using OpenAI Whisper in Power Apps

OpenAI has released a new neural network called Whisper, which is an open-source model that can convert speech to text with impressive accuracy. This model is specifically designed to transcribe spoken language into text with high precision and speed, making it an ideal tool for a variety of applications, such as virtual assistants and video captioning. Whisper relies on advanced machine learning algorithms to analyze audio signals from multiple languages and convert them into written text. OpenAI has recently made API endpoints available to the public since March 1, 2023, allowing developers to easily integrate this powerful technology into their own applications.

The Speech to Text Open API can

  • Transcribe audio into whatever language the audio is in.
  • Translate and transcribe the audio into English.

As of the date I am writing this post, this model is not available in Azure. In this blog post, I will cover how to use the Microphone control and File Upload control to convert speech to text using the OpenAI Whisper API in a Power Automate flow.

Download Link to the Sample App: https://github.com/ashiqf/powerplatform/blob/main/OpenAI-SpeechtoText.msapp. Replace the API Key in the Power Automate flow HTTP Action Authorization Header.

OpenAPI Speech to Text API:

The speech to text API provides two endpoints, transcriptions and translations. At present, the maximum file size allowed for uploads is 25 MB and the supported audio formats are mp3, mp4, mpeg, mpga, m4a, wav, and webm. In this blog post, I utilized the Translation API to demonstrate its capability to convert English audio into text, it can understand other languages as well

POST https://api.openai.com/v1/audio/translations

If you have not yet created an API key, please sign up/login for OpenAI and obtain it from there.

Body:

Integration with Power Apps:

I have used a Power Automate flow with the Power Apps trigger to invoke the Speech to Text API via the HTTP connector in Power Automate. Alternatively, you can achieve the same outcome by constructing a Custom Connector. This sample app can be downloaded from this github link.

Microphone Control:

The audio control captures audio input through the device’s microphone and will be sent to the Power Automate flow for conversion into text using the Whisper API. The audio format of the recording depends on the type of device being used

  • 3gp format for Android.
  • AAC format for iOS.
  • Webm format for web browsers.

I’ve tested this control from the app accessed through the web browser. If you encounter an unsupported audio format for OpenAI, you can use utilities such as FFMpeg. Additionally, a .Net version of the control is available for download which can be used in Azure Function. John Liu (MVP) has written a sample Azure function that handles the conversion of audio formats using the aforementioned utility.

Step 1: To add a microphone control to the canvas, insert the Microphone control from the command bar. To preview the recorded audio from the Microphone control, add an Audio control

Step 2: Add a button to convert and to trigger the Power Automate flow. Find below the Power FX code

//Generates a JSON Text with the binary of the Audio file or Recorded audio
Set(varJson,JSON(Microphone1.Audio,JSONFormat.IncludeBinaryData));
Set(strB64Audio, Last(Split(varJson, ",")).Value);
Set(strB64AudioContent, Left(strB64Audio, Len(strB64Audio) - 1));
//Extract Audio Format
Set(varAudioFileType,Mid(varFileContent,Find(":",varFileContent)+1,Find(";",varFileContent)-Find(":",varFileContent)-1));
//Call the Power Automate Flow
Set(audioText,'SpeechtoText-OpenAIWhisper'.Run(strB64AudioContent,varAudioFileType).audiotext);

The Power FX code performs the following task

  • Stores the audio captured by a Microphone control in a variable as JSON data, including binary data.
  • Extracts the base64-encoded audio content from the JSON data using the string manipulation functions Split, Left, Mid.
  • Determines the audio file type by parsing a string variable.
  • Uses the extracted audio content and file type to call the Power Automate flow ‘SpeechtoText-OpenAIWhisper’ to obtain the corresponding text transcription which comes in later section of this post.
  • Assigns the resulting text transcription to a variable named ‘audioText’, this is assigned to a Text Label to display the converted text from the OpenAI Whisper API.

Step 3: Add a Label control to display the converted Text set to the variable audioText

File Upload Control

As of the day I am writing this post there is no file control that can handle all types of files in Power Apps, I have created a custom component utilizing the Attachment control to create a file attachment control. For further details, please refer to blogpost Uploading Files Made Easy: A Guide to Using the Attachment Control in Power Apps to add the control to the app.

Step 1: Add the file attachment control to the app from the component library. Set the input property for Maximum Attachments to 1 from the component.

Step 2: To extract the binary content of an audio file, add an Image control to the app. The Image control is capable of working with any type of file to extract its content.

Step 3: Add a Button control to convert the Audio from the uploaded file. Find the PowerFX below

//Generates a JSON Text with the binary of the Audio file using the Image control
Set(varFileContent,JSON(Image1.Image,JSONFormat.IncludeBinaryData));
//Extract Base64 content
Set(varExtractedFileContent,Last(Split(varFileContent,",")).Value);
//Remove the last character " from the string
Set(varExtractedFileContent,Left(varExtractedFileContent,Len(varExtractedFileContent)-1));
//Extract Audio Format
Set(varAudioFileType,Mid(varFileContent,Find(":",varFileContent)+1,Find(";",varFileContent)-Find(":",varFileContent)-1));
//Call the Power Automate Flow
Set(audioText,'SpeechtoText-OpenAIWhisper'.Run(varExtractedFileContent,varAudioFileType).audiotext);

Step 4: Add a Label control to display the converted Text set to the variable audioText

Power Automate Flow

Now, let’s create a Power Automate flow with the Trigger type Power Apps to invoke the OpenAI Whisper API and convert speech to text. Step 1: Add two compose action (input parameters) to receive the audio format and content from either the recorded audio captured by the Microphone control or the uploaded audio file from the file attachment control in the Power Apps

{
  "$content-type": @{outputs('Compose-AudioFormat')},
  "$content": @{triggerBody()['Compose-FileContent_Inputs']}
}

Step 2: Add a HTTP connector to make a request to the Whisper API endpoint. Refer to the blog post How to use form-data and form-urlencoded content type in Power Automate or Logic Apps HTTP action for handling multipart/form-data in the HTTP action

Request Body:

{
  "$content-type": "multipart/form-data",
  "$multipart": [
    {
      "headers": {
        "Content-Disposition": "form-data; name=\"model\""
      },
      "body": "whisper-1"
    },
    {
      "headers": {
        "Content-Disposition": "form-data; name=\"file\";filename=\"audiofile.webm\""
      },
      "body": @{outputs('Compose-FileContent')}
    }
  ]
}

Step 3: Add the Respond to a PowerApp or a flow action to pass the converted text back to the app. To get the converted text, use the following expression

body('HTTP-CallaOpenApiModel')['Text']

The expression was constructed based on the response of the Whisper API call. In the event that the response property changes in the future, please ensure to update the expression accordingly.

Summary:

In this post, I’ve outlined a step-by-step guide on how to develop a basic app with Speech to Text functionality using Power Apps and a Power Automate flow leveraging the OpenAI’s Whisper API. The possibilities for using this technology are endless, from creating virtual assistants to generating audio captions and translations. Furthermore, the Whisper API can also be used to transcribe video files, adding even more versatility to its capabilities. It’s worth noting that while Azure offers its own Speech to Text service, it currently does not rely on the OpenAI Whisper Model. However, it’s possible that the two services will eventually integrate in the future. Hope you have found this informational & thanks for reading. If you are visiting my blog for the first time, please do look at my other blogposts.

Do you like this article?

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

Advertisement

How to copy an existing DLP Policy in Power Platform

DLP policies are essential in ensuring that data is managed uniformly across an organization, thereby preventing critical business data from being accidentally published to social media or other connectors. These policies can be created at both the tenant and environment levels, with management handled through the Power Platform admin center. However, it is currently not possible to copy an existing DLP policy from the Admin center. This limitation can create difficulties when there is a need to create new policies based on an existing one.

In this blog post, we will explore various options for copying existing DLP policies to streamline the process. By using these options, you can save time and effort when creating new policies based on existing ones.

  • Power Automate Flow
  • DLP Editor Power Apps from CoE starter kit app
  • Power Shell

Note: To create a DLP policy at the Tenant level, you must be a Power Platform or Global Administrator role in AD.

Power Automate Flow:

The Power Platform Connector for Admins, available in both Power Automate and Power Apps, offers a range of environment lifecycle management capabilities, including DLP policy management.

To copy an existing DLP Policy, we will be utilizing the action List DLP Policies and Create DLP Policy in a Button Flow

Step 1: In the trigger, create two parameters to get the input for the existing Policy Name and the New DLP Policy name followed with the action List DLP Policies from the connector Power Platform for Admins to list all the policies in the Organization

Step 2: To select the DLP policy that you want to copy in a Power Automate flow, add a Filter Array action. This action filters the DLP policies obtained from the List DLP Policies action based on a condition. Specifically, it checks whether the displayName of the DLP Policy from the DLP Policies list action matching with the trigger input Existing DLP Policy Name. Once the Filter Array action is executed, it returns a new array containing only the DLP policy that meets the condition. This filtered array can then be used as input for creating a New DLP policy

Step 3: Add the action Create DLP Policy from the Power Platform for Admins connector with the first property Display Name from the Trigger input. For the other input parameters for the action, use the expression from Output of the Filter Array action as shown below

body('Filter_array')[0]['defaultConnectorsClassification']
body('Filter_array')[0]['connectorGroups']
body('Filter_array')[0]['environmentType']
body('Filter_array')[0]['environments']

Save the changes to ensure that they are preserved. Once you have saved the flow, you can test it to make sure that it works as intended. I have the flow definition saved in my github if you wanted to take a copy of it.

CoE Starter Kit App:

The Center of Excellence (CoE) starter kit core components solution includes a Canvas app DLP Editor with a range of useful features to manage and administer DLP policies. One such feature is the ability to copy an existing Data Loss Prevention (DLP) policy, making it easy to replicate policies across multiple environments or tenants.

This app uses the Power Platform for Admins connector.

Power Shell:

Power Apps Administration PowerShell provides a convenient set of cmdlets that enable you to easily create and manage Data Loss Prevention (DLP) Policies. Microsoft has provided a helpful sample script that allows you to manage your tenant and environment policies. With this script, you can perform a wide range of tasks related to DLP policies, including creating new policies, reading existing policies, updating policies, and removing policies. The sample can be found here. By breaking down the sample script into manageable sections, you can gain a deeper understanding of how DLP policies work and how you can modify them to suit your organization’s needs with PowerShell.

Summary:

This blog post provides a overview of different methods that can be used to copy existing Data Loss Prevention (DLP) policies, which is currently not available from the Power Platform admin center. These techniques can help automate the DLP policy creation process, saving time and effort.

Do you like this article?

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

Uploading Files Made Easy: A Guide to Using the Attachment Control in Power Apps

The Attachment control in Power Apps is a useful feature that allows users to upload and delete files, but it can only be used with data sources such as SharePoint List or Dataverse table. However, if you need to upload and delete files without using these data sources, you can create a custom component using the Attachment control or you can directly use this control in the app. I have followed the tip from Shane Young in this YouTube video to add the Attachment control to a component library.

By creating a custom component Library for the attachment control, you can upload and delete files similar to a Picture control but with the ability to handle any file type across any apps within an environment. This blog post is not a tutorial on how to create the component, but rather

  • How to use it
  • To Save the file in SharePoint Document Library using Power Automate Flow
  • How to customize the component to fit your needs.

How to use it – Add the Component to the Power Apps:

To incorporate this component into your app, you need to first import it into your environment. Please find below the steps to follow

Step 1: Download the component library from my github repo.

Step 2: Create a Blank Canvas App with a temporary name, on the studio command bar, click on the ellipsis > Click ‘Open’, browse to select the downloaded .msapp package. Save the App and then publish it. You would now be able to see the component from the Component Libraries.

Step 3: After following the instructions outlined in this documentation to import the Published component into your app, the component will be available for use in any app within the environment as shown below.

Step 4: Modify the input parameters of the component to adjust settings such as maximum number of attachments, border colour, attachment size, and other defined parameters of the component.

Step 5: To display the uploaded file content within the app or to send the file to a Power Automate flow, you can incorporate any of the following controls based on the file type:

In the Media Property of the control, the formula to display the file content is

First(FileAttachment_1.Attachments.FileAttachment).Value

The file content will be uploaded to the app as binary data with the URL appres://blobmanager/ for each file uploaded from the attachment control. To get the file Name:

First(FileAttachment_1.Attachments.FileAttachment).Name

Note: In the above screenshot, I have set the Max Attachments Component property to 1 in the Step 4

Send the File to Power Automate:

In order to send or store a file using a Power Automate flow, I needed to convert the file content to Base64 format. To accomplish this, I used a image control to capture the file content in binary format. Here is how I configured the image control:

This control works with any types of files to get the binary content.

After obtaining the binary content of the file using the JSON function, I performed some string manipulations to extract the binary content while excluding the Content-Type. Specifically, I used a combination of Split(), Left() and Last() functions to separate the content into an variable varExtractedFileContent.

Set(varFileContent,JSON(Image2.Image, JSONFormat.IncludeBinaryData));
Set(varExtractedFileContent, Last(Split(varFileContent, ",")).Value);
Set(varExtractedFileContent, Left(varExtractedFileContent, Len(varExtractedFileContent) - 1));

By performing these manipulations, I was able to extract the binary content of the file in a format that could be easily passed to a Power Automate flow or other API or action.

This allowed me to send the file to a Power Automate flow, which could then save the file in a SharePoint library or call some other API or action that required the data to be in Base64 format.

The Power Automate flow used to save the file to a SharePoint Document Library is simple. The flow consists of a Power Apps trigger and a SharePoint action Create File, which takes two input parameters: File Name and File Content.

I have used the base64toBinary() expression to convert the base64-encoded string to binary data. This expression is a prerequisite for the SharePoint create file action and ensures that the file is saved correctly to the SharePoint Document Library.

PowerFx to call the flow from Power Apps:

ProcessAttachments.Run(First(AttachmentComponent_1.Attachments.FileAttachment).Name,varExtractedFileContent);

If you need to upload multiple files to a library using the Attachment control, you can use Gallery control with the Image control, Collections, ForAll function, and the OnAddFile property from the Attachment control. First, create a collection to store the files that are uploaded using the Attachment control using the OnAddFile property. Then, use the Gallery control to load the binary of the uploaded files in the Image control. Next, use the ForAll function to iterate through each file in the gallery and call the Power Automate flow on a button click.

Customizing the Component:

The component I’ve created is a simple one for handling file attachments, but it does not have all the properties from the Attachment control. If you need more customization, you can easily modify it to suit your specific needs by adding additional input or output properties.

To add a new property, you can simply edit the component code and include the new property as an input or output parameter.

By customizing the component in this way, you can tailor it to your specific requirements and ensure that it meets all of your file attachment needs

Summary:

In summary, the Attachment control in Power Apps is a useful feature for uploading and deleting files, but it is limited to certain data sources. To work around this limitation, you can create a custom component using the Attachment control, which allows you to handle any file type and bypass the use of data sources like SharePoint or Dataverse tables. Hope you have found this informational & thanks for reading. If you are visiting my blog for the first time, please do look at my other blogposts.

Do you like this article?

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

Changing Connections in Connection References on a Managed Solution

A connection reference is a component in a solution that holds information about a connector. It can be used by both a Canvas app and Power Automate flows. When importing a managed solution to an environment, the user is asked to either select an existing connection or create a new one. However, once a managed solution is imported, it cannot be edited as shown below

The solution to this is to use the Default Solution, which is a special solution that holds all the components within the environment.

Go to the Default Solution as shown below

To change a connection in a connection reference:

  1. Go to Connection references
  2. Select the connection reference you want to edit
  3. Click “Edit” button.

Change the connection and then click Save

This will update the connection to a new user.

Do you like this article?

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

How to send an Adaptive card to a Microsoft Teams Private channel using Power Automate flow

Within Microsoft Teams, private channels create focused spaces for collaboration where only the owners or members of the private channel can access the channel. The Microsoft Teams connector in Power Automate has an action to Post an Adaptive card in a chat or channel, which posts an adaptive card as a flow bot to a specific Teams channel. The following error will appear if this action is used to post the card as a Flow bot in the Private channel

Request to the Bot framework failed with error: ‘{“error”:{“code”:”BotNotInConversationRoster”,”message”:”The bot is not part of the conversation roster.”}}’.

The above action will work if the Post as property in the action is changed to User but the creator of this connection has to be a member of the Private channel. This article shows how you can send an Adaptive card to a Private channel using incoming webhooks without being a member of the private channel

Create the Adaptive Card:

An adaptive card facilitates the exchange of UI content in a unified and consistent manner with a simple JSON without the complexity of customizing HTML or CSS. The adaptive card I have used in this example is created from the designer portal. Find below the JSON card payload

{
    "type": "AdaptiveCard",
    "body": [
        {
            "type": "TextBlock",
            "size": "Medium",
            "weight": "Bolder",
            "text": "Adaptive Card in a Private Channel"
        },
        {
            "type": "TextBlock",
            "text": "Lorem Ipsum is simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the industry's standard dummy text ever since the 1500s, when an unknown printer took a galley of type and scrambled it to make a type specimen book",
            "wrap": true,
            "color": "Attention"
        }
    ],
    "actions": [
        {
            "type": "Action.OpenUrl",
            "title": "View",
            "url": "https://ashiqf.com"
        }
    ],
    "$schema": "http://adaptivecards.io/schemas/adaptive-card.json",
    "version": "1.4"
}

Create Incoming Webhook on a Private Channel:

Incoming Webhooks allows external applications to share content within Microsoft Teams channels, in this case the cloud will be the external application sending an Adaptive card message to the private teams channel. You can add and configure an incoming webhook on a private channel by following the instructions on this link from Microsoft. Copy the Incoming webhook URL as mentioned in Step 6 from Microsoft documentation as shown below

Cloud Flow to send the Adaptive Card to a Private Teams channel:

The adaptive card JSON and the Incoming webhook is configured, lets create now create a flow with a HTTP action to send the Adaptive card

Step 1:

Form the HTTP request body for the HTTP action. Replace the Text with the JSON payload of the Adaptive card

{
  "type": "message",
  "attachments": [
    {
      "contentType": "application/vnd.microsoft.card.adaptive",
      "contentUrl": null,
      "content": 
	  Replace the ADAPTIVE CARD JSON PAYLOAD from the designer portal
    }
  ]
}

Step 2:

Add the HTTP action to the cloud flow with the following values against each parameter

Method: POST

URL: Incoming Webhook URLBody: from Step 1

Find below the adaptive card in the Private channel

Do you like this article?

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

Send Teams channel message aka proactive message using custom Teams bot and Power Automate – Part 2

In the last post, we have seen till the installation of the Teams App with the Bot on a Microsoft Team. Let us now continue to send a proactive message, be it an Adaptive card or a simple Text message on a Teams channel using Bot Framework REST API from a Power Automate Cloud Flow.

A proactive message is any message sent by a bot that isn’t in response to a request from a user. Ex: Welcome messages, Notifications, Scheduled messages, Broadcast message etc

Power Automate Cloud Flow:

For this blog post, I have used a Power Automate Instant cloud flow with manual trigger to send the message to a Teams Channel. To follow along the blog post, be ready with the following information

  1. Team ID & Channel ID

This information is required to send the proactive message to a Microsoft Team Channel. To get this information, in Microsoft Teams Client identify the Team channel in scope > Click the ellipsis of the channel in scope > Get link to channel as shown below

After decoding the channel link, the url will be in the format as shown in the below image from which you can get the channel Id and Team group ID

If you are building a Teams broadcaster or communicator application using Power Apps, these information can be stored in Table or a SharePoint list. There are Graph API endpoints which can used to get the Channel Id’s etc.

  1. Bot ID [Part 1 – Step 6]
  2. Bot Secret [Part 1 – Step 7]
  3. Teams Service URL

The service URL is the base URI for all Bot framework API requests. In Teams the service URL will change based on user’s region [EMEA, America, APAC, India etc]. This example delivers messages only on the Team channel and not to the users directly so you can choose the service URL based on the Microsoft 365 Tenant Location. Find below some URL’s based on region

RegionService URL
EMEAhttps://smba.trafficmanager.net/emea/
Americahttps://smba.trafficmanager.net/amer/
Indiahttps://smba.trafficmanager.net/in/
APAChttps://smba.trafficmanager.net/APAC/

All the required information is now available to proceed with sending the channel message using REST API.

Generate Access Token – Bot Framework REST API:

There are SDK’s in Bot Framework for programming languages like .NET, JavaScript, Python etc to handle all conversations for you but an alternative to using the SDK is leveraging Bot Framework REST API. The first step in using the different REST API endpoints from Bot Framework is to generate an access token which is then added to the Authorization header of each API request in this format

Authorization: Bearer ACCESS_TOKEN

To request an access, make a HTTP request per the following details

Method: POST

Request URL:

https://login.microsoftonline.com/botframework.com/oauth2/v2.0/token

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

Body:

grant_type=client_credentials&client_id=botId&client_secret=botSecret&scope=https://api.botframework.com/.default

Replace the botId and botSecret with the values stored from the previous steps. The Bot Id and the secret are from the custom Teams app created based on the previous post.

Add a HTTP Action in your Power Automate flow to add the above details for generating the token

The JWT access token is valid for 24 hours, if the token expires make another request.

Send Teams Channel Message:

The Teams Channel conversation post or proactive message on a channel can now be sent using the REST API to Create Conversation with the access token generated in above step.

Simple Text Message:

Find below the HTTP request detail to send a simple proactive message on a Teams Channel. The conversation Id is the Teams Channel Id

Type: POST

Request URL:

https://smba.trafficmanager.net/emea/v3/conversations/teamsChannelId/activities

Replace teamsChannelId (conversationId) with the actual Team channel Id

Body:

{
  "type": "message",
  "text": "Simple Text Message"
}

Authorization Header: Bearer access_tokenvalue

The Authentication of the HTTP action should be set to Raw, the value should be in the format

Bearer access_token

You can use Parse JSON Action to extract the access token from the previous HTTP action HTTP-GenerateBOTToken or you can directly get the value using the following expression

body('HTTP-GenerateBOTToken')?['access_token']

The above HTTP request will create a HTTP response with the activity id which can be potentially used to send a reply etc.

Adaptive Card Message:

Adaptive cards are platform-agnostic snippets of UI authored in JSON that different Microsoft apps and services like Teams, Outlook can use. It can be designed using the Adaptive Card designer portal. To send an Adaptive to a Teams Channel, everything else remains the same when comparted with above give HTTP request for the Simple Text message except the Body as below

{
  "type": "message",
  "attachments": [
    {
      "contentType": "application/vnd.microsoft.card.adaptive",
      "content": {
       "msTeams": {
          "width": "full"
        },
        Replace the ADAPTIVE CARD JSON PAYLOAD from the designer portal
      }
    }
  ]
}

You can get the complete body of request from this Link. This method can be used to send the message on any standard channel but not on Private Teams channel, Microsoft has not opened the possibility to send a channel message on private channel using a Bot. Find below adaptive card message posted on the Teams Channel from the Power Automate flow

Summary:

There are lot of possibilities with the Bot connector service REST API, what I have shown above is only an endpoint to send a message in a Teams Channel. Look at this documentation on the available conversation operations like Reply, Delete, Update conversation etc. Using this approach you can build a Company broadcaster app with the possibility of reaching out to multiple Teams without the user being the member or owner of the Team. If you are visiting my blog for the first time, please do look at my other blogposts.

Do you like this article?

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

Deep Link SharePoint News post in Teams using Adaptive card and Power Automate

Microsoft Teams helps us bring together content from different Microsoft 365 services for easier collaboration. In Microsoft Teams connected SharePoint site, SharePoint News connector would help receive news updates from the site. In this post let us see how to create Notifications about new News post with deep link to the post from a communication site in an Adaptive card on a Teams channel using

  1. Adaptive card
  2. Stage View
  3. Power Automate cloud flow

The users from a Teams channel would be able to read & engage on the News post by click of a button as shown below

Adaptive card:

Adaptive Cards are a platform-agnostic method of sharing and displaying blocks of information using JSON across various host applications like Teams, Outlook, Bots etc. The above adaptive card has following information from the News Post

  1. Title
  2. Description
  3. Banner Image Url
  4. Author Profile picture
  5. Author Name
  6. Published Date
  7. Deep Link to the post

The card can be designed based on your requirements from the Adaptive Card Designer portal. The JSON content of the above adaptive card can be downloaded from here.

Stage View:

Stage View helps provide a seamless experience of viewing content in Teams. Users can view the content without leaving the context thus leading to have a higher engagement. For this post, I have used stage view through deep link for a SharePoint News post. Find the syntax below to deep link SharePoint News post

https://teams.microsoft.com/l/stage/teamsAppId/0?context={"contentUrl":" newsPostPageUrl","websiteUrl":"newsPostPageUrl","name":"Internal News"}

In the above syntax replace teamsAppId, newsPostPageUrl & title which I have named as Internal News.

teamsAppId: 2a527703-1f6f-4559-a332-d8a7d288cd88

newsPostPageUrl: The url of the News post in SharePoint.

In the adaptive card action set OpenUrl the following from the stage view syntax should be encoded

{
  "contentUrl":" newsPostPageUrl",
  "websiteUrl":"newsPostPageUrl",
  "name":"Internal News"
}

I have also tested stage view for Microsoft forms & Power BI. You can find the app id for other Microsoft 365 service here on this link

https://docs.microsoft.com/en-us/graph/teams-configuring-builtin-tabs

Power Automate cloud flow:

The Cloud flow is used to send an adaptive card to a Teams channel with the SharePoint News post deep link whenever there is a new News post published in a Communication site.

Step 1: Let us start with creating the Automated cloud flow with SharePoint trigger When an item is created or modified. In the trigger, the Site Address should be url of the communication site and the List Name as the Site Pages Library GUID as shown below

Step 2: In the trigger settings enter the following trigger condition to fire only on the first major version of the News Post

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

For more information on trigger conditions for SharePoint online, go through this blog post.

Step 3: The compose action Compose-StageViewURL with the following code

{
  "contentUrl": "@{triggerOutputs()?['body/{Link}']}",
  "websiteUrl": "@{triggerOutputs()?['body/{Link}']}",
  "name": "Internal News"
}

The trigger output Link should have the url of the News post.

Encode the content in the compose action Compose-StageViewURL using the expression encodeUriComponent() and form the remaining part of the URL.

https://teams.microsoft.com/l/stage/2a527703-1f6f-4559-a332-d8a7d288cd88/0?context= @{encodeUriComponent(outputs('Compose-StageViewURL'))}

on another compose action Compose-StageViewURL-Encoded.

Step 4: Add the action Get user photo to display the picture of the author in the adaptive card. The User (UPN) property of the action can be provided from the trigger output Created By Email. The next step is to convert the output of the Get user photo action to Base64 encoded string using the following expression

concat('data:',body('Get_user_photo_(V2)')?['$content-type'],';base64,',body('Get_user_photo_(V2)')?['$content'])

Step 5: Add the action Post Adaptive card in a chat or channel as shown below

In the adaptive card JSON, find below the mapping information of each property from the output of trigger or action

  1. Title: {triggerOutputs()?[‘body/Title’]
  2. Description: triggerOutputs()?[‘body/Description’]
  3. Banner Image Url: triggerOutputs()?[‘body/BannerImageUrl’]
  4. Author Profile picture: outputs(‘Compose-Base64ProfilePic’)
  5. Author Name: triggerOutputs()?[‘body/Author/DisplayName’]
  6. Published Date: formatDateTime(triggerOutputs()?[‘body/Created’], ‘g’)
  7. Deep Link to the post: outputs(‘Compose-StageViewURL-Encoded’)

The export version of the flow can be downloaded from this GitHub link.

Summary:

The adaptive card with stage view to a SharePoint News post allows the users to open and view the content without leaving the context. The user can also Like or Comment on the News post. Hope you have found this informational & thanks for reading. If you are visiting my blog for the first time, please do look at my other blogposts.

Do you like this article?

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

Get deleted SharePoint site details using Microsoft Graph API

The deleted sites are retained for 93 days and an Admin can restore them. In this blog post let us see how to get the deleted SharePoint site details using Microsoft Graph API application permission.

Step 1: Register an application in Azure AD and obtain the client id, client secret & tenant id for the registered application. Add Sites.Read.All Microsoft Graph application permission

Step 2: Find the list GUID of the Hidden List DO_NOT_DELETE_SPLIST_TENANTADMIN_ALL_SITES_AGGREGA which has all the deleted site information from the tenant.

Make a GET request to the following Graph API endpoint with the token generated from the above AD app using PostMan or using Graph Explorer if you are an Global or SharePoint administrator

https://graph.microsoft.com/v1.0/sites/tenantName-admin.sharepoint.com/lists

Replace the tenantName with name of your tenant from the above URL

Step 3: Graph API to list all Deleted Sites within the tenant

https://graph.microsoft.com/v1.0/sites/tenantName-admin.sharepoint.com/lists/1dd71312-bceb-48bb-b853-7c0d33ac5618/items?$expand=fields

Make a GET query after replacing the tenantName and the list GUID of DO_NOT_DELETE_SPLIST_TENANTADMIN_ALL_SITES_AGGREGA from Step 2.

You can filter from different fields available [Title, SiteUrl etc]. To filter based on SharePoint site URL

https://graph.microsoft.com/v1.0/sites/tenantName-admin.sharepoint.com/lists/1dd71312-bceb-48bb-b853-7c0d33ac5618/items?$expand=fields&$filter=fields/SiteUrl eq 'https://tenantName.sharepoint.com/sites/siteName'

Step 4: To do this from Power Automate or Azure logic app, follow this post

https://ashiqf.com/2021/03/16/call-microsoft-graph-api-as-a-daemon-application-with-application-permission-from-power-automate-using-http-connector/

There are activity alerts which you can setup from Security center for Deleted Site but it will send you information on the Site URL and the name of the user deleted the site, as of now it does not provide the Title, Site ID etc. So this API can provide you additional details. Hope you have found this informational & thanks for reading. If you are visiting my blog for the first time, please do look at my other blogposts.

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

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

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

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

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

Trigger the flow if a News post is published

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

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

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

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

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

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

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

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

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

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

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

Trigger the flow only for Major versions

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

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

Trigger the flow for a specific Content Type

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

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

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

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

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

How it will be done

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

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

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

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

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

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

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

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

Tip to write the trigger condition:

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

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

Copy the expression from the inputs parameter

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

Summary:

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

Do you like this article?

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