For the Time-Crunched:
Jump Ahead to the Script and Usage Instructions
What's Happening?
Back in August 2019, Microsoft announced the pending deprecation of several Actions and Formulas utilizing the Office 365 Outlook connector
If any of your Flows or PowerApps use any of the to-be-deprecated Formulas (eg: 'Send email', 'Get calendar', etc.) you will want to update them prior to their impending departure - or risk being left with broken PowerApp.
In a previous article, I covered how to identify any Microsoft Flows using the affected Actions and Triggers using PowerShell - if you haven't already, I highly recommend you give that a read to learn a bit of how PowerShell works and how we make it work for us in this scenario.
Once you've taken a look at that, come back here and I'll give you a run-through of how this new script works - since most of the same concepts covered in the other article were applied here.
Did you read it? Great! Let's continue...
To start, let's go ahead and give you the script that we'll be looking at today:
Once you've taken a look at that, come back here and I'll give you a run-through of how this new script works - since most of the same concepts covered in the other article were applied here.
Did you read it? Great! Let's continue...
To start, let's go ahead and give you the script that we'll be looking at today:
I'll steal a line from the previous article, since I think it's worth repeating:
If you're crunched for time, or simply have no interest in learning a very small bit of PowerShell, then you can jump ahead to the usage section.
If you're anything like me though, you'd want to understand what that thing That Stranger on the Internet gave you generally does before actually using it.
Especially one that looks across your entire Tenant using Global/Environment Administrator permissions!
As mentioned earlier, a lot of the concepts covered in the other article are applicable here - such as Arrays, Variables, Loops. For the sake of brevity, I won't restate those here.
Instead, I want to focus on the Logic that went into creating the script.
We'll start by identifying the initial challenge (Why even do this?), the proposed solution (What are we going to do about it?), and any performance considerations to keep in mind to accomplish this solution (How are we actually going to do it efficiently?).
The Challenge
When it came to Microsoft Flow, coming up with a solution was almost a necessity since (as of this writing) Microsoft does not yet provide us a way of exporting a list of Flows within a given Environment.This meant that, unlike PowerApps, you couldn't just download a report, filter by 'Office 365 Outlook' and start the process of going through each Flow that may* be affected. That ought to explain why I decided to start with Flow!
With PowerApps though, you can actually download a report of all of the PowerApps in an Environment, see what Connectors they are using - and determine that they may* be affected.
The astute among you would have noticed that I keep saying may* be affected. If you checked out the earlier article you'll read why. To restate:
*Why 'may'?
Because you'll notice that the 'Connection References' column only shows you that the Office 365 Outlook connector was used - but unfortunately doesn't tell you which of the many Actions or Triggers were used from it in the app.
When building our Flow script, I discovered that we can dig much further into the Flows to pull out only those that are definitely affected in some fashion - which should save you some valuable time in working through the affected Flows. This was relatively simple because the Flow PowerShell cmdlets actually returned the Actions and Triggers contained within them. Simply match them against the known deprecated list and tada!
But we're here to talk PowerApps! So enough about Flow!
Using the PowerApp cmdlets, we can determine what Connectors a PowerApp uses... but we can already do that through the Admin Center.
I want it to dig deeper and tell me that this app actually uses an affected Formula!
And that finally brings us to the challenge!
How do we look 'inside of' a PowerApp to see if it uses an affected formula? And once we figure out how to do that, how do we scale that to be able to look at possibly thousands without taking days to compute?
The Proposed Solution
If you've ever exported a PowerApp before, you'll know that once exported you are given an .MSAPP (eg: powerapp.msapp) file which you can use to backup/import the application as needed.
What some may not know, however, is that if you rename the file extension to .ZIP (eg: powerapp.zip) you can actually extract and explore the folder to see the content within it (including media, control and screen properties, etc.).
So what is our proposed solution?
If we can somehow download the App Package using PowerShell, extract the contents - specifically the Control and Screen properties, and then search those properties for affected formulas then we'll be in business!
Making it Happen
I spent some time digging around, but could not find a way to download the files through PoweShell. Luckily, I got the break in the case that I needed after reading this helpful post on the PowerApps Blog by Taiki Yoshida.
Namely, this portion of the post [emphasis mine]:
This action has a field called – “readonlyValue” which actually points to a link that stores the PowerApps msapp file. By combining the HTTP connector with this link, will allow you to retrieve the contents of the msapp file – thus, you can store your PowerApps backup to anywhere you want!
That put me on the scent of what I'm looking for - which for the curious was buried in the following property:
$_.Internal.properties.appUris.documentUri.readonlyValue
Obvious, right?!
Using this property, we are able to make what's known as a REST call to download the app package as a .ZIP file.
Once downloaded, we extract it to start to look inside of the folder and files - specifically those located in the 'Controls' sub-folder - to see what, if any, of those files include any of the affected formulas.
Using this property, we are able to make what's known as a REST call to download the app package as a .ZIP file.
Once downloaded, we extract it to start to look inside of the folder and files - specifically those located in the 'Controls' sub-folder - to see what, if any, of those files include any of the affected formulas.
After identifying the files that include said formulas, we open them up further to actually get at the specific Screens/Controls and their respective properties to see exactly where we need to make changes.
Ultimately, we end up with a row that we add to a collection of affected PowerApps and export to Excel.
Making it More Efficient
All of the above is all well and good, but actually making it happen efficiently requires figuring out ways of progressively trimming down the items we are working with so that we're saving as much time as possible.
Especially when dealing with possibly thousands of PowerApps to check!
To do so, we simply take as big a bite out of the proverbial apple as possible each time we 'do' something.
At a very high level, the script accomplishes this as follows:
- First, we get a list of all of the PowerApps in a given Environment,
- Next, we check to see what, if any, of those apps are using the Office 365 Outlook connector,
- Then, we download the app packages of only those that may be affected,
- Next we do a cursory search through the raw text of those applications to find those that include the Formulas being affected,
- And Finally, we look deeply at only the files identified above to give us the final line item to place in our report.
Each time we move onto the next set of steps we are working with less and less 'things', making the task at hand smaller and smaller.
We could further improve on this by turning some of those processes into sub-processes (meaning we can run multiple at the same time), but that is well outside the scope of this post and was not implemented here.
In Closing
I hope this post was useful in helping you both understand what the script is doing, in addition to how you can think of things 'programatically' to accomplish large tasks more efficiently.
While the script provided could almost certainly be refined further, you do ultimately end up crashing into the rule of diminishing returns eventually.
Check out the PowerShell script usage instructions for help getting this script running.
As always, feel free to leave a comment down below if you have any questions or run across any issues.
PowerShell Script Usage Instructions
- Download the script (link).
- Run PowerShell ISE (or PowerShell) as an Administrator to install needed modules.
- Log in using your Global/Environment Administrator credentials to look across all Environments for affected PowerApps.
- By default, the report will be placed in a (automatically created, if necessary) folder at 'C:\PowerPlatformReports\PowerApps'. It will also be automatically launched for convenience
Notes
- The 'affectedPowerAppScreens' and 'affectedPowerAppControls' show you all of the Screens and Controls (if any) that are affected.
- Each of these is semi-colon (;) separated and in the format of:
- [Control/Screen Name] - [Property] - [Formula Used].