How to create a Flow which stores Microsoft Forms answers in a common/separate folder

[Djarano]

Hi all,

I've created a Microsoft Forms with some upload questions. The answers should be accessible for other colleagues within the organization. The default setting, which is not adjustable, will store the data on my personal OneDrive.

I want to create a flow that stores the answers and attachments, in OneDrive in a folder that is generally available by the organization instead of my personal folder. It should create a folder per respondent included the answers/attachments.

Regards,

Djarano

[Jcook]

Hello,

May I ask why you are using OneDrive,
SharePoint might be a lot easier.
OneDrive will be linked to your account unless you can use a service account that will be used for storing the files on OneDrive.

The reason I think SharePoint is better for this, is because you can allow all users in org to be able to access and not have to worry about anything being tied directly to your account

—Josh
If you like my post please hit the "Thumbs Up" -- If my post solved your issue please "Mark as a Solution" to help others

[Djarano]

Hi,

The reason why I use OneDrive is because it is mandatory by the organization. I think it is a organizational policy, which forced us that the respondents are automatically stored in my personal OneDrive. What is the solution to use SharePoint?

Other option is to keep it like this and give the specific people access to the folders where the answers are stored. Important thing here is that the Folder name should have the same name as the answer of the first question in Forms. It should store the answers per question and the attachments.

[Jcook]

Hi,

Well if you organization wants you to use OneDrive. Than yes you can have a main folder on your OneDrive that is shared to everyone and just have the flow create sub folders for the questions.

For SharePoint. All office365 groups should have a SharePoint site. Again though if you organization wants you to use a specific connector or process due to policies. I would follow them, unless you can persuade otherwise.

—Josh
If you like my post please hit the "Thumbs Up" -- If my post solved your issue please "Mark as a Solution" to help others

[Djarano]

Alright, so I will create a main folder: Customer, and by using Power Automate it automatically should create sub folders with a specific name. And a summary of the answers + attachments should be saved in the sub folder. 

In example:

• Customer
  • Customer 1 - Project x
    • Answers + attachment(s)
  • Customer 2 - Project y
    • Answers + attachment(s)
  • Customer 3 - Project z
    • Answers + attachment(s)

So it shoud look like the structure as showed above.

Could you help me building the flow in Power Automate?

[Jcook · Accepted solution]

Hi @Djarano 

Here is a POC I built for you:

My sample form

This is a sample form that I have created, to simulate what you described 

 My Flows Overview of all the actions - As normal you will want to use When a new response is submitted followed by a Get response details .

Flow Overview

Step 1 - Use a Create file action

Folder Path - Use the Folder Icon to navigate to the Share Folder Customer.

Add the rest of the path manually. (This will create the folder structure for you if the path does not exist already)

File Name - Whatever you want the answers to be in

File Content - The answers from the Form (Not the attachments)

Step 1

Step 2 - Add a Parse JSON action

Content - Use the dynamic content for the Attachment question in the Form

Schema - {I Have pasted the Schema that you can use at the end of the steps}

Add a Apply to each loop

Output from previous step - Use the dynamic content from the Parse JSON

Step 2

Step 3 - Add a Get file content action

File - Use the id from the parse JSON dynamic content

Step 3

Step 4 - Add a Create file action

Folder Path - Do the same as Step 1, this time the flow will not create the folder structure because it already exists, we just want to add the Attachments here. NOTE: make sure the Folder Path is the EXACT same as Step 1   

File Name - use name from Parse JSON dynamic content

File Content - use the File content from Get file content dynamic content

Step 4

The Results is: 

Final Output

Here is the Schema to use for Parse JSON action:

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

If you have any questions let me know.

—Josh
If you like my post please hit the "Thumbs Up" -- If my post solved your issue please "Mark as a Solution" to help others

[Djarano]

Thank you

I can open the file, but the structure of the answers is not adequate. And i just see the answers, not the question anymore.

I prefer to see it like:

- Question 1 - Answer 1

- Question 2 - Answer 2

Possible to have a view where I can see the questions and the answers?

https://ibb.co/1r9whxN 

[Djarano]

Hi,

Other issue i'm facing with is that it will upload only 1 file to OneDrive. For now i've two questions with the possibility to upload documents. So what i did, is that I add both questions (where you can upload documents) to the 'Content' rule on Parse JSON. See link: https://ibb.co/gdk2t7N 

[Jcook]

Hi,

So for the SharePoint file. You will want to add the extension to the end of the file name when creating the file.

For multiple files different questions you will have to follow steps again for each question that has a attachment.

—Josh
If you like my post please hit the "Thumbs Up" -- If my post solved your issue please "Mark as a Solution" to help others

[Djarano]

Hi,

1) So from which step/flow do you need to copy the flow for each attachment? so I can create a parallel branch

2) And how to structure the answers, I prefer to see it something like:

- Question 1 - Answer 1

- Question 2 - Answer 2

[Djarano]

Hi @Jcook ,

i've created the second parallel flow for the 2nd attachment, so that's done! Thank you again.

Last thing is that i want to structure the way how the answers are presented. The file that now is created is not structured and the Questions are not visible, only the answers. Do you know if this is even possible?

[Jcook]

Hello,
unfortunately Flow cannot grab the question names from ms forms at this time.

You will have to hard code the question names in your file when creating.

—Josh
If you like my post please hit the "Thumbs Up" -- If my post solved your issue please "Mark as a Solution" to help others

[Djarano]

@Jcook,

Ok thanks!

1) Maybe other option is to create a URL in the file (or create new file with the URL), which redirects you to the answers in Forms, is that possible?

I prefer to have the following option, but not sure if this is possible. To send a copy of the excel file with the answers to the same folders as the attachments will be saved

https://ibb.co/19nH2Xt  

[Jcook]

Hello,

I have not seen anything regarding that. I don’t think Flow has this feature yet

—Josh
If you like my post please hit the "Thumbs Up" -- If my post solved your issue please "Mark as a Solution" to help others

[rsymmons]

Hi Djarano, 

I previously achieved exactly what you're trying to do here however you will need to purchase a Power Apps Plan 1 license in order to output the form data into a .docx file (a nice template document) or directly into an Excel spreadsheet using a table. Both options are very easy to achieve and Jcook has provided most of the flow needed to extract the files from MS Forms. I have a working version of this and the license upgrade is worth the investment (you can use this as a generic account as long as you build the forms and flow using the account you assign the license to). 

If you need help more help please don't hesitate to reach out! 

This is my flow:

Mine is slightly more complicated because we needed to build in an approval process. But you will need to create 2 branches for submissions that include files and others that don't otherwise the flow will fail. It's quite easy to build even for amateurs like myself. 

This is our template document:

Regards,

Rob

[Djarano]

Hi @rsymmons 

Thanks for your reply.

Don't fully understand the flow, but I smell some possibilities.

Is it possible to use 'Create table' as a right trigger to export the answers. https://ibb.co/YDYdG3b 

Could you help me set up the flow which automatically exports the answers (and questions) into a structured table in Excel?

See also this post, but here every new answer will presented on a new row. I would like to have a new excel for each new answer.

[rsymmons]

You'd probably want to "Create workbook" and then "create table" for each submission. Have you started building out the flow with the JSON script Josh provided above? You'll need this to extract or do things with the files being uploaded. 

[Djarano]

Hi @rsymmons ,

Yes the flow is almost done. All the uploaded files will be saved in a separate folder on OneDrive. Only the questions/answers are still not structured in a way that my colleagues can work with it.

The flow is currently as follow: https://ibb.co/Rv7Lycd

One JSON script for one questions with the possiblity to upload files.

Last thing is to 'Create Workbook' and then 'create table' and should be saved in the same folder as the attachments. Do I need to create them separately below each flow?

[Jcook]

Hello @Djarano

You can just create one action that creates the workbook. So the Parallel actions converge into one.

—Josh
If you like my post please hit the "Thumbs Up" -- If my post solved your issue please "Mark as a Solution" to help others

[Jcook]

Hi @Djarano

If your issue has been solved regarding getting files out of MS forms to OneDrive.

Please Select the solution, this will help others find the answer to your original question.

If you have any other questions. Please open another post.

Thanks

—Josh
If you like my post please hit the "Thumbs Up" -- If my post solved your issue please "Mark as a Solution" to help others