Scenario
Two onsite installation teams are working on a project at different locations and they need to fill out daily reports
An excel sheet has been shared to each team supervisor for filling out the daily report from a SharePoint Document List location
The project manager wants to go to one master sheet to see all of the time logged from the two other sheets
Files
- Excel file called Book1
- Excel file called Book2
- Excel file called Master List
In the example I will be using today’s date (4/16/2022)(The code will use utcNow() which will get your current date when the flow runs)
So anything in Book1 and Book2 with TODAY’S date will be copied to the Master List
How To
To get started first thing that needs to be done is to create a table from all the lists you want to use
Highlight your range of data and click Insert>Table from the menu
If you have headers be sure to check the box then click OK
I did that for the rest of the sheets so now I have two excel sheets with my data and a blank master list setup with tables
Next I will create a Scheduled cloud flow
Let’s say the procedure is the reports need to be done by 10PM
I will set the Scheduled flow to fire off at 11PM each day like so:
Once that is created next I will create an action called List rows present in a table
I will rename this action to identify it easier to “List rows present in a a table for Book1”
I will then fill out the information to point to my Book1.xlsx file and be sure to select Table1 that we created earlier
To get only today’s information I will add the following filter Query:
Date eq '@{formatDateTime(utcNow(), 'MM/dd/yyyy')}'
Next I will create an Apply to each action called Apply to each Book1 with the dynamic content value from the above
@{outputs('List_rows_present_in_a_table_for_Book1')?['body/value']}
After that create an action called Add a row into Master List table from Book1
In this action I will select the MasterList.xlsx File from the SharePoint list
Date: @{items('Apply_to_each_Book1')?['Date']}
Data: @{items('Apply_to_each_Book1')?['Data']}
More Data: @{items('Apply_to_each_Book1')?['More Data']}
Some more data: @{items('Apply_to_each_Book1')?['Some more data']}
I then repeated the above steps but renamed Book1 to Book2 and updated all the fields to point to the correct dynamic content
You can copy an action by clicking the … and selecting Copy to my clipboard (preview)
Now when I click on new action I can go to My clipboard and paste the actions you need to edit for Book2
Once that is all done run the flow and check the MasterList.xlsx (about 10-15 seconds)
If you need the previous or future day then check out the many time functions you can do with expressions:
Reference:
https://www.enjoysharepoint.com/power-automate-list-rows-present-in-a-table-filter-query/
