Nearly a year ago, I wrote about sending alerts to Microsoft Teams channel using Qlik load script and REST connector. With the coronavirus sweeping through the world, forcing people in lock-downs, we are seeing surge in the use of collaboration tools such as Microsoft Teams. So, I wanted to expand on this further and send a HTML table as notification to MS Teams channel and send emails using Microsoft Power Automate (Previously known as Flow) to help people get the notifications quicker.
What, Why and How?
Qlik Sense Enterprise provides notification API to monitor and subscribe to changes you wish to track, ex – task updates, app updates, data changes etc. This provides a way to integrate Qlik to other solutions. However, it requires some technical knowledge and understanding of the systems and not always as simple as you would like. These notifications also do not persists after a QRS service reboot, which causes another set of challenges.
I would like to present an alternative to this, where you use the existing knowledge of Qlik load script and REST connector, combined that with Microsoft Power Automate and MS Teams and send notifications to multiple places with no/very little coding.
This method is similar to sending emails using web connector, however this will not require you to provide your credentials anywhere, which makes an ideal solution that can be deployed in many scenarios.
For this demo – I will set up a flow to reload a Qlik Sense Application (provided here) which will retrieve information on reload tasks from your environment and post a message to MS Teams channel and trigger a chain email with the same message to a group of user(s).
The notification flow looks like this –
- First, using Qlik Sense load script we prepare the table we are going to send in our notification.
- Convert the table into a Qlik variable – this is where we add HTML formatting and prepare the JSON for our webhook call.
- Using Qlik’s REST connector we make a POST request to the MS Teams webhook.
- Power Automate monitors that channel for new messages and send email(s) once MS Teams receives a new message.
MS Teams setup
You will need to set up a webhook connector for the Teams channel where you want to send the notifications to. I have covered this in another blog if you need a refresher. This is all we need to do in Teams.
Power Automate setup
Next, we set up a flow in Power Automate. This will monitor the Teams channel that you will send the notifications to and trigger send email action when any new messages send to that channel.
There are multiple ways we can set this flow –
- Using templates – MS Teams and MS Outlook 365.
- From scratch using SendGrid as an option for sending emails (it’s free service).
For this blog – I am going to go for the second option, so you are not dependent on outlook permissions and also it will be more generic, suitable for sending email notifications.
- Navigate to “My Flows” > “New” > “Automated-from blank“.
- You will see a new popup screen. In that popup, fill in “Flow Name” and type “teams” in the search box.
- Select the “When a new channel message is added” option from the list and press “Create“.
- This will create the flow and open it in edit mode.
- Fill in the “Team” and “Channel” that you wish to monitor for the new message. This will be the channel you have set up the webhook connector for in the Teams setup section above.
- Now Click on “+New step” > Select “Mail” > Select “Send an email notification (v3)“.
Please note: this may prompt you for SendGrid T&C if you have not set another flow using SendGrid before.
- Fill in the To/cc/bcc with the email address(s) where you wish to send the email notifications to.
- Fill in the Subject of your email.
Please Note: you can make this dynamic if you wish to using the Teams message, but for this example, we will just keep it static.
- For “Email body” – you can provide HTML or just static text. This is where we will add some dynamic content from the teams message. You can follow the example from the screen shot below. Please make sure you add the “Message Summary” dynamic content as this is what we will be posting form Qlik Sense script to Teams. This will contain the reload summary table.
- Once completed, press “Save”.
Please Note: If you run a test and type a normal message in the Teams channel to trigger the task, the email body will not contain the dynamic summary as normal messages do not contain summary. Summary is added when we post the “Action Card” from Qlik Sense to Teams.
This is the hard part done. You can test the automation and when you run the test, you should see something like the picture shown. (Trigger the test by sending a message in that channel chat)
Qlik Sense setup
This part is relatively simple. I have prepared the Qlik Sense application with the script that will load data from the QRS endpoint (like the monitor apps) and prepare the JSON object for the webhook call to the Teams channel.
This app requires three REST connections in total. You only need to create one.(may have to update the second connection, see notes below)
- GET /qrs/about/api/enums
You will need to create this connection with the following name –
"GetRestQrsEnums". Look at here (or the screen shots at the bottom of the post) to see how to connect to QRS endpoint if you are not sure. This retrieves the lookup values for status code, which the next part of the code will need.
- GET /qrs/reloadtask
You DO NOT need to create this connection. However, if you are using anything other than default virtual proxy for your first connection or for monitor apps then please update the connection parameters in “Task Results” tab @
- POST https://jsonplaceholder.typicode.com/posts
This is a placeholder REST connector that the supplied app comes with. You DO NOT need to create this one. This gets over written by the webhook call using
Please download the Qlik Sense application from the link below. This contains the instructions on how to set up the environment.
When you have all the components configured correctly, after a successful reload of the Qlik Sense application, you should get two forms of notification.
- An action card posted to MS Teams Channel.
- Few moments later, an email should be delivered to the designated email inboxes.
Finally we got the end of this …..
This should take no longer then 20-30 minutes to set the flow. This opens up number of possibilities, for example – sending KPIs every morning to the users to remind them to log into the Qlik Sense application for details, conditional based email alerting etc. I have started played with flow from a very early days, and the platform has gotten much better over time like most things, it certainly has a lot of potential. If you have not had the opportunity to play with it yet, then this might be the perfect opportunity to take this for a test drive with this exercise.