I use a Power App to allow event managers to record information about their events to a SharePoint list. Amending data on the list runs a Power Automate flow. Very simple flow:
1) Checks a column in the list to see if the event is ready to be reported and if not terminates the flow as successful. Otherwise
2) A number of compose steps calculate some summary data and convert dates and money amounts to correct format
3) Email built from a variety of information from the original list and the compose outputs and sent to appropriate mailing list
So far, so good. This all works fine. BUT the Directors used to get a nice, colourful pdf of the data which was exported from a very complex Excel spreadsheet. They like their pdf and want it back. So I have taken a copy of the spreadsheet, cut out most of the tabs except the front page summary, added a table on the second tab that mimics the SharePoint list and pointed the fields and calculations on the summary page to the appropriate column in the data table. That all works OK if there is data in the table. Saved the spreadsheet in SharePoint as a template. Back to the flow.
4) Copy the template spreadsheet to a working template in a neighbouring folder in SharePoint documents - this is the bit that doesn't work! I'll come back to it in a moment
5) Add a row of data to the table in the working copy of the spreadsheet - that works
6) Make a copy of the spreadsheet in OneDrive so I can convert it to pdf and save it back to the working directory on SharePoint as a pdf - that all works (thanks to Wonder Laura
7) I have a copy of the earlier email, exactly the same but only sent to myself while testing and it adds the pdf as an attachment - that all works
Back to step 4) copying the template from one SharePoint folder to another. Sometimes it does and sometimes it doesn't. If it doesn't copy then the data still adds but I end up with multiple rows in the table and that confuses the functions in the summary sheet. And if I change something in the text of the template spreadsheet the change doesn't appear in the working copy. Except sometimes those things do happen. It seems to be fairly random whether the copy step occurs before the add row of data step. I might be barking up the wrong tree but it feels like a timing issue. Sometimes the flow just doesn't have time to finish the copy step before it starts the add data step so it just reverts back to the existing version of the working spreadsheet, rather than starting with a fresh, empty version.
So why not just delete the working spreadsheet after I have finished with it? Good question! I tried it. But then the table doesn't exist when I try to set up the add data step so it doesn't know what fields and columns I am talking about and has a sulk.
Please can anybody help?
