Our payroll provider, Gusto, sends out electronic birthday cards for sa.global employees. I’ve been terrible about signing them. Power Automate Desktop was highlighted at Ignite this week, so I figured I’d try some automation to do the signing for me!
A Solution in Search of a Problem
Power Automate is an interesting tool with many potential paths. In this case, I started my automation attempt with the desktop automation tool, but it wasn’t solving things in the way I’d hoped. I moved on to the regular Power Automate site. I experimented with adaptive cards to convey birthday wishes. The Flow I ended up flushing out includes:
- Trigger the Flow from the Gusto birthday email.
- Find the person’s name in the email body.
- Look up the employee record in Office 365 using their name.
- Send an adaptive card asking if they want a birthday surprise.
- Wait until their birthday.
- Lookup a random birthday gif from GIPHY.
- Send an adaptive card with the special birthday greeting to the sa.global Teams Social channel.
About Adaptive Cards
Adaptive Cards are “mini forms” that allow users to interact with small bits of information. Microsoft has a site to generate these cards in the proper JSON format. The first card generated uses the Microsoft Teams Chat interface to ask for my colleague’s birthday and their thank you message.
Technical Notes
Power Automate requires a lot of web searching to find the relevant steps. Some of the useful Flow steps I used include:
Text Functions
- Find Text – Find the start and end position of the Employee name within the body of the email.
- Substring – Extract the name from the body of the email. The math functions Add add ( Number1 , Number2 ) and Subtract sub ( Number1 , Number2 ) resolve the exact position and length of the string.
Office 365
- Search for Users (V2) – Returns Office 365 record(s) matching the search criteria of User Name. The resulting step provides the user’s email address for sending messages.
Variable
- Initialize Variable – There are records like the one above where the query can theoretically return multiple rows. Initialize Variable was used to define a new Email variable using the following function: body(‘Search_for_users_(V2)’)[‘value’][0][‘Mail’]. Effectively this function brings back the Mail field for the first record (“0”) in the JSON file. This avoids Power Automate adding the “For Each” section later.
Date Time
- Current Time – Today’s date controls the minimum date value for the Adaptive Card. The user needs to enter their future birthday and not the year they were born for the card to be sent on the correct day.
- Convert Time Zone – The adaptive card needs the Minimum Date in “YYYY-MM-DD” text format. In another step, the date had to be converted into a universal text format.
Schedule
- Delay Until – Pauses the Power automate steps to wait until their birthday. The Gusto email is sent out a couple of weeks in advance.
HTTP
- HTTP – The GET function and the GIPHY API builder returns a random gif JSON payload. The gif is “G” rated since this is for work and filtered for the keyword “birthday”. Only one gif is returned. https://api.giphy.com/v1/gifs/search?api_key=#####&q=birthday&limit=1&offset=####&rating=g&lang=en
Data Operation
- Compose – Generates a random number between 1 and 4999 for a the GIPHY API query. Could do this step with a variable as well. Random number syntax is rand ( 1 , 4999 ).
- Parse JSON – Required for Power Automate to understand the GIPHY JSON response.
Microsoft Teams
- Post an Adaptive Card to a Teams user and wait for a response – Uses the Flow chat in Teams to send an adaptive card. This was the first screenshot above asking for the birthday and a thank you message.
- Post your own adaptive card as the Flow bot to a channel – Regular Teams messages in Power Automate don’t allow for rich formatting (or gifs!). Adaptive cards allowed for flexibility to make the message look nice and include the gif.
Final Thoughts
One of the keys to Power Automate is to continually return with new scenarios or relook at old ones. Power Automate can be a frustrating tool when encountering fatal flaws for your bright idea. In this case, I could have easily gotten to the end and not been able to post the gif to Teams were it not for the Adaptive Cards Preview feature. I’m not sure automating a 30-second process of signing an e-birthday card is the pinnacle of what Microsoft had in mind for Power Automate. But I do believe Adaptive Cards will be a valuable tool for business interactions and workflows. Think of your cases where a business process needs tiny bits of information, and the existing process is a series of unformatted messages or emails. Now those bits of information can be efficiently collected and processed through a specialized form. The final Adaptive Card + gif posts to an All-Company Social channel where sa.global employees engage in deep, meaningful dialog. And post gifs. Mostly it’s gifs. Kevin tells me he “doesn’t do the socials” so the Happy Birthday Bot today is an unstoppable force hitting an immovable object. I guess no matter what happens today – I want to say Happy Birthday, Kevin!