How to populate a word template using data from Microsoft Forms. No need for the Premium Word Template action as this uses a Power Automate and SharePoint Document Library Trick.
Does your line of work require you to populate the same template over and over again? Maybe it’s invoices, certificates or receipts? What if you could complete a microsoft form and receive the completed file in return in under 60 seconds? Direct to you or your customer’s email or your document library in Teams?
Whilst I have made this form public, you could use it internally to generate invoices for your customers. The creation of the pdf is done using standard PowerAutomate features, mainly get file, create file, update file properties and convert file. It uses a little known feature of file metadata and word quick parts for document properties. I have also used a Premium Connector from Encodian which will turn your favourite website into a QR and embed on your pdf file.
Creating a dynamic word file in Power Automate generally requires the use of the premium action however there is a free but more involved way of doing this.
If you create a file in a document library that has bespoke columns / metadata, your file will inherit these as file properties.
You can then use this additional metadata to store and populate a word template using the quick parts of word, specifically the document propery parts where you will see the Award, CertificateData and PersonName metadata that I have created for my template file.
During my flow, once I have obtained the response from a form submission, I can then update the file properties to include the metadata for the fields I am looking to populate in my Word Document Template.
Once the file properties of the certificate Word Template have been updated, I have to move across to OneDrive. The reason being, if I want to convert a Word File to PDF in SharePoint, I need a Premium Action. In OneDrive, I can do it with the convert file action. I get a copy of the word file containing the new metadata properties, create a new file in OneDrive, Convert that file to PDF and then save the contents of that conversion into a new PDF.
Below is an example of the Word Template with Web Parts, during my form quiz, I ask the end user for their favourite website, I then use the Encodian create QR Code and add image watermark actions to add your favourite site as a QR code to the file.
If you would like to see this in action, you must supply a valid email for this live demo but please note I will not use it for anything beyond this purpose unless you need to contact me for further information. If you would rather use a disposable email, the routine runs in under 60 seconds so there are no problems there.
Let me know what you think? What could you use this solution for? Is there anything you would like to see me solve?
For more Power Automate Ideas and solutions, please check here. If you are looking to do the same using Excel Templates, maybe you want to have a read of this post I have done using Office Scripts.
Hi, and thank you for sharing this. I would like to get step by step instructions on how to build this from scratch. Like:
What columns did you set up in SharePoint?
What Forms did you create? Etc..
Basically I would like a recipe to follow, if possible.
Hi there. I’m hoping to do a video to compliment this and could cover the full build end to end. Would that be of use or do you think a more descriptive post would be more useful?
This is extremely helpful and came at the perfect timing as I’m building out some automation for my teams. I followed your YouTube instructions and got it to work successfully, thank you for that!
One question I do have is how to delete the Word doc after the PDF is created? Not sure if that’s possible but just thinking about being able to keep that folder clean for end users so all they see is the end result PDF.
Thanks for your time and making a great video!
Hi Phil, brilliant to hear! I am about to do another video on using the Word Templates without Premium Actions as I have tested a 2nd method for enabling metadata on files within SharePoint without having to create columns! When t comes to cleaning up the working files, you can use the “delete file” action, it will just need the site address and the dynamic file identifier from create file action. Let me know how you get on.
I just come back from viewing your tutorial. It is great. I tried to use the “delete file” action. Can I know from which “create file” action that I need to get the site address and dynamic file identifier? Can you show me how to do it?
You presumably want to delete the file on OneDrive that you created when doing the conversion? You can keep the converted file on SharePoint?
This is fantastic, I do have a question – I want specific text formatting on the data I’m pulling from from forms for the certificate, but Word does not want to let me increase the font size or change colour (error coming up: this text does not follow the required pattern)
Is there a way around this?
Hi Nikola, I’ve never seen that error message before but you should be able to edit the text size/colour in the quick part. You could try posting details to the forum and tag me in it. https://powerusers.microsoft.com/t5/Microsoft-Power-Automate/ct-p/MPACommunity
Totally Awesome! I needed to do exactly what you described in the video. It worked like a charm. Thank you for creating this content.
Cheers Robert, it makes my day getting feedback like this as it makes it all the worthwhile. If you need any help with anything else or have a challenge for Power Automate, please drop me a message!
Hi thank you for this tutorial. I’m having an error though on creating a new file after getting the content of the template and composing a timestamp. the test says that I’m having a BadRequest and the error details are as follows:
List data validation failed.
clientRequestId: 352bac80-de79-4187-a641-219b4fa810e8
serviceRequestId: 352bac80-de79-4187-a641-219b4fa810e8
Hi Justine, if your create file is failing, try removing and replacing it. Instead of a dynamic formatdatetime as the file name, just try 123.pdf or 123.docx for now and see if that works.
This is brilliant. I’ve been all over google and finally found a way that works through forms! Your tutorial is super helpful and worked 100% for me!
I have a question on an additional step, I would like to:
– people submit ms form
– generated into word
– approval sent to approver, they manually insert their signature
– once owner clicks approved then the conversion starts
I’m nearly there but when I “update item” it stops my workflow…
Please help!! 😉
Update item is for a list? Can I get a bit more detail? Feel free to email me [email protected].
This worked great, but now I have run into a problem, and I can’t seem to figure out how to fix it. It turns out there may be incidents where someone has to update the created file. Problem is that when people try to open the created word document, it opens the template not the merged doc. I managed to solve this through advanced settings in the actually Document Library, but I had created a document viewing library in Power Apps (see this video: https://www.youtube.com/watch?v=vqK29FWbLxU) so that managers could find the file and edit it. But no matter what I do when I try to launch the viewed document it opens the template not the merged document. I put in this code: Launch(Gallery1.Selected.’Link to item’), which should work, but as I said it opens the template with the quick parts, not the merged document. So then I tried, Launch(“ms-word:ofe|u|” & Gallery1.Selected.’Link to item’) and i just get a blank document titled Doc.aspx. I am really pulling out my hair here. I would be open to any ideas you have.
It must be all about the path. You are creating a new file. Can you add the path manually and open it via PowerApps? Then you need to work out how to construct the path dynamically or pass the path back to your powerapp via the flow?
Thank you for this demonstration! I was able to mimic this process successfully, with some trial and error. What if the Microsoft Form which triggers this process is being used as a survey? Typically, names are “anonymous” in a survey. However, in order to populate First Name Last Name onto the certificate, and how you instructed us to populate it, I had to include that as information that learners/students would need to enter in to the Microsoft Form. I guess I would like to know if the First Name Last Name could be pulled from elsewhere? Or, is there a better way?
Hi Savannah, If the form is anonymous, then I agree that you would need to capture names unless there was something unique that you capture like a student number that you could look up. But validation on a form is none existent and lends itself to user error and if the user was to mistype their number, you wouldn’t be able to look them up. I am glad you’ve otherwise got it working though. If you’ve got any other use cases that have got you stumped, feel free to drop me a note to [email protected].
Hi, Thank you for the great content.
Will this also wok with “Repeating Controls” in word?
It’s a very good question and I don’t know the answer. If you happen to try it out, please let us know how you get on.
Thank you for this great demo! It works perfectly for almost every step, except that it’s just saving a copy of the template rather than merging in the form data. I can’t see any errors in my setup. Do you have any suggestions?
Are you opening the file in Word Desktop? Quick Parts do not generally display in Word online.
Yep, definitely using Desktop. I finally gave up and rebuilt the template, and then it worked as expected. I didn’t think I had made any changes that would affect its usage, but evidently I had.
Thank you – I cannot seem to find how to enter a function in the “Compose” inputs field….
I have the same issue. However, since this was only to create a unique file name, I used some response data instead (responder’s email + id + submission date).
Thank you for this demo. I have a problem, if I create dynamic questions in Forms (the following question depend on, what question was answered before).
I want, that the PDF only show the answered questions, not the individual “hidden” question (depending on what the user answer).
I hope, you know what i mean and there is a solution to “filter” answered questions only for the PDF 😉
If you want to share more detail on my forum, I can take a look. Are you trying to avoid premium actions for word? Does your document have sections? Should they display or hide based on answers? If you provide more detail and some screenshots, I will take a look over the weekend.
Hi,
Thanks for the wonderful video. If we need to send the URL of the pdf document in the email, instead of attaching the file, may I please know how do we do that in the flow?
Is this for an internal user with access to the SharePoint Doc Lib or a user internal/external who needs a sharing link?
This was a fantastic tutorial thank you so much has done exactly what I needed it to!
Hello, thanks for this tutorial, it’s very interesting to use it.
Just a little hiccup here: i don’t find the pdf file after the conversion, and the workflow shows no error.
Ho, i found the solution ! I need again to understand that this a very ste-by-step process and the step create pdf is mandatory after the converting step 🙂
This is quite useful. A further step, is it possible that I can add a QR code to each certificate, so someone scans the code and he receives his certificate which is stored on my site?
I mean I figure it out how to generate a QR code, but have no idea how to insert it.
Definitely using the encodian connector. If you complete the live form on this page, it will ask for your favourite website and you will get a pdf with QR on the pdf.
Thank you, will check on that.
This (the non-Premium version in the video) was great. I do however get a timeout on the OneDrive convert file stage, which is really frustrating now that everything else seems to be working. Any ideas?
I was just using it yesterday for a demo and it was ok. What’s the specific error?
thank you its working! (few fields) but… i have got 72 form field from MS Form….!!! is it OK into column lol but is there any way better than that?
Thank you!
Hello, thank you for this. Is there a way to have the word template populated and then email the word document created? What I would like to do is populate the word template through a MS form and then send an email with the populated word files each time.