cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MS07
Level: Powered On

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
Community Support Team
Community Support Team

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

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

5 REPLIES 5
Community Support Team
Community Support Team

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

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

MS07
Level: Powered On

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

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

Community Support Team
Community Support Team

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

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.
MS07
Level: Powered On

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

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
Level: Powered On

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

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

 

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"}

Helpful resources

Announcements
firstImage

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 Release Wave 2 Plan

Power Platform 2019 Release Wave 2 Plan

Features releasing from October 2019 through March 2020.

thirdimage

Flow Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Flow Community Video Gallery!

Top Kudoed Authors
Users Online
Currently online: 422 members 4,741 guests
Recent signins:
Please welcome our newest community members: