Efficiently managing and communicating team availability in a hybrid world is crucial for smooth operations in any organization. This guide will show you how to create an automated yet interactive system for tracking and communicating the availability of general staff members using Power Automate, Microsoft Teams, and SharePoint.
Step 1: Understanding the Business Process
- Weekly Availability Surveys: Each Friday, staff members receive a personalized survey through a Teams chat message to submit their availability for the following week.
- Confirmation and Preview: Upon submission, staff members get a confirmation message and a preview showing the team’s availability as of the time they submitted their own.
- Comprehensive Monday Update: On Monday, everyone receives a complete overview of the team’s availability for that week, helping them make the most of everyone’s availability.
Video demonstration
The following video will demonstrate steps 2-3 (building the SharePoint list, and walking you through the Power Automate flow).
Step 2: Setting Up SharePoint
- Create a SharePoint List: Establish a SharePoint list for logging staff availability. Ensure it’s accessible to all staff members who will be participating and/or reviewing availability of teammates. It needs:
- A person column type for Employee
- 5 single line of text columns – one for each weekday. In my demo, I’ve repurposed and renamed the default Title column for Monday.
- Manage Permissions (optional): Assign appropriate permissions to staff members to view and edit the availability list. In order for someone to submit their availability via the chat request, they’ll need Contribute or Edit access at least. If you added the list to a site accessible to all relevant team members, they already have access to edit the list by default.
Step 3: Automating with Power Automate
- In-Office Status Survey Flow:
- Trigger: Schedule to run every Friday at 10:00 AM.
- It should first gather data from the SharePoint list (Get items) to know whom to ask for their availability
- Before asking an employee for their availability, I use the following in my Update item step’s Monday, Tuesday, Wednesday, Thursday, and Friday fields to reset them each week:
⏳ Requested- Post an Adaptive Card : The chat message to each employee uses the ‘Post an adaptive card and wait for a response’ action. For the Message of the adaptive card step, utilize the provided JSON below. The only thing you’d want to update is the company logo URL to include your own. You could also adjust things like ‘Telework’ to ‘Work from home’ or any other verbiage you choose.
{
"$schema": "http://adaptivecards.io/schemas/adaptive-card.json",
"type": "AdaptiveCard",
"version": "1.3",
"msTeams": {
"width": "full"
},
"body": [
{
"type": "ColumnSet",
"columns": [
{
"type": "Column",
"width": 5,
"items": [
{
"type": "TextBlock",
"text": "Good morning!",
"weight": "Bolder",
"size": "Medium",
"style": "heading"
},
{
"type": "TextBlock",
"text": "Let us know where you'll be working next week so we know how best to collaborate with you.",
"isSubtle": true,
"wrap": true
}
]
},
{
"type": "Column",
"width": 1,
"items": [
{
"type": "Image",
"url": "https://i0.wp.com/natechamberlain.com/wp-content/uploads/2022/08/cropped-Nate-Chamberlain-logo-icon-e1661192398264.png",
"altText": "Company Logo",
"size": "auto"
}
]
}
]
},
{
"type": "Container",
"items": [
{
"type": "ColumnSet",
"columns": [
{
"type": "Column",
"width": 33,
"items": [
{
"type": "TextBlock",
"text": "Monday",
"wrap": true,
"weight": "Bolder",
"horizontalAlignment": "Center"
},
{
"type": "Input.ChoiceSet",
"choices": [
{
"title": "In office",
"value": "🏢 In office"
},
{
"title": "Telework",
"value": "🏠 Telework"
},
{
"title": "Leave",
"value": "🌴 Out of office"
}
],
"placeholder": "Select",
"style": "expanded",
"id": "Monday",
"isRequired": true,
"errorMessage": "Please make a selection"
}
]
},
{
"type": "Column",
"width": 33,
"items": [
{
"type": "TextBlock",
"text": "Tuesday",
"wrap": true,
"weight": "Bolder",
"horizontalAlignment": "Center"
},
{
"type": "Input.ChoiceSet",
"choices": [
{
"title": "In office",
"value": "🏢 In office"
},
{
"title": "Telework",
"value": "🏠 Telework"
},
{
"title": "Leave",
"value": "🌴 Out of office"
}
],
"placeholder": "Select",
"style": "expanded",
"id": "Tuesday",
"isRequired": true,
"errorMessage": "Please make a selection"
}
]
},
{
"type": "Column",
"width": 33,
"items": [
{
"type": "TextBlock",
"text": "Wednesday",
"wrap": true,
"weight": "Bolder",
"horizontalAlignment": "Center"
},
{
"type": "Input.ChoiceSet",
"choices": [
{
"title": "In office",
"value": "🏢 In office"
},
{
"title": "Telework",
"value": "🏠 Telework"
},
{
"title": "Leave",
"value": "🌴 Out of office"
}
],
"placeholder": "Select",
"style": "expanded",
"id": "Wednesday",
"isRequired": true,
"errorMessage": "Please make a selection"
}
]
}
],
"bleed": true
}
]
},
{
"type": "Container",
"items": [
{
"type": "ColumnSet",
"columns": [
{
"type": "Column",
"width": 33,
"items": [
{
"type": "TextBlock",
"text": "Thursday",
"wrap": true,
"weight": "Bolder",
"horizontalAlignment": "Center"
},
{
"type": "Input.ChoiceSet",
"choices": [
{
"title": "In office",
"value": "🏢 In office"
},
{
"title": "Telework",
"value": "🏠 Telework"
},
{
"title": "Leave",
"value": "🌴 Out of office"
}
],
"placeholder": "Select",
"style": "expanded",
"id": "Thursday",
"isRequired": true,
"errorMessage": "Please make a selection"
}
]
},
{
"type": "Column",
"width": 33,
"items": [
{
"type": "TextBlock",
"text": "Friday",
"wrap": true,
"weight": "Bolder",
"horizontalAlignment": "Center"
},
{
"type": "Input.ChoiceSet",
"choices": [
{
"title": "In office",
"value": "🏢 In office"
},
{
"title": "Telework",
"value": "🏠 Telework"
},
{
"title": "Leave",
"value": "🌴 Out of office"
}
],
"placeholder": "Select",
"style": "expanded",
"id": "Friday",
"isRequired": true,
"errorMessage": "Please make a selection"
}
]
},
{
"type": "Column",
"width": 33
}
]
}
]
}
],
"actions": [
{
"type": "Action.Submit",
"title": "Submit"
}
]
}
- Get the updated list of availability (Get items 2) including the new response, making sure to use the following filter query to only get rows where people have responded for the coming week:
Title ne '⏳ Requested' and Title ne null- After someone responds, they’ll get a chat preview of the whole team’s availability for the week.
- In-Office Status Results Flow:
- Trigger: Schedule to run every Monday at 9:00 AM.
- This flow compiles survey responses into an HTML table (similar to how we did the first flow) and distributes it to the team via a Teams group chat message where they can ask follow-up questions, provide additional context, coordinate meetings, etc.
- Important – Exclude the Sender: The sender/owner of the flow should not be included in the Monday communication to recipients (assuming they’re using their credential to connect for that step in the flow). Since they’re the one connecting to Teams via Power Automate, they’re already included in the eventual group chat. Attempting to include them will result in an error.
Conclusion
By following these steps, you can set up a streamlined and automated process to manage the availability of your team. This system not only saves time but also ensures clear and consistent communication among all staff members.
Let Microsoft Flow and SharePoint Designer Workflows Do the Work
