Skip to content

Instantly share code, notes, and snippets.

@mountaindude
Last active November 25, 2022 06:00
Show Gist options
  • Save mountaindude/b43fa9d5b4990dbfc802b32ed59b0352 to your computer and use it in GitHub Desktop.
Save mountaindude/b43fa9d5b4990dbfc802b32ed59b0352 to your computer and use it in GitHub Desktop.
Post to Microsoft Teams from Qlik Sense
// Define webhook for the Teams channel you're posting to.
// You need to create this webhook in Microsoft Teams, then paste the webhook URL in the string below
let vTeamsWebhook = 'https://outlook.office.com/webhook/......';
// You need a data connection called "PostMessageToTeams" for this app to reload correctly.
// >>>>>>>>>>>>>>>>>>>>>>>>>>>
// Define subs
// >>>>>>>>>>>>>>>>>>>>>>>>>>>
// ---------------------------------------------------------------------------
// Subs for posting messages to Microsoft Teams channels.
//
// Posting messages to Teams channels can be used for various things:
// - Notifying developers and end users that reloads have finished and that apps now contain updated data
// - Provide a feedback mechanism to developers during long-running app reloads.
// I.e. providing continuous insight into the question "how far along is my ongoing reload?"
// - Notify developers or app owners that some metric or parameter has reached some value during
// the most recent reload. I.e. a form of alerting.
//
// ---------------------------------------------------------------------------
// Shared code that does the actual posting to the Teams API
Sub CallTeamsAPI(vL.WebHookURL2, vL.Body)
// Replace " with ' in JSON to make it compliant with what the Teams API expects.
let vL.TeamsMessage = Replace('$(vL.Body)', '"', Chr(39));
// Post message to Teams
LIB CONNECT TO 'PostMessageToTeams';
RestConnectorMasterTable:
SQL SELECT
"col_1"
FROM CSV(header off, delimiter ",", quote """") "CSV_source"
WITH CONNECTION(
Body "$(vL.TeamsMessage)",
Url "$(vL.WebHookURL2)"
);
[CSV_source]:
LOAD
[col_1] AS [col_1]
RESIDENT RestConnectorMasterTable;
// Clean up
Drop Tables RestConnectorMasterTable, CSV_source;
set vL.WebHookURL2 = ;
set vL.Body = ;
set vL.TeamsMessage = ;
End Sub
// Post generic message to a Teams channel.
// No title or anything - just a simple message.
Sub PostToTeamsChannel_GenericMessage(vL.WebHookURL, vL.Message)
let vL.Tmp = '{"text": "$(vL.Message)"}';
Call CallTeamsAPI('$(vL.WebHookURL)', '$(vL.Tmp)');
// Clean up
set vL.Tmp = ;
set vL.WebHookURL = ;
set vL.Message = ;
End Sub
// Post a notification from a Sense app to a Microsoft Teams channel in the form of a message card.
// The card will have title, subtitle and two lines of customisable information, together with
// a button that when clicked will open the app from which the message was sent.
//
// This is useful for example for app reload notifications.
// If vFactTitle2 and vFactValue2 are empty strings (''), that line will be excluded from the message card.
Sub PostToTeamsChannel_AppNotification(vL.AppBaseURL, vL.WebHookURL, vL.Title, vL.SubTitle, vL.FactTitle1, vL.FactValue1, vL.FactTitle2, vL.FactValue2)
// Post message to MS Teams channel.
// More info here: https://docs.microsoft.com/en-us/microsoftteams/platform/concepts/connectors
// Set up general info about the message
let vL.Summary = 'Info message from Qlik Sense';
// Icon to use in the message. NOTE: The icon must be publicly available online,
// as it is accessed by Microsoft's Teams service.
// Icons from for example Google Material Design's GitHub repository can be used, these will however
// be black and white only.
let vL.IconURL = 'https://openclipart.org/image/300px/svg_to_png/215532/1425710397.png';
// let vL.IconURL = 'https://raw.githubusercontent.com/google/material-design-icons/master/action/1x_web/ic_thumb_up_black_48dp.png';
// Build JSON that will be sent in body of HTTP POST to the Office365 Teams API.
// Include a button that can be used to open the app
// ==============================================================
// ==> NOTE the different URLs needed for LAB, DEV and PROD!! <==
// ==============================================================
let vL.AppURL = '$(vL.AppBaseURL)' & DocumentName();
let vL.Tmp = '{"@type": "MessageCard","@context": "http://schema.org/extensions","themeColor": "0076D7","summary": "$(vL.Summary)","sections": [{"activityTitle": "![TestImage](https://47a92947.ngrok.io/Content/Images/default.png)$(vL.Title)","activitySubtitle": "$(vL.SubTitle)","activityImage": "$(vL.IconURL)","facts": [ {"name": "$(vL.FactTitle1)", "value": "$(vL.FactValue1)"}, {"name": "$(vL.FactTitle2)", "value": "$(vL.FactValue2)"}], "markdown": true}], "potentialAction": [{"@type": "OpenUri", "name": "Open the app", "targets": [{"os": "default", "uri": "$(vL.AppURL)"}]}]}';
Call CallTeamsAPI('$(vL.WebHookURL)', '$(vL.Tmp)');
// Clean up
set vL.AppBaseURL = ;
set vL.WebHookURL = ;
set vL.Title = ;
set vL.SubTitle = ;
set vL.FactTitle1 = ;
set vL.FactValue1 = ;
set vL.FactTitle2 = ;
set vL.FactValue2 = ;
set vL.Summary = ;
set vL.IconURL = ;
set vL.AppURL = ;
set vL.Tmp = ;
End Sub
// >>>>>>>>>>>>>>>>>>>>>>>>>>>
// Load demo data
// >>>>>>>>>>>>>>>>>>>>>>>>>>>
Characters:
Load Chr(RecNo()+Ord('A')-1) as Alpha, RecNo() as Num autogenerate 26;
ASCII:
Load
if(RecNo()>=65 and RecNo()<=90,RecNo()-64) as Num,
Chr(RecNo()) as AsciiAlpha,
RecNo() as AsciiNum
autogenerate 255
Where (RecNo()>=32 and RecNo()<=126) or RecNo()>=160 ;
Transactions:
Load
TransLineID,
TransID,
mod(TransID,26)+1 as Num,
Pick(Ceil(3*Rand1),'A','B','C') as Dim1,
Pick(Ceil(6*Rand1),'a','b','c','d','e','f') as Dim2,
Pick(Ceil(3*Rand()),'X','Y','Z') as Dim3,
Round(1000*Rand()*Rand()*Rand1) as Expression1,
Round( 10*Rand()*Rand()*Rand1) as Expression2,
Round(Rand()*Rand1,0.00001) as Expression3;
Load
Rand() as Rand1,
IterNo() as TransLineID,
RecNo() as TransID
Autogenerate 1000
While Rand()<=0.5 or IterNo()=1;
Comment Field Dim1 With "This is a field comment";
// >>>>>>>>>>>>>>>>>>>>>>>>>>>
// Send basic message
// >>>>>>>>>>>>>>>>>>>>>>>>>>>
let vMsg = '<h1>Posting message to Teams from Qlik Sense</h1>
A basic message from <b>Qlik Sense</b>
';
// Send the message
Call PostToTeamsChannel_GenericMessage('$(vTeamsWebhook)', '$(vMsg)');
// >>>>>>>>>>>>>>>>>>>>>>>>>>>
// Send more advanced message
// >>>>>>>>>>>>>>>>>>>>>>>>>>>
// vSenseBaseURL is the URL where Sense apps are accessed, less the App ID
let vSenseBaseURL = 'https://qliksense.mydomain.com/sense/app/';
let vTitle = 'Daily sales metrics in Qlik Sense have been updated';
let vSubtitle = 'Click the button to open the app';
let vFactTitle1 = 'Total sales ' & Date(Today()-1, 'YYYY-MM-DD') & ' (kEUR)';
let vFactValue1 = '125';
let vFactTitle2 = 'Change compared to 7-day average)';
let vFactValue2 = '2.5%';
// Send the message
Call PostToTeamsChannel_AppNotification('$(vSenseBaseURL)', '$(vTeamsWebhook)', '$(vTitle)', '$(vSubTitle)', '$(vFactTitle1)', '$(vFactValue1)', '$(vFactTitle2)', '$(vFactValue2)');
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment