Power Automate Exchange

Please login or click SIGN UP FOR FREE to create your FlowUG account to join this user group.
 View Only
Expand all | Collapse all

Flow and excel problem

  • 1.  Flow and excel problem

    Gold Contributor
    Posted Oct 17, 2019 05:02 PM
    ​I posted this problem in the Flow community Forum,  but as yet there has been now real answer.

    I have one flow that currently goes for 8 Hours every day. I runs it from 4pm. I would dearly love it to be less, but it sends a an individual email to our 29 stores with their current list of open orders in an excel spreadsheet.

     

    I need to do this as I cannot put our system onto the Clients network, they are all blocked from office 365 Cloud platforms.

     

    My problem is the flow takes an excel template deletes any rows and then adds rows applicable to the store. It then send s it as an attachment to an email to that store.  The process loops ad it does this again.  Some larger stores can have over 1000 rows in the spreadsheet

    Everything was going fine for months,  but recently I started getting fails based on the delete row action.  The message was that the row ID does not exist.  When I look at why,  as the ID is unique,  I find it did exist but was deleted earlier in the process.  Now for some reason it looks for that row again.  This is not consistent,  it can be the next part of the process or a number of records later.

    I have found a work around by putting a number iterations based if the action fails.  In this particular flow it can fail up to 7 times but I get it to complete

    I have a number of smaller flows that do something similar in sending spreadsheet orders to suppliers and I am noting thise are starting to show the same problem

    Any ideas?



    ------------------------------
    Mark Pearson
    National Operations Manager
    61409321547
    ------------------------------


  • 2.  RE: Flow and excel problem

    Top Contributor
    Posted Oct 18, 2019 03:06 AM
    Why do you need to delete rows from the template excel file? Could your template not contain empty table(s).

    Have a look at asynchronous processing in your apply to each loops, it's off by default. You could significantly shorten your processing time if you can sacrifice the sort order of the table. Look in the settings of the apply to each action.

    You might also be able to create 29 parallel branches to speed things up.

    ------------------------------
    Will Page
    Technical consultant
    Christchurch
    ------------------------------



  • 3.  RE: Flow and excel problem

    Gold Contributor
    Posted Oct 18, 2019 03:32 AM
    Thanks Will,  but 29 Branches means 29 templates.  In all case there is a need to have the delete row action as the template needs to be either cleared or emptied after.  I will look at the asynchronous  and see if that helps

    ------------------------------
    Mark Pearson
    National Operations Manager
    61409321547
    ------------------------------



  • 4.  RE: Flow and excel problem

    Top Contributor
    Posted Oct 18, 2019 04:01 AM
    You can have one template.

    At the beginning of your Flow, get file content from SharePoint or OneDrive, then in each of the 29 branches, create file 29 times with 29 different names or locations using the content from the original get file content action, and run your array operations on those new files all in parallel.

    When you're done you can delete the 29 files, or move them/rename them/archive them whatever, and start again the next day from the blank template.

    ------------------------------
    Will Page
    Technical consultant
    Christchurch
    ------------------------------



  • 5.  RE: Flow and excel problem

    Posted Oct 18, 2019 10:30 AM
    Edited by Leslie McKenzie Oct 18, 2019 10:41 AM
    It will definitely be easier if you save multiple copies of the template (one for each store) as a new file, make your edits, then email each file to each store.  As said above, you could do this using parallel branches which may cut down on run time since the actions would all run at once.  You only need to start with the one template.  The easiest way would be to create a folder to hold all the copies like temporary files, then delete the folder at the end.  Sort of like:

    - Start with template
    - Create a new folder in an out-of-the-way spot
    - Open 29 branches
         - In each branch, create a copy of the template to start with
         - Run actions specific to the store file, save, and email
         - Close your branches
    - Delete the folder holding all the temporary files (or if it were me, I would save this folder in case of disputes from your stores in the future; set a timer for 30-60 days then delete the folder)







  • 6.  RE: Flow and excel problem

    Gold Contributor
    Posted Oct 18, 2019 04:17 PM
    I want to thank everyone for their input.  I the problem is much deeper.  Things like copy file creates issues for the process as the flow to add records cannot find the resource to add.  I think that relates to a timing issue.  I did also try creating the files first so a file did exist,  but same problem.  It may relate to file ID may not actually be the same after the copy.  The flow will be looking for the old ID?   When it comes to excel on OneDrive,  the action does not like dynamic (variable) type paths and I assume that is because it cannot see the table in design time.

    Adding branches makes no difference to the delete row failure (random an annoying)

    Turning Off asynchronous cause a catastrophic failure with an inability to fix the flow

    Where I never got this failure before,  I did on my rebuild.  Basically the list rows in table failed now as there was a mismatch in columns. I had 4 hidden Columns.  I unhid to get rid of the problem

    Creating 29 branches is not really ideal as it needs to be dynamic with no user input if possible. EG: We added 4 new stores yesterday which would have meant 4 new branches.  Using the loop means less work and greater flexibility.

    With a loop, using the same template is the only option, as finding sheets with a variable does not work.  Flow fails based on invalid path.

    It is one of those journeys I need to travel. At the moment my work around on the delete row failure keeps me sane,  it is just not satisfactory to have failures during a process if it can be helped.





    ------------------------------
    Mark Pearson
    National Operations Manager
    61409321547
    ------------------------------



  • 7.  RE: Flow and excel problem

    Posted Oct 18, 2019 04:30 PM
    Edited by Leslie McKenzie Oct 18, 2019 04:32 PM
    Hi Mark, it is absolutely possible to take an action on the copied document in a loop.  However, after you copy the document, all of your subsequent steps must use the ID from the output from Create File action and not the ID from the original template.  I thought you were saying that every store has a different way to manipulate the template, which is why I recommended branches to customize individually.  Instead, you are probably wanting a For Each loop.

    Try the below and if you still have issues then screenshots would be helpful.

    - Start with template
    - Create a new folder in an out-of-the-way spot
    - Open a For Each loop
         - Create a copy of the template to start with
         - Run actions specific to the store file (Using NEW file ID), save, and email
         - Close your for each loop
    - Delete the folder holding all the temporary files (or if it were me, I would save this folder in case of disputes from your stores in the future; set a timer for 30-60 days then delete the folder)

    ------------------------------
    Leslie McKenzie
    ------------------------------



  • 8.  RE: Flow and excel problem

    Gold Contributor
    Posted Oct 18, 2019 07:48 PM
    I completely understand the copy file process and have tried a number of variations with this,  but this is what I found

    1.  If I copy the file to folder I cannot map the Add Rows to an excel table in a design time if the file does not exist.  Add rows requires this data and it will not take variables.  Logically it gets the file so you can manage the data to be put in the file

    2. So if I create the file and have it there and then do a file copy to from the template create a blank table,  the flow fails as the copy file cannot do the action as it says it is already open and in use for editing.  When a flow starts I assume it locks all files it is going to access so that prevents the copy action over the pre existing file

    Hope all that makes sense. So unless I find a way other than the delete row in table action,  I am restricted to this at the moment


    ------------------------------
    Mark Pearson
    National Operations Manager
    61409321547
    ------------------------------



  • 9.  RE: Flow and excel problem

    Gold Contributor
    Posted Oct 19, 2019 03:40 PM
    As an addendum,  I tried another work around based on copy and parallel branches.   I created two flows to separate teh copy process with the add rows to table.

    My testing showed that the copy files was no worries and did as intended. I first ran this to create the 33 files I required for the second flow.  I then built the second flow and as the files existed was able to do the Add Row action.   When I ran this the first time it worked, so I thought we may have a winner.

    So next test - ran copy files  - worked.  Ran second flow - Failed.  It failed because as the first flow copied over the first files again,  this impacted teh mapping to the table in the Add Row Action of the second flow

    ------------------------------
    Mark Pearson
    National Operations Manager
    61409321547
    ------------------------------



  • 10.  RE: Flow and excel problem

    Gold Contributor
    Posted Oct 20, 2019 02:21 AM
    Ok,  for those that are interested I at least found a simpler work around to the delete row failure that made process one definitive loop.  Using two integer variables,  StartCount set to 0 and the other CountRows set to number of rows that are to be deleted

    Then creating an action of Do Until based Variable Startcount to Equal CountRows.  Then put the Actions:

    1. List Rows present in Table
    2. Delete rows based on the list
    3. Set Variable countrows to equal the new number of rows in the Table at the end of the Delete.

    When the countrows variable equals 0 then the table is empty

    This effectively meant that when the Delete rows met with an inability to delete a row,  the loop started again


    ------------------------------
    Mark Pearson
    National Operations Manager
    61409321547
    ------------------------------



  • 11.  RE: Flow and excel problem

    Posted Oct 21, 2019 10:22 AM
    Will the following work as an alternative solution:
    1. create Excel file that has Get and Transform Query embedded with store code as a filter, pointed at your source, so that on refresh only records relevant to this store will be pulled in.
    2. Simplest case is as many files as you need for stores with store codes hard coded, flow goes through them one by one, refreshes and sends to stores. More sophisticated version will be using a loop with store code as a parameter, using same template Excel file to create 29 updated files.
    Let me know what you think.




    ------------------------------
    Vlad Samoilov
    GroupM
    ------------------------------



  • 12.  RE: Flow and excel problem

    Gold Contributor
    Posted Oct 21, 2019 03:30 PM
    "flow goes through them one by one, refreshes and sends to stores"

    I do not know of a process where flow can simply refresh a spreadsheet and data updates?  I belive the sheet needs to be opened for that to happen


    ------------------------------
    Mark Pearson
    National Operations Manager
    61409321547
    ------------------------------



  • 13.  RE: Flow and excel problem

    Top Contributor
    Posted Oct 21, 2019 04:22 PM
    It's taken me a few days to get back to this thread. I understand that if you copy the file in the Flow, after the create file step, you can't then add an insert rows action on the file because you have to be able to physically browse to an existing file before you can select the name of the table and subsequently the columns to put the data in.

    Couple of ideas you may have already tried... If you do that for an existing Excel file; Browse to the file, pick the table from the drop-down and add dynamic content or expressions to the rows within the table, then after that, you delete the file and replace it with a template with the same file name, table name and schema, does it work on the next run?

    Secondly, this kind of flow would be better suited to Logic Apps anyway. If you were to import this into Logic Apps and go to code view, can you copy-paste the code for a working insert rows action elsewhere in the logic app, and change the file name appropriately? That might work.

    If you want the process to be able to adapt to a changing number of stores you do have to put everything into an Apply to Each (or For Each in logic apps) like you are, but the thing that's stopping you put that outer loop into asynchronous mode is the fact you have to work with one file in sequence. If you can nail that you'll cut your processing time down to about 5% of what it is now.

    ------------------------------
    Will Page
    Technical consultant
    Christchurch
    ------------------------------



  • 14.  RE: Flow and excel problem

    Top Contributor
    Posted Oct 29, 2019 11:21 PM
    @Mark Pearson I'm sure you're well and truly past this now, but I recently had the same challenge so I blogged by findings. Hopefully it's some use to you.

    https://willpagenz.wordpress.com/2019/10/30/excel-online-adding-rows-into-a-table-from-a-template/ ​​​

    ------------------------------
    Will Page
    Technical consultant
    Christchurch
    ------------------------------



  • 15.  RE: Flow and excel problem

    Gold Contributor
    Posted Oct 30, 2019 01:28 AM
    Thanks Will,  interesting blog,   well worth the read.  I do not seem to have the issue with add rows (touch wood)

    Mine was delete rows and I have successful got rid of the problem but did need to tweak my previous post some what as follow:

    Prior to doing the DO Until I needed to create two integer variables with one set to 0 Then

    1.   List rows in table
    2.   Create a filter array based on the list with my KeyID not eaqual to null
    3.   Set variable (1) to equal the row count of the Filter array
    4.   Do unitil variable (1) is equal to the other variable which is 0
    5.   List rows in table 
    6.  Delete row in table based
    7.  List rows in table (Set run after on fail of the apply to each, success and time out)
    8.  Filter Array on 7
    9.  Set Variable (1) to length of filer array

    The above now gives me a greater way to review.  My latest run had one list of 1200 rows.  The process had three errors but in the end still worked like a charm

    ------------------------------
    Mark Pearson
    National Operations Manager
    61409321547
    ------------------------------



  • 16.  RE: Flow and excel problem

    Posted Oct 21, 2019 10:29 AM
    A possible workaround idea...  if you have SharePoint you may consider manipulating all of the data through one or more SharePoint lists and then creating the final Excel from that data.  (Would be helpful to see screenshots of the errors though!)

    ------------------------------
    Leslie McKenzie
    ------------------------------



  • 17.  RE: Flow and excel problem

    Posted Oct 24, 2022 04:04 PM

    Hello,

    For anyone with similar issues...

    Seems like there are several good suggestions through the thread for more efficient re-designs of the flow. But if some of those are not possible for any reason, I have a more brute-force solution to all this by just making any Power Automate Excel CRUD operation much faster & more efficent.

    Excel Batch Upsert: https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/Excel-Batch-Create-Update-and-Upsert/td-p/1624706

    Excel Batch Delete: https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/Excel-Batch-Delete/m-p/1634375#M735

    If you're able to pick these up and Create, Update, or Delete 500-1000 rows a minute, then perfecting all the specifics of the use-case may not be necessary.



    ------------------------------
    Tyler Kolota
    Power Platform Developer
    ------------------------------