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

update row in excel based on answers in MS-FORM

HI guys,

 

So I have this problem that I would like to solve. My skills are very basic in FLOW.

 

I made a test MS-FORM for this purpose. (2 questions). Question 2 has 4 answers.

form.JPG

 

This is my Flow (very basic, to get the form data into an excel file (on sharepoint) I used the template.

flow.JPG

 

This is my excel file:

excel.JPG

 

 

My problem to solve:

if the answer to the 2nd question is for example "HIGH possibility" then I want the flow to put score 1 in the coloum F (SCORE) in the same row and ID 1.

 

is this possible to do?

Also, what if this score table is in another "Sheet 2"

 

thanks in advance,

regards

 

 

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: update row in excel based on answers in MS-FORM

@cs2301 

Hi there.  Short version:  Yes, totally possible.

Longer version: There are certainly more elegant ways to do this, but to kind of ease into Flow, it might be more fun to use a Switch than to build filters or conditions.

 

Based on what you've put so far, it looks like you are really just "adding" rows rather than going back and updating them with Flow, right?  You can add rows in either table (or both) no problem.  "Updating" might be if you had some information from the form that you wanted to use to update rows that are already in the table. Since your "Score" field probably only has four options, I think the Switch is a good way to warm up.  Switch is like "Condition" but you can have more than just a binary "true/false" scenario.

 

Right after your "Get Response Details", add an action called "Switch".  You'll need to identify which field will determine the path, my guess is it will be the "Does the project have potential" bit, right?  Then, for each of the potential options, just have a different "Add a new row" action that is exactly the same, except maybe the Score would be different.

 

Like I said, there are more elegant ways to do this.  But I think this is a good action to learn and is simple enough for the beginner.  Keep us posted, either way.

 

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

 

View solution in original post

9 REPLIES 9
Super User
Super User

Re: update row in excel based on answers in MS-FORM

@cs2301 

Hi there.  Short version:  Yes, totally possible.

Longer version: There are certainly more elegant ways to do this, but to kind of ease into Flow, it might be more fun to use a Switch than to build filters or conditions.

 

Based on what you've put so far, it looks like you are really just "adding" rows rather than going back and updating them with Flow, right?  You can add rows in either table (or both) no problem.  "Updating" might be if you had some information from the form that you wanted to use to update rows that are already in the table. Since your "Score" field probably only has four options, I think the Switch is a good way to warm up.  Switch is like "Condition" but you can have more than just a binary "true/false" scenario.

 

Right after your "Get Response Details", add an action called "Switch".  You'll need to identify which field will determine the path, my guess is it will be the "Does the project have potential" bit, right?  Then, for each of the potential options, just have a different "Add a new row" action that is exactly the same, except maybe the Score would be different.

 

Like I said, there are more elegant ways to do this.  But I think this is a good action to learn and is simple enough for the beginner.  Keep us posted, either way.

 

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

 

View solution in original post

cs2301
Level: Powered On

Re: update row in excel based on answers in MS-FORM

 

 

Hi Ed, thanks for your reply!

 

I tried to do what you mentioned, I'm lost as to where to put the score I want. "high possibility = score 1"

 

here is a picture of the flow corrections I made

 

SWITCH.JPG

 

 

The switch doesnt really work, ( I know im doing something wrong here)

 

What I was looking to do, is

"high possibility"= score 1,

"low possibility"=score 2 and so on till score 4

 

give me some pointers?

 

thanks:)

 

Super User
Super User

Re: update row in excel based on answers in MS-FORM

Ok, the way you have it set up is good... You'll still want to add the rest of the Forms data to the "Add row" step, but when you say "The switch doesn't work", what do you mean?  Is it not going down the right path?

 

The next step is to add more steps to the right, one path for a score of "2" and one for "3" and then the final "default" can be for "4".  Build those, and then run a test.  If it doesn't behave, we'll want to look at the criteria for the switch.  Something probably isn't lining up, is all.

 

Keep at it.

-Ed-

 

cs2301
Level: Powered On

Re: update row in excel based on answers in MS-FORM

Hi,

yes, the flow works fine (which is from the template, forms data into excel file, as seen in the excel file with data showing from 4 different responses on the form)

 

What I meant by switch not working is that, how do I assign value of "1" to the answer "high possibiity" and so on. right now i have just written 1 in the column score1 as seen in the picture.

 

regards

Super User
Super User

Re: update row in excel based on answers in MS-FORM

That's exactly correct.  You are essentially "hard coding" a "1" into that new row, if the Forms value is "high".  All of the other fields in the row can be dynamic from the Form, but that one field will be a fixed "1" whenever the value is "high".

Doing the same for each of the other values, but changing it to "2", "3", and "4" will mean that the row will have all of the data from the Form, and then the score will be directly connected with the Potential field on the form.

 

Does that help?

cs2301
Level: Powered On

Re: update row in excel based on answers in MS-FORM

hi,

 

yes, exactly. but the answer 1 for high possibility option in the question, as you said, does not appear in my excel file in the column,  only the rest of the coloumn are being filled.

 

regards

Super User
Super User

Re: update row in excel based on answers in MS-FORM

Ah, ok.  Take a look at your table in Excel.  In the image you show above, it almost looks like they are two separate tables which may make things weird.  Try re-making the table with all of the columns included and see if that makes a difference.

cs2301
Level: Powered On

Re: update row in excel based on answers in MS-FORM

HI Ed,

 

big thanks to you to be honest.

 

your solution works perfectly, and im building more on top of it now. I'm learning basic "condition" (and/or) now and using them in my flow, for now i guess it's enough for what I need to do. But, would love to learn more (arrays, etc) to build more complex flows.

 

quick question: where can I learn more about this. Also, will adding lot of these simple or/and conditions increase time of the time? (I have yet to test this). Also, any tips to not increase the time of the flow execution?, what happends when several people around the world use these forms, and the flow triggers every 1 hour, will it increase the time to get data in to excel?

 

sorry for the noob questions, I'm still learning.

 

also, is it okay if I reach out for more questions on this topic to you? (forms>flow>excel)

 

 

Super User
Super User

Re: update row in excel based on answers in MS-FORM

@cs2301 

So glad to hear everything is working out for you.  This community will probably be your best resource for learning.  Most of the heavy-hitters have their own blogs,YouTube, and Twitter...but start with Jon Levesque.  Specifically, the Online Flow Conference from last month has a session at the end from John Liu about speeding up your Flows.

 

Absolutely ok to come with more questions...that's why we're here.  I think this is the best place to post your questions as there's a good chance someone else may have the same question and this affords them an opportunity to learn, as well.

 

Best of luck.

 

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

Helpful resources

Announcements
firstImage

Power Platform Online Conference

Speakers, submit your sessions now! Call for speakers ends Feb. 10!

firstImage

New & Improved Power Automate Community Cookbook

We've updated and improved the layout and uploading format of the Power Automate Cookbook!

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 Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (4,420)