Creating a flow to pull names dates from excel, create html table, and add table to send an email

[bpws]

I have a spreadsheet with dates and names of employees. I would like to automate a weekly email that notifies dates that are four weeks out from the trip date listed in excel. I have been able to get the columns from excel, create the table, and send the email, but cannot iron out two main issues.

1) The formula for four weeks out from today does not accurately pull the dates (it includes dates in the past). It also does not format the date correctly to the yyyy-MM-dd format. Instructions were taken from this thread.

https://powerusers.microsoft.com/t5/Building-Flows/Send-an-email-based-on-date-in-excel/td-p/1287358

2) When the flow does load, it sends four emails. One for each of the test entries I have.

For the most part, my flow copies the above link. However, if you look at the comments below the solution, I am given the same error that the other users describe for filtering eligible rows "Flow save failed with code 'InvalidTemplate' and message 'The template validation failed: 'The repetition action(s) 'Apply_to_each' referenced by 'inputs' in action 'Filter_array' are not defined in the template.'.'."

[grantjenkins]

@bpws Are you able to show what you have for your flow so far? Also, are you able to show an example of your Excel Table, and the output (JSON) you get from List rows present in a table? To get the output, you can run the flow, then click on Click to download within the List rows present in a table action.

[bpws]

Thank you for the response, Grant. This is my current build. What I have is after banging my head on the wall for a week making and remaking the flow. This doesn't match the link I posted because I was piecemealing together different Powerusers threads that contained portions of what I was trying to do.

Formula 1 - formatDateTime(items('Apply_to_each')?['Date],'yyyy-MM-dd')

Formula 2 - addDays(utcNow(),90,'yyyy-MM-dd')

Below is my desired end state.

1) Pull dates and names from an Excel table on Sharepoint;

2) Filter the table for only the rows that are four and six weeks out from the current date;

3) Format the results to be in yyyy-MM-dd and into an HTML table

4) Put these results in an automated recurring single email every Friday at noon

[grantjenkins]

Notes:

1. Can you confirm what you mean by four and six weeks out?
2. Have you set the DateTime Format to ISO 8601 in your List rows present in a table?
3. Can you click on Click to download in your List rows present in a table and confirm what values you get for your Date column? Is it returning an actual ISO date yyyy-MM-dd or just a number?

In your Filter array you would need to change your formula as shown below:

//Formula 1
formatDateTime(item()?['Date], 'yyyy-MM-dd')

//Formula 2 - keep it as you currently have it - but with whatever days you want
addDays(utcNow(), 90, 'yyyy-MM-dd')

In your Select you can format the date using the following expression:

formatDateTime(item()?['Date], 'yyyy-MM-dd')

You're almost there - just need to work out the Date values and filtering.

[bpws]

@grantjenkins,

- The report is a list of employees that have up coming travel. I want a weekly email reminder of who is travelling in both the next four weeks and the next six weeks - Today()+28 and Today()+42. The issue I have with the date (in addition to the format) is that today "equal to or less than" today()+28 also includes dates in the past. I can't figure out how to specify this date range.

- I do have ISO 8601 selected.

- The current date output looks like "2023-03-01T21:00:00.000Z" and includes dates in the past.

I changed the formula as directed above, but my test flow still sends out individual emails for each test row in the spreadsheet.