If you’re managing projects using SharePoint lists, reminding team members of any tasks they’re responsible for that are nearly due, or indeed overdue, a few days before your team meeting can ensure the meeting is fluid and stuff gets done.
If you’ve set up your list using the guidance outlined in Flexible Project Management with SharePoint Lists and the Power Platform then scheduling a summary of tasks to be issued to members can really help. This article outlines how that can be accomplished using Power Automate.
1. First step is to head to Power Automate and create a new flow that is triggered on a schedule – perhaps set it to every Monday morning, or a few days before your regular team meetings.
2. Add a ‘Get items from a Sharepoint list’ action:
Choose the right site and list, filter to ‘Status’ does not equal ‘Completed’ and enter ‘TaskDue asc’ in the ‘Order by’ field.
3. Add an ‘Initialise variable’ action, call it ‘Responsible’, and choose ‘Array’:
4. Add an ‘Append to array variable’ action, choose ‘Responsible’ array and add the ‘Responsible’ column from the list as the value. This ought to auto-generate apply to each’s for the list and the values:
5. Add a ‘Compose’ action, and insert the following code as an expression into it:
union(variables('Responsible'),variables('Responsible'))
6. Add another compose action, rename it ‘Table CSS’ and paste the something like the following code into it to ensure the format of the email is lookin’ good. You may want to edit this so it aligns with your organisation’s branding:
<style>
Table {
font-family: Times New Roman; font-size: 16px;
}
Table th {
font-size: 15px;
font-weight: bold;
padding-top: 12px;
padding-bottom: 12px;
text-align: left;
background-color: #f5202e;
color: white;
border: 1px solid #ddd;
padding: 3px 3px;
}
Table td {min-width:80px)
</style>
7. Add an ‘Apply to each’ step and select the outputs from the first ‘Compose’ step and add a ‘Filter Array’ step beneath that.
From the ‘Insert dynamic data’ menu, choose the ‘Get data from list/value’ and enter the following as an expression to the first field:
string(item()?['Responsible'])…then choose ‘contains’ and ‘Current item’ as follows:
8. Have a cup of tea, we’re almost there! Add a ‘Create HTML table’ action and using the output of the Filter Array as inputs, create at least the following custom columns:
| Task | <a href="https://[your-company].sharepoint.com/sites/[your site]/Lists/[your list]/DispForm.aspx?ID=[Id]">[Title]</a> |
| Due | Enter the following as an expression:formatDateTime(item()?['TaskDue'],'dd/MM/yy') |
It ought to look something like this:
9. To personalise the email, add a ‘Get user profile’ action and add ‘the ‘Current item’ to the input field:
10. Last step! Add a ‘Send email’ action, inputting:
- The ‘Mail’ value from the Get user profile action to the To: field (though you may want to put your own email in there for testing purposes)
- Give the email a useful title
- In the body, start it off with the ‘Outputs’ of the Table CSS compose action
- Paste the following code after that, editing the text in red according to the instructions:
<table align="center">
<tbody>
<tr>
<td height="20"> </td>
</tr>
<tr>
<td> </td>
<td width="990">
<p><span style="font-family: times new roman; font-size: 50px; color: #f5202e;">[Big heading]</span><br /><span style="font-family: times new roman; font-size: 22px;">[Sub-title]</span><br /><br /></p>
<hr />
<table style="width: 990px;">
<tbody>
<tr>
<td style="vertical-align: top;"><p><span style="font-family: times new roman; font-size: 16px;">Dear [Given Name](from the Get user profile action),
,<br>
<br>
Here's a handy <a href="[URL to the 'My tasks' view of the list]">list of tasks you've taken responsibility for</a>, which have a due date, and are overdue or due soon...:<br>
<br>
<div style="width:90%;margin:auto">
[Add the following as an expression:
replace(replace(replace(body('Create_HTML_table'),'<','<'),'>','>'),'"','"')]
</div>
</br>
[Add some footer text, perhaps something like 'I hope this is helpful, do let me know if anything can be improved, and we'll see you on Wednesday!']
</span><p>
</td>
</tr>
</tbody>
</table>
</td>
</tr>
</tbody>
</table>Finally, test your flow; this is how our team’s one comes through for me:
This article is part two of the Flexible Project Management Information Handyman series:
- Flexible Project Management with SharePoint Lists and the Power Platform
- Issuing a weekly digest to each responsible person listing their upcoming and overdue tasks
- Adding sub-tasks, or actions, to your project tasks using another list and a Power Apps form
- Building a custom Power BI report to gain insights on team activities and report on progress