Power Automate has come SUCH a long way over the years. I remember when it first rolled out and I was wondering how in the world it would ever come close to replacing SharePoint Designer Workflows, and now I wonder why anyone is not taking advantage of it.
Yes, it has its faults and foibles, but overall it’s a solid workflow tool that just keeps getting better and better.
One of the things that have always been painful to do in Power Automate is things that you would THINK would be simple. Like… math? more conversion options? and what about simple calculations? Yes, we can generally jump through some massive hoops and make it work, but wouldn’t it be nice if we could just write some quick JavaScript or even use an Excel formula that does EXACTLY what we need to do?
That’s not a premium feature that is…
For instance, last week I had a requirement from a customer. They were migrating into SharePoint Online and they had this legacy process that generated a formatted hex value from numeric fields in a SharePoint list. This hex value was needed for legacy business systems that they were going to continue to use going forward. So, we had to replicate this functionality.
Sure, there are a lot of ways this could be tackled, but we wanted to take advantage of Power Automate for a myriad of reasons. Just… how the HECK do you convert a number to hex using Power Automate? And not just one number, but multiple numbers, AND ensure the format is correct. This would be so easy to do if I could just write JavaScript… or take advantage of that DEC2HEX formula in Excel? Wouldn’t that rock?
Enter Office Scripts in Excel. Using Office scripts in Excel in conjunction with the Run Office Script action in Power Automate you can create functions in Excel Online that utilize JavaScript, TypeScript, and Excel Formulas… and it’s a Standard Connector! I know… right?
Using this process I was able to create a script in Excel Online that I was able to pass numbers in from Power Automate. The script would place these numbers in a cell in the spreadsheet and convert them to hex using the Excel formula DEC2HEX. I then formatted the resulting hex results to make sure the format was xxxx-xxxx and returned that value to Power Automate.
It was SHOCKINGLY easy, saved me a LOT of time, and worked on the first try. Interested in how I did it? Below is a video that walks you through the entire process:
Caveats
Yes… there are a few gotchas you’ve got to take into account with this method.
- There is a limitation of 400 runs per user per day on the script.
- Keep in mind, if you are doing things like setting field values and manipulating data in a spreadsheet using the script, the spreadsheet is actually being updated. This means concurrency can be a pain. If you have a flow that is likely to have multiple people trigger it at the same time that executes a script you may have to jump through some hoops to make sure everything works well.
It’s a truly awesome feature, but be judicious with its use and don’t abuse it!!!
For more information check out the following resources and thanks for stopping by!