CODEWITHSUNDEEP
power-automatesharepoint-list
user1063287
user1063287

Reputation: 10879

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:

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:

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:

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')}

Upvotes: 0

Views: 6096

Answers (2)

jleture
jleture

Reputation: 1078

You're almost done with this formula:

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

Except the last part with the formatting is not needed!

Also, you have to get items before today (lower than), but after day -2 (greater than).

Try this:

Status eq 'Submitted' and Created gt '@{addDays(utcNow(),-2)}' and Created lt '@{utcNow()}'

I have a flow to get items older than a week (7 days), and my formula (filter query in a get items SharePoint action) is:

Created lt '@{addDays(utcNow(),-7)}'

Upvotes: 0

Ganesh Sanap - MVP
Ganesh Sanap - MVP

Reputation: 2228

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

Upvotes: 1

Related Questions