Sometimes it´s good to get data from a data source as a table and as quickly as possible in an automated way. While this can be done with multiple ways, here´s a quick solution if you want to get the items from a custom list in SharePoint Online sent as a HTML table in an email with Azure Logic Apps (or Power Automate).
In this sample, we assume we have a custom list in a SharePoint Online site that includes some hundred lines of data with license requests and billing information. We want to get specific items that will expire in near future where there´s no invoice set. We will do this with a filtered query. The result shall be formatted as HTML table and sent to an email address. Azure Logic Apps and Power Automate (Flow) is perfect for such a quick task.
The following graphics shows a complete flow in the designer view in the Azure portal. It´s just a trigger and 4 simple steps.
So, here´s what´s happening in the flow.
- The flow is triggered with an HTTP request to run only when needed - actually this is just a helper job to get data quickly. of course, this could be scheduled or connected with other actions if needed.
- Get items connects to a SPO list. In this sample the ODATA filter expression filters for records where the field "SubscriptionEndDate" is smaller than today in a month (in the future) with "addDays(utcNow(),30)" and where there is no invoice number already existing "Invoice eq null":
This query returns all records that match the filter expression.
- Data Operations - Select is to build our own result set. Since SPO delivers all fields of the custom list, we want to reduce the data to get only the relevant data we need. The Get items query delivers the results as JSON object and the Select operation allows us to access specific data as here.
Tip: Run the flow once (without mapping) and close and reopen the designer, so that the fields are available as dynamic expression to select in that action.
We can access the result them with body('Get_items')?['value'] expression.The designer shows that as the value object from the Get items action before. To add only required data, we can add our own mapping and define columns and the desired values that shall be added from the list item.
Just to illustrate that: When the flow runs, we see the Inputs and Outputs as here.
The data is reduced to a smaller set of fields. Also, we could transform data or add similar simple operations like combining fields, etc. Here it´s a simple mapping.
- The final Data Operation is Create HTML table. There are more useful actions available, e.g. Join to create a string out of the JSON data, or Create CSV table etc.
Here, we use the Ouput from the Select-action before with body('Select') as input for this action and we use automatic columns - we already defined that in the action before.
- Finally, we send the formatted HTML data as email with an M365 account to a given email address.
- Tip: If you want to inform about the number of items that are returned and that the email is formatted as HTML email (Send an email (V2) unfortunately no longer has the "Is HTML" option…), create a variable with a new Initialize variable action before and add this expression:
concat(concat(concat('items: <strong>',length(body('Get_items')?['value'])),'</strong><br/>'),body('Create_HTML_table'))
Then, use the variable in the body of the action, as here.
- When the flow runs, we get the result as HTML formatted table as here.
A quick solution to send filtered data. Hope this helps for simple scenarios.
You can find more samples in articles from Chaks at Building HTML tables in your flows for sending digest emails, from Marc at Building Nice HTML Output for Tables in Microsoft Flow Emails, on YouTube like at Power Automate Flow How to: Create HTML Tables and, of course with your search engine.