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

[MS07]

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.

[v-yamao-msft · Accepted solution]

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:

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

[MS07]

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

[v-yamao-msft]

Hi @MS07,

Does it work for you?

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

Best regards,

Mabel

[MS07]

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]

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

[milindh]

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.

[BrunoFigueira]

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]

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!

[BrunoFigueira]

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 

[rsymmons]

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. 

[Anonymous]

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 

[RudraJaiswal]

Hi, 

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

[RudraJaiswal]

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