Updating a row in Excel using Forms and Power Automate duplication problem

[Nelly834]

Hi,

I am currently using Microsoft forms to add responses to an excel spreadsheet, once the same individual posts another response power automate updates the excel spreadsheet, this is working perfectly. However when it updates the spreadsheet it also duplicates the row! How do I stop this as I do not want 2 rows with the same information.

Thank you for your help, I am an absolute novice and have only used these systems for 1 day. Please keep it simple if you can.

Nelly.

[gabibalaban]

@Nelly834 ,

Some printscreens with your flow will be helpfull ...

[Nelly834]

[gabibalaban]

@Nelly834 ,

From myunderstanding you have two flows triggered by the same event "When a new response is submitted". What we have to do is to join this two flows into single one.

First of all we need to check the "number" from the completed form is already in your excel file and based on this check we act differently.

The simpliest way to find if the "number" is already in your excel file is to put a Excel - "Get row" action . Unfortunately, this action does not return an empty array if there is no rows with your condition, it fails.

So you have to manage the following actions based on this behavior. Add two parallel branch actions and set the "configure run after" one for success (you can update the record because exists) and one for fail (you need to create a new one). 

Note that this solution will show as failed run when a new item is created (you can add a terminate succeeded to look nice in logs).

Hope it helps !

[Nelly834]

Thank you for the swift response. The problem is the number will not exist in the spreadsheet until it is first added by the person using the form, once it is added it will be updated. so until the user completes the form for the first time the spreadsheet will be blank.

[gabibalaban]

That’s what i said ... if the number does not exists then the get rows action will fail and the add a new row into a table action will create one. 

[Nelly834]

How do I get to that option of splitting them?

[gabibalaban]

The ellipsis button ... on each action give you the menu between update a row and add a row 

[Nelly834]

I understand how to get the extra menu, what I don't understand is how I split the box so you have them side by side like in the Image above.

[gabibalaban]

@Nelly834 

First, create one of the actions,  second click the "plus" sign, third select Add a parallel branch.

[Nelly834]

It still fails, what have I down wrong?

[gabibalaban]

the flow seems ok. After flow runs please check if a new row was created in your file.

If so add a terminate (succeeded) action on the Add a row action branch only to close the flow as succeeded.   

[Nelly834]

After the flow runs it does not create a new row, it says no row was found with the number

[Hardesh15]

@Nelly834 making it simple. After get response pls add one compose and add number from dynamic expression. Then use list row present in a table with o data filter query example excel column (Number) eq compose output. This will give your existing row or empty result if row is not there. Then use condition block and check if list row in a table output is empty use action create row in excel otherwise update row in excel.

Let me know if you need screens . 

Sorry @gabibalaban to pitch in your solution.

Thanks.

Hardesh

[Nelly834]

Screens would be great please

[craigyappert]

Gang ...

Huge point missed ..

He is using MS Forms. You'll never be able to match on Response ID (number) as every time you fill out an MS form, it's a new record by default. You need to match your Excel check based on the submitter's email address. At least you can do this as long as the form is not Anonymous.

[Nelly834]

How do I get them to add email address on forms? 

[Hardesh15]

@Nelly834  pls share your excel sheet one or two row sample. It will help us to understand your problem in detail and will be easier for me to design solution.

Pls give detail about number?

Thanks

Hardesh

[craigyappert]

You can add a required field Email Address (It's really any unique identifier for your excel file) or if the form is within your organization the Settings >> Only people in my organization can respond.

That will give you an additional two objects in the form ... Name and Email Address

[Nelly834]

It is used as a booking in and out system, when someone fills in the form and selects the option in it will book them in on the spreadsheet, once they fill the form out to book out I want it to change the column Booking to show they are now out.