Use Case: Send a weekly report of SharePoint list information to specific users
SharePoint List:
| Column Name |
Data Type |
| First Name |
Single line of Text |
| Last Name |
Single line of Text |
| OrderNo |
Number |
| Product |
Single line of Text |
| Quantity |
Number |
| OrderTotal |
Number |
| OrderStatus |
Single line of Text |
| OrderDate |
Date and Time |
Weekly Report:
Prepared a spread sheet (Excel file with extension .xlsx) with all the required columns with sample data and formatted as an excel table. Now, stored the excel file in the Documents library.
Flow at high level view (collapsed view):
Used "Scope" action to consolidate actions as sections for easy understanding. Also, we can use exception handling easily with Scopes.
Here is the details view of the flow:
Step 1: Flow Trigger
The flow trigger is a recurrence schedule. The flow runs on every Sunday morning (only once in a week)
Step 2: Get product orders created from last 7 days
The above action "Get Items" will retrieve all the product orders which were created from last 7 days from today.
Filter Query:
OrderDate gt 'addDays(utcNow(),-7)'
Note: Insert the above expression from expression pop up dialog box.
Step 3: Clean Weekly Report Spreadsheet in the document library
In this step, performing following steps:
1. Accessing the Excel table by using "List rows present in a table" action.
2. Checking if the table has any rows with following expression:
length(body('List_rows_present_in_a_table')?['value']) is not equal to 0
3. Deleting each row from excel table.
Step 4: Generate Weekly Report
Now, adding every single product order item which I received with Get Items action to the spread sheet. Mapped the columns in the spread sheet with the columns in the SharePoint list Product Orders.
Step 5: Send email with weekly status report as attachment
By using "Get file content" action, I am getting the weekly spread sheet content and sent it as an attachment to the specific users.
Important Note:
Attachments Name - 1 field in the above screen shot contains the file name. We MUST include file extension as .xlsx. Otherwise, the flow will send the email with attachment with no extension.
I hope the above post will help to address your requirement.
Regards
Krishna Rachakonda
