cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Lexicron
Resolver II
Resolver II

Emailing an Excel file with a table only emails the first row

Same as this question but the solution there isn't helping.

 

My flow is as follows:

  1. Triggered by a button in Power Apps
  2. List rows present in a table (Excel file hosted on OneDrive Business)
  3. Apply to each row above, delete the row (resets the Excel file to empty)
  4. List rows present in a table again (same Excel file)
  5. Compose, find length of value of #4 (number of rows in the Excel file)
  6. Condition, check that Compose #5 = 0 (confirm that the deletion in #3 was complete)
    1. If No, do nothing
    2. If Yes,
      1. List records in my CDS entity
      2. Apply to each record above, add row to a table (same Excel file that was emptied)
  7. List rows present in a table again (same Excel file)
  8. Compose, find length of value of #6.2.1 List Records (number of records in the CDS entity)
  9. Compose, find length of value of #7 (number of rows in the Excel file)
  10. Initialise variable "Timer" = addSeconds(utcNow(),20)
  11. Delay until Timestamp = Timer from above
  12. Condition, check that Compose #8 = Compose #9 (confirm that adding rows was complete and the Excel file is ready with all data present)
    1. If No, do nothing
    2. If Yes,
      1. Get file content using Path (same Excel file)
      2. Send an email with attachment same Excel file from above

 

My CDS entity always has more than 1 record so this is definitely an issue. Right now it has 5 records.

 

When I check my run history both Compose #8 and Compose #9 always matches, so Condition #12 always goes to Yes. Compose #9 is always the correct number I want and it's never 1, which should mean my Excel file is fully populated and ready. I've even added a wait timer at #10 to give OneDrive a bit more time AFTER it's already reported having the correct number of rows. 

 

But when I get the file content, the email attachment always just has 1 row in it.

 

WHY? I don't get it. There are no further changes to the Excel file after adding rows at #6.2.2. Flow is telling me it has 5 rows. If it has 5 rows at #7 List rows, it should still have 5 rows at #11 Delay until, and it should still have 5 rows at #12.2.1 Get file content. I've bloodied CHECKED and CONFIRMED that it has 5 rows AND I've given it extra time BEFORE doing get file content/send mail, and yet somehow when it's getting file content, waaaaay at the end after alllll the checks and balances, it goes down to 1 row only.

 

The solution given in the linked post at the top said to add a minute delay. If it comes to that sure I'll do it but I'm not happy about it. My flow without the 20s wait timer takes a mere 10s! Adding 20s is already a 200% loss in efficiency.

 

Alternatively, is there a way to force the Excel file to save or refresh or recache or whatever, so that any delay I need to add just needs to be what it takes to save a file, and not an arbitrary number of seconds that we think should be enough for Get file content to grab the right contents?

3 REPLIES 3
JohnAageAnderse
Super User
Super User

Hello @Lexicron 

Did you find a solution to your issue or is it still relevant?

Please note that there are several limitations to using Excel Online (OneDrive) that you should be aware of.

Let us know how it went.

Kind regards, John

Lexicron
Resolver II
Resolver II

Thanks John.

 

Yes the issue is still present, I'm using delays as a workaround to avoid it. I believe it is due to the known issue:

  • An Excel file may be locked in OneDrive for an update or delete up to 12 minutes since the last use of the connector.

 

When I monitor the file in OneDrive directly it is populated as required in real time, but when Flow picks the file up using the OneDrive connector it picks up some twillight zone file in a limbo state that doesn't reflect what I'm seeing directly in OneDrive.

 

I am disappointed at finding out that the potential delay is up to 12 minutes (in that 12 minutes is a very long time). My workaround delays currently only sum up to 2 minutes. If I get feedback from my users that 2 minutes is no longer sufficient then I will change the email to simply sending a download link to the file instead of trying to attach it using a connector.

Hello @Lexicron 

Have you tried to replace step 2 and 3 (list rows and delete them) by using one Excel file as the template, copy it, and then use the new Excel file to just add your data? No need then to first delete existing rows, as there are none.

 

Could you explain why an Excel file is needed? Like is it a requirement from the recipient. Could maybe a CSV file be used instead?

Just some ideas, hope it could help finding a solution

Kind regards, John

Helpful resources

Announcements
MPA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Users online (1,250)