Has COVID-19 changed the way we work, forever?
If we take a look at some of the industry giants, then it seems that everyone is preparing their remote workforce for the long haul – and some are even giving employees the option to work from home, indefinitely.
Currently, both Google and Facebook have informed staff that they won’t be returning to the office until July 2021 at the earliest. Meanwhile, Twitter, Square and Microsoft are all giving their workforce the option to work from home, on a permanent basis.
If your business and your employees are going to thrive in this new era of remote working, then you’ll need processes in place to support your remote workforce.
In this tutorial we’ll show you how to automate the time-consuming admin typically associated with managing a remote team. By the end of this tutorial, you’ll have used Power Automate to create an automated work and location tracker for your newly-remote workforce.
What we’ll be building
Perhaps you have staff who work in the field, or who regularly travel to different locations. Alternatively, maybe your remote workforce are mostly home-based, but you still need a way to record their whereabouts for health and safety purposes.
You may even be new to remote working, and want to create some kind of timesheet to help make this transition seem less daunting.
In this tutorial, we’ll be creating a workflow that employees can use to record all of their remote working activities, with minimal effort. We’ll be using Power Automate, Excel, and the Power Automate mobile app (available for Android and iOS). Once this workflow is up and running, your staff can log the time, date, and their current location, with the tap of a button.
To build this automated work and location tracker, we’ll need to create:
- A SharePoint site.
- An Excel workbook.
- A Power Automate workflow.
Excel & SharePoint: Creating a site and workbook
The first step is creating the site and workbook where employees will log their whereabouts, along with the data and time:
- Log into your Microsoft 365 account.
- Launch the SharePoint application.
- Click “Create site > Team site.”
- Select “Team site.”
- Choose whether this site should be public or private, and then click “Save.”
- On the subsequent screen, add anyone else who’ll require access to your site and workbook, and then click “Finish.”
- In the toolbar, select “New.”
- Select “Excel workbook.” You should now have a fresh Excel workbook.
- Give this workbook a distinctive name, by clicking the name field to make it editable, and then typing the name that you want to use.
- Now, we need to create a table where we’ll record all our data. I want to record the date, timestamp and the employee’s current location, so we need to create three columns. In your Excel document, drag to highlight three columns.
- Make sure the “Home’ tab is selected, and then choose “Format as Table.”
- In the subsequent dropdown, choose the style that you want to apply to this table. ● In the “Format as Table” popup, select “My table has headers.”
- Click “OK.” Excel will now create your table.
- Select each column’s header in turn and give these headers the following names: “Time,” “Date” and “Location.”
Now, you have a workbook and three columns where Power Automate can record all of its data.
Automatically record employee data: Create a Power Automate workflow
We’re going to collect all the necessary information and import it into Excel, using a Power Automate workflow. While you could create this workflow from scratch, you can save a considerable amount of time and effort, by using one of Microsoft’s ready-made templates:
- Log into your Microsoft 365 account.
- Select the “Power Automate” app.
- In the left-hand menu, select ‘Templates.”
- Search for the following template: Track your work hours and work location in Excel Online (Business).
- Select this template when it appears.
- Click to expand the “Add a row into a table” section.
- Select “Location” and enter the name of the SharePoint site that you just created. ● Select the “Documents library” field. In the dropdown that appears, select “Documents.”
- In the “File” field, click the little file icon. This launches the file browser.
- In the subsequent popup, select the Excel workbook that you just created.
- Click to select the “Table” field. In the dropdown, select the table that you just created.
- Power Automate should now load all your columns and add the necessary dynamic content to each column, for example adding “key-button-timestamp” to your “Time” column. However, if this doesn’t happen then you can add this information manually, to match the following screenshot.
- By default, this workflow will display the following push notification on the employee’s device: “Location and time logged successfully!” You can modify this message, for example I’ve edited the notification to include the date and time that’s recorded to the Excel spreadsheet.
- To modify the push notification, click to expand the template’s “Send me a mobile notification” section. You can then add, edit and remove static and dynamic content, to create the perfect push notification.
- When you’re happy with your workflow, click “Save.”
Put your Power Automate workflow to the test
Now, your employees can trigger this workflow from their smartphone or tablet, using the Power Automate mobile app.
If the employee doesn’t already have Power Automate installed, then they can download it for Android or iOS, and then sign in using their Microsoft 365 username and password.
Now, whenever the employee wants to record their location and provide a timestamp, they just need to:
- Launch the Power Automate app.
- Select the app’s “Button” tab.
- Find the “Track your work hours…” button and give it a tap. If the workflow runs correctly, then a push notification will appear on the user’s device.
Now, the employee’s location, date and time information will be recorded to the Excel workbook.
Ready to automate even more monotonous, everyday tasks?
Managing a remote workforce doesn’t have to be a confusing and time-consuming process. In this tutorial, we built an automated working hours and work location tracker, using Power Automate, SharePoint, and Excel.
Want to automate even more time-consuming and repetitive tasks? Then check out some of our other Power Automate tutorials:
- Automatically send a working from home email. Perhaps your employees are currently splitting their time between the office and home, or you’re anticipating returning to this setup in the near future. With this workflow, your employees can send a WFH email to their manager, with the tap of a button.
- Create an automatic vacation request and approval platform. Take the pain out of submitting, reviewing and approving your staff’s’ annual leave. This workflow uses Microsoft Forms, Outlook, SharePoint Online, and Power Automate, to create a complete vacation request and approval platform.
- Automatically track your business expenses. Create a “living” record of your staff’s spending. In this tutorial, we created a workflow that allows employees to take a photo of a receipt, train ticket, or other document, and then log this evidence to Excel and OneDrive.
- Automate your employee onboarding, with Power Automate. When someone joins your organization, there’s often a list of apps and services they need permission to access. Make sure your new hire has everything they need to hit the ground running on their first day, with this onboarding workflow.
Claim your exclusive cloud migration package
Let us help you migrate to the cloud, with 6 months free access to Office365 plus a guided migration led by our team of specialist engineers.
Speak to a member of our team today 0114 292 2911 or email firstname.lastname@example.org if you need any assistance.
Share this article on social media
If you found this article useful, please share it on social media.
Subscribe to our blog…
We will only use your email to send you new blog posts.