cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Create Excel workbook from SharePoint List last 24 hours

I would like to use Power Automate (PA) to create an Excel worksheet in and existing workbook from items in a SharePoint list.  The PA would run at 3:00PM everyday but would only include items submitted between 2:00PM the previous day and 1:59PM the current day, based on a Date/Time "Submitted" column.  

 

I believe I can filter the SP items with a "Filter Query" in the Sharepoint Get Items step but I don't know what the formula would be.  I'm also not sure how to insert a worksheet into a existing Excel workbook.

 

Thanks for the help!

1 ACCEPTED SOLUTION

Accepted Solutions

hi again!

"The PA would run at 3:00PM everyday but would only include items submitted between 2:00PM the previous day and 1:59PM the current day,"

So, when you mention items submitted, you are refereing the date time they where created, right?

My suggstion is to add a 'Filter array' , please see the steps I followed.

I am assuming your TimeZone is UTC, if not I guess we will have to adapt the approach just a little bit

Flow_DateTimeRangeFilter.png

The expression inside the 'Filter array' is:

@And(greaterOrEquals(formatDateTime(item()?['Created'], 'yyyy-MM-dd HH:mm'), addHours(addDays(variables('Timestamp'), -1), 14, 'yyyy-MM-dd HH:mm')),@less(formatDateTime(item()?['Created'], 'yyyy-MM-dd HH:mm'), addHours(variables('Timestamp'), 14, 'yy)

and you shall add it by clicking on 'Edit in advanced mode'

 

Hope this helps



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



View solution in original post

2 REPLIES 2
Super User III
Super User III

Hi!

Sharepoint, right?

If so, 'Create worksheet' action block is what you need

You can ise 'Filter query' or add a Filter array' action block just after 'Get items' assigning it as its input 'Get items' output.

Give me some minutes to work on it

Hope this helps



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



hi again!

"The PA would run at 3:00PM everyday but would only include items submitted between 2:00PM the previous day and 1:59PM the current day,"

So, when you mention items submitted, you are refereing the date time they where created, right?

My suggstion is to add a 'Filter array' , please see the steps I followed.

I am assuming your TimeZone is UTC, if not I guess we will have to adapt the approach just a little bit

Flow_DateTimeRangeFilter.png

The expression inside the 'Filter array' is:

@And(greaterOrEquals(formatDateTime(item()?['Created'], 'yyyy-MM-dd HH:mm'), addHours(addDays(variables('Timestamp'), -1), 14, 'yyyy-MM-dd HH:mm')),@less(formatDateTime(item()?['Created'], 'yyyy-MM-dd HH:mm'), addHours(variables('Timestamp'), 14, 'yy)

and you shall add it by clicking on 'Edit in advanced mode'

 

Hope this helps



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



View solution in original post

Helpful resources

Announcements
Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

New Super Users

Meet the Power Automate Super Users!

Many congratulations to the Season 1 2021 Flownaut Crew!

Microsoft Ignite

Microsoft Power Platform: 2021 Release Wave 1 Plan

Power Platform release plan for the 2021 release wave 1 describes all new features releasing from April through September 2021.

MPA Community Blog

Power Automate Community Blog

Check out the community blog page where you can find valuable learning material from community and product team members!

Users online (25,542)