Converting Complex Microsoft Forms responses to PDF using Power Automate

This is a 2.0 version of my previous blog post on Converting Microsoft Forms to PDF. In this post, we’ll convert a complex Microsoft Form control(checkbox) and also maintain the layout of Microsoft Forms.

Microsoft Forms uses Excel as it’s data-source to store responses. The Excel workbook with your response data can be downloaded to your desktop or stored in ‘OneDrive for Business‘, depending on the starting point of your form or quiz.

First we’ll create a ‘Microsoft Form‘ in ‘OneDrive for Business‘. Then, we’ll use the ‘Excel Workbook’ that holds the responses to build our Form layout in HTML and then Convert that HTML to PDF with the help of Muhimbi’s PDF Converter.

Prerequisites:

Please make sure the following prerequisites are in place:

• An Office 365 subscription with access to Power Automate (Flow).
• Muhimbi PDF Converter Services Online Full or trial subscription (Start trial).
• Appropriate privileges to create Flows.
• Working knowledge of Power Automate (Flow).

Extending Microsoft Forms with Power Automate at Hyderabad User Group!

Step 1: Setup and create a ‘Microsoft Form‘ in ‘OneDrive for Business‘ which is configured to capture the required information.

For this demo, we will convert the Sample Microsoft Form (below) to PDF.

From a High level our Flow will look like:

Step 2: Add the ‘Microsoft Form‘ trigger ‘When a new response is submitted’.

• This means that, whenever a new response is submitted to the configured Microsoft Form, the Flow will get triggered automatically.
• For the ‘Form Id’ in the image below, choose the correct Microsoft Form from the drop down menu.

Step 3: Add the ‘Delay‘  action to your Flow, so the Flow won’t start running before the responses are updated in Excel.

• Set the ‘Count‘ value to 1.
• Unit as ‘Minute‘.

Step 4: Add the Excel Online (Business) ‘Get a row’ action.

• Location: From the drop down select ‘OneDrive for Business‘.
• Document Library: Select the Document Library which holds the file.
• File: Choose the File path.
• Table: From the dropdown select the ‘Table1‘.
• Key Column: From the dropdown select ‘ID‘.
• Key Value: ‘Response Id‘ the output of the Microsoft Forms ‘When a new response is submitted‘ action.

Step 5: Initialize a variable of type ‘String‘ and Name is ‘FoodPreference‘.

Note: ‘Food Preference‘ is the name of our Choice Question in Microsoft Forms.

Step 6: Add the ‘Scope‘ action. We will group all the actions for creating a choice layout inside a scope so they can be visually collapsed inside of the designer and make our Flow look much simpler.

Step 7: Add the ‘Compose‘ action in side the ‘Scope‘.  In the Compose action, add the formula below.

split(outputs('Get_a_row')?['body/Food preference?'],';')

Explanation: 

• The Excel ‘Get a row‘ Body returns the choice question in format “Food preference?”: “Non-Veg;Veg;”
• The first step is to create an Array out of this text so that we can we can work with the individual values.
• We will use the ‘Split‘ function to create an Array.

Step 8: Add the ‘Filter array‘ action.

• From: Select the ‘Outputs‘ of the ‘Split Choices’ action.
• Condition:
  • empty(item()) is equal to false.

Explanation:

• The ‘Split‘ function returns an array with an empty value.
• With the ‘Filter array’ action we will remove the empty value from out array.

Step 9: Add the ‘Select‘ action to the.

• From: Select the ‘Outputs‘ of the ‘Filter array‘  action.
• Map: FoodPreferences to the function ‘item()‘.

Explanation: This will transform the array that we have to an array without the empty item.

Step 10: Add the ‘Parse JSON‘ action.

• Content: The ‘Output‘ of the ‘Select Choice‘ action.
• Pass the Schema:

Explanation:

• To capture the schema, add a compose action above the ‘Parse JSON‘ action and run your Flow.
• Click on  ‘Generate from sample‘ and paste in the sample.

Step 11:  Add the ‘Compose‘ Action and pass it HTML below and pass the ‘FoodPreferences‘ the output of the ‘Parse JSON‘ action.

<label class="container-checkbox"><span class="checkbox-text">@{items('Apply_to_each')['FoodPreferences']}</span><input type="checkbox" checked="checked"><span class="checkmark"></span></label>

Note: An ‘Apply to each‘ loop will automatically be added around it.

Step 12: Inside the ‘Apply to each‘ loop, add the ‘Append to String‘ action

• Name: From the dropdown select the ‘FoodPreference‘.
• Value: Select the ‘Outputs‘ from ‘Composing HTML‘ action.

At this point we have successfully re-created our ‘Checkbox‘.

Step 13: Add a ‘Compose‘ action to your Power Automate (Flow) solution.

• For the ‘Inputs‘ as shown in the image below, we will now configure some HTML to be added to the converted PDF document.
• Copy the HTML fragment provided below in the ‘Compose‘ action and then insert the various list fields in the appropriate places, see screenshot below.

<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="width=device-width, initial-scale=1">
<style>
body {font-family: Arial, Helvetica, sans-serif;}
* {box-sizing: border-box;}

input[type=text], select, textarea {
  width: 100%;
  padding: 12px;
  border: 1px solid #ccc;
  border-radius: 4px;
  box-sizing: border-box;
  margin-top: 6px;
  margin-bottom: 16px;
  resize: vertical;
}

input[type=submit] {
  background-color: #65756C;
  color: white;
  padding: 12px 20px;
  border: none;
  border-radius: 4px;
  cursor: pointer;
  width: 20%;
}

input[type=submit]:hover {
  background-color: #65756c94;
}

.container {
  border-radius: 5px;
  background-color: #FFFFFF;
  padding: 20px;
  max-width:800px;
  margin-left:10%;
  margin-right:10%;
}

.header {
  padding: 60px;
  text-align: left;
  background: #65756C;
  color: white;
  font-size: 20px;
  max-width:800px;
  margin-left:10%;
  margin-right:10%;
}

.office-form-title{
  font-size:21px;
  color:#0000006b;
  max-width:800px;
  margin-left:5%;
}

.question1-title{
  font-size:17px;
  color:#333333d9;
  line-height:1.3;
  font-family:wf_segoe-ui_normal,"Segoe UI","Segoe WP",Tahoma,Arial,sans-serif;
}

.container-checkbox{
  display: block;
  position: relative;
  padding-left: 35px;
  margin-bottom: 12px;
  cursor: pointer;
  font-size: 22px;
  -webkit-user-select: none;
  -moz-user-select: none;
  -ms-user-select: none;
  user-select: none;

}

/* Hide the browser's default checkbox */
.container-checkbox input {
  position: absolute;
  opacity: 0;
  cursor: pointer;
  height: 0;
  width: 0;
}

/* Create a custom checkbox */
.checkmark {
  position: absolute;
  top: 0;
  left: 0;
  height: 25px;
  width: 25px;
  background-color: #eee;
}

/* On mouse-over, add a grey background color */
.container-checkbox:hover input ~ .checkmark {
  background-color: #ccc;
}

/* When the checkbox is checked, add a blue background */
.container-checkbox input:checked ~ .checkmark {
  background-color: #2196F3;
}

/* Create the checkmark/indicator (hidden when not checked) */
.checkmark:after {
  content: "";
  position: absolute;
  display: none;
}

/* Show the checkmark when checked */
.container-checkbox input:checked ~ .checkmark:after {
  display: block;
}

/* Style the checkmark/indicator */
.container-checkbox .checkmark:after {
  left: 9px;
  top: 5px;
  width: 5px;
  height: 10px;
  border: solid white;
  border-width: 0 3px 3px 0;
  -webkit-transform: rotate(45deg);
  -ms-transform: rotate(45deg);
  transform: rotate(45deg);
}

.checkbox-text{
  color:#333333d9;
  line-height:1.3;
  font-family:wf_segoe-ui_normal,'Segoe UI','Segoe WP',Tahoma,Arial,sans-serif;
  font-size: 15px;
}

.sort-text {
  background-color: #f2f2f2; 
  color:#333333d9;
  line-height:3.6;
  font-family:wf_segoe-ui_normal,'Segoe UI','Segoe WP',Tahoma,Arial,sans-serif;
  font-size: 15px;
  width: 18%;
  padding-left: 2%; 
}

</style>
</head>
<body style="background-image: url(https://forms.office.com/Images/Theme/Intelligence/Getty/computer-table.jpg);">

<div class="main">
  <div class="header">
   <h1>BootCampDemo</h1>
  </div>
</div>
<div class="container">
    <div class="office-form-title"><span>BootCampDemo</span></div>
    <br/>
    <form action="/action_page.php" style="max-width:800px;margin-left:5%;margin-right:5%;">
      <label for="fname" class="question1-title">1. Please enter you name?</label>
      <br/><br/>
      <input type="text" id="fname" name="firstname" placeholder="@{outputs('Get_a_row')?['body/Please enter you name?']}">
      <br/><br/>
      <label for="country" class="question1-title">2. Food preference?</label>
      <br/><br/>
@{variables('FoodPreference')}
	  <br/><br/>
    </form>
</div>
</body>
</html>

Step 14: Add the Muhimbi ‘Convert HTML to PDF‘ action.

• Source URL to HTML: Pass in the output of the ‘Compose Final HTML‘ action.

Note: I use Muhimbi ‘Convert HTML to PDF’ action because it is the most comprehensive HTML to PDF Converter that support the latest HTML, CSS and JavaScript standards and provides the highest quality output.

Step 15: Add a ‘Create File‘ action.

• Site Address: Set to the target SharePoint site.
• Folder Path: Set to the target SharePoint library and folder.
• File Name: Give a meaningful ‘File Name‘ to the created PDF document.
• Note: Please make the file name unique, or multiple runs of the flow will overwrite the same file.  I recommend basing it on the source file name, but with some kind of suffix.
• File Content: Select the ‘File Content‘ field from the ‘Convert HTML to PDF‘ action.

• Test the Flow by submitting a response to the target Microsoft Form. Validate the Flow run has successfully executed.

• Check the PDF File has been created.

• Validate the PDF file.

Subscribe to this blog for the latest updates about SharePoint Online, Microsoft Flow, Power Apps and document conversion and manipulation.