When managing work items on a project, it's ideal for the Product Owners (and other stakeholders) to be working in the same system as the team doing the work. Sometimes, however, it's difficult to get people into the system so we have to 'meet them where they are' and draw them in from someplace else. This is why I've come up with a couple of solutions to summarize work items and present them in either Teams or an email.
There are a few apps out there, but the two I see most often are Azure Dev Ops ("ADO") and Jira. There are some pretty strong opinions on each in the community, but I'm not here to judge :) This article will cover Jira into an email, and I'll have another one listing ADO items in Teams. Feel free to mix and match to best suit your use-case. That is, after all, the beauty of the platform.
Before we begin with the Power Automate side, however, there are a few things we need to do in Jira to prepare. The first item is setting up an API Key to allow Power Automate to access our Jira data. Atlassian deprecated access to third party apps using passwords, so now the only way is via these API keys. Lucky for us, though, it's not too difficult to get one:
Begin by going to https://id.atlassian.com/manage-profile/security/api-tokens and logging in using your Jira credentials.
From there, click on the "Create API Token" button and assign a label, then click "Create"
This will bring up a new dialogue with a very long string of characters. Copy that string someplace since we'll need to access it for our flow.
The next step is to build the query that we'll use to get the list of items to send out. It's important to consider how many items might possibly be returned with your query to keep your emails (or Teams post) within a reasonable size.
For my list, I am looking for a list of open tasks assigned to a specific user (or group of users). Since I am not super familiar with JQL (the query language that Jira uses), and we'll need a JQL query for our Power Automate step. I've found the following method to work best for me, but you might have another way that works for you, and that is totally ok!
My method begins with logging into Jira and then selecting "Filters" from the top menu bar, and then the "Create filter" button on the right. This will take you to a search screen to start building the parameters for your desired work items.
If you see a bar like the one to the right, indicating a JQL query, click the "Switch to basic" link to the right of the bar to pull up a graphical user interface (GUI) for the search parameters.
Using the drop-down menus, I can select the appropriate project, item type, status, and assignee. It should look something like this (I've redacted the project name):
Clicking on the "Switch to JQL" link on the right will revert back to the query language and we can copy and store that query for when we're ready to build our flow.
At the core of the Power Automate side, we'll have a recurrence trigger and an action from an independently published connector by Paul Culmsee called "Jira Search". Surprisingly, the Jira connector does not include an option to run a JQL query or to list work items, so Paul built one for us using Atlassian's API.
There is one small trick when using Paul's connector, however. Since Atlassian deprecated the use of passwords for third-party apps, you'll log in using your usual login name, but put your API key from above in the password field.
You'll only need to establish the connection the first time you use the connector, and once that's done, you'll have a normal-looking action to populate the fields...
If you test your flow so far, and all is well, we can now work on taking the results of the search, and making them suitable for our HTML emails. I like to add a condition after these, though, to make sure to stop the show if there are no results from the search.
Inside the left part of the condition is the following:
empty(outputs('Searches_for_issues_using_JQL')?['body/issues'])This will produce a "true" result if there is nothing there, so in the "Yes" side of the condition, I just added a Terminate Flow step. How you terminate is up to you, but I choose to end it as "Cancelled" so that when I'm looking at the list of runs, I can tell which ones ended intentionally, and which ones had problems to investigate (failed).
As you can see on the right side, if the results are NOT empty I will create an HTML table choosing the columns I want along the way.
Since I am creating an HTML table, it made sense to me to make one of the columns a hyperlink to the Jira work item. I created a field called "IssueLink" and then built in some HTML using the "self" field (which is the work item URL) and the "key" field (the work items out-facing identifier).
That table, though, won't be super pretty. So I always add a step after which builds in some padding and borders using a trick from April Dunnam. To do this, I drop a Compose action after the Create HTML Table (I rename the step to "Make it Pretty"...
...and use the following expression:
replace(
replace(
replace(
replace(
replace(
body('Create_HTML_table'),
'<',
'<'
),
'>',
'>'
),
'"',
'"'
),
'<table>',
'<table border="1">'
),
'<td>','<td style="padding:20px">'
)Finally, you can use any Send email action (depending on your email provider), but this is the one for Office365 Outlook:
Remember, that all of these steps occur inside the "no" path of our condition.
That's all there is to it. Check out the bonus feature below if you'd like for this to only run on weekdays, and then next week I will push an article out on grabbing work items from Azure Dev Ops and posting them to a Teams channel.
Bonus Content: Run only on weekdays
To make certain that my product team isn't getting emails over the weekend, I added a condition just after the trigger that checks the weekday using the dayOfWeek() function. If the dayOfWeek was equal to "0" (Sunday) OR was equal to "6" (Saturday), then it would go down the "Yes" path where I terminate the flow with a status of "Cancel".
If it wasn't either of those days, I just dragged the rest of my flow into the "No" path of the first condition (as shown above).
I hope you found pieces of this useful, and are having fun building things in Power Automate.
Good luck!