Create an Array from Get Items and Convert Into an HTML Table in Power Automate

In this blog, we will use Get Items to get the items from an SP List and do a query. We will then create an array from the items that we will convert into an HTML table. We will use this HTML table as an email body. We will also look into how to subtract the SP list date field from today or subtract two date fields in MS Flow/PowerAutomate

Introduction 

 

In this example, we will send an alert notification to targeted users every week on Tuesday morning if any list items are expiring within the next 60 days.

 

Steps: 

Create an SP list with these list columns: Title, Expiration Date(Date), Active (Y/N). Here is how my list looks:

 

By the end of the MS Flow, we are trying to send this email to targeted users:

 

 

Let's get started. Create a blank new Flow and set the trigger to be scheduled.

 

 

Insert Get Items actions and use filter query to get items if IsActive is 1(Yes/true). IsActive is the Yes/No column.

 

 

Save and test your flow at this point. The output value should not be empty if your list has active items.

 

Create two variables: isExpiryItemExists(Boolean) and emailTable(Array) as shown: I will explain the use of these two variables later.

 

 

Now comes the fun part: we need to check if the Expiry date is within 60 days from today. For this, we need to subtract Expiry Date with Today Date and if the result is less or equal to 60 days, then add this item to an array of expiring items. In the end, we need to send alerts with this array of expiring items. I could not find direct functions to subtract two dates so I used ticks() function to do this described in this article.

 

For this first let's use Apply to each action and insert three Compose actions:

 

 

 

The expressions for these compose actions are:

 

utcNow= ticks(utcNow())

expiryDate = ticks(items('Apply_to_each')['ExpiryDate'])

The output of utcNow is the ticks value of today's date and output of expiryDate is ticks value of ExpiryDate column from the list

 

After that, calculate the difference between these two dates:

 

differenceofDates = div(sub(outputs('expiryDate'),outputs('utcNow')),864000000000)

 

Before we move further let's talk about ticks() function. This function represents the number of ticks between any date and Jan 01, 1601. It is important to know how much 1 tick is. 1 tick is a very small fraction of a second. To be specific 1 second = 10 million ticks. So when we do know

ticks(utcNow()), as it will give a number of ticks between today and 01/01/1601. Another question is where is this number 864000000000 coming from??

 

864000000000 is actually the number of ticks in a day, 24*60*60*10*10^6 to be exact. So mathematically, if we subtract the number of ticks between two dates and divide it by 864000000000, we get the number of days between these two dates.

 

Phew…

Ok let's get back to MS Flow: Now we have to check if the difference is less than 60 or not by using Condition Action:

 

 

Here, Outputs is the output from the differenceofDates action.

Under the Yes branch, insert Append to the Array variable action

 

 

The idea is to insert only those items to the array if the expiry date is with in 60 days. After this, I am setting isExpiryItemExists to true because we will only send email notification if there are any items in the list that are expiring within 60 days.

 

 

I am using an if condition to see if there are any expiring items by checking isExpiryItemExists is true. If true, we will use the Send Email action and in the email body, we will use the output of Create HTML table. I am converting our Array output from emailTable variable to an HTML element using another Data Operation action: Create HTML table:

 

 

Save and run the test, the email body will look something like this: