Stand Up, Teams bot
Every week at around 8:30am our Squad asks, who’s on Stand Up? How could we automate an answer for this, every week?
Workflows in Power Automate
I’ve seen workflows used in company wide messages, but I’ve never used one myself, or one that runs on a weekly schedule.
The flow needs to:
- Run every Monday at 8am
- Post a message in our Squad chat
- And @mention that Squad member, so they get a notification
Using the ‘post message in chat or channel’ is the first test. I linked the action to my Squad chat with a test message. Mentioning a user seemed too hard in the moment, so I gave up to come back later. Unfortunately ‘saving’ a flow can also test it, so it sent a test message to my Squad chat:
Workflows: @Name is on Stand Up this week.
To which my Squad responded:
Thank you Name!
Name can be on stand up every week imo
Well, now the secret’s out, I should probably make a working concept.
To mention a user you need their User ID and a ‘mention token’, which is really just their User ID with <at> tags, but you have to use the ‘mention token’ action, you can’t just use tags in the message text.
Find the User ID by searching their full name in the ‘search for users’ action, then pull that result through to ‘get an @mention token for a user’, then you can pull the token through to the ‘post message’ action.
But how do we know who’s on each week?
OneNote to Excel, and serial dates
I moved our roster from OneNote to Excel, and even shifted the roster to account for the summer break.
I asked Copilot to suggest a way to filter tables, and it lead me astray immediately… Suggesting a Data Operation to get the row and column, which may work, but the Excel actions are so much simpler!
Instead of using 3 actions to get the table, current date, and name column; 1 Excel action can do everything!
You’d expect this to be the end, however, you failed to recognise that Microsoft product family members do not play ball! Excel obviously stores dates as serial numbers, and Power Automate stores dates as ‘dates’, whatever dates are.
Copilot was actually helpful in writing an expression for this:
Convert now in UTC, to NZST, format date, covert to ticks (100 nanosecond intervals since 0001-01-01), subtract Excel’s epoch date (1899-12-30), divide the ticks by 864,000,000,000 (ticks in a day) to get the date serial number.
div(sub(ticks(formatDateTime(convertTimeZone(utcNow(), 'UTC', 'New Zealand Standard Time'), 'yyyy-MM-dd')),ticks('1899-12-30')),864000000000)
Great.
Works a charm! I updated the ‘post message’ action to be our Squad chat, and scheduled the flow for next week.
Here’s hoping it works on Monday!