Convert Microsoft Forms responses with attachments to PDF

[CFernandes]

In one of my previous blog post we converted responses from Microsoft Form to PDF.

Microsoft Forms provides us with a quick and easy way to create surveys, quiz’s etc.  Recently, Microsoft even improved it’s Form product by adding the “File Upload” question type to Forms.  Even with this improvement though, Microsoft Forms big downside remains.

What is the big downside to MS Forms you ask?  Well, completed Forms aren’t terribly portable- have you ever tried sending one to another person so they can review the results or perhaps archive the results in a file archive separate from where The Form was published and completed?  Sure, you can export the results to MS Excel, but that doesn’t help you if you want to quickly and easily create something that can be used in a presentation or otherwise keep the “Look and Feel” of the completed Form intact along with any files that have been uploaded to it.

To solve this issue,  this post will work with the Microsoft Forms “File Upload” control and merge all the attachments into a single PDF and then send that PDF as an email using Power Automate (Flow) and The Muhimbi PDF Converter. It is easy to modify this example to write the generated PDF to SharePoint, OneDrive, or anywhere you can send files to using Microsoft Power Automate.

Before we begin, lets create a simple Form with a Text question and take a deep dive and review how The Microsoft Forms “File Upload” question works.

Creating a sample Form:

• Add a question of type "Text" and configure your question with reference to the screenshot below:

• "File Upload" is simply a question type that can be added to The Form with reference to screenshot below:

• Once the “File Upload” question is added to the Form it looks like the screenshot below.

A limited number of settings are displayed by default, click the ellipses […] at the bottom right to see all options.

• How many files can be uploaded (default is 1, maximum is 10).
• The maximum size for each file (default 10 MB, maximum 1 GB).
• The file formats allowed (Word, Excel, PowerPoint, PDF, image, video, or audio).

Now that we know how the “File Upload” question works, there is still a question remaining- where does the Form store the uploaded files?

• The uploaded files for “Personal forms” are stored in the form creator’s OneDrive for Business account.
• The uploaded files for “Group forms” are stored in the document library belonging to the group.

Our Final Microsoft Form will look similar to the screenshot below:

Note: I have added a single “File Upload” control to the Form and set the “File number limit” to 10, the “Single file size limit” to 10 MB and allowed files of type Excel, PowerPoint, PDF, and Image to be uploaded.

The Use Case: Now that we have our Form ready, lets get back to our use case “Merge the Form responses to all the attachments as a single PDF and send it as an email using Power Automate”. This is a real world use case that one of our customers was struggling with.  They had been using Microsoft Forms, but found it very difficult to manage attachments. The solution we provided was to take all the attachments and merge them into a single PDF.

In order to complete the following steps, please make sure the following prerequisites are in place:

• Muhimbi PDF Converter Services Online full, free or trial subscription(Sign up).
• Appropriate privileges to create Flows.
• Working knowledge of Microsoft Power Automate and Microsoft Forms.

With everything in place, we’re ready to start building our Flow.  From a high-level our Flow looks as follows:

Step 1:

• Add the “When a new response is Submitted” trigger action. It will trigger the flow when a new response is submitted.
• In the trigger action specify the “Form Id” of the Microsoft Form.

Step 2: In this step we will Convert the Form to PDF.

• Add the Muhimbi “Convert HTML to PDF” action.
• Copy the HTML fragment provided below into the ‘Source URL or HTML’ area of the Muhimbi Action and then insert the various list fields in the appropriate places, just before each line’s <br/> element.

<html>
<body>
<h2>Quote Details-</h2>
Responders Email:<br />
Submission time :<br />
Feedback:<br />
</body>
</html>

Now that we have our First Text question converted to PDF, let's work with attachments.

Step 3: Add the “Get response details” action. This action retrieves a form response.

• Specify the “Form Id” of the Microsoft Form.
• For the “Response Id” add the output “List of response notifications Response Id” of the “When a new response is Submitted” trigger action.

Step 4: Add the "Compose" action to the Flow Canvas and set the Inputs to "Sample document" the output from the “Get response details” action and Save the Flow.

Note: This action is just a temporary action to get the JSON sample payload which we will use in the "Parse JSON" action. You can remove the action after getting the Payload.

To get the sample payload, fill in the Form and upload two or more files and Submit the Form. This will trigger the Flow.

• Open the Flow that just ran(succeeded) and expand the “Compose” action.
• The Expanded "Compose" should look like the screenshot below. 
• Copy the JSON from “OUTPUTS” of the “Compose” action to a clipboard and then delete the “Compose” action.

Step 5: Add the 'Parse JSON' action to the Flow Canvas.

• Content: Pass the “Sample document”, which is the output from the “Get response details” action.
• Schema: In the ‘Parse JSON’ action, click on ‘use sample payload to generate schema’, it should open a Modal dialog box, where you then paste the content from the Clipboard(The JSON we copied in step 3) to the Textbox and click on “Done”.

Your schema should look like :

{
    "type": "array",
    "items": {
        "type": "object",
        "properties": {
            "name": {
                "type": "string"
            },
            "link": {
                "type": "string"
            },
            "id": {
                "type": "string"
            },
            "type": {},
            "size": {
                "type": "integer"
            },
            "referenceId": {
                "type": "string"
            },
            "driveId": {
                "type": "string"
            },
            "status": {
                "type": "integer"
            },
            "uploadSessionUrl": {}
        },
        "required": [
            "name",
            "link",
            "id",
            "type",
            "size",
            "referenceId",
            "driveId",
            "status",
            "uploadSessionUrl"
        ]
    }
}

Note: If you look at the above schema you will see that the Output for the "File Upload(Sample document)" is an  array of objects that contains the file name, link to item etc. We will Parse these JSON objects and use it further in our Flow.

Step 6: Initialize a variable of type Array and Name it "Files".

Step 7: Add the "Get file content" action to the Flow canvas and add the item "id" output of the 'Parse JSON' action.

Note: It will automatically add the “Apply to each” loop around the OneDrive “Get file content” action, so that section of your Flow should look similar to the image below:

Step 8:  Add the “Append to variable” action and specify the following values:

Name: Files

Value:

{
  "source_file_name": @{items('Apply_to_each')['name']},
  "source_file_content": @{body('Get_file_content')['$content']}
}

NOTE: Please enter the expression ‘@{body(‘Get_file_content’)[‘$content’]}’ for the “Source_File_Content”, don't just drop the entire file.

Step 9: Outside the “Apply to each” loop, add the “Merge documents” action, click “Switch to detail inputs for array item” and pass in the “Files” variable (the output of the ‘Append to array variable’ action).

Step 10: In this step we will Merge the Microsoft Form Text Question to Attachments.

• Add another Merge document action, and configure it with reference to details below:

Note: Do not switch to detail inputs for an Array item in the Muhimbi Merge action.

• Source file name - 1 : InitialQuestion.pdf
• Source file content-1 : Processed file content - output of "Convert HTML to PDF" action.
• Source file name - 2 : Merged.pdf
• Source file content - 2 : Processed file content - output of "Merge document 1" action.

Step 11: Add the "Send an email(V2)" action to the Flow canvas and configure it with reference to details below:

• To: Email Address.
• Subject: Order - Details "Submission time" the output of the “Get response details” action.
• Body: Hi, Request had been submitted by "Responder's Email" the output of the “Get response details” action.
• Attachment Name: sample.PDF
• Attachments Contents: Processed File Content.

That’s it- you’re done!  Publish your Flow and fill in the Microsoft Form and upload a few sample files. After a short wait, you will find the email in your Inbox.

Subscribe to my blog for the latest updates about SharePoint Online, Power Automate (Microsoft Flow), Power Apps and document conversion and manipulation using The PDF Converter.