Friday, 20 December 2019

Playing Spotify to Google Home using Flic and Power Automate

04:58 Posted by Benitez Here , , , , , No comments
This is a special holiday edition #WTF episode where I came up with a fun idea of playing a Spotify Christmas on my Google Home through one click from my Flic button and Power Automate. I had a lot of fun with Megan Walker's MVP Advent Calendar - check out my gift here, and I wanted to do one more holiday themed vlog before the end of 2019.

The warm-up

I had a look at the Spotify developers API documentation to identify the requests I needed. Once I found the API requests I needed, I had to sort out the authentication method and in the Spotify Authorization Guide there was a method that I could use called "Authorization Code." I knew of the tool Postman from my last project that I worked with Natraj Yegnaraman when I was living in Melbourne but I didn't know how to actually use it. I asked my awesome colleague Rex Wessels (🦖) about Postman and he taught me how to work with Postman. After a few hiccups the API requests were successfully sending as I was getting the responses I needed. Hooray!

An obstacle I experienced was that the access code used by the Authorization Code expires and requires a new token which can be generated from the refresh token. I was wondering how to replicate this in Power Automate. I had a go with the HTTP action in Power Automate I couldn't get it work, an error was occurring.

That API Guy

I messaged one of the #FlowFam crew, Vivek Bavishi, for some guidance because I knew he successfully worked with the Spotify APIs from his road trip to UG Summit with Jon Levesque, Ed Gonzales and Anton Robbins. They had this awesome idea of getting the Microsoft Community to interact with them on Twitter by tweeting a song and artist with the hashtag of #ElectricMayhemRequest 

Vivek created a Power Automate that collected the songs requested through Twitter and added them to a Spotify playlist so that they could listen to the music in their road trip. If you want to watch their cool road trip you can do so here.

After sharing my error with Vivek he said that I could connect with Spotify through a custom connector. We jumped on a Microsoft Teams call and he shared with me how to do it. As a result he has blogged about it which you can read here. I won't outline the steps on how to set up the custom connector as Vivek has already provided a guide in his blog post so read it as a prerequisite before following my steps in this #WTF episode.

Setting up the Spotify actions in the custom connector

When you create a custom connector in the Power Platform you can configure actions. In Power Automate this will be something you are already familiar with - think of CDS List Records action, CDS When a record is created action and so forth. Adding actions in the custom connector allows Power Automate makers to select the action when they apply it in their Power Automate with Flow.

There will be two Spotify API requests that I'll be using in my Power Automate with Flow.
This Spotify API request will return a list of active devices. Take note that in the Spotify API documentation it states
When no available devices are found, the request will return a 200 OK response with an empty devices list.
If you see no devices in the returned response it means that you don't have active devices. I've found that with my Google Home it will drop off from the list after a period of time if no music is playing. The JSON response will be an array where any device found is in a row.

To configure the action click on the Import from Sample button.

In here you can select the method, the URI and if required the JSON body. Below is an example screenshot.

Once you import it will be available as an action that can be selected in Power Automate through your custom connector.

Start/Resume a User's Playback 

This Spotify API request will play a playlist, album or artist based on the Spotify ID that is provided in the JSON body. You can find the Spotify ID through the Spotify web player which I've highlighted in yellow in the below screenshot.

One of the query parameters that is optional in this Spotify API request is the device ID.

If a device ID is provided, Spotify will play the playlist, album or artist to the device. This is why we need to call the Spotify API that will retrieve the list of active devices so that Google Home can be called to play the selected Spotify playlist/album/artist.

Follow the same steps as per above and don't forget to add the JSON Body which you can find in the Spotify API documentation.

Note: As per Vivek's blog post it's important that you provide the scope outlined in the Spotify API documentation for the API Spotify request you are calling. This is done in the Security tab of the custom connector otherwise an error message will be encountered as the scope has not been defined in the authentication.

Testing your APIs

One thing I didn't demonstrate in my vlog is that you can test your actions within the custom connector to set up prior to using them in Power Automate. You can run the action and check whether the response is successful.

Lets Automate

The following is what my Power Automate looks like.

1.0 When Flic is clicked

The trigger will be when a single click is executed on the Flic button.

1.1 Retrieve Devices

In this step My Spotify custom connector can be used where we select the action that will retrieve the devices.

There is no need to provide any parameters.

1.2 Parse JSON

The Parse JSON is used for the purpose of using the Filter Array action downstream.

If we don't Parse the JSON and if we attempt to use the Filter Array action the following error will occur.

Power Automate doesn't recognize the output as an array.

1.3 Initialize Variable

To use the Filter Array downstream in Power Automate we need to use the Initialize Variable action for the output from the Parse JSON action. 

1.4 Filter Array

Since multiple devices will be returned from the Spotify API in 1.1 Retrieve Devices, the Filter Array action is used to only retrieve the row in the array that has the Google Home device ID so that we can use it in the action that will call the Start/Resume a User's Playback Spotify API request.

I used the name of my Google Home device as the condition.

The JSON response will then only return the row in the array where the name property is equal to my Google Home device. This is so that we can use the device ID property value in the final action of Power Automate.

1.5 Play on Device

This is the magic finale! I am using the other action in My Spotify custom connector where I can reference the device ID from my Filter Array action in an expression.

The expression will look like the following,


I am also providing the context URI for my chosen Spotify Christmas playlist. This is required as seen in the the Spotify API documentation for the Start/Resume a User's Playback request.

Power Automate in action

I did a video on my Twitter which shows me clicking my Flic button and my Google Home playing my chosen Spotify Christmas playlist which you can view here.


Using the Flic button and a Power Automate custom connector to call the Spotify APIs, a chosen Spotify playlist/album/artist can play on a device such as Google Home.

As mentioned earlier, devices will only be returned by the Spotify API if they have been active or idle. Don't forget to check out Vivek's blog post for the steps in setting up a custom connector for your Spotify account.

If you want to purchase a Flic button head over to their website. They have recently released their next version of Flic called Flic 2.0. I am not sponsored by Flic by the way, I simply like Flic and like being creative with it using Power Automate 😄

One final note

Thanks again for supporting me and subscribing to my YouTube channel.
I hit my 1,000 subscribers milestone last month 😁 I am grateful, thank you!

Wednesday, 11 December 2019

Displaying dates in a Create HTML Table action in Power Automate

This is a continuation from my previous WTF episode where I shared how to email a tabular list of cases that were either five most recent cases or five oldest cases of a Contact. One of the column values is the Created On field from the Case record. When you view the sent email the dates will be displayed as UTC.

As mentioned in previous episodes, Power Automate treat date and time as UTC therefore will be displayed in a UTC format when retrieving the value. The answer as always from my other WTF episodes is to convert the UTC to the desired time zone which is what I’ll cover in this WTF episode.

Two Options  

There are two options that can be used to display the date and time correctly.
  1. If Contacts are in a single time zone, a single expression can be used in the 1.2 Create HTML table action in the Flow seen in my previous WTF episode. For example in New Zealand there is only one time zone. If an Organisation is sending emails to customers that are based in New Zealand and no other country this option would be appropriate. 
  2. If Contacts are in multiple time zones, a few more actions are required which has been shared in previous WTF episodes. For example in Australia there are multiple time zones. If an Organisation is sending emails to customers nationwide then a single expression with a static time zone value is not valid.

Option 1 – single expression 

Since the Created On date is a UTC value the function to use is convertFromUtc

This function requires 
  1. The property that represents the UTC value 
  2. The destination time zone name which will be a string value that represents the name of a time zone (note I use the time zone names from this article)
  3. The format of the date and/or time of the desired output, for example dd/MM/yyyy 
The expression will look like this 

convertFromUtc(item()?['createdon'], 'AUS Eastern Standard Time', 'dd/MM/yyyy')

This expression will convert the Created On UTC value to a date and/or time that is more understandable by the recipient. The email will now display the Created On date value using the defined time zone in the expression.

This option is suitable if the Organisation has Contacts in a single time zone because of how the string value is explicitly referenced in the expression. It is not flexible for Contacts in multiple time zones.

Option 2 – a few more actions 

In previous WTF episodes I shared how to convert a UTC value into a value of the Contact’s local time zone. 
You’ll see that the following actions is used in all three episodes which I’ll once again use in Option 2:
  1. Bing Maps action 
  2. HTTP action 
  3. Compose action 
These three options need to be before the Create HTML table action. 

The next steps to be explained are covered in my last blog post for my Flow Online Conference session which I’ll use again for this WTF episode. 

1.2 Get Contact Location by address 

As seen in a previous WTF episode there is a Bing Maps action "Get location by address" that allows the location latitude and longitude to be identified based on address information. The Address 1 fields in the contact record will be used. 

1.3 Identify local time zone of Contact 

There are additional Bing Maps Time Zone APIs available which you can read from this Bing Maps blog post. These APIs are not available as Bing Maps action in Flow however the HTTP action can be used to call the APIs.

In order to find out the time in the primary contact's time zone, the "Given location coordinates, find the time zone of the place" Bing Maps API can be used through a HTTP action.

Simply reference the latitude and longitude outputs from the previous step followed by inserting a Bing Maps key., longitude?key=<bingmaps-key> 

1.4 Retrieve windowsTimeZoneId property 

To ensure the contact views the date and time the email in their local date we need the name of the time zone they are located in.

The windowsTimeZoneId property will have the name of the time zone which can be retrieved from the JSON response of the previous HTTP. Below is a screenshot of the response.

There are two methods which can be used to retrieve the windowsTimeZoneId property which I’ve covered previously. The Compose action will be used to reference the property without using the Parse JSON and simply an expression instead.

The expression will look something like this

1.5 Create HTML table 

The convertFromUtc function can now reference the localTime property using the output of the compose action.

The expression will be the following
convertFromUtc(item()?['createdon'], outputs('1.4_Retrieve_windowsTimeZoneID'), 'dd/MM/yyyy')

1.6 Send email to Contact 

Same set up as to the email as per the previous WTF episode and in Option One where the Created On date will be displayed in the local time zone of the Contact based on their latitude and longitude address 1 coordinates.


When dealing with date and time values where it is to be displayed to a user or a customer, it is important to remember that Power Automate treats date and time as UTC. The date and time value will need to be converted to a destination time zone which can be done using the Convert Time Zone action or several functions such as convertFromUtc as per this blog post.

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.