Collaborate on Invoice Model Excel for Purchasing with Ease Using airSlate SignNow

Invoice model excel for Purchasing

do you want to know how to populate excel templates using power automate i'm the productivity coach and i've got a friend who can show you how [Music] hi i'm stuart readout the productivity coach and i'm a modern work customer success manager at microsoft and today i'm joined by a friend damian bird who's going to show some great stuff to do with power automate and excel and how that all comes together so damon do you want to introduce yourself and tell us a little bit about what you do and uh how you kind of put into the power platform yeah sure hi stuart um my name is daniel bird i've recently uh entered into the power platform community as demod 365 i've been a systems analyst for the past 20 years and the last three years taking an interest in teams and sharepoint and uh was key to the role at aberdeen city council so in the last 12 months i've started playing with the power platform and in particular power automate and in the last two months i was on one of the big events learning how to how to do all the tricks of the trade and decided to start doing my own uh videos and uh blog posts uh showing you how to do the clever stuff absolutely i think one of the great things i really like about the power platform is that it's got one of the most supportive communities i know there's so many people who are just there they're everybody else is just trying to learn from each other and there's loads of great content out there on youtube and lots of great tutorials etc so um so what are you gonna show us today then so i've put together a demonstration today where i'm going to take a series of data invoice data so imagine you're a shop or a business you're producing invoices for your customers um i've put together a solution that will take that information and pre-populate an excel template and so that excel template is actually from the microsoft website itself they have several template invoices um traditionally you use microsoft word but i thought i'd look at something different see if i could do it via excel so that's my aim today is to show you how i uh populate a microsoft template document cracking that's great so i'd love to see how it works so uh so let's dive in okay so i'm already in my flow here and i'll maybe go through that in a bit more detail but we'll talk about some of the other components first so if i look first of all the template template invoice i've got here this is just an excel file that like i say i downloaded off of the microsoft website um it's in an invoice invoice format it's got the various columns for your your orders uh and the good thing about this is because it's because it's come from microsoft it's pre-built here with some of the sums and some of the logic the functions that are required to to calculate your order lines and your uh total discount your your vat and such like so my my aim really is to pass all my data from another source into this template document and this template is a file that i keep saved away in another folder and never overwrite i'm always taking a copy of it and populating it um so what i'm going to do here is i've i've got a sharepoint list and i've got a couple of customers in here at the moment and i'd like to add ewan uh stewart as a new customer um just for the purpose of this demo so i'm gonna stick you in if you'd like to create a company name or anything like that you fancy keep me you can call it read out enterprises okay any old streets you fancy any uh so let's say number 10 uh pooch avenue this is this is a pet supplies isn't it yeah my particular voice is certainly for uh pet supplies yeah anywhere in the world the lovely milton keynes there we go that's right is that that's right yep and then last their phone number 1908. one two three four five six if this is someone's real telephone number okay so i've added you as a customer and the next thing i'm going to do is stick in my customer order line so imagine you've come into that pet shop and you've uh ordered several uh items actually in fact i'm going to the edit in grid view just to make it easier for me to put in multiple lines so we're gonna say you've ordered three i'm gonna give it uh i'm gonna make up some random order item numbers here and can you think of anything you'd like to buy in the pet shop three or four well let's let's buy bags of uh dog food and this doesn't have to be done through through sharepoint does it so it could be that you might have a power app that that you could populate all of this with exactly exactly so at the moment i'm just showing you how to use an external source um so i'll put in a value for that and a discount will give you five percent discount as well and maybe we'll just stick in a few more items a few more items in here so again your customer three will say you've ordered eight of these we'll stick in a few more item numbers and or should we go for some cat letter as well and just look at our price there expensive bag of cat litter we'll give you 10 and we'll just put one more thing in i think just for this particular invoice so for are you allowed to buy animals in this particular pet shop uh well let's do dog leads let's say dog my dog has just chewed through her own dog lead so uh that's particularly on my mind at the moment expensive dog leads but because you've bought four of them i'm going to give you a whole 25 discount okay so like let you say that this data could come from any source um but it's just purely to show you how i'm going to sort of populate this particular invoice file using my power automate so if i jump back onto my flow um what should i do here shall i give it a test run and then walk through how how it comes together that sounds perfect to me yep okay so we're going to test here and one of the first things that my flow does is it asks for the customer number so if you remember i gave you customer number three so i'm gonna stick the number three in there run the flow and if everything goes okay if i jump into my invoices folder on my onedrive we should see a file appear here in a minute so there we go we've got our file which is a copy of that template template invoice and you'll see now we've got your details here that we provided earlier as your your customer details and here we've got our three lines for our invoice uh the bags of dog food cat letter and dog leads the discount that we were applying and uh like i mentioned earlier because this template includes sums or functions already as part of the the template it's already calculated our running line total and further down we've got our subtotal and our our final total there um so that's that's that's the basics of it i've taken data from an external source passed it to a power automate and then populated um an excel uh template so i suppose how did i do that this is the next his next question absolutely i think i can see so many uses for this so uh so yeah so let's walk through how you got there so if i jump into my uh power automate or my cloud flow um i'm going to go into the edit mode so remember one of the first things i said was this invoice i'm using it as a template so the first thing first action i do here is to actually take a copy of that original template file and give it a unique unique name using the format date time expression um and then uh pulling the customer number that i provided in a compose action so that i can then use it within the get item action and the purpose of that get item action is to retrieve all the customer details for for custom number three in this particular example can i just ask you just to pop up uh just up for one on the one where you've copied the file there was you had that function there can we just take a look at the contents of that function the format date time one so the get item uses the customer number and returns that particular customer um so in this case it was it was custom number three and that'll get the single customer and their details that we provided then i'm using get items to return all of that the orders for that particular or or the order line sorry for that particular customer so this is where i've actually used a filter query um where the the title um which just happens to be my customer id in my uh my list on sharepoint is equal to again that customer number so number three that will return my lines um and then the next set of actions is where i turn the return data the values that come back from these get items and get item i uh turn them into an array using the the select action uh and you can see it's just built up here this is this is based on the requirement of my script that i'll show you within excel when we get to the bottom um because i've defined that the content that this script is looking for or expecting so there's my customer array i do the same again with a sales array and because there's multiple lines or multiple elements within that array all with the same format with my quantity item number description unit price and discount which makes up each of the rows within that excel file i've got two compose actions which are just purely calling those arrays so that i've got them as dynamic content for my final action which is running a script so you'll see that the the script is in a preview uh at the moment uh and one of the things i have discovered is there is a 200 run time limit per day on this new feature but you can only imagine it's going to increase over time hopefully and so what you do here is you you build a script in excel like it says in its type script um i was brand new to typescript when i looked at this possible scenario um but there's a lot of documentation online from microsoft demonstrations from microsoft and so i was able to take that and turn it into my bespoke solution for this particular scenario and so once you've built that script and you've picked this action and chosen your particular script it gives you all the different dynamic fields that you're looking to to populate as part of that script they're all input values on my typescript and this is where i've passed for instance my sales array and my customer array but also some of some fixed fields like the invoice number and check but equally these could be dynamic values um so i think it's probably worth me jumping onto excel and just showing you a bit of the uh type script that i've built absolutely yeah please so back on back onto the invoice here there there is a menu bar a menu item here called automate which is part of excel online you don't see that in excel desktop and that allows you to access the scripts that have been written for either this particular workbook or for excel in general so if i go into all scripts you can see i've got my final invoice script and and these scripts are quite conveniently stored on your onedrive so very easy to copy and paste elsewhere so if i go in to edit that script you'll see now it looks like uh javascript and ultimately that's what it is it's uh typescript and this is where i built up the input variables so some of these fields here you'll recognize from my power automate when i was using the excel script the invoice number and date payment method but also you will hopefully see the array for there we go there's the customer array um that i pass uh in order to run this excel script on this particular template and then really it's just trying to uh populate the different uh fields and uh some clever looping logic looking for the the fields by their sort of um cell names and i go through them all and i populate them more and that's the end of the script it hands back to power automate and and the job's done um there's a good like i say good documentation online there's office scripts documentation and this is where i kind of learned about how to use it there are the sample scripts um and and that is in a nutshell how about how the solution works perfect that looks that looks absolutely awesome now i know that you've kind of documented a lot of this uh on your blog haven't you and you've got your own youtube channel that's right yeah pretty pretty new to both blogging and youtube um just the last six to eight weeks really um but there's interest and i've got lots of uh crazy ideas i love it i love a challenge i think that's the thing i love people asking me the question how could i do this or um you know is there a better way to do this and that's what sort of keeps me interested keeps me going so how do people find all of your stuff whether that's twitter or blog or your youtube or your linkedin or anyone in those so my profile name i'm i'm demu bird365 so if you want to find me on linkedin or twitter or even on the power platform community i'm very active there if you stick demobile 365 into alexa google you'll find me really easily and you'll find my content if you have any great ideas hit me up with them or any questions any challenges i love the challenges so please and we'll pop all we'll pop all your links uh into the uh into the youtube notes as well and on the linkedin post so i mean there's some great posts that are on uh damian's site i really like there was one i think that you posted a few weeks ago now about how you could generate a pdf uh from uh using forms data so that might be useful if you had like certificates or things like this so you know there's a great content on there so i would definitely uh definitely recommend taking a look and this is where i found this uh it was actually from uh from looking on your blog and uh and uh and your youtube content as well brilliant well thank you ever so much for joining uh today hopefully you found this useful um if you did then please uh like it share uh go on to subscribe onto youtube hit the notification bell so you hear about new content that comes in and of course go across to damien's blog and his youtube and kind of follow his content because he's got some great great stuff there um which is really useful if you're kind of getting up to grips with the power platform and they're trying to use it in your situation so hopefully you enjoyed that uh reminder i put all my content out on youtube and on linkedin and i do live content as much as i can as well so thank you very much and have a great day you