cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jamesjson
Helper III
Helper III

Send Weekly Reports Consisting of One Week

Hi All,

 

I currently have a form that is being processed and going through approval using MS Flow. The questions/answers, when it was submitted/approved, etc all goes to a shared excel sheet. 

 

Is it possible to send weekly reports just consisting of the PRIOR week's approved forms as form of an email? 

 

So every week, it should send just the last week's approved forms. 

 

 

 

Thanks!

2 ACCEPTED SOLUTIONS

Accepted Solutions

@jamesjson 

 

Dates are tricky when you read from excel into Power Automate. They will come as numbers.

 

Please check below post:

https://powerusers.microsoft.com/t5/Building-Flows/Excel-Online-Date/td-p/134200

 

Send email with options does not provide you the option to get what time the option was selected by the user. But, there is a workaround:

After "Send email with options" action, place "Compose" action with expression "utcNow()". It will give you the current time this action executes. 

SendEmailwithOptions.JPG

 

Please let me know if you have any questions.

 

Regards

Krishna Rachakonda

If my replies helped you solving your issues, please mark the reply as Accepted Solution.

View solution in original post

Hi @jamesjson ,

 

For the question "Does Email with Options provide a time when a option was selected? ", yes, the condition wouldn't run before the option is selected in the reply email.

 

Best regards,

Alice   

Community Support Team _ Alice Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

12 REPLIES 12
rsaikrishna
Dual Super User
Dual Super User

@jamesjson 

 

Where is the shared excel sheet stored - One Drive or SharePoint document library?

 

When you read the excel file from Power Automate, you can filter the items by placing appropriate conditions in Filter Query field as highlighted in the below screen shot:

Excel.JPG

 

Please give a try and let me know if you have any more questions.

 

If you need additional assitance, please post the screen shot of the excel file (not with business data) and flow.

 

Regards

Krishna Rachakonda

If my reply helped you solving the issue, please mark the reply as Accepted Solution.

Hi,

 

Thank you for your reply! It is on a SharePoint documents

 

Can you briefly go over how I can utilize the Filter Query field? 

 

Also, if I create another flow outside of the current one, it won't affect my data will it? 

 

@jamesjson 

 

Please take a look into the following post:

https://powerusers.microsoft.com/t5/General-Power-Automate/examples-of-how-to-enter-a-filter-query-f...

 

As you are going to read the excel spread sheet, it will not update your data.

 

Regards

Krishna Rachakonda

If my reply helped you solving the issue, please mark the reply as Accepted Solution.

Hi,

 

Once I filter and get the necessary rows, how can I send it as a report through email?

 

 

 

Thanks!

Hi,

 

Also, this method has to have a specific date as a variable to filter out (e.g. last 5 days from XX/XX/XXXX). 

 

I want to automatically pull the prior week's data from when this flow is scheduled to run. 

@jamesjson 

 

Filter Query in the "List rows present in a table" has limitations as it supports only few operators. It does not support "Greater than or equal to".

 

FilterExcelt.JPG

 

After creating above steps, I am trying the next steps to get the data into excel file so that we can attach with email.

 

WIll update you here shortly.

 

Regards

Krishna Rachakonda

If my reply helped you solving the issue, please mark the reply as Accepted Solution.

@jamesjson 

 

Here is the complete example:

p1.JPGp2.JPGp3.JPG

 

Filter Array expression:

formatDateTime(addDays(utcNow(), -7), 'MM/dd')
 

Please review and let me know if you have any questions.

 

Regards

Krishna Rachakonda

If my reply helped you solving the issue, please mark the reply as Accepted Solution.

Hi,

 

Thank you for all your help! 

 

One last thing, does the date added on excel sheet have to be in a specific format? 

 

I am currently using Email with Options for when it gets moved to Excel ( I am giving options to a shared mailbox so Approval isn't an option)...Does Email with Options provide a time when a option was selected? 

 

 

Annotation 2020-05-21 155506.png

 

Thanks!

@jamesjson 

 

Dates are tricky when you read from excel into Power Automate. They will come as numbers.

 

Please check below post:

https://powerusers.microsoft.com/t5/Building-Flows/Excel-Online-Date/td-p/134200

 

Send email with options does not provide you the option to get what time the option was selected by the user. But, there is a workaround:

After "Send email with options" action, place "Compose" action with expression "utcNow()". It will give you the current time this action executes. 

SendEmailwithOptions.JPG

 

Please let me know if you have any questions.

 

Regards

Krishna Rachakonda

If my replies helped you solving your issues, please mark the reply as Accepted Solution.

View solution in original post

Hi @jamesjson ,

 

For the question "Does Email with Options provide a time when a option was selected? ", yes, the condition wouldn't run before the option is selected in the reply email.

 

Best regards,

Alice   

Community Support Team _ Alice Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

@rsaikrishna 

 

I tried the example you provided and this is what I got. Any thoughts? 

 

Annotation 2020-05-22 162919.png

 

 

 

Thanks!

 

James

Hi @jamesjson ,

 

Please share the screenshot of the configuration of your flow.

 

Best regards,

Alice   

Community Support Team _ Alice Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
MPA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Users online (1,314)