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.


OneDrive 
New V" Upload v 
Folder 
Word document 
Excel workbook 
PowerPoint presentation 
OneNote notebook 
Forms for Excel 
Link 
Search 
Sync Automate v 
Sort v


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


3ack 
BootCampDemo 
1. Please enter you name? 
Enter you r 
2. Food preference? 
Non-Veg 
Veg 
Submit 
Computer 
C] Mobile 
This by the Owner Of The data j•Cu submit be Sent to the form Never give Out 
P3vered Sy Privacy an-d I



From a High level our Flow will look like:


ㄇ c 1 「 ML 10 2 
ㄇ c ; HTML 
, to Choi 一 一 
叿 ; 一 一 Ch



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.


pauuuqns SI. as uodsau Mau e uaqM


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


Delay 
• Ι—ΙηίΤ


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.


uun103 
Luqn •


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

{х} 
lnitiali:e variabIe Choice 
• Туре 
Food7reference 
•.•ilu•


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 ‘Spit‘ function returns an array with an empty value.
  • With the ‘Filter array’ action we will remove the empty value from out array.


Filter array 
•veg- 
"Mon-Veg"




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

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


Select Choices 
Fxd>reference: 
Кеу 
Вму - ltem х 
•.•аГие


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:


Parse 
" properties " : 
"Foodpreferences " : 
"type": "string" 
"required": [ 
"Fwdpreferences" 
Generate from sample


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


Scope - Creating Choices 
Split Choices 
Filter array 
Select Choices 
Body K 
refæ 
Parse 'SON 
Apply to each 
Composing HTML 
{x} 
Append to Choice variable 
—n action 
Add



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.

Convert HTML to PDF 2 
Outputs x 
Show advy•ced `, 
0



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.


yueulp pg•v 
47쬐 • 0,.`예:驢 
0 
飜니N 하」 • 
하」•u,,℃00 P•』•나5/

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


0 
0 
0 
0 
0 
0

  • Check the PDF File has been created.


u un 00

  • Validate the PDF file.
BootCampDemo 
BootCampDem0 
1. Please enter you name? 
2. Food preference? 
Non

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

One thought on “Converting Complex Microsoft Forms responses to PDF using Power Automate

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s