You will have probably used the Populate a Microsoft Word Template (Premium) action in Power Automate before but will be saddened to learn that Excel doesn’t offer the same functionality. Via Tables in Excel you can add and update rows in Excel but it can be quite complex to achieve anything more. However, are you aware of Office Scripts in Excel On the Web which is currently in preview (March 2021)?
I’ve build a TypeScript routine in Office Scripts that will intuitively accept dynamic data via Power Automate, including arrays and it dynamically fills out an Excel Template Invoice. Not only will it complete the customer details, name, address, invoice number, date etc, it will dynamically complete the rows that make up the item order. If you haven’t discovered Excel Templates it is worth a look. I downloaded the Blue Invoice for my proof of concept.
Before I get started, I will admit that in the past 24 hours, I have had to learn TypeScript from scratch. I apologies to the seasoned programmers for any obvious mistakes in my code but what I have achieved is a working proof of concept ready for UAT.
I have the blue invoice template stored on my OneDrive and will take a copy of this file each time my Power Automate runs, saving a new copy with dynamic name, allowing multiple invoices to be saved. I have prepared two arrays, one for the customer data and another containing the multiple line order details. I also supply my Action with several other data fields, including invoice number, date, vat rate amongst others, all of which can be supplied dynamically if you require. I have prepared my two arrays as follows:
The Cloud flow is currently manually triggered, it copies the template file from One Drive, Composes the two Arrays, and Runs the Excel Script which is in Preview Mode. The flow looks like follows:
So where does the script live? Your One Drive for Business Files under Documents -> Office Scripts. This allows you to easily backup and share scripts that you have built, in fact if you read on and reshare this article online and tag me in your tweet or linkedin mention, I will send you a copy of the code. Thanks.
Within Excel Online, and only Online, as you cannot edit in the Excel Desktop Client from what I can tell, you can access Automate and All Scripts.
The Power Automate takes literally a few seconds to run and the Excel Template is populated and ready to send on to your client via the next action or file away for when you next need it.
What do you think about this? Is it something your business could make use of in Power Automate? Leave your comments below and please share the idea!
Want to read more about Power Automate Ideas? Click here for more!
P.S. The script file is saved on my site. If you search twitter for DamoBird365 #TypeScript you will find the link to the file. Don’t forget to like and retweet.