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.

MattGrayndler · ‎2022-02-20

Hi @jmcy2408 ,

I'm sure there was a nicer way to do this, but I crudely made a mapping table to support up to 26 columns (column Z) as you've discovered. This can be extended as you have done.

martinamangiagli · ‎2022-03-29

Hi @MattGrayndler,
I tested your app and it works perfectly but i need to replace the logo both in the email and in the excel's heather.
Where can I make these changes?

MattGrayndler · ‎2022-03-30

Hi @martinamangiagli ,

The templates I created for the Excel and Email are just examples, and can be updated to suit your needs.

The block 'Variable - templatebase64' contains the Excel template in Base64 format. You can copy the base64 data and use an online converter to convert it back into a file (make the relevant changes) and then convert it back into base64 and update the data in the block.

QAA has the ability to convert files to base64, so if you think you might need to make changes to the template often, you can modify the workflow to read the file from external storage and use the 'Base64 encode' formula. I pasted the base64 data directly in a variable so that the template was more easily portable to share.

For the email, again I've directly included the image data in the template in base64 format.

"<img width="161" height="47" src="data&colon;image/png;base64,iVBORw0...."

You can replace the data that appears after 'base64,' to me your logo converted to base64. And can update the width and height values to match the correct size.

The entire email template can be updated to suit your branding and needs.

TaMahdi · ‎2022-04-08

Thanks @MattGrayndler for the informative blog,

I used Scheduled report and when I try to lookup for a sheet ID, it shows no items found, any possible reason for that?

MattGrayndler · ‎2022-04-28

If anyone is experiencing issues in using the 'Create Binary File (Personal OneDrive)' block and are receiving a 401 error - please refer to:

https://docs.microsoft.com/en-us/sharepoint/troubleshoot/lists-and-libraries/401-error-when-using-gr...

TaMahdi · ‎2022-05-11

Thanks @MattGrayndler

When using Get Straight Table Data, any possible reasons why the table generated from Qliksense is empty?

Sonja_Bauernfeind · ‎2022-05-17

Hello @TaMahdi

For further and in-depth troubleshooting assistance, I would recommend you reach out to our larger community by posting about your issue on the Qlik Automation forums.

All the best,
Sonja

agigliotti · ‎2022-05-18

Hi @MattGrayndler ,

I followed every step to test the Example 1 - Scheduled Reports.

Unfortunately the table rows from Qlik Sense straight table have not been added to the excel table.

It seems the block "Microsoft Excel - Add Rows to Table (Batch)" does not works as expected.

I get the below table with only headers!!!

Could someone help me to fix it?
Many thanks in advance for your collaboration.

Best Regards

dudleytmc · ‎2022-05-18

@agigliotti I am having the exact same issue right now. I believe there must be a limit on how many rows you can add at a time in that block. I created a separate loop and was adding them in 20 row batches and it would work, but unfortunately, the block isn't smart enough to append the new rows to the bottom of the table and instead just overwrites the same 20 rows each time.

MattGrayndler · ‎2022-05-18

Hi @dudleytmc & @agigliotti - I have re-tried this automation and am not facing the issue you are both seeing.

If you look at the history of the 'Get Straight Table Data' block, do you see items in the output?

If you do not, how big is the table?

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