Answered

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

(0) Share

Report

Posted on by 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.

Categories:

Building flows

I have the same question (0)

All responses (13)

Answers (1)

Verified answer

v-yamao-msft on at

Like (1)

Report
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

Was this reply helpful? Yes No
MS07 8 on at

Like (0)

Report

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

Was this reply helpful? Yes No
v-yamao-msft on at

Like (0)

Report

Hi @MS07,

Does it work for you?

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

Best regards,

Mabel

Was this reply helpful? Yes No
MS07 8 on at

Like (0)

Report

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.'.

Was this reply helpful? Yes No
MS07 8 on at

Like (0)

Report

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

Was this reply helpful? Yes No
milindh 6 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
BrunoFigueira 4 on at

Like (0)

Report

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

Was this reply helpful? Yes No
rsymmons 222 on at

Like (0)

Report

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!

Was this reply helpful? Yes No
BrunoFigueira 4 on at

Like (0)

Report

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

Was this reply helpful? Yes No
rsymmons 222 on at

Like (0)

Report

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.

Was this reply helpful? Yes No