I have a very similar flow that I use to generate excel documents for hourly billing each week. The flow gets some info from powerapps, gets data from a SP list, then does some fancy looping and var manipulation to populate tables in an excel sheet that was generated from a template file.
First thing I did was make an excel template with matching columns to my SP list. I formatted it and added any other calculations I wanted it to have.
Next I made a flow that was triggered on click in a power app. The reason I did this is b/c the finance team choose a start date and an end date then submits the flow. the flow takes a start date, an end date, the user's email that submitted it, and the list of names for reports that need generated.
In this first image you can see I set a ton of variables as well as parse the JSON string I sent to the flow from power apps. Here are the variables I use and why:
UserEmail: this stores the email address of the submittor of the flow and also is who the excel sheet will be emailed to when the flow completes
JSON Start String: this is a JSON object/string sent from power apps using the JSON function
Parse JSON: this takes the schema I generated and parses the output of the JSON Start String step above
TodaysDate: this is the end date for my flow. used to filter out entries that the user doesnt care to see.
TwoWeeksAgo: this is the start date for my flow. used to filter out entries that the user doesnt care to see.
TravelVar: this holds the string "Travel" as I use it later to filter out the fields that are travel types
CurrentAuctionName: name of the current auction that I am totaling for the current user
AuctionTotalText: This is a string for the auction totals that will be build and added to the email body at the end
CurrentAuctionTotal: this is the total hours spent on a single auction for the current user
CurrentIndexPosition: this is an index variable that starts at 0 and is used to know when I am at the end of the double loop that I have to make this work. This helps get the last entry into the excel table correct.
NumberOfEntries: this is a var that is the size of the array we are looping through. Used along with CurrentIndexPosition to confirm if we are at the end of the loop or not.
After these steps I start my outer apply to each 'loop'. The input for this step is the output of the parseJSON step above. Here are the steps for the outer apply to each 'loop':
Get Items from SP: I use this to get the items from the TimeLedger SP list and filter the results to only get entries where the name matches the current user and the end date is not equal to null. If an end date is equal to null then the user didnt clock out and that needs to be handled by not adding it to the paycheck. During this step I also sort/order by Auction name so that the rows with similar auction names are next to eachother. This is a very important step.
Next I set the auction name variable to the auction name of the first item in the apply to each loop.
Next I set the number of entries variable to the length of the get items output array.
Next I get my excel template from the SP site
Next I create a new file using the SP connector again. make a name and pass in the body of the get template step as the file content for this step. the name of this should end with .xlsx if you want it to be an excel document.
This is where I do the inner apply to each but i will skip it for now and finish the outer apply to each loop first.
Now I have a delay timer for 1 min. This allows the flow time to open the file
Now we get the file content using the sharepoint connector as well. The file ID will be the ID from the create new file step.
Last step is to send an email to the user that started the flow. The advanced options show the area to add an attachment. The name will be the name output from the create a file step and the content will be the body of the get file content step.
Now for the inner loop where all the magic happens... bear with me if your not lost already bc this took a while for me to figure out and get working. the inner loops input is the ouput from the get items step inside the outer loop.
This loop starts off with a condition that checks to make sure that the current item has a valid date that is greater than the start date sent by the user and less than the end date sent by the user. If this condition fails nothing is done in the IF NO side. If this passes then in the IF YES side I do another condition that checks to see if the current items auction name is equal to the variable current auction name. If it is then we know that we are still on the same auction and can keep adding to the variables. IF it is I first compose the current item into a form taht matches the excel sheet, then add a row to an excel table, then increment the current auction total variable by the hours of the current item. If the auction name is different then we know we have ended an auction for that user and so its time to record that and then reset the variables for the next iteration of the outer loop. The steps for this are as follows... First I compose the auction name and add that row to a table in my excel sheet that tracks auction and the total hours for that auction. next I compose the item for the entry into the ledger table in the excel sheet. This is then added to the excel table. I then concat the current auction name and total to the variable auction totals text. I then reset the variables current auciton name and current auction hours to the value of the current item in the apply to each step.
Last of all and OUTSIDE of the 2 conditionals I have a 3rd conditional that checks if the current iteration is equal to the number of items variable. If its not then we do nothing. If it is then it means that I now need to add the current auction and time entry to the excel tables before the flow ends. this is because that is all done in the step that checks if a new auctino name has been found but we will never reach that step to use it to record the data as this is the last item. This step is also important bc this is when we reset the index variable to 0 to let the next users excel sheet start to be built.
I know that was a lot but here are some images if they help and an image of hte template that I use along with one of them i got in the email.
