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

Get row from excel does not read formula data

Dear experts!

I'm new in Power Automate. I'm trying to insert form submission results into Excel table, then calculate these results with formulas in excel (vlookup for replacing text answer with number, for example). 

So, first I use add_row function, the next step is get_row - the same row that I just added, but already with calculated formulas.

And the result is very unstable. For form submission event it does not work (#N/A is returned), but when I resubmit this flow - it sometimes works and correct numbers are returned.

Could you explain me how to force Excel connector to return correctly calculated data from cells with formulas instead of #N/A?

 

Thanks!

 

1 ACCEPTED SOLUTION

Accepted Solutions

@rsaikrishna , thanks a lot for your support.

 

It seems I found the solution. The issue happened only after form submit and up to half an hour after this. I used for my calculations the same excel file that was connected to the form to store answers.  And every form submission invalidates reading of formulas by excel connector for a long time. I don't know why.  But I found  a workaround 🙂 Now I just use ANOTHER excel file, that is not connected with my form. And it works!

I believe I need to report this bug. 

View solution in original post

5 REPLIES 5
rsaikrishna
Community Champion
Community Champion

@iryna_kavalchuk 

 

Looks like when it is getting the data, it seems to be the excel is not getting latest information. To commit the changes into excel file and refreshing the connection's cache to get the latest takes few seconds.

 

I recommend two options:

Option #1: Create a column in Excel to know if the Row is updated with your changes or not. Example: RowUpdatedFlag. While adding the row, enter value for RowUpdatedFlag column. While getting the row, read the value of this column to check if the Row is ready for reading. If not, keep it in Do Until.  Did not try this option but thinking it may work.

 

Option #2: Place a delay action with few seconds in between Add Row and Get Row actions. The delay action will give sometime for the Excel connection to commit the changes into Excel file and clear the cache in the connection. This is NOT optimal but it will work.

 

Regards

Krishna Rachakonda

If this reply helped you to solve the issue, please mark the post as Accepted SolutionMarking this post as Accepted Solution, will help many other users to use this post to solve same or similar issue without re-posting the issue in the group. Saves a lot of time for everyone.

Thanks for your response!

 

I tried to set a delay between these actions even 1 minute, but it does not help 😞

 

During my last test I inserted a row and then tried to read it by scheduled flow every 10 minutes. It was read successfully on 3rd try (~20-30 min)

Something happens with Excel during this time, but what?...

@rsaikrishna , thanks a lot for your support.

 

It seems I found the solution. The issue happened only after form submit and up to half an hour after this. I used for my calculations the same excel file that was connected to the form to store answers.  And every form submission invalidates reading of formulas by excel connector for a long time. I don't know why.  But I found  a workaround 🙂 Now I just use ANOTHER excel file, that is not connected with my form. And it works!

I believe I need to report this bug. 

I'm having the same problem, can you explain to me in more detail how you solved it?

JoelMills
Helper I
Helper I

Same problem.. Reading formula should be easy but Power automate simply returns zero every time, even when the formula has correctly created a real integer and not just a string...

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 (2,056)