08-21-2020 03:16 AM
Use Case: Send a weekly report of SharePoint list information to specific users
|Column Name||Data Type|
|First Name||Single line of Text|
|Last Name||Single line of Text|
|Product||Single line of Text|
|OrderStatus||Single line of Text|
|OrderDate||Date and Time|
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.
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.
Thank you very much Krishna for the vlog/blog, But i am having issue still followed as you posted. It runs successfully, But It doesn't generate all rows ,
I've posted more here , if you could assist pls.
Love the post and the video. Detailed and to the point.
I am having significant issued getting Dynamic Content to appear in step 2. I've tried other flows with different triggers and they work fine. For some reason I can't get this one work. Any suggestions? And thanks again!
One additional tip. I was having an issue where my excel sheet in the email only returned 1 line of data, while the actual file was updating perfectly in OneDrive. I added a 30 second delay between the Generate Report and Send Email actions. Problem solved!
I got hung up a little bit here too. Were you able to add a SCOPE section as a first step?
After that, then you can ADD AN ACTION to get to Delete a Row. Make sure you choose Excel and not Dataverse.
Thank you for the feedback. I was not able to add the scope section which i have been able to do with your help thanks. Now in step 3 how do i create a sharepoint site