SHIFT

--- Sjoerd Hooft's InFormation Technology ---

User Tools

Site Tools


Sidebar

Recently Changed Pages:

View All Pages


View All Tags


LinkedIn




WIKI Disclaimer: As with most other things on the Internet, the content on this wiki is not supported. It was contributed by me and is published “as is”. It has worked for me, and might work for you.
Also note that any view or statement expressed anywhere on this site are strictly mine and not the opinions or views of my employer.


Pages with comments

View All Comments

flowsharepointlistreport

Create Email Report with Flow to Report All SharePoint Online List Items

It can be very beneficient to create an email report on all items that were created in a SharePoint Online list in the previous month. To do so, I created two flows, one to create the email and another one to intercept that email to store it on sharepoint online as a bonus (and as a evidence case).

Flow 1: Send Month Report of Daily Checks 1

Step 1 and 2: a monthly recurrence trigger and get items from the sharepoint list:
flow-sharepointlistreport001.jpg

Step 3 and 4: Filter the list of items to only select the items that were created last month and then compose the remaining items into an html table:
flow-sharepointlistreport002.jpg

Note the following expressions and input:

  • Created contains expression: addDays(utcNow(), -30, 'yyyy-MM')
  • If the list contains more than 100 listitems you need to change the settings of the “Get Items” step to enable pagination and configure the threshold to whatever fits your needs, for example 2000.
  • Html table:
    <table style="width:100%; border: 1px solid #dddddd; text-align: left;"><tr>
    <td style="width: 200px; text-align: left;"> EXPRESSION BELOW </td>
    <td style="width: 200px; text-align: left;"> EXPRESSION BELOW </td>
    <td style="width: 200px; text-align: left;"> EXPRESSION BELOW </td>
    <td style="width: 200px; text-align: left;"> EXPRESSION BELOW </td>
    <td style="width: 200px; text-align: left;"> EXPRESSION BELOW </td>
    <td style="width: 200px; text-align: left;"> EXPRESSION BELOW </td>
    <td style="text-align: left;"> EXPRESSION BELOW </td>
    </tr></table>
  • Expressions:
    • items('Apply_to_each')?['Check']?['Value']
    • items('Apply_to_each')?['Created']
    • items('Apply_to_each')?['Author']?['DisplayName']
    • items('Apply_to_each')?['Editor']?['DisplayName']
    • items('Apply_to_each')?['Approval']?['Value']
    • items('Apply_to_each')?['TOPdesk']
    • items('Apply_to_each')?['Comments']
Note that you need to know the names of the fields and their values to display them correctly in the html table. If you don't know the name, add “Current item” of the dynamic content so all data will be displayed in the email.

Step 5: Send an html email out to the postmaster mailbox:
flow-sharepointlistreport003.jpg
Note the following expressions and input:

  • Email subject expression: addDays(utcNow(), -30, 'yyyy-MM')
  • Email advanced properties: HTML: Yes
  • Body email:
    All Infra checks that were done last month: <br>
    <br>
    <table style="width:100%; border: 1px solid #dddddd; text-align: left;"><tr>
    <th style="width: 200px; text-align: left;">Check</th>
    <th style="width: 200px; text-align: left;">Created</th>
    <th style="width: 200px; text-align: left;">Created By</th>
    <th style="width: 200px; text-align: left;">Modified By</th>
    <th style="width: 200px; text-align: left;">Status</th>
    <th style="width: 200px; text-align: left;">TOPdesk</th>
    <th style="text-align: left;">Comments</th>
    </tr></table>
    EXPRESSION BELOW
  • Expression to join the composed data: join(actionOutputs('Compose'),'')

Flow 2: Send Month Report of Daily Checks 2

This is a quite simple flow:
flow-sharepointlistreport004.jpg

Resources

You could leave a comment if you were logged in.
flowsharepointlistreport.txt · Last modified: 2021/09/24 00:24 (external edit)