Create a Flow that reads Data of an Excel and sents a Reminder-Mail

[Daniel_B]

Hi,

I'd like to create a Flow that reads data from an Excel sheet, specifically the timestamp (1) and the user (2). If the timestamp is older than 7 days, I want to send a reminder email to the user. In the first step, I've already created a flow that captures data from a Microsoft Forms survey and records it in an Excel table (see Screenshot1).

Now, I've started working on another Flow to read data from the Excel table and check if the most recent timestamp for User X is older than 7 days. If it is, I want to trigger a reminder email; otherwise, nothing should happen. The problem I'm facing right now is that I'm unsure about what to specify in the conditions. On the left side, I have the variable (timestamp), and on the right side, I've used the code "addDays(utcNow(), -7)" to represent "older than 7 days." However, when I tested this condition, it always resulted in the same outcome regardless of the data in the Excel sheet (I even tried switching from <7 Days to >7Days )

I hope this explanation clarifies my issue. I apologize that the screenshots are in German. If there's anything specific I should translate, please let me know.

Thanks

[creativeopinion · Accepted solution]

@Daniel_B You should use a Filter Array action instead. The way you currently have your flow set up means that you will loop through all items in your Excel table checking each item to see if the timestamp is older than 7 days. This would be inefficient especially as your Excel table rows increase. To learn more about how to use the Filter Array action, please reference this YT tutorial that I uploaded a few weeks ago. 

The reason that your condition isn't working is because you are comparing a timestamp. Depending on when you are running the flow, your condition argument is looking to see if the items in your Excel table are 7 days from the current date/time the flow has run. Instead, you should check to see if any items in your Excel sheet have a date that is 7 days old (not including the time). 

Another thing to note is the timestamp that is stored in your Excel sheet. Are you storing the UTC timestamp or a timestamp in your local timezone? This will effect the condition argument and you may need to make adjustments to your flow accordingly. Keep this in mind when running your flow while testing. This is why Compose actions and reviewing your outputs will be absolutely crucial when building your flow. 

Limit the Rows Returned

I'm unsure of how many rows you have in your Excel table. I would recommend limiting the number of rows returned by using the Top Count field in the List Rows Present in a Table action. Alternatively, you can create a test Excel Table that includes a few examples with dates older than 7 days from today. 

Select the ISO 8601 DateTime format. Otherwise the date returned from this action will be a number instead of a date. 

Optional: Add a Compose action to store the outputs from the List Rows Present in a Table action. I find it easier to view the content right in the outputs. 

You can click to download the outputs from the List Rows present in a table action if you prefer. 

Compose a Dynamic Date

Add a Compose action to store the dynamic date (7 days ago). 

I'm not sure what the format of your date in Excel is, but you need to confirm this from the outputs of your List Rows present in a table action or in a Compose action (as I've demonstrated above). 

Your expression was correct though you need to format your date to match the output from Excel. 

addDays(utcNow(),-7,'yyyy-MM-dd')

In my Excel output example above the timestamp looks like this:

2023-08-05T00:00:00.000Z

We'll use the Filter Array action to check for items that are greater than 7 days ago from today. To do this, we'll need to see if any rows have a date value (regardless of the time) are greater than 7 days ago. 

Filter Array

The List Rows Present in a Table action returns an array of the rows in the Excel table. By using a Filter Array action you can filter out the items that meet your criteria rather than unnecessarily looping through all rows in your Excel table. 

Because you are storing the timestamp in Excel you will need to format that value so it only returns the date (formatted the same way as the dynamic date above). 

You'll need an expression for this. Insert the formatDateTime() function. 

Unfortunately when use an expression, the dynamic content from the List Rows Present in a table action arent' available. 

You'll need to use the item() function and a key to access the column that is storing your timestamp. You will need to reference the outputs from the List Rows Present in a Table action. All the keys for the content is the red text between the double quotes.

The key for my column is Task Due Date. Yours will be different.

You need to insert:

item()?['']

between the formatDateTime() function. The key will go between the single quotes—exactly how it appears in the outputs.

My expression looks like this: 

formatDateTime(item()?['Task Due Date'])

Yours will look different. 

Depending on your criteria you can choose the is less than or is less than or equal to operator. In the second value field, insert the outputs from the Dynamic Date Compose action.

Important: Refer to this section of my YT Tutorial that covers how to return dynamic content from a Filter Array action. It will be helpful to review if you need to return values in an action later on in your flow. 

Count Items

Add a Compose action to return the number of items returned from the Filter Array action. If no items are returned, you can stop the flow. Otherwise you can continue on with your flow. 

Use the length() function.

Insert the Body output from the Filter Array action.

Run a test!

Add a Condition

Add a Condition action to your flow. Insert the outputs from the Filter Array Count action (above) into the first value field. Change the operator to is not equal to. In the second value field, enter a 0.

Insert your actions into the appropriate branch of the Condition action.

Hope this helps!

If I helped you solve your problem—please mark my post as a solution ✅.
Consider giving me a 👍 if you liked my response!

👉Watch my tutorials on YouTube
👉Tips and Tricks on TikTok