Sending Email Alerts for errors during QlikSense data reloads/refresh tasks using Microsoft Flow

[nak]

This is a quick an easy way of sending email alerts using the script editor during data reload process.

It utilizes Microsoft Flow which you have access to if you have Office 360 subscription.

It is a 2 step process. First we setup a MS Flow which accepts REST requests with three parameters(to, subject, body) and emails to these values to the address using first parameter value.

REST API is a one time setup that you can reuse within all of your scripts. No need to setup different one for each instances!

Then use a REST Call from script in the event of an error to send an alert.

1. Login to Office 365 and go in to flows.

2. Create a new flow using INSTANT FROM BLANK

3. click SKIP on this screen.

4. Search for REQUEST and choose WHEN HTTP REQUEST IS RECEIVED

5. Paste the following JSON body area choose POST in the ADVANCED area. Then click NEW STEP

{
    "type": "object",
    "properties": {
        "to": {
            "type": "string"
        },
        "subject": {
            "type": "string"
        },
        "body": {
            "type": "string"
        }
    }
}

6. For next step search EMAIL then choose Send an email (v2)

7. In the email properties, click on each property and choose the matching dynamic parameter name from the right

8. When finished, it should look like this.

9. Now SAVE.

10. Once  saved you will see the request URL. COPY THIS URL 

Now in you application create a new REST connection using the URL you just copied.

Now you just have to insert this in your script to send email alerts for load failures.

//....INSERT THIS INTO BEGINNING OF YOUR SCRIPT

SET ErrorMode = 0;



If ScriptErrorCount > 0 then

// .....SET THESE 3 Variables before trunning

     LIB CONNECT TO 'YOUR_REST_CONNECTION_NAME';
     SET vAPI_URL = "https://YOUR_MICROSFOT_FLOW_API_URL";
     SET vEmail = "Target@emailAddress.com";


     LET vRequestBody = '{ "to": "$(vEmail)", "body": "AppID=' & DocumentName() & '<br>'
& 'DocTitle=' & DocumentTitle() & '<br>' & 
'Error=' & ScriptErrorList & '<br>~ ' & ScriptError 
& '", "subject": "ReLoad Error='& DocumentTitle() & '"}';
     LET vRequestBody = replace(vRequestBody,'"', chr(34)&chr(34));

     RestConnectorMasterTable:
     SQL SELECT 
     "col_1"
     FROM CSV (header off, delimiter ",", quote """") "CSV_source"   WITH CONNECTION (
     BODY "$(vRequestBody)",
     Url "$(vAPI_URL)"
     );

     DROP TABLE RestConnectorMasterTable;

     exit Script;
end if

That's It. You now should get an email alert every time there is a failure with details of the error and the APPID & name.

[jarebpat]

Hello,

Is this solution still actively working? I think Flow has changed a requisite for the post. How would you add: "Remember to include a Content-Type header set to application/json in your request." now to the script in Qliksense? The flow is being triggered but no variable values being passed to flow.

Thanks.