The Challenge
Exporting data into a excel table is simple. You can export it as a CSV file that can be opened by excel. The way to do it is with the use of flows in Power Automate. The method is to create a flow that will accept a JSON from PowerApps then have that JSON data converted into a CSV format and inside the flow there is a method to generate a CSV file. The CSV file will be downloaded and all you have to do is to open it in Excel.
This is my attempt at recreating the Excel file template for the skill matrix of each employee.
​
Note: It looks nothing like the original but I need to show something to have an idea of how and what I need to put on the template.
As you can see at the top part of the template is the information about the employee. The bottom half is a table that has the skills and the levels associated per skills.
Exporting it as a simple CSV file is impossible since I need to use a template. The image at the top the profile information is located on different cells and the only table is below. In the end I did end up making it work but it was buggy since it was still on experimental stage. Yes, the "actions" in Power Automate that I used had experimental written on it.
​
NOTE: I couldn't recreate this part because my account doesn't have (automation) on Excel. In order for this to work, the account needs to have access to automation in Excel but I did my best to recreate the parts that I have access to.
The Solution
There is only one way that I found to make this work and that is to use automation in Microsoft Excel. I'll put in here the step by step of how I managed to make it work.
Set up
There are two thing you need to set up in order for this to work.
-
The template (empty/no data)
The template that you will need to use and it has to be inside a SharePoint as a file or inside a one drive. Both SharePoint and One Drive works.
-
Office Script
These are scripts inside Excel that you use to automate repetitive tasks. Office Scripts are created using Type Scripts which are based on Java Scripts. It's almost the same as Java Script but with some added API created by Microsoft. You need this made first and put inside either a SharePoint or One Drive.
The office scripts that I used are custom made, so they were tested first before actually using them.
Dev Note: I couldn't recreate them. I have no examples to show.
​
Create the flow
After you the set up. You need to make the flow in Power Automate.
It needs to have a Power Apps Version 2 trigger and as you can see there are multiple data that needs to be passed here.
Alternative
Yes, you could pass on a single data (the ID) and use the flow to get the information you need but I chose the first one because it is 1/5 the processing time as this alternative. (I tested it multiple times). Sample is below
Complete Flow
The alternative requires a lot of data manipulation inside the flow in order for it to be used on a "script" action inside the flow. This requires a lot of processing and significantly increase the processing time.
The completed flow below is what it will look like if I used the first one instead of the alternative
-
Get triggered in Power Apps along with the data
-
Left: Copy the template saved in SharePoint then place it on a different folder, rename it by, employee ID + Date Today or what ever you want to name it.
Right: There is a JSON text passed at the very bottom. It needs to be converted into an array JSON readable by the flow. (in Power Automate) -
The "Run script from SharePoint library" will not begin unless both branches are completed.
-
If by any point there is an error it will go to the right and respond with a Failed message.
I added a response back to Power Apps so I can tell if it worked or not without checking the SharePoint and looking for the file
Power Apps side
In Power Apps there is a button that will call the flow and initiate the export to Excel flow. I set it up in a way that I just need to select from the gallery a single item.
There is an option to export everyone's profile to Excel into individual Excel files. Like in the "Update Target Level" problem I had, I could use the flow concurrency. I made it work once, then I couldn't anymore. It was really buggy. So I decided to just remove that option completely from the app.
Conclusion
It took me quite some time to finish this part of the project. I learned a lot from this as I had to push my knowledge and expertise to the absolute limit. I found out the limitations of PowerApps and it's integration towards other MS tools. I saw where the direction of Microsoft is going with Power Tools and this is one of the reasons why I became a fan of this platform and decided why I should pursue a career with this as my focus.
When I was making this some parts were still in the experimental stage but it is exciting to know that the parts they are trying to make are very useful in the future.