cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MS07
Regular Visitor

Flow to look at Excel file and send email if date matches todays date

I have an Excel file in OneDrive (and can put in SharePoint if it makes a difference). The file has rows of information with one of the columns being a date column. I would like a flow that looks at this file once a day (I'm OK with setting up the Recurrence bit) and if there is a date in the date column that matches today's date to either:

1. Just send an email with a link to the file or

2. Send an email with an extract of the row that contains the date.

 

I have found solutions for using SharePoint lists but am unable to create/amend lists (locked down).

 

Thank you.

1 ACCEPTED SOLUTION

Accepted Solutions
v-yamao-msft
Community Support
Community Support

Hi @MS07,

 

Would you like to send an email with items that date match Today’s date?

Please check the following flow for a reference.

For testing, I added a Button trigger. Please just feel free change it to a Recurrence so that it can run per day.

Add the Excel action Get rows.

Add action Filter query and input the following code in advanced mode to filter out items that date match Today’s date:

@equals(formatDateTime(item()?['date'], 'yyyy-MM-dd'), utcNow('yyyy-MM-dd'))

 

Then add the action Select to select value from previous action. And map column name and date with the following code:

Name:

item()?['name']

 

Date:

item()?['date']

 

Add the action Create HTML table, select Output from the action Select.

Then send an email. Choose Output of HTML table in the Body field, and make sure enable Yes for the Is HTML field.

A screenshot for your reference:

1.PNG2.PNG

The following docs could be helpful for you:

Action Select

Create HTML table

Please feel free post back if you need more help.

 

Best regards,

Mabel

Community Support Team _ Mabel Mao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

13 REPLIES 13
v-yamao-msft
Community Support
Community Support

Hi @MS07,

 

Would you like to send an email with items that date match Today’s date?

Please check the following flow for a reference.

For testing, I added a Button trigger. Please just feel free change it to a Recurrence so that it can run per day.

Add the Excel action Get rows.

Add action Filter query and input the following code in advanced mode to filter out items that date match Today’s date:

@equals(formatDateTime(item()?['date'], 'yyyy-MM-dd'), utcNow('yyyy-MM-dd'))

 

Then add the action Select to select value from previous action. And map column name and date with the following code:

Name:

item()?['name']

 

Date:

item()?['date']

 

Add the action Create HTML table, select Output from the action Select.

Then send an email. Choose Output of HTML table in the Body field, and make sure enable Yes for the Is HTML field.

A screenshot for your reference:

1.PNG2.PNG

The following docs could be helpful for you:

Action Select

Create HTML table

Please feel free post back if you need more help.

 

Best regards,

Mabel

Community Support Team _ Mabel Mao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Thank you. I'll give it a go and report back.

v-yamao-msft
Community Support
Community Support

Hi @MS07,

 

Does it work for you?

If it works for you, please mark it as answer.

 

Best regards,

Mabel

Community Support Team _ Mabel Mao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Sorry for dealy in response. I've not een in for a few weeks.

 

It fails at the Filter Array with the following, error below. My Filter Array is a Value with: @equals(formatDateTime(item()?['DateToRemove'], 'yyyy-MM-dd'), utcNow('yyyy-MM-dd'))

 

Error:

InvalidTemplate. The execution of template action 'Filter_array' failed: The evaluation of 'query' action 'where' expression '@equals(formatDateTime(item()?['DateToRemove'], 'yyyy-MM-dd'), utcNow('yyyy-MM-dd'))' failed: 'The template language function 'formatDateTime' expects its first parameter to be of type string. The provided value is of type 'Null'. Please see https://aka.ms/logicexpressions#formatdatetime for usage details.'.

MS07
Regular Visitor

Its working now - I needed to change date format. Thank you 🙂

 

Can i ask for help with a couple of slight changes please.

 

1: It will send an email even if there is nothing in the spreadsheet for that date (the email is blank so easy enough to ignore) - can the flow be changed so it doesnt send an email if there is nothing?

2: The email that does have the data from the row is not in a great format (example below). Can I get it sent in a better format?

 

{"@odata.etag":"","ItemInternalId":"senqscVJMZA","Your_x0020_name":"xxx","Service_x0020_Request_x002f__x0020_Incident_x0020_Number_x002f_Email":"13245678","UserID_x0020_of_x0020_user":"xxxx","Access_x0020_given_x0020_(Security_x0020_Group)":"ug_test","Date_x0020_access_x0020_given":"2018-12-13T00:00:00Z","DateToRemove":"2019-01-24T00:00:00Z","Time_x0020_access_x0020_to_x0020_be_x0020_removed":"1899-12-30T08:00:00Z","Action_x0020_completed":"Yes","Access_x0020_removed_x0020_by":"xxxx","__PowerAppsId__":"senqscVJMZA"}

Hi,

 

I have a table with 2 columns. Date and description. I want to send the email for the values in table matching with current date. 

 

When I use get rows, then I can see the mandatory fields Key Column and Key Values. After selecting Key Column, when I go to key Value - I am not able to see the dynamic content (no dynamic content available). 

 

Could you please help me resolving this.

Good Afternoon, 

 

I am having the same issue with date:

InvalidTemplate. The execution of template action 'Filter_array' failed: The evaluation of 'query' action 'where' expression '@equals(formatDateTime(item()?['date'], 'yyyy-MM-dd'), utcNow('yyyy-MM-dd'))' failed: 'In function 'formatDateTime', the value provided for date time string '43999' was not valid. The datetime string must match ISO 8601 format.'.

how did you solved it? 

 

Best Regards,

 

Bruno 

rsymmons
Resolver I
Resolver I

I'm struggling with this also. 

 

I have a table with 1 column titled "date" and formatted to use dd-mm-yyyy. 

 

I've created the flow using:

 

1) For selected row

2) Filter array = @equals(formatDateTime(item()?['date'], 'dd-mm-yyyy'), utcNow('dd-mm-yyyy'))

3) Send an email to myself

 

When I add today's date to the spreadsheet I do not receive an email. 

 

Help!

Hi

I solved my issue with a turnaround. Even with the right date format  in the Excel file. Power automated recognizes as a full number number not as a date. 

 

@equals(formatDateTime(addDays('1900-01-01', sub(int(item()?['date']), 2)), 'yyyy-MM-dd'),

 

Follow the discussion in the bellow link:

 

https://powerusers.microsoft.com/t5/Building-Flows/Excel-Online-Date/td-p/134200

 

I hope it helps. 

 

Best Regards, 

 

Bruno Figueira 

Hi,

 

Thanks for the tip. I still haven't managed to get my head around it and not sure how to adapt my array to make it work. 

lisacutforth1
Frequent Visitor

Hi,

 

I am looking for a similar flow but I am trying to get the flow to look at a certain column so if date within column X matches today's date and column Y = no send email.

 

I think I am coming up with problems because of the date formatting - the spreadsheet uses DD/MM/YYYY but the flow keeps finding YYYY/MM/DDTHH:MM:SS 

 

Any help would be greatly appreciated!

 

Thank you 

 

Lisa 

 

lisacutforth1_0-1595584625657.png

 

Hi, 

i am facing the same issue how did you resolved it, please help me with it. 

Hi, How did you resole this isse i'm facing same issue pkease help me with this.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (1,278)