Using Qlik Application Automation to create and distribute Excel reports in Office 365

MattGrayndler · Jun 26, 2023 3:26:33 AM

With Qlik Application Automation, you can get data out of Qlik Cloud and distributing it to different users in formatted Excel. The workflow can be automated by leveraging the connectors for Office 365, specifically Microsoft SharePoint and Microsoft Excel.

Here I share two example Qlik Application Automation workspaces that you can use and modify to suit your requirements.

Content:

Considerations
Example 1: Scheduled Reports
Example 2: On-Demand Reports
Environment

Video:

Considerations

This example is built on distributing a SharePoint link. It is also possible to use attachments with the Mail block (see Creating a Qlik Reporting Service report).
Qlik Application Automation has a limit of 100,000 rows when getting data out of a Qlik Sense straight table object.
The On-Demand example uses an extension in QSE SaaS to send data to the Automation. An update to the Qlik Sense Button object is expected soon, which will provide a native way to pass selections to an Automation.

Example 1: Scheduled Reports

Download the 'Scheduled Report.json' file attached to this document.
Create a new Automation in QSE SaaS, give it a name, and then upload the workspace you just downloaded by right clicking in the editor canvas, and selecting 'Upload workspace'.
Select the 'Create Binary File (Personal One Drive)' block, select 'Connection' in the block configurator to the right, and then create your connection to Microsoft SharePoint.
Select the 'Get Straight Table Data' block. Under 'Inputs' in the block configurator, lookup your the App Id, Sheet Id, and Object Id for the relevant QSE SaaS table you wish to output.
Select the 'Create Excel Table With Headers' block, select 'Connection' in the block configurator, and then create your connection to Microsoft Excel.
Select the 'Send Mail' block. Under 'Inputs' in the block configurator update the 'To' to reflect the addresses you wish to deliver to.
With the 'Send Mail' block still selected, select 'Connection' in the block configurator and add your Sender details.
To test, Save and then Run the Automation
If you receive any warnings or errors, navigate to the relevant blocks and ensure your Connection is selected in the block configurator.
Select the 'Start' block. Under 'Inputs' in the block configurator, change Run Mode to Scheduled and define your required schedule.

Example 2: On-Demand Reports

Note - These instructions assume you have already created connections as required in Example 1.

Download the 'On-Demand Report v3.json' file attached to this document.
Download and install the 'qlik-blends' extension. See:
https://github.com/rileymd88/qlik-blends/files/6378232/qlik-blends.zip
Create a new Automation in QSE SaaS, give it a name, and then upload the workspace you just downloaded by right-clicking in the editor canvas, and selecting 'Upload workspace'.
Ensure your Connections are selected in the block configurator for each of the following blocks, 'Create Binary File (Personal One Drive)', 'Create Excel Table With Headers', 'Add Rows To Excel Worksheet Table (Batch)', 'Create Sharing Link', and 'Send Mail'. Save the Automation.
Select the 'Start' block and ensure Run Mode is set to Triggered. Make note of the of URL and Execution Token shown in the POST example.
Open your chosen QSE SaaS application, and Edit the Sheet where you wish to add a Button to trigger an On-Demand report.
Under 'Custom Objects' look for 'qlik-blends' from the Extensions menu and drag this into your Sheet.
Under the 'Blend' properties to the right, add-in your POST webhook URL and Token as noted in Step 5.
We will now add three measures to the 'qlik-blends' object. It is important you add them in the order described. Add the first measure, using the following function in the expression editor: GetCurrentSelections()
Add the second measure, using the following function DocumentName()
The final measure will be the Object ID of the table you wish to use. To find the Object ID, select 'Done Editing'. Then right click on the table, select 'Share', select 'Embed', then look for the Object ID under the preview. Copy this value, go back into Editing mode and paste this as your third measure value.
With the 'qlik-blends' object selected, under Form select 'Add items'. For 'Item type' select Text. Under default value you can choose to add a default email address. For 'Label' and 'Reference' type 'Email'. It is critical that Reference is updated to 'Email'. Turn 'Required input' on.
You can change the Appearance properties to suit your preferences, such as updating the Button label and message, enabling Dialog, and changing the Color under Theme.
Back in the Automation, under the Start Block. Set 'Run asynchronously' = yes to allow multiple requests to run at the same time (This will also increase the max run time from 1min to 60min)
Once happy, test the On-Demand report by entering an email and clicking the button.

This On-Demand Report Automation can be used across multiple apps and tables. Simply copy the extension object between apps & sheets, and update the Object ID (Measure 3) for each instance.

Environment

The information in this article is provided as-is and to be used at own discretion. Depending on tool(s) used, customization(s), and/or other factors ongoing support on the solution below may not be provided by Qlik Support.

agigliotti · ‎2023-02-27

Hi All,

I'm facing an unexpected error after a long time without any errors, see below:

/*****/

{
"error": "Error calling endpoint \"Microsoft Excel - Update Rows In Worksheet\"",
"endpoint": {
"name": "Update Rows In Worksheet",
"datasource": "Microsoft Excel"
},
"request": {
"url": "https://graph.microsoft.com/v1.0/drives/{\"@odata.context\":\"https:\\/\\/graph.microsoft.com\\/v1.0\\/$metadata#users('b21e7089-a407-4dd5-a2e9-0fea8b9a45cd')\\/drive\\/root\\/$entity\",\"@microsoft.graph.downloadUrl\":\"https:\\/\\/gammainformaticait-my.sharepoint.com\\/personal\\/a_gigliotti_gammainformatica_it\\/_layouts\\/15\\/download.aspx?UniqueId=c442ebee-12bc-465b-97fa-72c0ae496296&Translate=false&tempauth=eyJ0eXAiOiJKV1QiLCJhbGciOiJub25lIn0.eyJhdWQiOiIwMDAwMDAwMy0wMDAwLTBmZjEtY2UwMC0wMDAwMDAwMDAwMDAvZ2FtbWFpbmZvcm1hdGljYWl0LW15LnNoYXJlcG9pbnQuY29tQGYyNzViZTU2LWUzZjctNDhkNC05YmQ4LTdjMGM0YWE1ZTMzMCIsImlzcyI6IjAwMDAwMDAzLTAwMDAtMGZmMS1jZTAwLTAwMDAwMDAwMDAwMCIsIm5iZiI6IjE2Nzc1MTMyMjMiLCJleHAiOiIxNjc3NTE2ODIzIiwiZW5kcG9pbnR1cmwiOiJIbEJ3NWR2NldFWXQyRjM0Q3JiYU9yM1hiLzVvbmdXS2N1ampiUWFHTDI0PSIsImVuZHBvaW50dXJsTGVuZ3RoIjoiMTczIiwiaXNsb29wYmFjayI6IlRydWUiLCJjaWQiOiJNR0UyWmpZek5EWXRZamxtWWkwME56bGtMV0poWmpndFpUZzBPVEV3T1RVM016RXoiLCJ2ZXIiOiJoYXNoZWRwcm9vZnRva2VuIiwic2l0ZWlkIjoiWW1Sa09UQmpOemt0WW1GbE5DMDBaVGRqTFRrM016QXRPRGt4WkdNeFpERXlOVGxrIiwiYXBwX2Rpc3BsYXluYW1lIjoiUWxpayBBcHBsaWNhdGlvbiBBdXRvbWF0aW9uIC0gU2hhcmVwb2ludCIsImdpdmVuX25hbWUiOiJBbmRyZWEiLCJmYW1pbHlfbmFtZSI6IkdpZ2xpb3R0aSIsImFwcGlkIjoiZjZjN2QwZmUtZTA0My00NDZlLWIyZWYtZWNjODRkNmZmNjlhIiwidGlkIjoiZjI3NWJlNTYtZTNmNy00OGQ0LTliZDgtN2MwYzRhYTVlMzMwIiwidXBuIjoiYS5naWdsaW90dGlAZ2FtbWFpbmZvcm1hdGljYS5pdCIsInB1aWQiOiIxMDAzMjAwMTI2MjEyRkRFIiwiY2FjaGVrZXkiOiIwaC5mfG1lbWJlcnNoaXB8MTAwMzIwMDEyNjIxMmZkZUBsaXZlLmNvbSIsInNjcCI6ImFsbGZpbGVzLnJlYWQgYWxsZmlsZXMud3JpdGUgYWxsc2l0ZXMubWFuYWdlIGFsbHNpdGVzLnJlYWQgYWxsc2l0ZXMud3JpdGUgYWxscHJvZmlsZXMucmVhZCIsInR0IjoiMiIsInVzZVBlcnNpc3RlbnRDb29raWUiOm51bGwsImlwYWRkciI6IjIwLjE5MC4xNTkuMTAwIn0.ckd5SEZiRUZUTk0rQVR5aVhLRVZKRi9icDBoam1wcGxCNHlhREpzS1FUdz0&ApiVersion=2.0\",\"createdDateTime\":\"2023-02-27T15:53:43Z\",\"eTag\":\"\\\"{C442EBEE-12BC-465B-97FA-72C0AE496296},1\\\"\",\"id\":\"01LJIOF7XO5NBMJPASLNDJP6TSYCXESYUW\",\"lastModifiedDateTime\":\"2023-02-27T15:53:43Z\",\"name\":\"report-F0E09168-74F3-4F76-BC25-1B4E1A62DF3F.xlsx\",\"w...",
"method": "PATCH"
},
"response": {
"body": {
"code": "CURLE_URL_MALFORMAT",
"message": ""
}
},
"external error": true
}

/*****/

until the block "Qlik Cloud Services - Get Straight Table Data" there is no errors.

The error comes with the block "Microsoft Excel - Create Excel Table With Headers".

I can't understand what could be the reason.

Anyone could help here?

Many thanks in advance for your time.

Best Regards

AndyTSG · ‎2023-03-16

Hi @MattGrayndler ,

We have been using this extension for a while but it has just started corrupting the top of the sheet within the tenant when you either move the Extension onto a sheet or go to the sheet in question.

Is there an update that will correct this.

Kind regards

Andy

Lech_Miszkiewicz · ‎2023-03-20

Hi @MattGrayndler

Same with us - its rather urgent and looks really bad...

btw - I hate extensions for that exact reason!!!!

Lech_Miszkiewicz · ‎2023-03-20

Also just to add salt to the wound it looks like now there is new extension (instead of the old one https://github.com/rileymd88/automation-trigger ) which does not inherit settings from old form entries meaning we have to go back and update all objects where it was used. I didnt manage to get it working neither...

MattGrayndler · ‎2023-03-20

Hi All,
I have not been able to update the article since it was moved to this section of Qlik Community, so it's links and everything it mentions is accurate at Oct 2021.

I have tried to be active in the comments instead, where I have provided new versions of automations (that now support direct email) and some different logic depending on different use-cases.

The intent of the article has been to highlight what is possible with Qlik Application Automation, and encourage Qlik developers to extend and update as needed. And it's been great to see other users suggest ways to adapt the automation depending on different needs.

The extension I referenced was renamed Automation-Trigger late 2021, and there have been several enhancements and updates to this extension: https://github.com/rileymd88/automation-trigger/releases

If you are not leveraging the latest version of this extension, please do so.
I am currently not seeing any sheet formatting / usability issues with the latest version of this extension.

If you are, please let me know what region your tenant is in and I will check against your QCS instance.

I also have an update to the on-demand example as the extension has a new method to pass selections by creating a bookmark (this is how the native button also behaves).

You can download this update here: https://qliktechnologies365-my.sharepoint.com/:u:/g/personal/myg_qlik_com/EejVBX3IO3BDlaXX75v_OgcB-W...

If the sheet the extension is placed in only has one table, you don't need to add any measures. The automation will retreive the sheetID and find the first table object ID on the sheet. If there's multiple tables on the sheet (or the table exists elsewhere in the app) then continue to specify the Object ID as a measure.

There's no need to also add GetCurrentSelections() and DocumentName() - the extension now passes the AppID and BookmarkID.

You still need to add a form and a text input with a reference as 'Email'.

And now under Automation, select 'Include Selections'.

This example Automation will now email the file directly (taking advantage of Mail attachments that were added since this article was published).

The author of this extension has actually contributed to Qlik's open source native action button, and I am hopeful we will see native capability delivered to that button soon that enables this use-case.

I have been holding off asking the Qlik Community team to remove this article (and me publishing a new one) until this capability is native to Qlik and I can re-write the on-demand use-case without the need for an extension.

epatinos · ‎2023-05-30

Hello there, Im getting this error constantly with that block, in every flow I try to use it...

Can I get some help? I really don't understand deeply the API's world 😞

Sonja_Bauernfeind · ‎2023-06-01

Hello @epatinos

Please post your query directly in our active Qlik Application Automation forum, which is being actively monitored.

All the best,
Sonja

LuisFloresT · ‎2023-06-02

I appreciate the contribution, it is very helpful.

In which part of the code can I change the Qlik image?

Sonja_Bauernfeind · ‎2023-06-26

Hello @LuisFloresT

I am reviewing this for you and will get back to you soon.

All the best,
Sonja

Sonja_Bauernfeind · ‎2023-06-26

Hello @LuisFloresT

The header you are encountering is stored in the template used by the automation in block 3.

If you want to replace it:

Run only the first 3 blocks
Open the created file on Sharepoint
Remove/update the header
Download the file
Use a base64 encoder tool to convert the file to a base64 string
Paste the new string in the templatebase64 variable block

If you need more direct assistance with this, please post your requirement and the challenges you face in our Qlik Application Automation forum.

All the best,
Sonja

Using Qlik Application Automation to create and distribute Excel reports in Office 365