cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Rewriter2
Frequent Visitor

Update Excel spreadsheet from Forms response

I am trying (and failing) to create a flow that takes a Forms response and either updates or adds a row to an excel spreadsheet based on the value of 'Vehicle registration'. 

 

I've looked at the other Forum posts and have had no joy... I am new to the O365 world and particularly the Forms and Flows!! 

 

The feilds are as below.. the idea being is that a form is submitted every week for a 'vehicle check' when completed. if the registration is already on the table all I want to update is the rest of the fields.... 

If the registration is not on there then I would like the flow to create a new row and populate the fields. 

Table 1.PNG

 

And this is the flow as I have it so far, however it fails to complete each time...

Flow 1.PNGFlow 2.PNG

Thanks in advance! Any guidance is greatly appreciated!! 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Gristy
Resident Rockstar
Resident Rockstar

I created the following spreadsheet:

1.png

 

I created the following form:
2.png

 

I created the following flow - get rows fails if the row does not exist - so if you expect new entrys make sure to configure run after failed.

 

flow2.png

 

 

 

 

I then added a response and tested the flow: 1234 and it got added to the spreadsheet.

 

updated.png

 

I then submitted the same registration again and as you can see data got updated

 

row.png

 

 

There may be a better way to check if a row exists, as this flow will fail each time a new record is added - but im sure you can figure that out 🙂

 

View solution in original post

13 REPLIES 13

Hi @Rewriter2 

 

You will have to modify the filter query as --

excelcolumnname eq 'select the form attribute from dynamic selector'

 

Ensure that the value selected from the dynamic selector is wrapped in a single quotes. Also, the 'excelcolumnname' needs to be replaced with actual name of the excel column that you have in the excel sheet for this particular attribute. 

 

Note that if there are spaces between the name of the excel column, e.g., 'excel column name', the query will have all the spaces replaced with '_x0020_' and the query will be excel_x0020_column_x0020_name eq ''. 

 

Hope this Helps!

 

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

Gristy
Resident Rockstar
Resident Rockstar

I created the following spreadsheet:

1.png

 

I created the following form:
2.png

 

I created the following flow - get rows fails if the row does not exist - so if you expect new entrys make sure to configure run after failed.

 

flow2.png

 

 

 

 

I then added a response and tested the flow: 1234 and it got added to the spreadsheet.

 

updated.png

 

I then submitted the same registration again and as you can see data got updated

 

row.png

 

 

There may be a better way to check if a row exists, as this flow will fail each time a new record is added - but im sure you can figure that out 🙂

 

@Rewriter2 

 

There are a couple of ways to do this. As @Gristy  has mentioned, you could use the get row too. Just add the terminsate as success in your flow under the add a row into a table action and the flow will not register as failed. aa.PNG

 

Hope this Helps!

 

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

Gristy
Resident Rockstar
Resident Rockstar

didnt know that action existed thats what i was looking for!

Hey @Gristy !

 

It's actually pretty cool. You could do some real good exception handling and error logging kind of stuff with that. Explore it out and you will see it for yourself! Let us know what you build with that and any exciting stuff that you are doing!

 

Thanks, 

Yash. 

Thanks for all the replies so far!

I think im slowly getting there, what I have so far is form submission which updates the spreadsheet if the registration is already there. 


I cant get the add row function to work and cant seem to add the terminate function or work out where it would go within the 'apply to each'. 

 

Flow 1.PNGFlow 2.PNG

Ultimately what I would like (and I dont know if it is possible). 

Is that the Flow would recognise which vehicles are due for service and send an automated email with certain values from the table included in it. 

 

Sam 

 

 

Gristy
Resident Rockstar
Resident Rockstar

Hi

Your flow is still wrong mate

You need to remove the condition and add a parallel branch like my flow

On the right branch click the options and choose configure run after failure - you will get the red line like mine. Then put the terminate complete on the right branch:

Your other description would be a seperate scheduled flow.
Rewriter2
Frequent Visitor

Im getting stumped by the 'apply for each'.... i cant create the flow without an apply for each being used and i cannot add the terminate within an 'apply for each'.

 

complicated stuff this!! 

Gristy
Resident Rockstar
Resident Rockstar

apply for each definately not needed.

 

Recommend starting over and configure as per my picture and you should be good mate :).

Rewriter2
Frequent Visitor

Yours is configured within an 'apply to each'. Flow will not allow a terminate to be put within one of these and you cannot populate all of the fields without creating the flow with the 'apply to each'.. 

 

Maybe its me being stupid, or tired as its 0536am....

 

Gristy
Resident Rockstar
Resident Rockstar

no just me being stupid at work!

 

just put the terminate after the apply to each and set it to success, this way the flow will always suceed if it gets to the end.

v-alzhan-msft
Community Support
Community Support

Hi @Rewriter2 ,

 

The "Get a row" action and the "Update a row" action wouldn't work if there are more then one row in your table have the Vehicle registration value the same as the submited Vehicle registration value.

Please check from all your rows in the table and make sure there is only one row has the same Vehicle registration value

You could just add a "filter array" action under the "list rows present in a table" action as my screenshot below to add the Condition to check the length of the Body from the Filter array and the flow would work:

1.png

Best regards,

Alice       

 

Community Support Team _ Alice Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

I know this is a little stale, but I wanted to let you know I looked at a half-dozen different examples of how to implement an "update and existing row or add new row" flow, and your solution was by far the simplest. Thank you.

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

New Ideas Forum MPA.jpg

A new place to submit your Ideas for Power Automate

Announcing a new way to share your feedback with the Power Automate Team.

MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

MPA Licensing.jpg

Ask your licensing questions at the Power Automate AMA!

Join Priya Kodukula and the licensing team, super users and MVPs to find answers to your questions on Power Automate licensing.

Users online (1,590)