Tag: Flow Friday

Flow Friday: Getting random row from Excel

Finally back for Flow Friday after some much needed rest over the holidays. This week is sort of a proof of concept. There are times when you need to just get a random value. This could be for a lottery or get a random name for the person that should take out the trash this week.

  1. Create an Excel Sheet in OneDrive with a table containing all of the names you need to randomize
  1. Go to Microsoft Power Automate and create a new flow
  2. Since I want it to run automatically I set it on a schedule
  1. Next we read the Excel file and figure out the number of rows
  1. We then create a variable that is a random number between 0 and [number of rows in the table]
Formula: rand(0,int(length(body(‘ListRows’)?[‘value’])))
  1. Then we get the name column form the row with the random number
Formula: body(‘ListRows’)?[‘value’]?[variables(‘RandomRow’)]
  1. Finally I sent the name in a notification
outputs(‘Compose’)?[‘Name’]

That is all… I will now get a notification with the name of the person that will take out the trash 🙂

Flow Friday: Posting Azure VM Auto Shutdown notifications to Microsoft Teams

When you have Azure VMs up and running there is a function to auto shut them down when they are not being used. You can do that in a couple of ways. One of the newer ones is to use the Azure DevTest Labs functionality to do this. Half an hour before the VM is shut down Azure sends an email to a pre-defined adress where you have the option to post-pone or cancel the shutdown.

Today one of my colleagues asked me if it was possible to get this email, with the links, into Teams. My first thought was to send it to the Teams Channel email. Unfortunately did not display correctly…

My next try was to use the Webhook in DevTest Labs functionality and the incoming webhook connector in Teams. When I did this the message did not look very “user friendly”

So I thought I would give Power Automate a try. I set up DevTest Labs to send the email to my mailbox. The I creating a trigger for an incoming email and with a filter for the email adress that Azure DevTest Labs user

The I add a block posting to Teams Channel. In order to get it to work I had to cut down the message to size. I used the title tag in the email and the phrase “Note that” at the end of the message to cut away the beginning and the end of the message body to fit the message in the Teams post.

The Expression:
substring(triggerBody()?[‘Body’],indexOf(triggerBody()?[‘Body’],'<H1′),sub(indexOf(triggerBody()?[‘Body’],’Note that’), indexOf(triggerBody()?[‘Body’],'<H1′)))

Finally I move the message to my Archive folder.

The message is now in Teams 🙂

Friday Flow: Using Microsoft Forms with Power Automate

At the company where I work we regularly have knowledge transfer sessions called Brown Bag Lunches (BBLs) which I think is a great way to share and give back. I am in charge of scheduling these.

I wanted automate the process for planning these sessions and give my colleagues a way to submit suggestions for content. I started by setting a simple Microsoft Form for them to add suggestions

Sorry about the Swedish 🙂

Microsoft has a page for looking at the results of the form submissions which I could have used to get the subjects and plan them manually… but where is the fun in that :-). My solution was to add a flow that is triggered every time a form is submitted which adds it to an excel spreadsheet and adds a todo task in my todo list of choice Todoist.

The result from Forms is in a JSON format so the first thing we need to do is to parse the JSON message.


The parsing of the JSON also means that we can use the content as dynamic content and add it without having to write an expression… the No-Code way. The JSON message also only contains the response id so we will have to use a “Get response details” block to get the actual information of the form.

The reason we are extracting the information is to put it in the Excel file… The response only contains the submitting users email adress so to get the excel file to look a bit nicer I use the Get User Profile block to look up the users real name from the Azure AD/SharePoint. This is a really cool block since it can get a whole lot of cool info from you organization such at a users manager etc.

Finally I add a line to an excel file and put a reminder i my todo list

I would really continue building this logic to automate the planning even further… I might have to revisit this in a later post 🙂

Have a nice weekend

Johan

Flow Friday – Troubleshooting Tips

This weeks Flow Friday is a bit late… Sorry…

Sometimes it can be a bit tricky troubleshoot flows… espesially if you are not fluent in JSON (I’m not). Trying to figure out exactly how to write the formula might sometimes require a couple of tries and my first solution to this was to send my self an email.

There is a better solution… You can use the compose function to get everything working. Just add the function you want to test in the Compose Window

The formula I added was this one

adddays(triggerOutputs()[‘body’][‘date’],1,”)

I am just trying to add one day to a date field that I input manually in the previous step.

When you have run the Flow you can just look in the run history for the output of the compose step

Flow Friday – Adding flagged email to todo list

As you all know Microsoft Flow together with Outlook is able to add tasks when you flag an email. In my workflow I am unfortunately not using Microsoft ToDo… I have put all my tasks in Todoist but I think the feature is awesome… Microsoft flow to the rescue

  1. Lets start with creating the trigger which is flagged email in Outlook

  2. I want the tasks to end up in Todoists Inbox with prio 1… The reason for this is not that all email is important but it is important to triage the inbox in order to set the correct prio, labels and project.

    The expression is for getting todays date and looks like this:

    formatDateTime(utcNow(),’yyyy-MM-dd’)

Flow Friday – Feeding news to MS Teams

The other day I asked my colleagues to provide me with good real world problems that I could help them solve in Power Platform. The first challenge was “Could you add news feeds in Microsoft Teams”. No problems 🙂

Create a new Flow and start with the RSS component and add the adress to the RSS feed.

Add the Microsoft Teams component called Post a Message V3 (Preview), select your Teams Tenant and the channel where you want to post the news posts.

Create the message and add Feed Title, Primary Feed Link and Feed Summary. In my case I want the title to be clickable and go to the original post in a new window. This required a small trick. Add a link from the UI using dummy text and link and the go to the code view ( </> ). Here you can replace both text and link with Feed Title and Primary Feed Link

Now we wait… for a post

That is it for today…

Keep Flowing

Flow Friday: Notifying your a bit extra

Today’s Flow Friday is a fairly simple one. Since I some times is a bit preoccupied and not able to keep up with my email and I also try to minimize notifications on my phone it would be very nice to have an extra notification when I receive an email where someone specifically mentions my name.  With flow this is simple…

In this flow I might be going a bit overboard 🙂

  1. When an email where I am mentioned arrives I the flow is triggered.
  2. The email is flagged
  3. A task is created in Todoist saying that I need to handle flagged emails
  4. … end a notification is sent to my phone.

This means that I will only get notified when I am mentioned 🙂

Happy Weekend

Flow Friday: Sync selected Calendar items between calendars

The other day I thought I would make myself use Microsoft Flow more… the best way I could think of was to force my self to write a series of blog posts every week… that should work 🙂

My first post is a description on how to keep your partner happy (or at least happier) when you need to work late.

I keep two calendar, one personal and one for work, where I share my personal calendar with my wife in order for her to know what I am up to. When I need to work late or have an event at work I would like her to know (since I often forget to tell her). This flow adds all events in my work calendar that end after 17.00 to my personal calendar and it also sends an invite to my wife.

We start with the trigger that triggers on all new events. Then we converts the time to Swedish Time Zone (1) and then we check if the event “Show As” field is “Busy” (2)

If the event is not busy we simply terminate the flow, if it is Busy we first validate that End Time is later than 17.25 (3) and if it is we create a new event in personal calendar with the same settings where I invite my wife as an optional attendee (4).

 

Automation is King!!

/Johan

Links:
https://powerusers.microsoft.com/t5/Building-Flows/Help-with-ShowAs-in-a-condition/td-p/157937

Flow Friday: Using Approval Status for Sharepoint lists in Microsoft Flow

Tonight I was trying to filter a Sharepoint List based on Approval Status in Microsoft Flow… this turned out to be a bit tricky… maybe even more than a bit.

Apparently the column Approval column is a system column and those are not visible as properties in Flow. There is however a way to get it… The property you need to use is OData__ModerationStatus. The different status values are

  • 0 for “Approved”
  • 1 for “Rejected”
  • 2 for “Pending”

The correct statement in Flow is

OData__ModerationStatus eq ‘0’

Another field you can access this way is OData__Moderation_comments

That is it for tonight

Links:
https://powerusers.microsoft.com/t5/Building-Flows/Approval-workflow-Sharepoint-List-Field-Update/td-p/13186