Creating an email based on excel sheet entries from the last day

[DuncanVicente]

Situation:

We are trying to improve data tracking and reduce steps when marking mail that has been incorrectly delivered as Return-to-Sender. Our current process uses Power Automate to take the results of aa Microsoft Form that is filled out, places it into an excel spreadsheet hosted in a Microsoft Teams group and sends an email to our logistic team that a package has been marked as Return-to-Sender and the courier will need to be notified to pick it up. 

Problem:

Our logistics team has expressed that receiving a dozen + of these automated emails every day is an inconvenience for their team and that they would prefer that we send out a single email at the end of the day with all packages that arrived that day listed. Currently I am doing this manually but would prefer to automate it through a PA flow so it is still done on days I am off without asking someone else to touch (and potentially break) spreadsheets used for other PA flows.

Where I Am Stuck:

I am not sure how I can make Power Automate recognize which entries in a spreadsheet have been added in the last 24 hours at the time the flow runs. The entries do contain the time they were entered into the sheet but I am unsure how I can filter based on this. Alternatively I would be open to something that goes directly from the form to the email if this would be easier. 

Any help would be appreciated, unfortunately I cannot share any images of the Flows as I have them constructed since they include some private internal addresses. 

[David- · Accepted solution]

I thought I had something, but when I tried it, it did not work. My ideas was to use a scheduled flow to get the rows in the spreadsheet with a date filter since you are capturing that information. However, the query filter in Excel does not allow for using ge (greater than). It allows eq (equals), but that probably would not work for you since you want to grab everything created in the past 24 hours.

One thing you might be able to do would be to add two columns to your spreadsheet. One would be tagged from your other flow when the items are added, and the other would be a calculated column in the spreadsheet. Something like this with dummy data generated from the Internet:

The "Processed" column would be set to no by default when you add the rows to your spreadsheet from your other flow. The Row column is a calculated column using the Excel expression =ROW([@[ColumnRef]]) to return the unique row ID.

Then in your flow to generate the e-mail, when it runs you would use a filter query where Processed eq 'No'. Then it does not matter about the date. After you generate your e-mail, you do an apply to each based on the Row value in your get items action and set Processed to Yes so the next time your flow runs, it does not get those items:

[DuncanVicente]

This is super helpful and definitely looks like it will solve what I need it to. In fact I think that this is much better than going by date since if for whatever reason my flow gets broken we will have a record of which have been processed and which haven't rather than guessing by date.

Do you happen to know if I can order the rows I get from the "List rows present in table" function based on more than one column? I would ideally like to order them based on the courier that delivered it, then the location it was delivered to.

[David-]

If you put the output of the Get items action into a Compose action that will create an Array. You can then follow the steps here Power Automate - How to sort an array (tachytelic.net) to sort the array. However, I ran into performance issues when I tried this with larger data sets.