Post to Microsoft Teams from Qlik Sense

post-to-teams-from-qliksense.qvs

// 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)');