Power Automate - Populate Word Template (Convert to PDF)

[Music] hey guys i'm ben fetters with sovereign sp and in today's video we're going to be going over the populate a microsoft word template action inside of power automate it's an action i use all the time i've been meaning to put it on youtube here for a while but basically how it works is we're going to take a word template file like this put it into a sharepoint library connect it to our power automate flow and it's going to get some data from sharepoint populate it into this word template and then it'll convert it into a pdf and our final product will look something like this basically if you've heard of mail merge before this is the power automate version of mail merge and this is something that we use in our company all the time with different clients we use it for invoices progress reports uh weekly reports they cannot that get automatically created every week as well as approval processes so at the end of an approval process we'll have a final pdf version of that process and it'll include signatures approval dates and everything right it's really awesome works great there's a lot of use cases for it so if you have any use case ideas as we're going throughout this video please let me know in the comments i'd love to know what your ideas are and how you utilize this action this is of course a really simple and uh pretty interesting scenario here but it will showcase all the different things that you can do first thing we're going to do is i'm going to come back here into our word document here and we'll start a new file click on new you could use one of these but we'll just use a blank document there we go now the first thing i like to do i'm going to give you some helpful tips i've made a lot of these so i wanted to share some things that i do personally to make this whole process a little easier and the first thing is i believe i always forget what tab it's in but i believe it's the view tab maybe it's the design layout tab lay it down there it is when i go to margins and i click on narrow so that just gives us some more space here and the next thing i'm going gonna do is actually insert a table and usually i just kind of come down here and do four or five or something like that um i'll do five and we'll create a table here once i select it go to layout and i promise i'm going to explain why i do this it's actually table design so let me go to borders and i'll click on no borders now the reason i do this is it's going to make things line up a lot easier it just kind of structures everything because you can see that there's border lines in here right now but once this is converted into a pdf all these lines are actually going to go away as you can see in our example over here this actually is a table you can see everything's lined up nice and neat and maybe this isn't a perfect example but if you've got a big old form and you want to make sure things are spaced appropriately and everything and lined up good table is a really easy way to do that so that's what i usually do and so that's the first thing we've done here the next thing that we'll do i'll just kind of copy what i did over there i'm not going to try to make this look good because i know you can spend a lot of time and this is really just simple word stuff but i will make this a little bit bigger invoice and let's check over here so we've got invoice number date client and then we're going to have a table down here description date and price all right so let me go back to our word file over here we got invoice number oh client and date i don't know if that's the right order but there we go then we have description oh date of the service or product for our invoice items and price right here and at the bottom we're going to have total price that's working out all right and again i'm not going to take too much time and making this look nice i want to just give you the idea of how this all works and then when you really can make these look nice though i've spent some time and they can really turn out pretty great um so let's actually now go over here i'll shrink this in a little bit right there and the first thing we're going to do so this right now is all just static right none of this text is going to change but we do want to put in some fields here and these fields are where we're going to pull in data dynamically and so what i mean by that is based on the data that we're getting this invoice number that i'm going to put right here where this cursor is that's going to be different the client is going to be different right all these things are going to be different based on the data i'm pulling so that's why i need some fields to come in so the to actually do that to add fields you might have noticed them at the beginning with my other word template example but we're actually going to come up to this developer tab you normally don't see this tab if you do want to have this enabled you can right click on one of the tabs so i can go to view and right click and then come to customize the ribbon once i do that i can just make sure right over here on the right i can look at this developer box and just check it so if mine isn't checked i'll just check on that and then hit ok and then you should see the developer tab show up right here there's a lot of options on here but the only ones we're really going to care about is this control section and although you can actually use all of these controls in word the power automate or the populated microsoft word template action isn't going to allow you to use all of these which is kind of a bummer but that's right let me go through the three that do work so the first one is this one right here it's if you hover over it'll say plain text content control this one right here next to it is the rich text this doesn't work so that's in the html text right that one is the one i would love to have working you can really do a lot with that but that one doesn't work um populate a word template the action and flow which we'll go over in just a little bit uh just any of these fields you add they just won't show up for you to be able to put data into and that'll make more sense a little later on but the plain context text content control is the first one so i'm going to go ahead and click on that and you can see this little content control pops up right here and if i just make sure i've selected it i'll come over here to properties and the only thing i knew the only thing i need to do right here is name it and here's another quick suggestion so this is a really simple form but lots of the times we'll have a lot of different names for our fields and sometimes we name things that aren't exactly the same as what we might see on the form so for example the field i want to put in here might be called number but you can see it's saying invoice number right here so what i normally do is instead of putting invoice number i'm going to just type in the name the exact name of the field i'm going to use so i'm pretty sure if i can remember right our field and sharepoint is called invoice number um i'll leave it like that i won't worry about too much with this example but if i've had these forms where we've got you know 40 different controls on here and there's 40 different fields and things can get real complicated and the more simple you make for yourself uh you're going to save some time and save yourself some headaches it does get a little confusing trying to manage which field is which right so that's all we're going to do we don't have to do anything else i'll just hit ok and you can see the field name is now showing up right there and we're going to see this field name in the flow once we get to that step so i'm going to add two more of these right there i'll call this one properties and just call this one client and i'll add this date one call this date and now this actually one more right here is the total price i'll go up to this one and i can actually just click on properties and i'll say total price hit okay and let's see so now we have two other controls that are gonna work and the second one we'll go over is actually just an image you can notice example here we have a little image of a tree images are kind of interesting normally you're not gonna bring in a dynamic image right so normally maybe right here you put the image of your company and you could just do that in word if you needed to if that was just going to be the same image no matter what i could just come over here and insert a picture or something like that but if you do need to put in a different image maybe you're going to put in the logo of the company that relates to the invoice or something like that and that's going to be different each invoice then you could put in the invoice control or the image control so that's just this one right here the little picture on it so i'll click on that i'll shrink this up there we go and i am going to change the name from picture to i'll just call this logo change it like that and now the last control we're going to do is probably the most complicated one but once you get your head wrapped around it it's really pretty simple but basically what we're going to do is i'm first going to highlight all these cells here and we're going to merge them so now i just have one blank row and the next thing we're going to do is actually insert a table within side of this row so i'm going to come over here and i'll explain why i'm doing this in just a moment so i'm going to have one two three columns and now i'm going to highlight this table here and i'm going to come over to the developer tab and you have to make sure the table is highlighted to use this last type of control and what it's called is a repeating section content control so if you're watching this you might have heard of these you might not have basically what this is going to allow us to do is let me just click on it real quick so now i've put a control around this entire table right here if i highlight if i click on these little dots right here it's going to select the whole table that means you you're pretty sure you've done it right and i'll go to properties and i'm just going to say table and so now what this is going to allow us to do is i'm going to be allowed to put in as many items as i need here so when i'm doing creating an invoice sometimes there's one item right here sometimes there's five sometimes there's ten and so what this allows me to do is just put in however many items i need to and it's going to adapt to that and so that's really nice because then i i could have technically done you know one of these fields right here these text fields one for description one for date one for price and done you know three rows of that same thing but then i could only have three invoice items if that makes sense with this repeating section table i'm able to have as many as i want and the great thing is it's going to be it's since it's put inside of word or it's put inside of this table if this table grows if i have you know 10 items this whole thing is just going to come down here and the total price right here will be lined up just great and it'll it'll work out pretty well um so that's what this one is it's a little confusing maybe if it's new to you oh geez it's really confusing there we go uh but i think you'll get the hang of it you'll get the idea once we get into the power automate portion let me come over here real quick so you can see right here i just have two this is one repeating section control but there's two items that got passed into it if i did another invoice and just put in three then there'd be three rows of items right the last thing i need to do actually is just space once once i click inside of this table i'm going to add in other fields so i know i have a description date and a price so i'll put the description right here these will just be text fields i'll put in description and the date and one more text field and i'll do the price so that's how that works i've got three different fields inside of here price date and description it's basically a mini list of data is going to show up in here let me just pull this over here it's a little better the last thing we'll need to do is highlight the entire thing and we're just going to change the font color to black and there we go so that'll look really nice there our nice pretty invoice form and i'm actually not going to upload this into our sharepoint library since we already have one but let me show you how that works real quick okay so here is our sharepoint library and this is on our showcase hr little demo site here and i've got a library called example documents and what i'm doing here is i've just put in a invoice template one is the word file of our template and i have a little image right here this is our tree image that we see in the little example pdf and the what i like to do is put i if you're going to use a lot of these which lots of the times you'll have just a bunch of these and they'll start piling up you can store them in one drive but i like to put them in a sharepoint library and only share access with people that are going to want to be able to edit these if you're building this for a client it's great to be able to share these templates with clients and something that i love about this is because it's very little code especially just with these templates is if i wanted someone you know if i've if i've built this solution for someone and i've handed it off to them then the great thing is i can sleep well at night knowing that they can just come in here and change something if they need to they can't change it completely unless maybe they watch this video or something like that but what they can do is they can open up the template and if they wanted to you know change this to a different color make the font different space things out differently [Music] add another image into the document make it look prettier right they can totally do that without needing to call you uh and spend some of your time on doing that so that's why i like this solution a lot is it allows the customer to really own the solution at least a good bit of it um if they wanted to add fields they might have to ask you or watch this video or something like that right but it's for to just do these basic word things most people can do that so that's a great thing coming back into our library though let's now build out the flow section and now we'll come to our flow portion of this video i'm going to click on the create and i'm not going to focus too much on how we're going to get the data or what type of flow we're using because it really doesn't matter the populated word action or word template action can really work with a bunch of different styles or types of flows can come from when an item is created or modified or maybe it's happening it's getting a bunch of data summing the data up and then storing those totals and different fields in the template right so this doesn't have to relate to just one item but we're going to go ahead and just click on when an item is created or modified and i'll click on create and next we're going to choose our site and we'll choose our invoice list i'll choose our little invoice list right here and the next thing i'm going to do is i'm going to do the get items action now this is just depending on how i've set this up so i actually have a separate list for our invoice items that table or with the repeating section control so i'm going to get this really quick go to our invoice items table and i'm going to do a filter query right here and all i'm going to say is title equals and then i'll come over here to get the invoice number and again this is just how i've set up the data i'll try to remember this real quick but now i'm going to get all the invoice items that pertain to the invoice that we've created and once i've done that i'm now going to do the populate a word template action so i'll click on a microsoft word template it is a premium action but i really it's so worth it it's definitely something that a lot of people a lot of clients have enjoyed having this and the next thing we'll do is select the location there we go there's our sharepoint site our document library is the example documents and our file is going to be the invoice template so you'll notice it's not pulling up that other image that was in there it's only going to get word documents in here so i'll select that and as soon as i select this it's going to start bringing in all of our fields uh and our field names right here so here's our invoice number field the date here's our table our repeating section table that looks a little bit different and for some reason the picture field has come over like this not entirely sure why it did that that's why i always like to rename the images and this is an old one where i don't think i renamed that field but that's why i came over like that but this is our picture field so we'll put that picture right here but the next thing we're going to do is start to fill out some of these things so the invoice number all i'm going to do is put in the dynamic content for invoice number the date will do the same thing so right there and the client is right there and this is again why i like to name my word template fields the same as exactly what i'm going to put in there because then i just read that type it in click on it it's a lot faster than trying to remember what field goes to which which doesn't apply to every situation but it does happen so the total right here i'll do total which is actually invoice amount so you can see right there it gets a little confusing even though that one wasn't too bad and lastly we have our picture and then we have our repeating section so these simple text fields are really easy right we could maybe format the number right here but the picture is not too bad we will cover that at the very end but this one is the next one we'll go over is this repeating section control you can see that right now what i could do is put in no description right here and a day 10 a price and so on and then i can add another item and do the same thing right but what i that this is really limiting because i can you know i have to put in one two items right if uh if i want to allow the person to be able to put in as many items as they need i'm gonna need to do something a little differently and so all i can do or what i can do is come over to this switch to input entire array and click on that and the great thing about this is it's actually going to save me a little bit of time because if i put a little bit of data into here it's going to create this little syntax for me right here i'm going to get rid of our second item and you'll see why this is helpful in just a moment because i'm actually going to copy this and take it out and we're going to come up here and create an array variable to store our invoice items into initialize variable and i'll just call it table we'll make sure it's an array then i'll add one more action we're going to use the append to array action all right i'm in the table and the next thing i'm going to do is paste in that same syntax so it's really pretty easy it's description and then we want to be in the description date we want to be in the date right and it's not it's not too difficult i'm going to get rid of these straight brackets here and the next thing we're going to want to do is put in what we want to go into these items so the append or array variable is going to add an item into the array into our array variable and we want to use this action for every item that the get items action is going to get so what i can do is type over here and do description make sure it's coming from get items and it's going to put that into apply to each you can see that i'm looping through each one of the items from the get items action so each one of our invoice items i'm going to do the same thing i'm going to come down here and i'll put in the date which will be the get items date comma and well let me get rid of that i guess there we go let me get rid of that and i'll put in the price here there we go and so now our little table here is nice and ready so the great thing about this is it's it's pretty simple it's pretty easy to do you just loop use this a pen to array variable action make sure you got your syntax right with what fields there are in there um put in the dynamic content so now this variable should be ready to just put straight into the populated microsoft word template action so let me close these and again if you have any questions about that i know that is a little confusing just put them in the comments section down below i try to answer every one of the comments let me come over here to the table i'll put in the table variable and that should be good to go last thing we're going to do is just put in the image here so i can show you how to do that real quick and then we'll test this thing out i'm going to add an action and we're just going to do the get file content so there's lots of different ways of getting a file right i could get file properties and select a certain file that i'm wanting but the end thing you're going to need to do if you're getting a file from sharepoint is use the get file content action so i'm going to click on that right here i'm going to select the site and i'll click on this little icon i'll go to our example documents and i know it's this trees rs example click on that and that's getting the file content for that file right there um it's not the perfect example because i'm getting the same one every time no matter what so it's probably what it's pretty redundant right but anyways that's going to show you a little bit of how that works if you do need to get a file from sharepoint or onedrive you're going to do a similar action just like that and then for this field right here i'm just going to put the file content of the get file content action and so now if we ran this the flow would then grab all of this data when we create a new invoice item it would go get the invoice items related to our invoice get this picture right here and put in all the data nice and neatly into our word template but this word template doesn't have anywhere to be saved or sent to right so we still need to do something there so the next step i'm going to do is actually first i'm going to create a file in onedrive and then i'm going to take that file created in onedrive and use the convert a file the pdf onedrive action to create it into a pdf so create file there's a few different ways of creating pdfs this is the same one i do every time i don't have a great folder for this i'll this to the root um but if you're doing this for another client it is nice to put these in a specific onedrive folder and i'll give you a last little tip at the end here on how to kind of manage this a little bit better so the file name this is a good tip for this is i normally do invoice and then i do put some dynamic content in here start typing dynamic content let's type in invoice number and then you can even do a dash and put in like the id of the invoice item from sharepoint uh just so you can find the specific item this was running on and the reason i do this i put in the dynamic content is so that it better ensures madden the dot do cx real quick i do this i name it this way because if i have multiple of these running at the same time and our file name was just in well invoice.docx if i ran multiple multiple of these at the same time they would kind of step over each other right because i've named the file the same thing and it's going to the same folder and everything so that's why i like to put in the dynamic content there makes it so it's not going to step on each other if you are running a ton of these um i'll give you a tip on how to uh manage that a little better so you don't have just a million files in your onedrive for the file content though in the create file we're gonna put in microsoft word document so that that'll be our word template all nice and filled out the next thing we're gonna do is type in convert it should show up there convert file let's use cover file right here uh the unique identifier for the file will come from this one id or the unique identifier for the file put that right there and it's that easy i could you know choose something else i guess but i'll leave that as pdf and for our last step is we can now once this files now that this file is converted we can send in an email we can create a new file on sharepoint um do lots of different things with it i'm going to just do the send email action here and i'll put my email in here there we go subject will be new pdf here's a new invoice for you here we go nice and professional i'll hit the advanced options change the importance to normal and for attachments i'll just say invoice maybe put in the invoice number here and this time it'll be dot pdf since it'll be a pdf and the attachment content will be from the convert file the file content and now lastly what we can do as i said i was going to explain at the end to help manage our files in onedrive is we can use the delete file want for onedrive for business put that right here and so once we've actually sent this file out you could probably technically put it back here but what i always like to put this at the end is so i make sure that the email gets sent out and then i go and delete the file is i'm now going to click on this and put in the id from create file and so all it's going to do it's going to create a new file on one drive convert it to a pdf send it out and then delete that same file so that might be what you want to do it might maybe you do want to store it somewhere if you're just sending out an email maybe you would want to store it somewhere but we're just going to leave it just like that and i'll go ahead and click on save and let's go test this thing out and in my little powerapps application i built i'm going to click on new invoice and for our invoice number we'll say 777. date is today the client is trees are us uh let's add some invoice items so we'll say new tree and we'll say this is a nice tree so it's 500 it's got gold leaves or something like that and we're going to say tree installation forty dollars lastly watering the tree ten dollars all right so our total is 550 and our invoice number 777 so let's go ahead and click on save here and we'll get taken to our nice little success screen i do have a video on how to create an animated success screen similar to that one the description or the link to that video will be down in the description below and if we check our email we can see that our invoice is looking pretty good it looks very similar to the other one we've got a nice tree here ambush number 777 today's date client is trees rs new tree tree installation watering the tree the total is 550 of course this doesn't look perfect but i hope you've been able to think of some different use cases for this this is great for summary reports you can actually change your word template so it's a landscape format so it's now horizontal instead of like this or it's vertical i've seen these used just everywhere they work really great that's really awesome again it's great to leave in the clients hands because they can edit the templates a little bit maybe you don't even want to convert it to a pdf you just want to get someone started on a word document that they're going to keep editing and using later that also works pretty great as well but again let me know if you have any comments or questions we do powerapps services powerapps training i will help you build the app for you i do have a powerapps training course actually on our website at sovereignsp.com about a three hour long training course we also do training on power automate got some great blogs on there so go check those out and thanks for watching and have a wonderful day

Channel: Ben Fetters

Views: 43,352

Keywords: Power Automate, Microsoft Flow, Populate Word Template, Microsoft Word, Mail Merge, Power Automate Tutorial, Convert to PDF, Power Automate Convert to PDF, Power Automate Mail Merge, Power Automate Report Generation

Id: fpspoJNu5SI

Length: 32min 8sec (1928 seconds)

Published: Mon Jun 27 2022