Skip to main content
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE

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

100% helpful (2/2)
cancel
Showing results for 
Search instead for 
Did you mean: 
MattGrayndler
Employee
Employee

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

Last Update:

Jun 26, 2023 12:26:33 AM

Updated By:

Sonja_Bauernfeind

Created date:

Oct 14, 2021 9:34:34 PM

Attachments

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:

 

QAA_O365_Excel_Sharepoint_Report_EmailEG.pngQAA_O365_Excel_Sharepoint_Report_ReportEG.png

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

  1. Download the 'Scheduled Report.json' file attached to this document.
  2. 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'.
  3. 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.
  4. 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.
  5. Select the 'Create Excel Table With Headers' block, select 'Connection' in the block configurator, and then create your connection to Microsoft Excel.
  6. Select the 'Send Mail' block. Under 'Inputs' in the block configurator update the 'To' to reflect the addresses you wish to deliver to.
  7. With the 'Send Mail' block still selected, select 'Connection' in the block configurator and add your Sender details.
  8. To test, Save and then Run the Automation
  9. If you receive any warnings or errors, navigate to the relevant blocks and ensure your Connection is selected in the block configurator. 
  10. 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.

  1. Download the 'On-Demand Report v3.json' file attached to this document.
  2. Download and install the 'qlik-blends' extension. See: 
    https://github.com/rileymd88/qlik-blends/files/6378232/qlik-blends.zip
  3. 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'.
  4. 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.
  5. 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. 
  6. Open your chosen QSE SaaS application, and Edit the Sheet where you wish to add a Button to trigger an On-Demand report. 
  7. Under 'Custom Objects' look for 'qlik-blends' from the Extensions menu and drag this into your Sheet.
  8. Under the 'Blend' properties to the right, add-in your POST webhook URL and Token as noted in Step 5.
  9. 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()
  10. Add the second measure, using the following function DocumentName()
  11. 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. 
  12. 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. 
  13. 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.
  14. 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)
  15. 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.

 

Labels (1)
Comments
Rf2
Contributor
Contributor

@Kayleigh_Milewski 

after changing the column format to MM-DD-YYYY the actual column will show the correct date however if we do a sorting on the table it will show the wrong date. see below:

Rf2_0-1669048912539.png

I tried to change column format to DD-MM-YYYY which still brings wrong date as 12-01-2022

See below sample of the data from History tab:

Rf2_2-1669049481084.png

Is there any other possible solution you could suggest?

 

Many thanks

 

Kayleigh_Milewski
Contributor III
Contributor III

@Rf2 

It appears to me what is happening is that Excel is reading the date as MM/DD/YYYY and displaying as DD/MM/YYYY but not actually converting the date. Therefore, it is still reading the date as the US format. When I tried testing this in Excel, I can see that when I click on a cell containing a date, the date is the opposite format in the formula bar. I believe this could also be solved by changing the region settings in your operating system's settings.

I think the best solution would be to convert the date to number format within your straight table in Qlik before performing the automation. That way Excel always gets the correct date and converts it to whatever format you specified in your Base64.

Rf2
Contributor
Contributor

Hi @Kayleigh_Milewski  many thanks for your prompt reply and suport on this.

the first option might not work as file will be open in different pcs from different countries.

the second option I believe will affect the data that is already visible? If I convert the column to number the date will be a number on online report which will have no use. This report is already being consumed.

within the automation will it be possible to convert all data to number ? Or detect date and convert to number? or maybe even convert all data to number regardless (although it might have impact on strings starting with 0)? 

thank you

RF

asmith
Partner - Contributor
Partner - Contributor

Hello @MattGrayndlerMattGrayndler,

Thank you for your work on this and for your quick response a couple weeks ago to my post regarding my empty report with only headers.

While my data did not have any backslash or double quotes, I discovered today that some of my cells contained a newline. I was able to replicate this error using alt + enter in a new source file for the app (reference picture attached), it appears to have the same effect as (") and (\), producing a file with only headers. Removing the newline fixes the issue.

newlineA.png

Thank you so much again!

-A Smith

Sonja_Bauernfeind
Digital Support
Digital Support

Hello @asmith

Let me see if I can get you an answer to this!

All the best,
Sonja

Sonja_Bauernfeind
Digital Support
Digital Support

Hello @asmith 

After speaking to the team, I want to recommend logging a support ticket regarding this so that the issue can be investigated as a potential defect or limitation.

All the best,
Sonja 

AneeshKanakdande
Partner - Contributor
Partner - Contributor

Hey @MattGrayndler
Firstly, thank you for the awesome template!

I have been trying to make this work, and I was going through the comments section to check if I'm missing something, but for me, the output report has no rows, I am formatting numbers without double quotes and alphanumeric with double quotes, still as you see in the image below, the history of the payload shows that it gets the value in the input but the output is nothing.

Thanks in advance!

aneeshginqo_0-1671567345714.png

 

gardenierbi
Creator
Creator
The "Open File on Microsoft SharePoint" block before the "Send Mail" block let me attach the spreadsheet to the email for distribution.

Hi @Kayleigh_Milewski , would you mind sharing how exactly you managed to do this? I would also like to include the file as an attachment in the email message. 

gardenierbi
Creator
Creator

Great @MattGrayndler !!! Thank you very much!

Version history
Last update:
‎2023-06-26 03:26 AM
Updated by: