Following up last chapter’s discussion of whether to use Microsoft Forms or Power Apps, this chapter covers the situation where Forms is appropriate, but we need to copy the results AND the uploaded attachments to a SharePoint list. It builds on a solution by Norm Young1 from Canada.
Configuring the Form and List
A personal form in Microsoft Forms will store its attachments in that user’s OneDrive. Since it is typically the organization’s data, I prefer to use group forms, which store their data and attachments in the SharePoint site associated with the group/team. Figure 20-1 shows how to scroll down to My groups and then select the one you want.
We click on New Group Form, as shown in Figure 20-2.
We add as many questions as we want. In our case, we add a text question and then choose Upload File2 (Figure 20-3).
We can allow up to ten files with the maximum size per file of 1 GB, as shown in Figure 20-4.
Figure 20-5 shows how the form looks to the end user.
As I mentioned before, the implication of making this a Group form is that the responses and, most importantly for us, the attachments, are stored in the SharePoint site for the group. Figure 20-6 shows this location. Note that it is in this format:
Documents/Apps/Microsoft Forms/<Name of the Form>/Question
Our last setup task is to create our list for storing the responses and attachments. We thought long and hard An illustration of a smiley emoticon. to come up with the AttachmentsList name, as shown in Figure 20-7.
Note that we are showing the Attachments column to make it easier to see if our Power Automate flow is working.
Setting Up the Flow in Power Automate
The heavy lifting for this solution is within Power Automate. As we have done previously, we create an automated cloud flow with the trigger being When a new response is submitted from Microsoft Forms (Figure 20-8).
A current challenge of group forms is that they don’t show up in the form list. Instead, we need to Enter custom value, as shown in Figure 20-9.
We get this value by previewing the form and copying the info after “id=”, as shown in Figure 20-10.
We use this value twice (“When a response is submitted” and “Get response details”). The latter action uses the Response Id from the former, as shown in Figure 20-11.
We then use the SharePoint Create item action, select our site and list, and then load the Email and Summary information from the form (Figure 20-12).
We now need to check to see if the form has any attachments. We do that with a Condition action and check if the Please upload up to three files question is not equal to <blank>. If the answer is yes, we use the Compose action and use this same question as the Inputs (Figure 20-13).
At this point, we save the flow and test the form (being sure to include at least one attachment). We go to Power Automate’s run history and click the one run showing (that hopefully has a status of Succeeded). As shown in Figure 20-14, we go into the Compose action and copy the JSON shown in the Outputs. This will help us complete our flow.
Power Automate doesn’t care about the specific data but instead wants the schema (format): name, id, type, size, etc. We edit our flow and add a Parse JSON action and load the Outputs from the Compose action into the Parse JSON's Content. This is shown in Figure 20-15.
We click the Generate from Sample button and paste in the JSON from the clipboard (Listing 20-1). The top part of Figure 20-16 shows the resulting schema.
Since there can be multiple attachments, we use the Apply to each action on the Body from our Parse JSON action (Figure 20-16).
We now need to read each attachment so we can copy it to our SharePoint list item. Since the files are in a SharePoint document library (see Figure 20-6), we use SharePoint’s Get file content using path action, as shown in Figure 20-17.
We select our site and then navigate through its document libraries and folders to pick one of the attachments (Christmas_Jeffrey Rhodes.jpg in this case). This allows us to get the path right. We then load in the actual file name from the JSON, which we are looping through to get each file attached for a particular form response. This is shown in Figure 20-18.
We are almost done. We just need to attach the file to our SharePoint list item. For that, we use SharePoint Add attachment action, as shown in Figure 20-19.
We go all the way back to our Create item action (Figure 20-12) to get the ID of the list item we created. From there, we are ready to add the File Name and File Content, as shown in Figure 20-20.
We get the File Name from our Parse JSON action (Figure 20-15) and the File Content from our Get file content using path action (Figure 20-18).
Viewing our Results in SharePoint
We are now ready to test. We complete a response with the maximum of three attachments, as shown in Figure 20-21.
Note the names of the uploaded files. We expect the name to be the same but with the user added.3 So IMG_2558_Jeffrey Rhodes.jpg, for example. We will, of course, also want to click on the files from the SharePoint list item to ensure they open correctly and are the same files. Figure 20-22 shows the results in our SharePoint list.
Notice that all three attachments have been added to the list item. They are also in the original document library. So we have backups, if needed. Success!
Summary
In this chapter, we learned how to use Power Automate connected to Microsoft Forms to check if there are any attachments and, if so, use the Compose action and then Power Automate’s run history to copy the JSON output. We then returned to the development view and used this output to Generate from sample and then parsed the JSON containing the attachments. We used SharePoint’s Get file content using path action and to get our hands on the file and then its Add attachment action to write it to our list item. We are now ready to put all this knowledge together and build some complete applications.