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

4 REPLIES 4
rsaikrishna
Dual Super User
Dual Super User

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

View solution in original post

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

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Users online (1,123)