Power Automate + HTML = Survey123 PDF Report
I work for a local government with an Office 365 subscription that includes Power Automate (aka MS Flow) and I wanted to see what I could do with it. After Zoltan Kovacs posted his 3rd blog on using Power Automate to send an email using HTML from a survey I was talking with my colleague (a developer) who told me that it was possible to generate a PDF from HTML using Power Automate (PA).
Wow! Really?
It's true. It's possible. There is a convert file action that will convert HTML to PDF.
The Survey123 Feature Report generates a nicer output and it includes attachments and its much easier to create a Word doc template than it is to create HTML. My method does not include attachments from Survey123 because it hasn't been exposed to Power Automate by Survey123 yet. My method also requires you to know HTML and CSS because you will need to fiddle with it. You can save a Word document as HTML (two options, more on that later) to get started but I just went old school and typed it out.
Here is the flow. You will notice the second step is cut off. It is a parallel branch that would have been too wide to capture and see it.
I'll explain my steps here and include tips along the way.
Step 1 is where you trigger the flow with your survey.
Tip: Any step or action you add to your flow can be renamed. Clicking on the 3 dots on the side will show a drop down menu. Rename is one of them. Adding comments is another.
Step 2: parallel branch where I initialize and set variables inside a switch case operation
This survey records water meter replacements. The choice value is stored with a code. I wanted the report to show the label and not the value. I know that Survey123 has jr:choice-name but I built this survey 3 years ago and I didn't want to change it. I looked at using a SharePoint list to lookup the value but I couldn't figure it out (I felt like I was this close to a solution! but I had to walk away and move on).
I tried using the If expression as it seemed similar to the If expression in Survey123. However, unlike Survey123 it cannot contain nested expressions.
There is a switch case operation. My solution was to initialize a variable and then use the switch case operation to set the variable to a nice looking label for every value.
The reason why I cannot show the entire parallel branch in the screen shot is because I have 4 branches and each branch has 4 switch cases. Two of the switches have multiple cases (17 in one of them!). It is wider than my screen can show when I click into them.
Tip: Have all of your parallel branches figured out in the beginning because it is currently not possible to add one in later without needing to redo your entire flow.
Step 3 and 4: Read part 2 from Zoltan Kovacs for how to set this up.
Step 5: Create the HTML file (add step: create file action in OneDrive for Business)
A side note about HTML: It's the trickiest part because … well … it is HTML. While testing I typed the HTML myself because I wanted a simple form.
I did have a Word doc of this form. In Word, if I saved it as web page, opened it in Notepad++ it was over 1300 lines long! If I saved it as a web page filtered it was about 300 lines long. Filtered means it strips out all of the Word tags making it impossible to turn it back into the same Word document again.
A side note about challenges using the PA user interface: This report I created is not very long. About a half of a letter sized page. However, I was unable to see all of it in the PA interface. One of the challenges with the interface is that the dynamic content is in a floating window all of the way down at the bottom right.
It doesn't follow you as you scroll up. If you want to insert the content you have to mouse click exactly where you want it, scroll down, click on the content and then scroll back up to see if it put it where you want it. If you didn't mouse click exactly (even if you think you did) it will appear at the top of the file content. While it is possible to click on the dynamic content and drag it up through the file it is slow and it may need to be done multiple times to get it where you want it.
The next screenshot is the beginning of the HTML. The folder path is where the HTML file is saved in OneDrive for Business. This is one of the connections you need to set up in advance. I believe you cannot go deeper than one folder off of the root folder.
Tip: I found that it was possible to insert code in the HTML while using Notepad++. For example, the dynamic content for anything from Survey123 is triggerBody()?['feature']?['attributes']?['field name']. In Notepad++ I used
@{triggerBody()?['feature']?['attributes']?['field name']}. Once it was copied into the PA window it changed into the normal looking green dynamic content.
Tip: I'm using inline CSS but it is possible to reference an external stylesheet. I have not tried this yet.
The file name can be a dynamic value. I was never able to figure out how to do this in Integromat. The green icon with the checkmark is coming from the survey. In this particular case it is the service address. I added .htm
PA uses different colors for dynamic content. So far I've seen green for Survey123, blue for the built-in functions used to generate the time stamp and purple for the variables I set.
Step 6: Create the PDF file
When you create the file in Step 5 from above the dynamic content will now include ID. It is all that is needed for this step.
Step 7: Save the file to OneDrive
When the HTML file is created in Step 5 it actually saves the file to OneDrive. That doesn't happen in Step 6. You have to explicitly save it using the Create File operation (I've already renamed it in this screenshot). Choose the folder and add the file name and file content from the dynamic content window.
Step 8: Send email (V2) preview
I didn't really have to send an email to myself with the attachment as it was saving it to OneDrive. It was part of my testing.
I went through a lot of trial and error and Google searches a couple of weeks ago figuring this out even though I know (through the early adopter program) that a PDF option is coming to Survey123.
I'm a GIS specialist who dabbles in the development world. I'm no Power Automate or webhook expert. There is probably a different way to do this but I wanted to share this with the community. It might inspire others to find a different solution or be inspired to try something new.
p.s. I finally finished writing up this post and I've already found a possible alternative to using HTML!