Get row from excel does not read formula data

[iryna_kavalchuk]

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!

[rsaikrishna]

@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 Solution. Marking 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.

[iryna_kavalchuk]

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

[iryna_kavalchuk · Accepted solution]

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

[Cim]

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

[JoelMills]

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

[LucasBringas32]

Hi! I have a similar problem... insert form submission results into Excel table, then calculate these results with formulas in a diferent excel file but #VALUE! is returned. Could you explain me how to force Excel connector to return correctly calculated data?

Thank You!!!

[PhanCongQuyen]

hi @iryna_kavalchuk 
I'm facing the same issue. Please kindly explain in more detail your solution.
Thank you very much.

[PhanCongQuyen]

hi @Cim .
Have you fixed this issue? If it yes, hope you share to me your solution.
Thanks!