As a software product trainer, on an average training day, you must work through a lot of things. Besides the actual modules of a course, there might be labs, breaks for coffee, lunch, and/or tea, and, of course, throughout the day, there will be all kinds of conversations about the training, etc. All this makes it important to manage time properly.
In earlier days, I kept track of my progress on time by using a notepad to write down when I started/completed each module of the course, when the attendees started/completed assignments, and the duration of the breaks. After the training, I entered that information in an Excel sheet that contained the timings from previous training runs. This information helped me to understand where I might have to reduce spending time and where I could spend a bit more time. However, in the flow of a training day, it is easy to forget to write down all the events of a training day.
In this blog, I explain how I made things easier for myself by using a StreamDeck for time tracking with PowerAutomate.
Overview
To make things easier for myself, I decided to create a PowerAutomate Flow that I could trigger from my StreamDeck. The PowerAutomate Flow would write the events to an Excel sheet in OneDrive, so logging an event would be as easy as pressing a button.
To achieve this, at a high level, we do the following :
- Create an Excel sheet in OneDrive.
- Create the PowerAutomate Flow.
- Trigger the PowerAutomate Flow from StreamDeck.
Let’s have a look at all the individual steps.
Create an Excel sheet in OneDrive
We write all events to an Excel sheet in OneDrive. This ensures that the PowerAutomate Flow can access the Excel sheet. The sheet contains information about the customer, when we deliver the training, and the following event data:
- Date and time of the event.
- The nature of the event: Start or Stop
- Description of the event: Name of module or Break
The (populated) Excel sheet looks like the screenshot shown below.
Note: The table holding the events must have a name (see the red arrow). This enables the PowerAutomate Flow to understand where to write the events. We discuss this in the step about creating the PowerAutomate Flow. After saving the Excel sheet, we can proceed with creating the PowerAutomate Flow.
Create the PowerAutomate Flow
Next, we create a PowerAutomate Flow that writes the events to the Excel sheet we created in the previous step.
We trigger our Flow via an HTTP request because that enables us to trigger it from a StreamDeck. After completion, the flow looks like this.
As you can see, the flow has four steps. Let’s examine what happens at each step and what’s special.
Step 1 – Manual
This step is triggered via an HTTP GET request. It accepts two parameters for the Event and the Module. The relative path of the GET request looks like this:
Event/{Event}/Module/{Module}
An example looks like this:
Event/Start/Module/0-Introduction
By using these two parameters, the Flow is flexible, and we can use it for all events that happen.
In the Parameters section of this step, we find the URL that triggers the Flow from the StreamDeck.
Step 2 – Current time
In this step, the Flow collects the current date and time. Next, the step converts the date and time from UTC to CET and writes them to the Excel sheet.
Step 3 – Convert time zone
Here, we take the date and time from the previous step and convert them from UTC to CET.
Step 4 – Add a row into a table
In this final step of the Flow, a couple of things happen. Firstly, we point out the Location and Name of the Excel sheet and the name of the Table within the sheet. With this information, PowerAutomate checks what columns exist in that table, enabling us to populate the corresponding values.
Based on the screenshot of the table, we have seen that the following columns exist:
- Date – The Date when the event happens: formatDateTime(body(‘Convert_time_zone’),’d MMM yyyy’)
- Time – The Time when the event happens: formatDateTime(body(‘Convert_time_zone’),’hh:mm tt’)
- Event – The Event that happens. In this scenario, the values are Start or Stop. We take the value from the first action.
- Module – The name of the Module that we start/complete, or Break. We take the value from the first action.
Each time we trigger the Flow, a new row will be added to the Excel sheet.
Trigger the PowerAutomate Flow from StreamDeck
Now that the PowerAutomate Flow is ready, we use it as a parameterised HTTP Request; the only thing that is left is to create the required requests and add them to my StreamDeck.
We open the configuration software from the StreamDeck to configure the actions that must be executed by the StreamDeck buttons. In this case, I use my StreamDeck XL, which already contains configured buttons that I use when delivering training.
In the screenshot above, the buttons in the red frame execute different events. Each vertical set of a Start and a Stop button corresponds to starting or stopping the timing of one of the training modules. There are also buttons to Start or stop a coffee or tea break.
Invoking an API Request with StramDeck
To store an event, we need to invoke the URL of the PowerAutomate Flow and make sure we decorate it with the required parameters. With StreamDeck, we invoke a URL with an API Request action; I downloaded this free plug-in from the Elgato Marketplace, but it is also possible to use a default StreamDeck plug-in, which is less feature-rich but might work well in your scenario.
You can simply drag actions from the library on the right to the design surface. After dropping an action, you need to configure it and in our scenario, this comes down to providing the correct HTTP Method (GET) and the URL with parameters.
The original URL from the PowerAutomate Flow looks like the one below. For the sake of simplicity, the below URL has been shortened.
In curly braces, you can see placeholders for the event and the module.
So, an actual URL will look like the one below.
In this case, the scenario, the Start of the Introduction module, will be logged to the Excel sheet. Similarly, events can be written to start/stop the other modules, including the break.
That’s it! It is now just a press of a button to log events in the Excel sheet. If you have completed a training run, it is just a matter of deleting the values in the table, and you can start writing events for a new training run.
Conclusion
In this article, we have seen how to use a StreamDeck for Time Tracking with PowerAutomate when delivering trainings. But do you want to know the sad part of all this? Despite the efforts to make this as easy as possible, I still manage to miss writing some events! 😀
I have written another article about how trainers can use a StreamDeck. You find the article here:
How trainers benefit from the Elgato Streamdeck
See you in the next one!