Microsoft Forms and Power Automate - automate the creation of Planner tasks with file attachments

Today, I had to delve in to our organisation's Office 365 setup, to use Power Automate to hook up the responses from a Microsoft Form, to automate the creation and assignment of new tasks in a Microsoft Planner board.  As an added complication, the form had a "file upload" field - the file from which needed to find it's way through to Planner as a file attachment.

Incidentally, why the change to "Power Automate" Microsoft?  "Microsoft Flow" sounded much more zen like and beautiful. "Power Automate" just sounds brash and cheap -  but I digress. 

You really would think that this process would be a lot easier than it actually was - but there are a few issues to tackle:

• You have to extract the Uri and name of the form file attachment from a JSON object (yes, really). There is no automated task method of just saying "copy this file attachment from the form", and the work-around requires multiple steps.
• You have to allow for the fact the user might not have provided a file when filling in the form (if the field is optional)
• By default, it seems that form attachments are stored in a OneDrive folder belonging to the form creator, so need to be copied somewhere else, so they become accessible to other users who may be viewing the Planner board.
• When you use Power Automate to create a new task in Planner, you have to do it in two steps. The first is to create the task, then the second step is to fill in the details (such as the description). However, after creating the task, you can't access it immediately, so you have to add in a pause to avoid errors (yes, really). 

You might have guessed from this, that I'm not particularly in love with Power Automate. I think it has the potential be a really useful tool - but as a tool presumably designed for people with no programming skills, how can such a simple use case require so many steps to achieve?

Here's how I solved the problem:

• Create a Form with a file attachment field
• Create a Flow using the same user account (so the Flow runs as the same account that created the Form, and therefore has access to the attachments).
• In the same group/Team as the Planner board, create a SharePoint list with some fields that match the fields you have in your form (we use this to store the attachments in a way that anyone who has access to the Team resources, can also access the attachments)

Here are some screenshots from the Flow, with the data unique to my organisation greyed out for privacy/security reasons:

• The first box is the trigger that tells the Flow to run when the form (pick the form you created from the dropdown list) is submitted.
• In the second box, I grab the Response Id from the form submission (this Id uniquely identifies a single submission of the form)
• In the third box, I fetch the identity of an employee in our company, who I ultimately want to assign the task to, so they get a notification (I do so my specifying their Office365 email address).
• In the fourth box, I fetch the identity of the employee who filled in the form (so I can add it to the description of the Planner task)

Next, we create a new SharePoint list item, and populate the fields:

The next step is to create and populate the task in the Planner board.  First we create the task (with just the title, and the assigned user Id).

Then as you may remember, we have to create a delay to allow a few seconds for the task to be created:

... before being able to re-open the task and update the Description field ...

At this stage, we now have a working Flow that will turn a Microsoft Form submission in to a new task in Planner.  However, what about that file attachment?

This is where it gets a bit tricky.

First we need to check if the file upload form field has actually been completed.  To do this, I check to see if its length is greater than 0.

Looking in more detail at that length() condition... I click in the box, pick Add dynamic content.  I then switch to the Expression tab, and type length() in the box.

I then position the cursor in the middle of the two brackets, and switch to the Dynamic content tab.  From here, I pick the file upload field from my original form - and a reference to it gets inserted in to the expression:

I can then click on the Update button to return.  Make sure the middle drop down is set to "is greater than" and the final field to "0".

If the condition is satisfied, we then know that we have to process the file upload field.  First job is to parse the JSON content:

"Please upload..." is the file upload field from the form.  The schema is:

 {  
   "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"  
     ]  
   }  
 }  

The final step is to fetch each file from its original location, attach it to the SharePoint list, and then add a reference to the Planner task - which we do in a loop (in case there is more than one file):

• "Get file content using path" - to set the path, use the folder icon to navigate to the folder in your own OneDrive storage, which contains the form attachments for the particular field in question.  Then use the dynamic content picker to place the "Name" property on to the end of the path.
• "Add attachment" - you pick the SharePoint site, then the SharePoint List, the row Id of the SharePoint list item we created in an earlier step, the filename, and the file content from the previous step.
• "Update task details" - reference the task by the Id we got when we created it during a previous step. I then repeat the content in the "Details" field (in case it gets overwritten during this step). Under "References Alias" I pick the DisplayName field (filename) from the previous step, and under "References resource" I pick the AbsoluteUri field also generated during the previous step.

And that should be it - test the form and see if a few seconds later, a task has been created, assigned to a user, and with the file added as an attachment.