Learn how to send SharePoint List data to Word Documents and PDFs using Power Automate

hello today we're going to take a look at something that applies to just about everybody you often want to be able to print information from SharePoint most commonly to a Word document and then if you're sending out something like an invoice you want that to be a PDF you want to make it where it's not going to be editable anymore power automate provides functionality that lets us take information that would be in a SharePoint list and populate a template that we can create as a Word document and then further functionality and power automate lets us convert that over to a read-only PDF if you want to understand how to create your own Word documents and PDFs from SharePoint lists then this is the video for you let's get started [Music] all right here I am in SharePoint online and we're going to be looking at an example which is a tool to build invoices for customers and send those out as PDFs so this is an example to help us understand the print capabilities that we can do through Power automate connecting to SharePoint lists so first of all let's try to understand what it is that we want to accomplish so I have a template that I want to match for an invoice and it looks something like this you can of course make whatever template you want but this is our goal we want to get output where we have an invoice with customer information that's going to have all those critical details that need to go in an invoice our video today is really to understand the concept of just producing formatted Word documents and be able to convert those to PDFs but this is the example that we're going to be working through now in order to be able to do this you're going to be creating a Word document and then modifying it to create this template now note this is a regular Word document it's not saved as a template file or anything like that it's just a template in terms of how we use it so your first step in your process if you want to get your SharePoint list data to Word document is to create a document and then we're going to put references in here for that data the first thing you should check is make sure that you have the developer tab available and installed for your environment so that's really easy if you don't see the developer tab in word just right click on any one of these tabs and select customize the ribbon in this screen on the right side you'll see a drop down you simply need to make sure that the developer option is checked and once that's checked and you hit OK and you come back out you're going to see the default developer tab show up in your Word document so basically you should start out by just inserting tables into word and you can format those and get those to look however you want so we're not going to be talking about how to format tables in Microsoft Word in this video there's plenty of information out there to help you out with that part of it but that's all that you need to focus on first and then just type the names of things you're going to put in there once you've done that we're going to start to put in these references so I'm going to go to various sections of this so I have static content in the top that's never going to change I'm interested in the parts that will change so the way this works is you can just um you know put in a section of text and do something like this so let's just say oops I was going to have um field information here you can highlight that and then in this developer section you're going to see this block for references there are multiple options what you're going to be using over and over again is this option that says plain text content control and you just simply need to click on that button and it will take that you'll see a little handle show up next to it now if you want Additionally you can go into the properties and you can you know provide a label you can give it a title and stuff like that but it's not necessary for the sake of what we're doing and you're going to do that for every one of these fields now what you can do additionally is have a repeating table that's a bit more of an advanced concept but after you've applied the fields you can highlight a row and Define that as well so that is the case for this middle section if I go in here I've got this whole row defined in this table row so the way I was able to do that I just highlighted the row and we do have an option up here under references that says repeating section content control and it basically identifies that whole set of information as a group I would recommend that you first begin by just doing the non-repeating sections to make sure that that's working before you do that part but that is going to be a part of our demonstration okay so that's the first thing you want to do is get your template ready okay so let's drop back out of there let's come back to SharePoint and take a look at what it is we're doing so first of all I've got a customer's list and this is all to support my invoice model so I have some dummy customers here with information that I know is going to repeat any time that I'm going to send an invoice to a customer so I have the name of the customer an email address and then address one address to you Etc and then also the payment terms and the name of my contact so that way I can reuse and reference this information when I generate invoices then I have two more additional lists that I'm managing here's the invoices list you're going to see some fancy buttons on here this list is actually pretty straightforward because all I really need to do is have an invoice number pick my customer and then you're going to see how we do the line items in just a second I also have a notes field that I can take advantage of as well and then the third list that we're using is the lines list these are for the line items on the invoice so the invoice I just referenced I had a couple items on there and it references the invoice number that's how I'm able to take advantage of this these repeating rows that I'm going to have in my print template okay so now you kind of see the main data construct I'm going to go ahead and put together a new invoice so I'm going to go to the invoices list and let's do new and I do need to supply my own invoice number so we'll make this invoice 5002 this will go to Bradley banana farmers and it automatically is populating with today's date um and I'll just say thank you for your business please let me know if you have any questions okay so that's going to just show up in a notes box in the bottom of the template okay as you can tell creating an invoice on this list is very simple okay now I need the line items for the invoice I've created a button control here this is using Json column formatting I do have a website you can reference with a free sign up where you can get all the buttons that you see in this demonstration so you can go to SharePoint dashboards.com and if you go to the gallery and go to buttons uh this is just using um the edit button option which is free and then the other thing I'm using is the button to format hyperlink so if you're interested in those button controls you can get those for free at my site all right let's bounce back to SharePoint I just wanted to explain how that worked so I need my line items for my invoice let's go ahead and do a couple of line items just to demonstrate the concept so I click on lines it's automatically going to this filtered option and I'm going to do two lines now this is key I do need to reference that invoice number so it knows which ones to put on the bill okay so now I'm just going to put Tommy in some information um let's do uh it Support Services um server updates uh and custom development two hours and we'll say this was 120 okay so that's what goes in the line item so there's one there it is there's a line item and then I'll do one more line item and that should be enough so I'll do one more again I need to reference that invoice number so when my workflow runs it's going to go ahead and connect with that and then I'll do um let's say um professional [Music] Consulting Services [Music] um all right so you can obviously put whatever you want we'll say four of these at 130 I'm just making up numbers obviously this could be anything you want it's just to illustrate a concept of repeating row in a document okay all right so you can see these are group um so that way it's going to know these are connected to invoice 5002 and then I can just click on the button to go back to invoices and the stage I've done everything I need now one thing that takes place is I have a workflow I created where every time a line item is updated it's going to run a workflow to re-update the total in this list so if we wait a second um we're going to see that that's still running we'll jump over the workflow just to show you a little bit about that part of it okay so I have three different workflows you're going to see in this system I created and this is really the key part as far as what you want to learn in terms of how is it that you can create uh Word Documents uh from SharePoint list that's what we're going to be looking at in depth and this lines total is simple it's just um listening for whenever there's an edit to any of those lines records and if there is it gets the total of all the ones matched in that invoice and then it's going to go back and you can see the title just updated automatically that just makes it so I don't have to figure that out and type that information in the main invoice record so that's automatic that's not really the focus of the demonstration but I just wanted to show you how those ones are connected now I do want to mention being able to print Word documents or create PDFs from the SharePoint list it does require what's referred to as the per user plan with power automate that is a 15 per month cost for that obviously I do recommend this it's not fifteen dollars per month just to do documents that opens up a huge array of options in power automate but this is a special type of action it's called a premium connector and you will need that in order to tap into these capabilities okay all right so the key one first of all is being able to create the word document so we're going to look at that first the conversion of PDF is actually really simple once you have that set so I'm going to go to my make word invoice workflow in power automate now I'm using for a selected item that's important because I need to make it where when I click on this button to make a document it's automatically going to do the reflected item and that's another one of these buttons that you can get to for free in the SharePoint dashboard so one of the buttons in here is for flow so you can just go to button flow and all you do is drop in your flow ID if you're interested in having that show up as well all right let's take a look at what's going on so when you click the button it needs to get that record from SharePoint so I have a reference for the selected item I have a reference to the ID of the record that was selected and from there I can proceed forward with making this word document now we're going to go through a series of steps I do need to use this apply to each although it's simple because there's only one record nevertheless it does need to be in that construct in here I've got some formatting going on this is just helping convert that data that's sitting in SharePoint into the format that I'm expecting to see on the invoice so sometimes you do need to do some formatting in order to get that output to look just the way that you want so there is an option for number formatting and I'm just taking it and making sure it's going to have the comma separations uh you know the currency sign all of that now what I have to do additionally this is basically getting the data ready to insert into this word template that I've created so in SharePoint I made a special library and this is from my invoice template file this is the data that's this is the template that's going to receive that data this is never going to get updated it's just a reference point from which to create the word document so that earlier step I showed you after you've created the word document You're simply going to drop it into a directory here and obviously you want to permission that so that other users aren't going to change that or anything like that now I will provide a link if you also additionally want to read through some steps I'm going to provide a link to an article I read which was really beneficial to help me understand um you know how to do these steps in fact let me just open up the page I need to give credit where it's due there's a gentleman named Matthew devaney who published this article so that can also be a useful reference point to you in addition to the video all right let's look at the next thing that's going on I need all of the line items that match that invoice number now I need to reference the internal field in SharePoint and I'd simply use this filter query it says where the invoice number is equal to the invoice number from the item I retrieved from this get items okay so that's going to get our case there's two line items that match that we need that data because that's what's going to populate our word template now next we have a data operation and this is reformatting data from that getlines query so that it can be consumed by our next step so this is a data operations select function and it's simply taking the specific reference fields that you're going to map over and getting them packaged if you will in this array that I'm going to reference the template so we have all these steps to prepare and make sure everything's just perfect and complete and ready to go for when we get to this next critical step again I do have some formatting going on that's to ensure that this data is formatted in a currency style format so there's a format number function and power automate and that's going to let us convert that over so that we're going to see what we'd expect you know a cash sign or whatever type of currency that you're working with you are not going to need to know all these things initially there's going to be a back and forth where you're going to try out your workflow and run it and you're going to see some funny things in terms of the output it's not going to look exactly the way you want or you may see something funny like a decimal point with 10 zeros after it and you'll shake your head and say that's not what I wanted and then you can come back into your flow and do things like you see here all right so here's the critical step as I mentioned this particular flow block is what's referred to as a premium connector this is called populate a Microsoft Word template there's another closely related one we're going to look at in The Next Step which is to convert over to PDF so that takes place after you've produced the word document and that can be handled in a separate flow at this stage it's going to last all of those fields that you referenced in your word document so everything you see in here that's a full list of everything I set up as references um earlier you're going to see that all show up in SharePoint so that prep work that you did it's going to start to become more clear why you did that and how it connects up with your power automate workflow so it's simply going to ask you the site library and the specific template file to reference and then it's going to pull that and then insert the SharePoint information in there now there's a special section you're going to see for the table if you did a repeating rows I called my table table rows and you do want to make sure that's toggled over by default it's going to have a listing for each the rows that's not the mode that we want to use we want it to be in this single row so that we can simply pass that array of output which came from the select lines block other than that it's real intuitive hopefully you've named your SharePoint fields to match exactly and it's going to be quite obvious you can see I have things like address one value address one state value State now these are value because all of those are lookup field references in my case I was using a lookup to pull a bunch of customer information and so in order to get the information I need to use the value reference otherwise I would be getting the index of the selections instead so that's why you see a lot of those in there and then again you see more formatting conversions and doing things like making adjustments for the date time I wouldn't for example want to see midnight showing next to the date in my invoice so you have to do a little bit of cleanup work to format your data to get to look the way that you would expect in your word template it's a back and forth exercise you try it out you'll find something's wrong you come back into SharePoint and come back into Power automate more specifically and you apply formatting functions to get what you want all right and then once you've done that you're not completely done you have file content but now we actually need to instruct power automate to create the file and drop it back to SharePoint at this stage it's very simple you simply provide the site reference and then the library to which you're going to drop that file and then you and you want to give it a dynamic name so in my case I just typed out invoice and then reference the invoice number and then dot docx you do need to provide the file extension and then it just asks you for the file content and you can just pick from the Picker over here it's going to suggest to you it's going to kind of already know what you had in mind you just select body which is the output of the preceding step up here for Microsoft Word template and that's it this will produce that word file let's go ahead and execute it just to watch it watch the gears in motion so to speak so I'm going to go to invoices and then I've made this button I'm just going to click make Doc that will open a side panel and it has the name of my power automate flow and just click run flow and then it'll give me a nice prompt and say it started and then I should be able to pop over to my invoice documents and there it is okay so let's take a peek there it is okay so this is the stuff we did earlier in the video there's my two line items you can see it's got the grand total it dropped in the notes all that Dynamic information automatically populated into my invoice so that's cool now when you send invoices you want to send them as PDFs right normally you would so we have one additional step to do that so let me close that and this is real simple at this point we just want to reference this and convert it to PDF so the flow for this part is quite easy to understand like this word template this is a premium connector so as long as you have that per user plan you're good to go you can tell they have little diamonds next to them in the uh interface and power automate so the next one's make PDF invoice and this one's a piece of cake so again I'm going to do it for a selected item and what I want to do is get the item that was selected and then I drop into this Loop and um I need to reference the document so basically it's asking me oh you want me to change the word document into a PDF no problem where is it and so you say it's in the invoices site and the invoice documents library and then you need to tell it precisely the name of the file to be converted I'm able to do this dynamically I can insert this reference from that which tells that the invoice number so since I already know what it was to be named I'm ready to go so that converts it to a PDF just like in the other workflow I still need to create the file from the content that was created so just like the last one I can go ahead and point it to the site folder I needed to give it a name and just like the preceding step you do need to include the file extension which obviously is dot PDF and you give it the body and that's it so this workflow is very simple so I'm going to go back to SharePoint let's go back to the invoices list and I'm simply going to click this button which references that power automated workflow so I just click make PDF just like before it's going to open a side panel and I just hit run flow that's it and the gears go in motion and then it'll chew on that for a second I can do a page refresh if I'm feeling a little impatient but it is in the process of making a PDF there we go okay so now we've got that nice read-only PDF it's exactly what's in the word document it's just it's a PDF now instead and then obviously I can download that now further automation I could do um would be you know I might like to automatically generate an email to send that PDF to my customer recall that in the customers list I have the email address reference so that's super handy um so I have lots of opportunities to continue to improve this system that I've created here and then if I need to maybe I need to recreate the document or PDF I could go back I can click on lines and that dynamically just goes to a filtered view of the wines list I can say oh shoot I forgot to Bill them for this additional thing I could add another line and just repeat the process by clicking on the buttons in the invoices list so um lots of steps and things to consider there I think the first thing I would recommend to you is to get your foothold in doing this and you will do that you should do that by skipping the repeating section that's a little more of an advanced concept it's not terribly hard to pull that off but kind of want to get to your first Milestone which is just produce a basic Word document so what I would recommend to you even if you have something live that you want to use in your business environment do a practice exercise first make yourself a dummy list like I did and just insert a handful of fields maybe five or six fields in a document maybe there's a standard cover letter that you like to send out and just make a single SharePoint list and just do that to become comfortable and get to know how that works again all you really have to do is Select sections in your document and you're going to click on this references section for plain text control and you just need to set that up for each of the parts of your word document where you're going to insert this data so this document could be anything whatsoever we just use this example to illustrate to keep Concepts one just inserting regular field information the other doing something more advanced which would be the repeating table I will not save okay so a lot to chew on there a lot of different possibilities a lot of people do have a tendency to ask about how is it that I'm going to print information from SharePoint there are not any native print capabilities you can work around by doing things like screen capture or you know other different kind of methods but in any case there's no native functionality to print this directly from SharePoint I could also export that to Excel and I could manipulate the data and do things in Excel and that's another path that you can take but this is going to be the most polished precise professional method you could use because really you can create anything that you want in Microsoft Word in terms of the formatting so if you really need to do something like create a purchase order or something very structured specific and you want to completely control the layout then this is going to be be your best method now it as I mentioned it does need to have your user count on the per user plan that does not mean that that's the case for all the users using the system the flow will execute according to the person who created so you can create a system using this print functionality set up the flow yourself and be the author of that and then other users in your environment can tap into that even though they don't have the per user plan they can consume the service that you are providing so that's a quick uh look at some different print capabilities if that's something that you've been curious about if you have not yet before done any of that that's just something you can explore so I'm going to provide a couple of different links under the video and this will just give you some additional references so you can do that practice I'm guessing a lot of you watching probably have never produced a Word document through Power automate like this and that's fine it's not a real common activity but it is something that's very useful and if you can get through that and do your first practice then you're going to become more comfortable and this becomes a tool in your toolbox that you can pull out and use in all kinds of different scenarios so the next time someone comes along to you and in SharePoint they want to get a custom printout from something they're doing from that SharePoint list you're going to be ready to go you know exactly what to do you know how to pull out that word document you know how to put in those field references and you know how to set up your flow to put that all together format the data and output not only word document but the PDF F output as well and once you've got that all connected together you're you know going to be very comfortable you can rinse and repeat and use this process in all kinds of different scenarios for those custom printouts so I hope you guys found that useful and interesting there's a lot of different directions you can go with this if you do have any questions or comments and you're wanting to get going with this let me know and if you don't have it already of course before you attempt it make sure that you get your user account set up with that per user plan that Microsoft provides so that you're able to tap into those premium connectors that I mentioned in the video goodbye [Music]

Channel: SharePoint Smart

Views: 11,084

Keywords: SharePoint, Power Automate, Power Apps, Microsoft 365

Id: fBqi0VujmO0

Length: 32min 43sec (1963 seconds)

Published: Mon Jan 30 2023