Adoption with fun and OpenAI

I’ve detailed how to use an API with Power Automate to inject some engaging content automatically into Microsoft Teams.

Adoption with fund and astronomy

Thanks to API’s I want to extend this to use OpenAI and ChatGPT.

What I want to achieve is to ask a question of ChatGPT via an API everyday something like this:

“Tell me about 1 May as a list”

When I do this interactively at chat.openai.com I see:

I now want something like that to appear in Microsoft Teams daily for each day.

You’ll need to head over to OpenAI and get an API key. Here’s how to do that:

How to get an OpenAI API key in just 5 easy steps

I’d also suggest signing up for an OpenAI API subscription if you want to continue using this service. Here’s an idea of the costs.

Create a new Scheduled cloud flow. Don’t forget to change the repeat option to 1 day typically.

Add the Current Time action.

Add the Convert time zone action to get the time in your location. Set the format string to Month/day pattern (i.e. May 1).

Add the HTTP action. Remember, this is a Premium connector. Set teh following options here:

Method = POST

URI = https://api.openai.com/v1/chat/completions

Accept = application/xml

Content-Type = application/json

Authorization = Bearer (your OpenAI API key)

Body:

{
   “model”: “gpt-3.5-turbo”,
   “max_tokens”: 2000,
   “temperature”: 0,
   “messages”: [
     {
       “role”: “user”,
       “content”: “Tell me about @{body(‘Convert_time_zone’)} as a list”
     }
   ]
}

Ensure you double check all these settings as it is easy to make a mistake.

Add the Parse JSON action.

I’ve put the JSON schema you need here:

https://github.com/directorcia/general/blob/master/Power%20Platform/OpenAI/JSON-for-GPT

Add the Initialize variable action. We need to extract the returned text and format it so it will display in Teams. That means replacing the ‘/n’ string ‘ ’. That can be done via this expression:

replace(body(‘Parse_JSON’)?[‘choices’][0]?[‘message’]?[‘content’],’/n’,’ ’)

NOTE: if you copy and paste this expression and receive an error when you try and save it, you’ll most likely have to change the apostrophe from ‘ to ‘. It’s subtle, but you need to use the one to the left of the ENTER key on most keyboards to get it to work in Power Automate.

Finally add the Post message in chat or channel action.

Ensure you swap to the HTML editor using the </> item in the top right of the message as shown above.

Format the message the way you want to and insert variable created previously as shown.

When the Flow executes the output should look something like the above.

Note that GPT isn’t always right! You can also change the AI models if you wish, but remember they have different prices.

Typically, I’d suggest you run this Flow once a day to promote more engagement in your Microsoft Team. Better yet, you can show everyone how you are integrating AI into Teams. Yet another point of engagement in your Microsoft Team and perhaps a way to spark ideas for how you can expand the use of OpenAI in yoru business.

Power Automate Twitter connector failing

*** My resolution to this here – https://blog.ciaops.com/2023/04/27/connect-power-automate-to-twitter-using-a-bring-your-own-app-approach/

Since the 20th of April 2023 the Power Automate Twitter connector has been failing to authenticate. This means you get an error in your Flow that looks like:

When I tried to fix the connector I am greeted by.

There have no doubt been changes in the Twitter API which have caused this.

I’m trying to find an answer from Microsoft on how this will be handled going forward, but as yet I have not heard nothing back. If I find out, I’ll post here again.

Adoption with fun and astronomy

A while back I detailed how to schedule a Dilbert comic to appear daily in a Microsoft Teams channel:

Adoption with fun

Sadly, Dilbert has moved behind a paywall which means that process no longer works. As such, I have been searching for a suitable replacement and have settled on the Astronomy Picture of the Day from NASA.

The basic concept from the Dilbert process is the same. This process also requires a premium Power Automate connector, which you can easily configure using with either a Power Platform Premium license or using Power Platform PAYG configuration with Azure which I have shown previously.

The starting process is to create a new Scheduled Cloud Flow and select the time when you wish that Flow to execute.

You will then need to add a HTTP action as shown above. This is a premium connection mentioned previously. This HTTP action will need to use a GET method for the URI:

https://api.nasa.gov/planetary/apod?api_key=DEMO_KEY

Open that URI in a new browser tab and you should see some JSON information returned as shown above. Copy all of this.

Next add the Parse JSON action to the Flow. Then select the Generate from sample button at the bottom of this action as shown above.

Paste the text obtained from browser window previously in here and select Done.

Ensure you have the Body option selected in the Content area as shown above.

Next, add the Post message in a chat or channel as shown above. Add the appropriate Team and Channel. Then in the Message area select the </> icon in the top right to enable HTML editing.

Complete the formatting any way you wish but this is what I used:

<h1>Space Image of the day</h1>
@{body(‘Parse_JSON’)?[‘title’]} 
<img src = “@{body(‘Parse_JSON’)?[‘hdurl’]}”> 
@{body(‘Parse_JSON’)?[‘explanation’]}

Basically, I’m going to display a heading, then the title, high definition image and explanation (from the returned result).

The result when the Flow runs is:

and when the imaged is clicked on, you see something like:

Remember, the whole idea here is to encourage people to regularly visit the Team in questions and hopefully drive more engagement of the environment.

An introduction to Power Automate

Direct video link = https://www.youtube.com/watch?v=ob2ShmlMxv4

In this video tutorial I run through the creation of three basic Flows using Power Automate:

1. Capture new tweets that mention some specific text

2. Storing a copy of an email attachment in OneDrive for Business

3. Saving the output from a web API call to Microsoft Teams

All of these are simple to create and use. They can also be easily extended and enhanced upon. I hope they inspire you to spend more time using Power Automate.

Power Automate PAYG costs

Recently, I detailed how to enable the Power Platform PAYG billing:

Power Platform PAYG configuration

I now see the following in my environment that has Flows with premium connectors:

which basically says:

You can use premium capabilities in this environment. It’s covered by your org’s pay-as-you-go Azure subscription.

The reason I enabled this was because I wanted access to use Premium connectors without having to pay for a higher fixed monthly license cost.

I have the following Flow in this environment that uses two premium connectors:

– Azure Key Vault

and

– HTTP

If I now look at the recent Flow runs I see six in total 1 in November and 5 in October.

Now looking at the Azure costs by service for November I see:

and for October:

Therefore, with 5 runs in October my average cost was $3.70 / 5 = $ 0.74 while in November, with only 1 run so far it was $0.92.

Assuming the highest run cost of $0.92 and with the execution of 4 premium connectors in the Flow (3 x Azure Key Vault and 1 x HTTP) that comes to a cost of $0.23 per premium connector.

The big benefit of the Power Platform PAYG option is that it allows quick and easy access to Premium connectors without the need to purchase a higher Power Platform license at a fixed rate per month regardless of usage. This means the PAYG option is great for testing prior to committing to a higher fixed value license or occasional use of Premium connectors. This should be really appealing to many who may only need to use a Flow with Premium connectors a few times in a month. When the PAYG billing approaches the full license cost you can always switch over.

In summary then, from what I can determine, you should allow around $0.25 per Premium connector per Flow run when calculating your PAYG costs with the Power Platform.

Create a Dataverse database in Microsoft Teams

What I want to achieve in this process is to create a single Microsoft Dataverse database inside a Microsoft Teams and allow a basic Power Automate to add data to it.

Firstly, navigate to a Microsoft Team in the environment (here Automation), and select the + (plus) icon along the menu on the right as shown above.

In the list of options that appears, search for, and select Power Apps as shown above.

The first interesting thing, once you do that, is you typically can only select from pre-existing Power Apps that are listed in the dialog. However, there is an option create an app in Power Apps that you can select towards the bottom of the dialog as shown above.

You should then see the dialog display, like the one above, telling you to wait while things get set up.

If this process gets hung up after a minute or two, just refresh the page in your browser. You should now see something like that shown above with a list of the Microsoft Teams on the left. If you select the Microsoft Team you want to put the Dataverse database into (here Automation) you should see that nothing is built yet in the information area on the right.

Select the New button on the right and then App from the options that appear as shown above.

If you take a quick peek at the Power Platform admin center, in a new browser tab, and then Environments from the menu on the left or use the direct link:

https://admin.powerplatform.microsoft.com/environments

You’ll see that a new Power Platform environment has been created matching the name of the Microsoft Team (here Automation).

As the Microsoft documentation on Power Platform environments says:

https://docs.microsoft.com/en-us/power-platform/admin/environments-overview

A Power Platform environment is a space to store, manage, and share your organization’s business data, apps, chatbots, and flows. It also serves as a container to separate apps that might have different roles, security requirements, or target audiences.

In essence, think of an environment as a container to store things you create in the Power Platform. When you create a Power Platform App inside a Microsoft Team, it creates them in a unique container.

The idea is that you should be able to easily switch between environments. However, if you navigate to the Power Platform service directly at:

https://make.powerapps.com/

You are not able to see the environment just created in Microsoft Teams as shown above for some reason. It seems the only environments you can see here are those created directly in the Power Apps make portal.

You can drill into the new Teams environment you just created in the Power Platform admin center by selecting it from the list. Information about the environment will be displayed as shown above.

If you return to your app creation process inside the Microsoft Team, you’ll now need to give your app a name (here Capture)

Typically, you build a full app here but for now all we want to create is a single database, so select the Data icon on the left (cylinder) as shown and then select the Create new table button to the right of it.

You’ll then be asked to give the table a name (here Id). If you open the Advanced settings option at the bottom of the dialog, you’ll see that there are not many additional options to select from.

Select the Create button to continue.

You should now see the table displayed as shown above. You’ll also notice that there is already a column called Name created. This is a bit like when you create a new SharePoint list and get a single column created for you as well.

If you try and edit this initial column by selecting the header and then the Edit column option from the menu that appears above,

you’ll find there are not a lot of options available. This maybe limiting or just annoying as it is in SharePoint, but for now just leave that column in place. You’ll just need to remember to put some data in it as it is a required field.

You can then add any addition columns you require. Here I’ve added the columns Domain, Date and Value. These are the fields I want to populate with custom data.

If I return to the previous screen you should now see the Dataverse database listed as shown above.

Returning to the Build page in Power Apps in Microsoft Teams, and selecting the Microsoft Team (here Automation), you should now see some entries in the Items created for Automation list on the right. Here, you should also see the database just created as noted above.

If you select the database directly from this screen you can drill in and see the table and any entries as shown above. No data appears in the table yet as none has been added.

The way to get data into the database here will be via a very basic Power Automate Flow. It is a good practice to create this also inside the same Power Platform environment in which the Dataverse database was just created. Do this via the Cloud Flows option on the left as shown above.

To create a Flow, select Cloud Flows, then from the menu at the top on the right select the + New button. From the options that appear select Cloud Flows then type of Flow desired (here an Instant Flow).

The process for creating a Flow is the same as if you were creating a stand alone Flow via the Power Automate service. In this case, simply add the Dataverse Add a new row action as shown above. Configure this action to connect to the Dataverse database created earlier (Ids), then add some random text for the required default Name field (Hello), then data for Date, Domain and Value as shown above.

Save and Run the Flow.

If everything is correct, the Flow should run without errors as shown above.

If you then look at the details of the database you should see that it now has data inside it as shown above.

You could also create a Flow directly from the Power Automate service, but remember to switch to the new Microsoft Teams environment that was created by adding a Power Automate app to the Microsoft Team before creating the Flow.

The final interesting item here is to look at the capacity of the new database in the Power Platform admin center where you’ll find that, although you have a total size of 2GB, about 25% has already been consumed by the system.

For more information about the Dataverse for Teams consult the Microsoft documentation here:

About the Microsoft Dataverse for Teams environment

Using Power Automate to send SMS

I recently had the need to send an SMS programmatically. The reason I wanted to be able to do this si because Microsoft Bookings currently doesn’t send SMS reminders to attendees of a booking outside the US. That means, here in Australia, I needed to add the capability via another SMS integration option.

The most obvious choice, once again, is Power Automate. To handle the SMS component I needed to work with a SMS provider. The one who came to party was Mondotalk.

The first step was to consult the Mondotalk API to discover how to send an SMS using code. Luckily, their documentation contained a handy PowerShell script:

$messagebody1 = @{

id = $id

key = $key

username = $username

password = $password

to = “<phone>”

sender = “<phone>”

msg = “hello world”

replyto = director@ciaops.com

}

$url = <API url>

Invoke-RestMethod -Method POST -Uri $url -Body $messageBody1

In essence you create the body of the API request using an array and then you simply POST that to the API URL. Pretty straightforward and after I worked out all the variables like username, id and key, I had this working.

The key action is HTTP as shown above. It is important to remember that this a ‘premium’ connector and you may need a more advanced license to use this.

Initially, I thought I’d just bung in the parameters into the HTTP action as I have done before and it would be good to go. Not the case as it turned out. Everything I tried came back with a variation of the following error:

{“_meta”:{“status”:”ERROR”},”records”:{“errorCode”:401,”userMessage”:”Must login or provide credentials.”,”devMessage”:”Please provide credentials by either passing in a session token via cookie, or providing password and username via BASIC authentication.”,”more”:null,”applicationCode”:”Unauth:1″}}

In essence, the API is failing to login to my account to send the SMS. The PowerShell worked fine but Power Automate didn’t.

The solution ended up lying with adding a header field to the HTTP action:

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

and formatting the body with an ‘&’ between the fields i.e:

username=#####&password=*******&to=61######&id=#####&key=<GUID>msg=hello&replyto=director%40ciaops.com&sender=61########

as well as setting the authentication in the HTTP action to None as it was being done in the body of the request.

After all of that, I finally had success. The lesson here is that APIs don’t always want JSON!

I then decided to wrap a Power Virtual Agent bot around the Flow and publish it into a Team to allow users to send SMS directly from Teams. They simply use a key phrase to call the bot and tell it when number to send to. The bot passes those details to the Flow and runs what I had worked out previously

The only change here from the initial on demand Flow was the passing of a variable into the Flow from the bot and creating the body of the API request, also as a variable.

Again, it is important to get the body field in EXACTLY the right format for the API request when it is created.

So, that’s how you connect an SMS API gateway to Power Automate and Power Virtual Agents. The real trick was getting the right format of the API POST request which had to be deduced from the documentation. Hopefully, sharing that process here gives others a shortcut to getting it working in their environments because it took me a LONG time to work it out!

Thanks again to Mondotalk for being kind enough to give me a demo account and assist getting this working.

Displaying a percentage with two decimal places in Power Automate

In this section from my Power Automate I initialise two variables (currentscore and maxscore) with values using the Set variable action. I want a third variable, sspercentage, to be equal to:

(currentscore) / (maxscore) * 100

that is, a percentage but with only two decimal places. To do that I need to use the following formula in the Set variable 3 action:

formatNumber(mul(div(variables(‘currentscore’),variables(‘maxscore’)),100),’0.00′,’en-US’)

which you will note, if you look carefully, actually results in sspercentage being a string not a float!

(div(variables(‘currentscore’),variables(‘maxscore’))

handles the division

mul(div(variables(‘currentscore’),variables(‘maxscore’)),100)

multiples the division by 100 to get the percentage value.

The 0.00 in the formula is the actual formatting that you can adjust if you need and the en-US is the language format for the number. The Microsoft reference is for the formatnumber function is here:

https://docs.microsoft.com/en-us/azure/logic-apps/workflow-definition-language-functions-reference#formatNumber

The end result looks like:

If the formatnumber function is not used then the result contains a lot of numbers after the decimal place and remains of type float, which I would only need if I wanted to display the value of Pi!