© The Author(s), under exclusive license to APress Media, LLC, part of Springer Nature 2022
J. M. RhodesCreating Business Applications with Microsoft 365https://doi.org/10.1007/978-1-4842-8823-8_20

20. Copying Microsoft Forms Attachments to a SharePoint List Item Using Power Automate

Jeffrey M. Rhodes1  
(1)
Colorado Springs, CO, USA
 

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.

A screenshot of Microsoft Forms points to the C I O Software Projects option from the My groups menu using an arrow.

Figure 20-1

Going to My groups in Microsoft Forms

We click on New Group Form, as shown in Figure 20-2.

A screenshot titled Group Forms has a listing for C I O Software Projects. The cursor points to the New Group Form option under the same.

Figure 20-2

Creating a New Group Form

We add as many questions as we want. In our case, we add a text question and then choose Upload File 2 (Figure 20-3).

A screenshot of the Questions tab is titled Testing the Saving of Attachments. A cursor highlights the Upload File option from a drop-down menu.

Figure 20-3

Addng an Upload File question

We can allow up to ten files with the maximum size per file of 1 GB, as shown in Figure 20-4.

A screenshot has an upload option, drop-down menus for file number limit, and single file size limit with an add new option below.

Figure 20-4

Configuring the upload question

Figure 20-5 shows how the form looks to the end user.

A screenshot of a form is titled Testing the Saving of Attachments. It has a text box for summary of file and an upload file option.

Figure 20-5

Testing form with up to three attachments

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

A screenshot for the C I O software projects folder has documents highlighted on the left pane. The right pane has the location path for attachments circled.

Figure 20-6

Attachments location in SharePoint

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.

A screenshot of the C I O software projects folder in SharePoint has attachments list highlighted in the left pane. The right pane has columns for email, summary, and attachments.

Figure 20-7

Attachments List for storing form responses

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 screenshot of build an automated cloud flow window has input boxes for flow name and choose your flow's trigger on the right.

Figure 20-8

Configuring the automated cloud flow

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.

A screenshot of a dialog box has form I D and pick a form drop-down menu. The option for enter custom value in the menu is highlighted with the cursor.

Figure 20-9

Choosing custom value since using Group form

We get this value by previewing the form and copying the info after “id=”, as shown in Figure 20-10.

A screenshot comprises a part of the U R L address selected with the cursor and the copy option highlighted in the menu list.

Figure 20-10

Copying Form Id from Form URL

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.

A screenshot of a dialog box has input boxes for form I d and response I d. The latter is pointed at using an arrow.

Figure 20-11

Also using the Form Id for response details

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

A screenshot of a dialog box has 4 input boxes for the site address, list name, email, and summary. The summary box is pointed at using an arrow.

Figure 20-12

Copying the form responses to SharePoint

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

A screenshot comprises 3 adjacent dialog boxes. The please upload option is pointed with arrows in condition and compose boxes.

Figure 20-13

Using the form attachment in the Condition and Compose actions

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.

A screenshot comprises 2 dialog boxes for Condition and Compose. The outputs text box in Compose is selected with a speech balloon next to it.

Figure 20-14

Copy JSON Output from Power Automate run history

Listing 20-1 shows what this JSON looks like.
[{"name":"IMG_3063_Jeffrey Rhodes.JPG","link":"https://plattecanyon.sharepoint.com/sites/CIOSoftwareProjects/Shared%20Documents/Apps/Microsoft%20Forms/Testing%20the%20Saving%20of%20Attachments/Question/IMG_3063_Jeffrey%20Rhodes.JPG",
"id":"01NW6LEUE76ZSTE53Y7NCJUG7OGLGBMVYC",
"type":null,
"size":2440764,
"referenceId":"01NW6LEUA4ESLOFYBYNVHL5UDNLA3W2UFH",
"driveId":"b!obmEHFCH7ky3Q4qJa1btFD8x9HA7JPFFuC5fXa2KrIKD2RNm80LRR7uelV7z2HVD",
"status":1,
"uploadSessionUrl":null}]
Listing 20-1

JSON Output to be used as a Sample.

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.

A screenshot highlights the outputs option with an arrow in the text box for Content under the Parse J SON dialog box.

Figure 20-15

Parse the JSON and Generate the schema from a sample

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.

A screenshot comprises 2 dialog boxes. The body option in select an output from previous steps, under apply to each dialog box is highlighted with an arrow.

Figure 20-16

Looping through the attachments from the JSON Body

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.

A screenshot highlights an attachment from the menu list for the file path of the dialog box, get file content using path.

Figure 20-17

Selecting the path and name of one of the attachments

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.

A screenshot of get file content using path dialog box has the file titled name, in the file path box. An arrow points to it.

Figure 20-18

Replacing the hard-coded file name with the dynamic name from the JSON

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.

A screenshot of add attachment dialog box highlights a file labeled I D with an arrow in the input box I d.

Figure 20-19

Adding the list item attachment and setting the Id

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.

A screenshot of add attachment dialog box has input boxes for site address, list name, I d, file name, and file content. An arrow points to the file labeled file content in the last input box.

Figure 20-20

Adding the File Name and File Content of the attachment

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.

A screenshot of a form is titled Testing the Saving of Attachments. It has an input box for summary of file and a list of 3 uploads with the submit option at the bottom.

Figure 20-21

Testing the form and flow

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.

A screenshot highlights a panel on the right with input boxes for email and summary, attachments with 3 added attachments, and save and cancel buttons at the bottom.

Figure 20-22

Viewing the attachments from SharePoint

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.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.118.24.128