How many hours are spent within businesses created documents from submitted data. From Employee onboarding, Health and Safety Recording to Staff and Student records, the data collection is endless.
In this article, I'll show you how to collect your data and produce consistent business documentation using Power Automate and Microsoft Word saving time and money on repetitive tasks.
Normally most businesses will have distributed Word templates such as Letters, Invoice and Contract Documents however, it is likely that these documents are created through manual tasks such as extracting information from Emails, typing details from had written forms or copy and pasting information from some other business system. This can inevitably introduce errors as well as inconstancies with the final produced document.
Here is a typical Business Template. We expect a user to open this template and produce the document from the information supplied to them.
Experience tells us that we won't expect consistent results. Maybe the Copy and Paste takes in the source formatting, maybe the users types in the wrong information in the wrong fields. The possibilities for document errors exist in every action a user takes.
Preparing the Template in Word for Automation
Lets open our template in Microsoft Word Desktop and open the Developer Tab.
The Developer tab isn't displayed by default, but you can add it to the ribbon. On the File tab, go to Options > Customize Ribbon. Under Customize the Ribbon and under Main Tabs, select the Developer check box.
We are going to be using the Plain Text Content Control highlighted above. At this moment, you are only able to insert plain text into documents using Power Automate so you will need to take this into consideration when designing your template.
Place your cursor within the document where you want to add the content then click the Plain Text Control Control from the Developer Menu. The Control will be added to your document
When the Control Control is highlighted, give it an appropriate name. The Naming Conventions you use within the template will be shown within Power Automate.
Continue to you have all the Content Controls added to the document.
Making the Template available for Automation
For Power Automate to access this Template, it needs to be stored wither within a SharePoint Site Library or a OneDrive Folder. I personally prefer to have a SharePoint Site that is dedicated to storing content such as templates and, which is restricted to a number of users. This could also be a Corporate Brand / Communications Site that is dedicated to Content Authors. Within the site I have a separate library that I store all templates in. Your templates should be named so that you can identify these easily within Power Automate
Collecting the Data
Power Automate has the ability to collect the data from a number of sources. In this example we are going to use a simple Microsoft Form which matches the fields that we have defined within the Microsoft Word Template.
Building your Power Automation
We now need to create our new Power Automation Cloud Flow. Navigate to Power Automate from the Waffle Menu in Office 365. Click Create > Automated from blank. Provide a Flow name, i.e., “Employee Status Notice”, select the Microsoft Form “When a new Response is submitted” trigger and click “Create”.
Once the Automation opens, select the correct Form that you are gathering the responses from in the dropdown menu.
I'm going to create two Initialize Variable actions. These will be used for the Naming Conventions within the SharePoint Library
Name : “File Name”
Type : “String”
Name : “File Title”
Type : “String”
Next we are going to add a Form Response Action. Select the correct Form that you are gathering the responses from in the dropdown menu and ensure it matches the previous form you selected when creating the automation. Set the Response ID to the Response ID from the "When a new response is submitted".
Next we are going to add a Scope Control, Get User Profile (V2) action and Get Manager (V2) action. I like to add Scope blocks to keep the actions grouped together however this is a personal preference and not required. In the Profile and Manger Actions, Set the User (UPN) to the Responders Email from the "Get response details" action. This will enable us to populate the Document Template with the correct names as well as route any approval or email notification we wish to conduct.
Next we are going to add a Scope Control, and Populate a Microsoft Word Template action. This action is a Premium Action and dependent on the License you are using may require an additional subscription.
In the Populate a Microsoft Word Template action Set the Location and Document Library to the SharePoint Site you uploaded the template to. Select the Template from the File selector. Once the template has been recognized, the Content Controls that you added to your template earlier will become visible.
I want all the date fields to be formatted in a specific way so I'm going to use Expressions for these.
Select the Expressions Tab rather than Dynamic Content to build the expressions you require. All my date fields will use the same Expression however, you fill need to change the [Field Name] placeholder below to the correct field defined below.
formatDateTime([Field Name],'MMM dd, YYYY')
We are now going to set the fields from the Actions that we have previous used. Set the fields
Requested By: Display name from the Get User Profile (V2) Action
Location: Location from the Get response details Action
Commencement Date: Commencement Date from the Get response details Action
Approval Date: I am using the expression formatDateTime(utcNow(),'MMM dd, YYYY') as, I'm not routing this for Approval however, this could be from an Approval Step Date
Approved By: Display name from the Get Manager (V2) Action
DOB: Date of Birth from the Get response details Action
Hours Per Week: Hours per Week from the Get response details Action
Employee Name: Employee Name from the Get response details Action
Position: Position from the Get response details Action
Staff #: Staff # from the Get response details Action
Status: Status from the Get response details Action
Change or Comments: Change / Comments from the Get response details Action
Requested Date: Submission Date from the Get response details Action
I'm going to create two Set Variable actions. These are for the File Name and File Title in SharePoint. I'm using Expressions for both of these variables. You can change these to reflect any Corporate Naming Conventions you may use.
Name: “File Name”
Value: concat('ESN-',triggerOutputs()?['body/resourceData/responseId'])
Name : “File Title”
Value: concat('ESN - ',outputs('Get_response_details')?['Employee Name'])
Next we are going to add two SharePoint Controls, Create File and Update File Property actions.
We are now going to set the fields in the Create file Action
Site Address: SharePoint Site you want to stored the Document in.
Folder Path: Library within the SharePoint Site selected.
File Name: File Name variable. You will need to add .docx after the variable to ensure its saved as a correct file type
File Content: Microsoft Word Document from the Populate a Microsoft Word template action.
We are now going to set the fields in the Update file properties Action
Site Address: SharePoint Site matching the site the Create file action
Folder Path: Library matching the library in the Create file action
ID: Item Id from the Create File Action
Title: File Title variable
These last two actions will create a new Word Document from the Template using the fields you have provided then, will update the File Title to give a more meaningful description. When I am creating documents in Automation, I normally use some Unique Identifier such as the Response ID as the file name which eliminates the possibilities of duplicates. In addition, if you use a field that a user has entered data into, you can occasionally get errors due to illegal file name characters. The additional of adding the Tile to a document both enhances the search experience within Office 365 as well as enabling the user to display the Title field in Windows Explorer if they are using OneDrive Sync.
Next we are going to add a Convert Word Document to PDF action (this action is a Premium Action and dependent on the License you are using may require an additional subscription) and two SharePoint Controls, Create File and Update File Property actions.
We are now going to set the fields in the Convert Word Document to PDF Action
Location: SharePoint Site matching the site where you created the Word Document
Document: Library matching the library where you created the Word Document
File Name: File Name variable. You will need to add .docx after the variable to ensure it finds the file
We are now going to set the fields in the Create file Action
Site Address: SharePoint Site you want to stored the Document in.
Folder Path: Library within the SharePoint Site selected.
File Name: File Name variable. You will need to add .pdf after the variable to ensure its saved as a correct file type
File Content: PDF Document from the Convert Word Document to PDF action.
We are now going to set the fields in the Update file properties Action
Site Address: SharePoint Site matching the site the Create file action
Folder Path: Library matching the library in the Create file action
ID: Item Id from the Create File Action
Title: File Title variable
Its time to save the flow and test it. Lets open our Form in Preview mode and add some data into it. Once we have submitted the Form, we should see a successful Automation run and two new documents sitting in our SharePoint Library.
Finally, lets check our PDF Document output.
Some Final Thoughts
The possibilities to the Document Creation Process are endless and although you are constrained to only using Plain Text Controls, the ability to format these controls within the Word Template using styles will enhance the options you have.
In addition, using compose statements within your Automation to construct complete paragraph structures based on conditions enables greater flexibility.
I have used this technique on a number of Document Templates so far including Occupation Health and Safety Reporting, Contract Management and Competence Evaluations. The image below is a couple of pages from a Competency Evaluation Template that spans 16 pages, has 9 Sections in it and over 230 Content Controls.
