Intermediate | Flow of The Week: Create and insert a document approval sheet using Flow approval data

Jay from Encodian has compiled this guide showing how we can use Power Automate to create a document approval sheet using Power Automate approval data before embedding it within a document.

This is a common use case which is a regular topic within the community!

Encodian is one of our trusted Power Platform connector partners which contains a whopping 33 different actions covering document format conversion, redaction, watermarking, OCR, PDF to PDF/A, data extraction, merging, splitting, securing, search and replace, archiving (ZIP files!) and a whole boat load more!

Flow Overview

Many organisations include the document approval sheet in the document either at the start or end of the document. Using this approach requires the user to manually update the approval table which is difficult to manage especially when using an automated approval process to generate an approved version of the document, i.e. you can’t change the document once it’s approved! And you can’t really complete the approval table (dates, comments) until the approval process is complete.

This Flow will demonstrate how to dynamically generate the approval sheet from Flow approval data and add it to an approved rendition of the document (PDF).

Let’s build the Flow!

Navigate to the SharePoint document library which will store and manage the documents that will be sent for approval.

Click ‘Flow’, click ‘Create a Flow’

Click ‘Show more’

Select the ‘Complete a custom action for the selected file’

Click ‘Continue’

Select the default name of the Flow text and change to ‘Submit for Approval’

Add a SharePoint ‘Get File Content’ action to the Flow

 

Configure the ‘Get File Content’ action as follows:

Site Address: This must be set as per the ‘Site Address’ value set in the ‘Get File Properties’ action

File Identifier: Select the ‘Identifier’ property returned by the Get File Properties’ action

Add a ‘Start and wait for an approval’ action to the Flow

Select the required approval type – this demo uses the ‘Approve/Reject – Everyone must approve’ approval type

 

 

Complete the ‘Start and wait for an approval’ action configuration as follows:

Title: Set to ‘Official Document Approval Request’ or similar

Assigned To: Enter the users who will be asked to approve the document

Details: Enter additional detail if needed using markdown

Item link: Select the ‘Link to Item’ property returned by the ‘Get file properties’ action

Item link description: Select the ‘File name with extension’ property returned by the ‘Get file properties’ action

At this stage the Flow approval process is now complete. The next stage is to create the official rendition of the document, obtain the approval data and build the approval sheet that we will insert info the approved PDF document.

Add a ‘Initialize variable’ action

Name: Set to ‘ApprovalSheetHtml’

Type: Select ‘String’

Value: Copy and paste the contents of the ‘ApprovalsheetHtml-InitialValue.html’ file

              NOTE: This action is required later within the Flow… more details to follow

Add a ‘Condition’ action

Choose a value: Select the ‘Outcome’ property returned by the ‘Start and wait for an approval’ action

Equation: Set to ‘does not contain’

Choose a value: Enter ‘Reject’

 

This step is optional; it demonstrates how to change document content using Power Automate. In this example an action is added to update the document footer to specify the correct approval date:

Inside the ‘If No’ branch, add some relevant handling to inform the requesting user the approval has been rejected (see example below):

Inside the ‘If Yes’ branch;

Add the ‘Search and Replace Text’ Encodian action

File Type: Select ‘DOCX’

Filename: Select the ‘File name with extension’ property returned by the ‘Get file properties’ action

File Content: Select the ‘File Content’ property returned by the ‘Get file content’ action

Phrases Search Test – 1: Enter [APPROVALDATE]

Phrases Is Expression – 1: Select ‘No’

Phrases Replacement Text – 1: Select the ‘Completion Date’ property returned by the ‘Start and wait for an approval’ action

 

 

NOTE: If required you can specify exact font size, colour, etc. otherwise the current format of the word document text will be inherited.

 

Add the ‘Convert to PDF’ Encodian action

Filename: Select the ‘Filename’ property returned by the ‘Search and Replace Text’ action

File Content: Select the ‘File Content’ property returned by the ‘Search and Replace Text’ action

PDF Filename: Select the ‘Filename’ property returned by the ‘Search and Replace Text’ action. The Encodian action will automatically ensure that the output filename has a PDF extension.

Click ‘Show advanced options’

 

Remove Markup: Select ‘Yes’, this ensure that any comments / tracked changes are removed from the resultant PDF document.

 

The next step is to create the approval sheet. We have several options/approaches which would work; if the document already contains an ‘Overview/Title Page’ we could:

Use the ‘Insert HTML to PDF’ action to insert the approval sheet at a specific location within the PDF document.

Use the ‘Insert HTML to PDF’ action to append the approval sheet to the end of the PDF document.

Split the source PDF document into two separate documents using the ‘Split PDF’ action, create the approval sheet using the ‘Convert HTML to PDF’ action and the merge the three files in the correct order using the ‘Merge Document Array to PDF’ action

However, we advise our customers not to include an ‘Overview/Title Page’ or ‘Approval Sheets’ in the actual documents as these can easily be added dynamically when needed, providing enhanced control and options for adding these types of pages in a document. This is what we’ll now do…

Add an ‘Append to string variable’ action

Name: Select the ‘ApprovalSheetHtml’ variable

Value: Select the ‘Responses Approver email’ property returned by the ‘Start and wait for an approval’ action

NOTE: This should automatically place the ‘Append to string variable’ action in a ‘Apply to each’ loop.

Value: Enter the following text

<tr>

<td class=”TdData utilW100″>@{items(‘Apply_to_each’)?[‘responder’]?[‘displayName’]}</td>

<td class=”TdData”>@{items(‘Apply_to_each’)?[‘responseDate’]}</td>

<td class=”TdData”>@{items(‘Apply_to_each’)?[‘comments’]}</td>

</tr>

 

Underneath the ‘Apply to each’ loop, add an ‘Append to string variable’ action

Name: Select the ‘ApprovalSheetHtml’ variable

Value: Enter the following text

</table></div></div></body></html>

Add a ‘Insert HTML to PDF’ action

NOTE: This screen shots of the ‘Insert HTML to PDF’ action show some configuration options (Page Number, Page Size, Page Orientation) which are currently under deployment and may not reach your region for another 2 to 3 weeks.

Filename: Select the ‘Filename’ property returned by the ‘Convert to PDF’ action

File Content: Select the ‘File Content’ property returned by the ‘Convert to PDF’ action

HTML Data: Select the ‘ApprovalSheetHtml’ variable

HTML Insert Location: Select ‘Prepend’

 

 

NOTE: If you cannot see the ‘HTML Insert Location’ your region has not yet received the pending updates, simply click ‘Show advanced options’ to update the ‘HTML Insert Location’ option

The approval sheet containing document information and approval details has now been added to the document. You may now wish to dynamically add a document title sheet.

Optional Document Title Sheet Insertion Example

This is already a lengthy post, so we’ve just included a basic example for dynamically creating a document title sheet as a taster!

A Word template is retrieved from SharePoint, the Encodian ‘Search and Replace’ action used to replace placeholder text with the document title and approval date, a PDF rendition is created and then merged with the actual PDF document which already contains the approval sheet.

The resulting title sheet document:

Final Flow Action

Add the resulting approved document to the required location. In this example we’ll simply add the file back to the originating location.

Add a ‘Create File’ SharePoint action

Site Address: This must be set as per the ‘Site Address’ value set in the ‘Get File Properties’ action

Folder Path: Select the ‘Folder Path’ property returned by the ‘Get file properties’ action

Filename: Select the ‘Filename’ property returned by the ‘Insert HTML to PDF’ action

File Content: Select the ‘File Content’ property returned by the ‘Insert HTML to PDF’ action

Let’s Test!

Before you test, we advise reviewing the following blog post which outlines how to share your Flow with users correctly to ensure connections are shared with the correct configuration to negate the need for users having to enter unnecessary connector authentication information such as API Keys:

Reference Guide: Prevent users needing to enter an API Key in Power Automate

The ‘run only users’ configuration for this demonstration flow has been set to the following configuration:

Go to SharePoint and start the Flow

Click ‘Continue’

The approved PDF file with embedded approval sheet should have been created

Closing thoughts…

Although this post has focused on the creation and manipulation of a PDF document, this could also be achieved with a Word document (docx) using the ‘HTML to Word’ and ‘Merge Word Documents’ actions to replace their PDF equivalents.