for Microsoft Power Automate and Azure logic apps

Merging files submitted by a Microsoft Form to a PDF Document

By Jay Goodison

22nd December 2020

Merging files submitted by a Microsoft Form to a PDF Document

Building upon our previous posts regarding how to Convert Microsoft Forms Responses to Word or PDF Documents and Handling files provided in a Microsoft Forms response

We’ve been asked a few times recently how to collate all the files provided as part of a forms response and create a single PDF document formed from those files, and this post covers this exact scenario!

Setup your Microsoft Form

This post assumes you have already created your Microsoft Form and added one or more questions which allows a responder to attach one or more files to the response. I have created the following demonstration form:

Create a Flow to merge files provided by the Forms response.

This post focuses on Microsoft Forms and not Microsoft Forms Pro, however, this flow should work for the latter where you replace OneDrive actions with the equivalent SharePoint action.

1. Launch Power Automate – https://powerautomate.microsoft.com

2. Create a new Flow using the ‘Automated cloud flow‘ option

3. Enter a name for the Flow, select the ‘When a new response is submitted‘ Forms trigger, click ‘Create

4. Select the target form:

5. Add a ‘Get Response Details‘ action

5.a. Form ID: Select the form as per step 4

5.b. Response Id: Select the Response Id‘ property from the ‘When a new response is submitted‘ action

6. Add a ‘Initialize variable‘ action

6.a. Name: Enter ‘FormFiles’

6.c. Type: Select ‘Array

6. We now need to test the form as we need an example response (data) to help us to build the remainder of the Flow. Test your Flow and create a response containing some example files, see the example below:

7. Once the test has completed, select the ‘Get response details‘ action

The response detail contains the data provided for each question, and we’ll use this data to retrieve each file provided within the response from OneDrive.

Steps 8 to 12 should be repeated for each question in your response where a file is created.

8. Copy the return value (JSON) for the question which contains the uploaded files.

9. Add a ‘Parse JSON‘ action

9.a. Content: Select the question which relates to the value copied in step #8

9.b. Click ‘Generate from sample

9.c. Paste the value copied in step #8, click ‘Done

9.d. Action configuration complete

When files are uploaded via Microsoft Forms they are placed into your OneDrive Folder following this convention:

/Apps/Microsoft Forms/<Form name>/Question/

We’re now going to retrieve each file, and add them to the file array so that they can be passed to and merged using the Encodian ‘Merge document array to PDF’ action.

10. Add a OneDrive ‘Get file content‘ action

10.a. Select the ‘id‘ property provided by the ‘Parse JSON‘ action

11. Power Automate will automatically place the OneDrive ‘Get file content‘ action into an ‘Apply to each‘ loop as the returned value is an array (even if it only contains one file).

12. Add an ‘Append to Array Variable‘ action

12.a Name: Select the ‘FormFiles’ variable

12.b. Value: Add the following JSON to the ‘Append to array variable‘ variable

12. Add an ‘Append to Array Variable‘ action

12.a Name: Select the ‘FormFiles’ variable

12.b. Value: Add the following JSON to the ‘Append to array variable‘ variable

 {
   "fileName": ,
   "fileContent": 
 } 

12.c. Place the cursor as per the image and select the ‘name‘ property from the ‘Parse JSON‘ action

12.d. Place the cursor as per the image and select the ‘File Content‘ property from the OneDrive ‘Get File Content‘ action.

Underneath the ‘Apply to each‘ loop repeat steps 8 to 12 for any further questions in your form where files are submitted. The demonstration form for this post has two questions which contain files and the current flow construct is depicted below, noting I have renamed some of the actions for ease of reading!

At this point the ‘FormFiles‘ variable should contain a copy of all of the files submitted within the forms response and we can now use the Encodian ‘Merge document array to PDF’ action to merge the files.

13.  Add the Encodian ‘Merge document array to PDF‘ action underneath the ‘Apply to each‘ action

NOTE: The Encodian action will silently convert any non-PDF documents to PDF as part of the merge process

13.a. PDF Filename: Enter a name for the output PDF document, optionally using Flow dynamic data.

13.b. Click the ‘Switch to input entire array‘ icon

13.c. Pass the ‘FormFiles’ variable

The configuration to create the merged PDF document is now complete! Next, you need to add an action to do something with the PDF document formed from the merged files. For this example, I’m just going to upload the merged file to SharePoint.

14. Add a SharePoint ‘Create File‘ action

14.a. Site Address: Set to the target SharePoint site

14.b. Folder Path: Set to the target SharePoint library and folder

14.c. File Name: Select the ‘Filename‘ field from the ‘Merge document array to PDF‘ action

14.d. File Content: Select the ‘File Content‘ field from the ‘Merge document array to PDF‘ action

Your Flow is now complete and should follow this construct (or similar depending on the number of questions handling files).

You can now test your flow! However, please ensure you use a newly submitted form and not an existing response… why? I hear you ask! There are currently issues affecting Power Automate where re-using an existing form can result in a null value returned from the OneDrive ‘Get File Content‘ action, see below:

Successfully executed when using a newly submitted response ( a manual test):

Optional: Adding the Form Response to the Merged File

You may wish to also add the form response to the merged file, which is a fairly simple task!

The following post covers this topic in detail: Convert Microsoft Forms Responses to Word or PDF Documents

1. Add the Encodian ‘Convert HTML to PDF‘ action below the ‘Initialize variable‘ action created in Step 6.

1.a. Filename: Enter a name for the output file

1.b. HTML Data: Copy and paste the HTML data into the field and interlace as required with Flow dynamic data from the response.

2. Add an ‘Append to Array Variable‘ action

2.a Name: Select the ‘FormFiles’ variable

2.b. Value: Add the following JSON to the ‘Append to array variable‘ variable

 {
   "fileName": ,
   "fileContent": 
 } 

2.c. Place the cursor as per the image and select the ‘Filename‘ property from the ‘Convert HTML to PDF‘ action

2.c. Follow these steps to append the ‘File Content‘ property:

Done! The PDF document formed from the HTML provided to the Encodian ‘Convert HTML to PDF‘ will be added as the first document within the merged document. Merging files submitted by a Microsoft Form to a PDF Document

Finally

We hope you’ve found this guide useful, and as ever, please share any feedback or comments – all are welcome!

You can find further documentation and guidance on the Encodian support portal: Merge document array to PDF

 

2 Comments

  1. Beatriz Monje says:

    I would like to add the responses to the form to the merged pdf, but using a word document created from a template and populated with the answers to the form, instead of directly using the “Convert HTML to PDF”.
    Following the same reasoning, I have added after Initialize variable a Get file content from Sharepoint to fetch the template, then the Search and Replace Text to populate, and then I’ve added the result from that process to the array variable.
    The process runs ok until the end, where it gets stuck in the merge document array to PDF part.
    Any chance this can be solved?
    I could always use the HTML conversion, which I have tested successfully, but I would prefer to use the template, with brand image and specific formatting.

    1. Jay Goodison says:

      Hello Beatriz – please review the following post which covers your requirement – https://www.encodian.com/blog/mail-merge-a-word-document-with-a-microsoft-forms-response/

Leave a comment

Your email address will not be published. Required fields are marked *