Search code examples
power-automatesharepoint-list

In Power Automate flow, how to Get Items from SharePoint List that were created yesterday?

Desired Behaviour

I'm wanting to run a recurrent flow daily, that gets items from a SharePoint List:

  • That have the status of Submitted
  • That were created yesterday

What I've Tried

Initially, I tried to add this to the Filter query in the Get items action:

Status eq 'Submitted' and Created eq '@{addDays(utcNow(),-1,'yyyy-MM-dd')}'

It doesn't return any results, probably because the Created column value is something like:

2023-08-17T05:07:46Z

Whereas, the output of the addDays() expression is something like:

2023-08-17

Of course I've googled for phrases like:

power automate sharepoint list get items created yesterday

And it returns results like those below:

https://powerusers.microsoft.com/t5/Building-Flows/Flow-to-Get-SharePoint-Items-created-yesterday/td-p/802949

https://powerusers.microsoft.com/t5/General-Power-Automate/Filter-SharePoint-list-to-only-include-items-created-yesterday/td-p/758918

Strangely, the accepted answers are:

Created gt '@{addDays(utcNow(),-1,'yyyy-MM-dd')}'

Created ge '@{addDays(utcNow('yyyy-MM-dd'),-1,'yyyy-MM-dd')}'

Neither of these make sense to me, because they are both saying:

  • Get entries that were Created after 24 hours ago

In both of these cases, that would also return entries from today as well (I only want entries that were created yesterday).

Question

What filter query do I need to use in the Get Items action that equates to:

  • Get all items that were created yesterday

For Reference

I've created a bunch of time-related Compose actions in the process of troubleshooting the issue.

They are pasted below for reference in case they help in figuring out a solution.

Compose - Example Created value:

2023-08-17T05:07:46Z

Compose - UTC now converted to Brisbane timezone:

@{convertTimeZone(utcNow(),'UTC','E. Australia Standard Time')}

Compose - UTC now converted to Brisbane timezone formatted:

@{formatDateTime(convertTimeZone(utcNow(), 'UTC', 'E. Australia Standard Time'), 'yyyy-MM-dd')}

Compose - UTC yesterday converted to Brisbane timezone formatted:

@{formatDateTime(convertTimeZone(addDays(utcNow(), -1), 'UTC', 'E. Australia Standard Time'), 'yyyy-MM-dd')}
Solution

  • Try using filter query in get items action like below:

    Status eq 'Submitted' and Created ge datetime'@{concat(addDays(utcNow(),-1,'yyyy-MM-dd'),'T00:00:00Z')}' and Created lt datetime'@{concat(formatDateTime(utcNow(),'yyyy-MM-dd'),'T00:00:00Z')}'

    Where Status and Created are the internal names of your SharePoint list columns. You can get the internal name of your column by following this article: How to find the Internal name of columns in SharePoint Online?

    Reference: Filter Created in SP REST API