cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Antooox
Level: Powered On

Multiple forms to one Excel database

Hi guys,

 

Today we send out a PDF with some information every week. I have managed to add this information to Microsoft forms instead. So, lets say that there's 52 categories (alcohol and drugs, driving with care, powertool safety etc) and there's one form sent out every week with just some plain information about that weeks subject and a "Have you read through all the text?" and "Insert name and date" buttons. 

 

The data that is collected is the name of the participant and yes and no. 

 

What I want to do:

 

An Excel document with the teammembers names and all the 52 categories. Every week when a new form is sent out and someone submits it, I want this to happen =

Person1 (anyone of the teammembers) submits the report and the cell in Excel becomes green (on the right category and name) when Person1 have answered "Yes" in the form

 

So, Exceldocument with 10 names, and 52 categories. 52 forms are created. When a person submits a "Yes" on the form I want the cell to become green on that specific persons cell under that specific category. 

 

Ty

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Multiple forms to one Excel database

Hi @Antooox the following Flow should get you quite close to what you want. I'll explain why I'd class it as "quite close" at the end.

 

I have a Microsoft Form which is only available to people within my organisation to complete. When the Form is in this configuration, it will automatically track who has submitted, therefore I don't need to ask who the user is.

Form.PNG

When this Form is submitted it will fire off my Flow, which at high level looks like this:

High Level.PNG

Let's look at each action a little more closely:

Trigger: The trigger is based up on "When a new response is submitted", to which I have supplied the name of the Form that I want to monitor.

 

Action: I have then used "Get response details" to take the response ID from the trigger and pull back the answers to the questions. This get's returned as an array, so you can either just use dynamic content and accept that this action will then appear within a loop, or you can use the following expression to pull back the details in a neater way:

triggerBody()?['value'][0]?['resourceData']?['responseId']
Get response details.PNG
Action: As I have a name rather than a UPN displayed in my Excel, I've used the "Get user profile (V2)" action to take the responder email and get their profile. This will then allow me to track details such as display name, contact information, and even managers name
Get user profile.PNG
Action: Now that I know who I need to update within my Excel spreadsheet, I can use the "Update a row" action to change the value. For this I will simply tell it where the Excel workbook is stored (in my case SharePoint), which table I want to update (I've called mine results), the column and key being used to identify the row. So in my case, I have taken the key column as "Name" and looked for the Display Name from the previous action.
Update a Row.PNG
This then results in the following update to my Excel workbook:
Ecel.PNG
 
The reason why I said this is quite close to what you need, is because the Forms trigger has a one-to-one relationship with a Form. Therefore if you have 50 Forms, then theoretically you need 50 Flows all being triggered by it's corresponding Form. You may tell me that this is fine, or that in your scenario there's an identifier within the Form which will mean that you use one Form and therefore have one Flow.
 
If you're in scenario 1 and you're looking at a possible 50 Forms and 50 Flows, then a possible solution could be to have a separate list which acts as  a "Campaign Manager". In this solution you could use a single Form and a single Flow, and then based on the date of submission look up against the Campaign Manager to see which subject is currently being assessed. E.g. Week 1 = Alcohol Misuse, Week 2 = Shark Baiting etc
 
Does that help?
 




Did my reply help? Please give it a thumbs up.
Did I answer your question? Please mark my post as a solution!


Proud to be a Flownaut!





Community Leader: Black Country PowerApps & Flow User Group

View solution in original post

2 REPLIES 2
Super User
Super User

Re: Multiple forms to one Excel database

@Antooox 

Hi there. Not sure what the question is, but roughly sketching this out, it will probably have the Forms trigger, then Get response details, right?

 

Follow that with an "Update Row" from excel, and the color-changing can just be conditional formatting inside Excel.

 

Maybe give that a go and let us know if you bump into any trouble.  Keep us posted.

 

-Ed-

 

 

If this reply has answered your question or resolved your challenge, please consider marking it as a Solution. This helps other users find it more easily via search.

Super User
Super User

Re: Multiple forms to one Excel database

Hi @Antooox the following Flow should get you quite close to what you want. I'll explain why I'd class it as "quite close" at the end.

 

I have a Microsoft Form which is only available to people within my organisation to complete. When the Form is in this configuration, it will automatically track who has submitted, therefore I don't need to ask who the user is.

Form.PNG

When this Form is submitted it will fire off my Flow, which at high level looks like this:

High Level.PNG

Let's look at each action a little more closely:

Trigger: The trigger is based up on "When a new response is submitted", to which I have supplied the name of the Form that I want to monitor.

 

Action: I have then used "Get response details" to take the response ID from the trigger and pull back the answers to the questions. This get's returned as an array, so you can either just use dynamic content and accept that this action will then appear within a loop, or you can use the following expression to pull back the details in a neater way:

triggerBody()?['value'][0]?['resourceData']?['responseId']
Get response details.PNG
Action: As I have a name rather than a UPN displayed in my Excel, I've used the "Get user profile (V2)" action to take the responder email and get their profile. This will then allow me to track details such as display name, contact information, and even managers name
Get user profile.PNG
Action: Now that I know who I need to update within my Excel spreadsheet, I can use the "Update a row" action to change the value. For this I will simply tell it where the Excel workbook is stored (in my case SharePoint), which table I want to update (I've called mine results), the column and key being used to identify the row. So in my case, I have taken the key column as "Name" and looked for the Display Name from the previous action.
Update a Row.PNG
This then results in the following update to my Excel workbook:
Ecel.PNG
 
The reason why I said this is quite close to what you need, is because the Forms trigger has a one-to-one relationship with a Form. Therefore if you have 50 Forms, then theoretically you need 50 Flows all being triggered by it's corresponding Form. You may tell me that this is fine, or that in your scenario there's an identifier within the Form which will mean that you use one Form and therefore have one Flow.
 
If you're in scenario 1 and you're looking at a possible 50 Forms and 50 Flows, then a possible solution could be to have a separate list which acts as  a "Campaign Manager". In this solution you could use a single Form and a single Flow, and then based on the date of submission look up against the Campaign Manager to see which subject is currently being assessed. E.g. Week 1 = Alcohol Misuse, Week 2 = Shark Baiting etc
 
Does that help?
 




Did my reply help? Please give it a thumbs up.
Did I answer your question? Please mark my post as a solution!


Proud to be a Flownaut!





Community Leader: Black Country PowerApps & Flow User Group

View solution in original post

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

firstImage

Incoming: New and improved badges!

We've given our badges an overhaul and also added some brand new ones!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Power Automate Community Video Gallery!

Top Kudoed Authors
Users Online
Currently online: 296 members 2,848 guests
Please welcome our newest community members: