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:

https://docs.microsoft.com/en-us/azure/logic-apps/workflow-definition-language-functions-reference#date-and-time-functions

Reference:

https://www.enjoysharepoint.com/power-automate-list-rows-present-in-a-table-filter-query/