In the context of the first automation, we manipulated Power Automate by focusing on Microsoft Teams, Microsoft Planner and Microsoft Outlook.
Like any type of ticketing tool, it’s important to be able to quickly and efficiently extract statistics. We’re therefore going to learn how to achieve this.
I was particularly eager to write this second article, because we’re getting to the heart of the matter. And most importantly, we’re going to focus on Excel; it’s going to be pure joy. If – like me – you appreciate this software, and it’s difficult to quench your thirst for learning about this software… You’ve come to the right place.
No need to be verbose about Excel, we have one objective: automate, automate, and automate some more.
While writing this article, I thought it might be a good idea to propose automation levels – proportional to everyone’s skills.
Thus, with the first level, we’ll stay fairly on the surface, with a fairly rudimentary – but effective – analysis level. We’ll simply create connections using Power Automate blocks and our Excel file. Then, we’ll create a “Dashboard” sheet to capture the most prominent features of the issues reported in our ticketing tool.
At the second level of automation, we push the vice slightly by diving deeper into Excel’s depths. What does this mean? We’re going to implement a macro (with VBA language) to analyze the content of received issues, thanks to email subjects. This analysis will allow us to categorize the tickets.
At the third level, we increase the difficulty and this time include JavaScript to analyze the problems encountered. This automation will also be an opportunity to familiarize ourselves with Office Scripts.
Excel Desktop vs Excel Web = VBA vs JavaScript
Why propose two similar automations, but in two different languages? Behind this question lies the very problem of automation itself. Personally, when someone makes an automation request to me, I try to rationalize the requested process. The mental approach is often the same: which applications can we connect? What data is exchanged? Etc. Then, a preliminary testing phase allows us to eliminate options as we go.
These options can be proper uses, for example, using Microsoft Teams as a means of communication. Or diverted uses, that is, transforming Microsoft Teams into a ticketing tool thanks to Microsoft Planner or Microsoft Outlook.
Regarding Excel, Microsoft made the choice (or was forced to, no idea) not to allow macro execution on the web version of Excel. I use many macros daily and it’s heartbreaking not to be able to use them on the web version, especially since we’re led to use this version if the file is shared. It’s not impossible to use macros on an Excel file hosted on a SharePoint site or OneDrive directory; you simply need to open the file with the desktop application. By doing this, it’s possible to activate macros since we fall back on a classic Excel view.
To properly understand the stakes of this automation, we’re going to test this method.
We’ll compare it with the JavaScript (JS) based method. With JS, we emphasize Excel’s web version. The automation is similar to VBA, except it’s based on Office Scripts.
Office Scripts allows automations using Excel’s web references. Unlike VBA, the script won’t work on the desktop application.
Two schools, with different difficulty levels.
SPOILER: the VBA approach will be less fluid than the JS one, as it will require opening the file to activate macros. The version using Office Scripts is the coolest, as it won’t require any human action
