top of page
Writer's pictureScott McKenzie

Collect data and produce business documentation using Power Automate and Microsoft Word

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.

Microsoft Word Template

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.

Word Ribbon

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.

Word Content Control

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

Word Content Control

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.

Word Template with Content Controls
 

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

SharePoint Library

 

Collecting the Data


Microsoft Form
Microsoft Form

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”.

Power Automation

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

Variable Controls
  • 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".

Form Control

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.

User Controls

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.

Populate Word Template

I want all the date fields to be formatted in a specific way so I'm going to use Expressions for these.

Expression Box

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')
Assign Fields to Word Template Control

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.

Set Variables
  • 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.

Create Word File Controls

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.

Create PDF Controls

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.


Process Completed

Finally, lets check our PDF Document output.



PDF Output
 

Some Final Thoughts

Compose Statements

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.

TQS Document

262 views0 comments

Comments


bottom of page