Wednesday, 4 December 2019

Rickrolling these holidays with Power Automate and Microsoft Teams

Earlier this month I was asked by the wonderful Megan Walker whether I'd like to participate in the MVP Advent Calendar 2019. Of course I said yes! I thought about what I could share and decided to do something fun rather than my usual "How-To's." I learnt some new things as a result.

The idea

Since the holidays 🎄 is around the corner I wanted to do something in the theme of the season. One thing that most of us will be doing is saying bye to everyone before heading out the door or finishing up for the day. I came up with an Instant Flow that I could use where it would be triggered from my Power Automate app from my mobile. With one tap I can notify my crew in Microsoft Teams and send an email to all my primary contacts of the clients I work with.

BUT... I took a leaf out of Ashlee Culmsee's book where I decided to Rickroll my team from the message I post in the channel in Microsoft Teams 😁

Watch my vlog below and you can download my Power Automate here.

Let's Automate

This is what my Power Automate with Flow looks like, refer to screenshot below. We're using a parallel branch so that the emails sent to the contacts and the message posted to the channel in Microsoft Teams can be processed at the same time independently.

1.0 Manually trigger a Flow

As mentioned this will be an Instant Flow where the trigger will be a Flow button so that I can run my Power Automate with Flow from my mobile.

1.1 Retrieve Primary Contact records

When your Power Automate with Flow is in a solution and the CDS List Records action is used, an additional field will appear in the action called FetchXML.

This will be familiar to professionals who have been working with Dynamics 365 for a while. It's been used to query data to produce results that can't be achieved out-of-the-box in Advanced Find and for custom reports.

Using the FetchXML field in the CDS List Records action allows us to apply queries that we're already used to in Power Automate. For my query I only want to return contacts that are a primary contact for an account where I am the Owner of the contact record.

This is achieved by building the query in Advanced Find in the model-driven app and then selecting the download FetchXML button from the Advanced Find window. The FetchXML will then be available to use.

Copy and paste into the Fetch XML Query field.

1.2 Apply to Each

To email all the contacts returned in the CDS List Records action, an Apply to Each action will be used.

1.3 Send an email to Contact

The Office 365 Send an Email (V2) action will be used to send the email to the contacts returned in the CDS List Records action.

Bonus tip

One thing to be aware of is even though the full list of fields will appear when you use dynamic content, you can only reference the fields that were included as part of your FetchXML query. In my scenario I only included the fullname Contact field and therefore am referencing this field since I did not include firstname Contact field in my FetchXML query. If you do reference fields that are not in your FetchXML query from the CDS List Records action it will appear as blank.

Below is an example screenshot of an email when referencing the firstname. Since firstname is an attribute not defined in the FetchXML query, nothing is displayed in the output of the sent email.

1.4 Invoke an HTTP request

Currently we can't process mentions in the available Microsoft Teams actions however the Graph APIs can be called. Two Graph API requests will be used in my Power Automate with Flow.

First of all in order to perform any Graph API request there needs to be some type of authentication which can be achieved using the Invoke an HTTP request action as it will use Azure AD. In this action you must provide the following for the connector
  1. Base URL
  2. Base Resource URI
In my scenario it will be Below is an example screenshot.

Once the connector has been successfully completed, you'll then see the ability to
  1. Declare the type of API request 
  2. Provide the JSON body for the API request
Below is a screenshot from my Power Automate with Flow.

The List channels ID API request is used as per the article to retrieve the Channel ID and the name of the Channel so that we can use these two properties downstream in Power Automate with Flow. Part of the API request requires the Teams ID.

1.4.1 How to identify the Teams ID

The ID of a Team can be identified using the List all teams in Microsoft Teams for an organization API request and using Graph API Explorer to perform the request, you can retrieve the Teams ID.

1.5 Channel Name

Using a Compose action we can reference the displayName property from the response in the previous 1.4 Invoke an HTTP request action so that it can be used in our final action downstream. The expression I am using is the following


Since I'll be using it more than once in the final action I thought a Compose action would be more practical rather than referencing/writing the expression multiple times.

1.6 Channel ID

I'll also need the ID of the channel for the next action and using a Compose action to reference the ID property from the List channels response from the previous 1.4 Invoke an HTTP request action. The expression I am using is the following


1.7 Invoke an HTTP request

The finale is using the Create chatMessage in channel API request as per the article. In the request URL this is where we can reference the channel ID from the output of the 1.6 Channel ID action.

In the JSON Body there will also be a reference to the channel name from the output of the 1.5 Channel action.

I came up with the schema for the JSON Body from using the "Get Messages" action in a separate Power Automate with Flow where I was able to grab the body that contained the channel I wanted to post a message to.

This is what it looks like when viewing the entire response in Notepad++ where I've highlighted the part I used.

By using that section of the JSON Body I adjusted it by adding my own message and a URL reference to the Rick Roll video on YouTube.

Rickrolling in action with Power Automate and Microsoft Teams

Here's the fun part!!!!

On a more serious note

The emails will actually send too for my clients to be notified of my out-of-office dates as part of the holiday season.


I learnt a lot about the Microsoft Graph API and Graph Explorer in this fun holiday themed Power Automate. There's a lot more that we can do with the Microsoft Graph API if there are Microsoft related actions that are not available today in Power Automate. One thing to bear in mind is that Create chatMessage in channel API request is a beta API and it says clearly that it's not recommended for Production purposes.

Make sure you stay tuned for more gifts as part of the MVP Advent Calendar and shout to Megan Walker for kindly asking me if I'd like to participate.


Post a comment