How to add Forms uploads into a SharePoint list as attachments?

Microsoft Forms are a great way to get information from people. I’ve mentioned this before, but it’s important to stress. Microsoft Forms are a great way to collect data, but not to store it, since data is stored in a temporary folder on your OneDrive. Also, it’s pretty easy to go to forms and purge all data quickly, so today, we’ll focus on a specific feature—upload files. More importantly, how to add Forms uploads into a SharePoint list as attachments and keep your data safe and organized.

SharePoint is much better to store your data since we can hold each form response as an item and add the attachments to that item. SharePoint also has the possibility for versioning and other security feature that keep your data safe.

So let’s look at how to get a response, upload all files as attachments and save the data.

The Form

Let’s start with the form. We don’t need to make it too complex since we’re focusing on the attachments. To do that, let’s create a new form and add it to an upload section. To do that:

Select “File Upload.”

Microsoft Forms will notify you that the files will be stores in your OneDrive.

Since we want to test with multiple attachments, let’s increase the limit.

Let’s pick 3.

Finally, let’s add a Title to match it with the “Title” column in the SharePoint list.

Nothing fancy. Let’s now look at the list.

The List

The good news is that any list that you create already supports attachments. The field is hidden, but it’s added automatically to your list. Let’s add it. In your list:

Select “Attachments” and click “Apply.”

That’s it. Nothing more is needed.

Bringing things together.

As always, Power Automate is the “glue” that makes things talk with each other. So we’ll use the “When a new response is submitted” trigger to catch the responses.

Here’s the trigger and the action.

Let’s see what we get when we upload a couple of files.

Looks good, but let’s look a bit further into the “body” part:

{
  "responder": "manuel@manueltgomes.com",
  "submitDate": "10/11/2021 9:04:53 AM",
  "<redacted>": "First upload",
  "<redacted>": "[{\"name\":\"Screenshot 2021-10-07 at 16.28.59_Manuel Gomes.png\",\"link\":\"https://manueltgomescom-my.sharepoint.com/personal/manuel_manueltgomes_com/Documents/Apps/Microsoft%20Forms/Form%20upload%20to%C2%A0SharePoint%20list%20item%20attachments/Question/Screenshot%202021-10-07%20at%2016.28.59_Manuel%20Gomes.png\",\"id\":\"<redacted>\",\"type\":null,\"size\":320814,\"referenceId\":\"<redacted>\",\"driveId\":\"<redacted>\",\"status\":1,\"uploadSessionUrl\":null},{\"name\":\"Screenshot 2021-10-07 at 16.29.00_Manuel Gomes.png\",\"link\":\"https://manueltgomescom-my.sharepoint.com/personal/manuel_manueltgomes_com/Documents/Apps/Microsoft%20Forms/Form%20upload%20to%C2%A0SharePoint%20list%20item%20attachments/Question/Screenshot%202021-10-07%20at%2016.29.00_Manuel%20Gomes.png\",\"id\":\"<redacted>\",\"type\":null,\"size\":3758063,\"referenceId\":\"<redacted>\",\"driveId\":\"<redacted>\",\"status\":1,\"uploadSessionUrl\":null}]"
}

It seems a bit complex, so let’s break it down and fetch the array of the uploads. Let’s clean it up a bit and format it:

[
    {
        "name": "Screenshot 2021-10-07 at 16.28.59_Manuel Gomes.png",
        "link": "https://manueltgomescom-my.sharepoint.com/personal/manuel_manueltgomes_com/Documents/Apps/Microsoft%20Forms/Form%20upload%20to%C2%A0SharePoint%20list%20item%20attachments/Question/Screenshot%202021-10-07%20at%2016.28.59_Manuel%20Gomes.png",
        "id": "<redacted>",
        "type": null,
        "size": 320814,
        "referenceId": "<redacted>",
        "driveId": "<redacted>",
        "status": 1,
        "uploadSessionUrl": null
    },
    {
        "name": "Screenshot 2021-10-07 at 16.29.00_Manuel Gomes.png",
        "link": "https://manueltgomescom-my.sharepoint.com/personal/manuel_manueltgomes_com/Documents/Apps/Microsoft%20Forms/Form%20upload%20to%C2%A0SharePoint%20list%20item%20attachments/Question/Screenshot%202021-10-07%20at%2016.29.00_Manuel%20Gomes.png",
        "id": "<redacted>",
        "type": null,
        "size": 3758063,
        "referenceId": "<redacted>",
        "driveId": "<redacted>",
        "status": 1,
        "uploadSessionUrl": null
    }
]

Looks a bit better. As you can see, we have the following fields:

1. Name
2. Link – this is the place on my OneDrive where the file is stored.
3. Id – for us to fetch the file.

The remaining ones serve their purpose, but they are not relevant for now.

I’m showing you this for a reason. As you can see above, we don’t have the file contents, only a link to the file on our OneDrive. However, some actions return the file’s contents, so be aware of this difference.

So what do we want to do?

1. Fetch all attachments
2. For each upload, fetch the file contents
3. Add the attachments to the created item

Fetch all attachments and add them

As you can see above, we have a JSON array in the response. But to Power Automate, it’s a string. As you can see above, all items have \" meant that we’re defining double quotes as any other character. If you try to add it to a “Apply to Each” action, you’ll get the following error:

The execution of template action 'Apply_to_each' failed: the result of the evaluation of 'foreach' expression '@outputs('Get_response_details')?['body/<ID>']' is of type 'String'. The result must be a valid array.

It “makes sense” if you think about it. The “Apply to Each” action expects an array, but we have a string. So let’s convert it using the “JSON” function.

Here’s the formula:

json(outputs('Get_response_details')?['body/<ID>'])

Please note that you need to replace the formula with the value from your form. Power Automate uses the form’s ID so you need to include it here, but you can build the formula yourself. Here’s how to do it:

For each item

Now that we have the information ready, let’s look at what SharePoint expects. To do it, we will use the “Add attachment” action:

As mentioned above, we don’t have the file contents, but we do have the file ID, so we can use the “Get file content” action using the ID that we have.

Let’s build the “Apply to Each” action.

Now let’s add the ID to the file contents.

We have a problem. Since Power Automate converts the string that we get from the “Get response details” action when the Flow is running, we don’t know the structure now so that Power Automate won’t display it. We have two ways to solve this:

1. The visible way, by adding a “Parse JSON” action and use it to reference the elements.
2. The geeky way is by calling the elements directly.

Let’s look at both. Since we know the structure, we can use the “Parse JSON” action and provide the example above in the “Generate from sample”. We’ll get the following:

The schema will always be the same so that you can use this one:

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

First, let’s look at the “Get file content” action and try to add the ID:

Don’t forget to replace, in the “Apply to each” action, the “Parse JSON” since it’s the one that will contain the array we would like to parse.

There are a lot of arrows, but I want you to understand where things come from.

The “geeky” option

Ok, let’s finish things up with the “geeky” way. Since we know the structure of the array, we also see the element’s name (it’s called “id”) that we want to fetch. We also see the syntax to get the elements from the JSON. In the “Apply to each” action, we can use the “item” function to fetch them. It will be something like this:

item()?[<name of the field>]

The item will return the current element parsed of the array inside the “Apply to each” action. After that, we indicate that the element may exist with the “?”. By putting this, the Flow won’t break if the item doesn’t exist. It’s also safe because if it’s empty, then the “Apply to each” action won’t parse any element. After that, we indicate what the name of the field that we want to get is. In this case, we want “id” and “name”, so let’s check how it works:

Now let’s use the “Add attachment” action:

What it looks like in SharePoint

Now that Power Automate inserted the data, let’s look at the SharePoint list.

Notice that we have one item with multiple attachments. Don’t duplicate items for multiple attachments since it’s not necessary.

If you look at the details of the item, you can see the attachments there.

That’s it.

Final thoughts

The overall process is relatively straightforward, but I wanted to show you some things that could be confusing, like converting the array or inserting the values in SharePoint when the item doesn’t show up in the dynamic content. As always, you can find the template in the “cookbook” section under add Forms uploads into a SharePoint list as attachments. 

Photo by Markus Winkler on Unsplash