Within the available connectors in Qlik Application Automation are Microsoft Onedrive & Microsoft Sharepoint . This article intends to describe some potential use cases that can be performed through the use of these connectors and provide an easy to use template to do so. Most examples are built using the Onedrive connector, however the same use cases can be performed for shared drives using the Sharepoint connector.

• Monitoring changes to a user's drive.
• Automatically modifying file locations and renaming of files
• Create sharing links for files and share with emails
• Set up sharing permissions for files.

Authentication for the connector happens with oAuth2. We are making use of the following scopes for the Onedrive connector:

• Files.ReadWrite.All
• User.Read
• offline_access

These scopes should be accessible for all users in a default Office 365 tenant. If you are having troubles attempting to authorize Qlik Application Automation to obtain these scopes consult your Office 365 administrator.

Monitoring changes to a user's Onedrive

Using Automations in Qlik Cloud it is possible to monitor changes to a user's personal Onedrive or to a Sharepoint shared drive. When changes to a personal or shared drive happen, automatic actions can be performed, such as automatically obtaining data from an uploaded excel spreadsheet and updating a Qlik Sense app.

Onedrive makes use of a delta token to view changes that have happened since that token. As we do not have a data store within Automations, we are bound to use a separate store for the delta tokens. Options could be an excel file / text file stored on Onedrive, an app on Qlik Sense or any other place where data is persisted.

An example automation to monitor changes might look like the following. The automation is also added as JSON to the article to import into a Qlik Cloud tenant.

Modifying file locations and renaming files

The Microsoft Onedrive and Microsoft Sharepoint connectors in Qlik Application Automation provide blocks to Move and Rename items on drives. These can be used for example in conjunction with other connectors to perform these actions whenever … happens

An example could be to move all files from an incoming folder to an archive folder after a Qlik Sense reload is complete.
A simple example automation is added as an attachment to this article. In the automation editor this might look like the following:

Create sharing links for files and share with emails

The block Create Sharing Link can create a shareable URL that can be send over email to a user.
It offers the user the choice whether the URL works for the organization of the user or anonymous. The scope can be set to view, edit or embed and it is possible to provide a password as well as an expiration time for the shareable link.

The following automation would obtain data from a table in Qlik Sense, copy the rows to an Excel file hosted on Onedrive, create a shareable link and share this link to a user by email:

Setup sharing permissions for files

Sharing permissions can be done with the Send Sharing Invitation. This block sends an invitation to an email recipient including a message, subject, reading or writing permissions and the choice if an invitation has to be sent out and whether a user has to be signed in. A password can also be provided, but only if the user is using a personal Onedrive account.

Furthermore it is possible to list sharing permissions, updating these permissions and removing permission to access a file on a user's Onedrive.

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.

This article explains how a reload task chain can be built that will run longer than the maximum automation run limit of 4 hours. 

This is an advanced article, if you’re new to automations or task chaining, I suggest you first read this basic article.

Content:

• General idea
• Preparation
• Automation 1: Task Chain Initiator
• Automation 2: Task Chain Manager
• Results
• Bonus: reducing total executions of automation

General idea

The maximum run time of an automation is 4 hours. This can be a problem when you want to build a task chain of multiple reloads of big apps that could take longer than that. To solve this, we'll build a system of two automations that run together:

Automation 1: Task Chain Initiator, this automation will run once and start the task chain. It will trigger the first app reloads.

Automation 2: Task Chain Manager, this automation will be scheduled to run every 5 minutes. It will verify if any reloads have finished, whether they were successful and if any new reloads should be triggered.

The Task Chain Manager automation will need to know which reloads were triggered by the Task Chain Initiator and will need a way to store which reloads it already processed in previous runs. In other words: these automations need some sort of state to track the progress of the reload task chain. We’ll use Google Sheets for this, but of course, feel free to use any other cloud storage tool.

Preparation

The state will be stored in one file and divided over 4 sheets. Go ahead and create a new file in Google Sheets.

Sheet 1: Apps for initial reload, this sheet contains the ids and names of the apps that should be reloaded in the first automation. They will be executed by the Reload Initializer automation and run in parallel. Add a new sheet to the file you created, give it the name “Apps for initial reload” and add each app's id and name as in the below example. The order of the apps in the sheet is ignored.

Sheet 2: Queued reloads, this sheet will keep track of which apps were already triggered for reloading.

Add the sheet to the file and name it “Queued reloads” but don’t fill in any fields, the content of the sheet is autogenerated by the automations.

Sheet 3: Finished reloads, this sheet keeps track of all reloads that have finished, it contains their id, name, the status of the reload, and the reload log. Add the sheet to the file and name it “Finished reloads” but don’t fill in any fields, the content of the sheet is autogenerated by the Task Chain Manager automation.

Sheet 4: Chained reloads, this sheet dictates which apps should be reloaded after certain apps finished reloading. Add a new sheet to the file and name it “Chained reloads” specify the order in which apps should be executed.

The order of the rows in the sheet is ignored, the sheet is processed as follows:

If a reload has finished for an app from column “First_id”, then trigger a new reload for the app that’s specified in the column “Next_id” on the same row.

Add this sheet to the file and specify the apps as in the example below:

Automation 1: Task Chain Initiator

This automation will only run once at the beginning of the full task chain process, it will execute the following steps:

1. First, it will clear the Queued reloads & Finished reloads sheets, this will remove all data from previous task chain executions.
2. Next, it will add headers to these sheets
   1. Examples of the generated headers in the Queued reloads sheet:
      
      
      
      
   2. Examples of the generated headers in the Finished reloads sheet:
      
      
      
      
   3. Your automation should now look like this:
      
      
      
      

       

3. After initializing these sheets, the automation will get all apps from the Apps for initial reloads sheet and trigger a reload for each of them in parallel (by setting the Run Mode in the Do Reload block to ‘Start reload and continue’).
   1. Each app’s id, name, and reload id are written to the Queued reloads sheet:
      
      

       

   2. This part of the automation looks like this:
      
      

Note: an export of this automation is attached to this article as a JSON file named "Task chain initiator.json".

Automation 2: Task Chain Manager

This automation will run on a schedule and will be the engine of our task chain process. Feel free to adjust the schedule according to your needs. On each run of the automation, the following steps will be executed:

1. Get all records from the Queued reloads sheet, the Finished reloads sheet and the Chained reloads sheet.
   
   
   
   
   1. Tip: we won't use the loops on these blocks, right-click these blocks and choose "hide loop" to make your automation smaller.
2. Compare queued & finished reload records to find all apps that are in the Queued reloads sheet but not in the Finished reloads sheet. This will return a list of all apps from the queue that haven’t yet finished. Make sure to configure the settings of the Compare Lists block as follows. (List 1 should contain that queued reloads and list 2 should contain the finished reloads)
   
   
   
   
   
3. For each record returned by the Compare Lists block (= unfinished reloads), execute a Get Reload Record block to get the reload status and use a Case block to verify if the reload has finished:
   
   
   
   
   1. The reload finished successfully (status == SUCCEEDED):
      1. Write the app & reload information to the Finished reloads sheet.
      2. Use a filter list block & filter the records from the Chained reloads sheet. Filter it to get all records where First_id == the id of the app of the reload that succeeded.
      3. For each record returned by the Filter List block, trigger a new reload with the Do Reload block and write the reload id to the Queued reloads sheet.
      4. This part of the automation should look like this:
         
         
         
         
   2. The reload failed (status == FAILED):
      1. Write the app & reload information to the Finished reloads sheet.
      2. Optionally, send an email or a Slack message with the reload log.
      3. Configure this part like this:
         
         
         
         

Note: an export of this automation is attached to this article as a JSON file named "Task chain manager.json".

Results

The reloads in the Finished reloads sheet will look like this:

Bonus: reducing total executions of automation

When scheduled every 5 minutes, the "Task Chain Manager" automation will generate 288 daily runs. This bonus paragraph explains how the number of total runs can be reduced. Instead of having an automation that has many executions that run short, we'll build an automation that has only a few executions that run for a longer time.

1. Take all blocks from the Task Chain Manager automation and place them inside a Loop block. Use the explode formula to create a loop in the Loop block's List input field. Specify a comma-separated list of 30 items. For example: 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30.
   
   
   
   
2. Then add a Condition block at the end of the automation right after the blocks from the original Task Chain Manager automation. Configure the block to validate the number of items returned by the Compare Lists block (use the Count formula for this). If this equals 0, the Task Chain has finished. Add an Exit Loop block to stop the automation.
3. Now add a sleep block after the condition to sleep for 60 seconds.
   
   
   

    

4. Finally, change the automation's schedule to run every minute. This will reschedule the next run of the automation 1 minute after the previous automation has finished. The automation will now run for 30 minutes (30 loop executions x 1 minute from the sleep block) + the total execution time of the other blocks in the loop.
   
   

Notes

• You can easily modify this automation to send messages to Microsoft Teams or emails.
• If multiple apps should be reloaded after a certain app’s reload is finished, just add multiple rows to the Chained reloads sheet with each time a different Next_id
• In the Task Chain Manager automation, if the output of the Compare Lists block is empty, the task chain has finished. Feel free to add a condition block to the end of this automation to verify this and alert your team.

The information in this article is provided as-is and to be used at your 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.

Related Content

How to task chaining with Qlik Application Automation
How to import and export automations

Environment

• Qlik Sense Enterprise SaaS
• Qlik Application Automation

This article shows how to generate a report from a Qlik Sense app, upload the report to any cloud storage platform, create a sharing link, and send out an email with the link in the body. 

We can send the report as an attachment using the Send Mail block in the Mail connector, but this block only supports attachments of size 40MB. An alternative solution is to store reports in any of the cloud storage platforms that support the creation of sharing links and send emails with the links.

I will go through a few examples to show how this can be achieved using the following cloud storage connectors:

1. Dropbox
2. Microsoft OneDrive
3. Microsoft SharePoint
4. Amazon S3

I will not go through the steps on how to generate the report using Qlik Reporting blocks as it's already explained in this article.

The four automation files used in the examples are attached to this article. More information on importing automations can be found here.

Dropbox

The following steps explain how to create a public URL using the Dropbox connector.

1. Store the generated report on Dropbox using the "Copy File on Dropbox" block. Configure it to use the output from the "Generate Report" block as the Source File and set the Destination connector to Dropbox. Include the Path from the output of the "Generate Report" block in the Destination Path parameter to use the generated filename.
   
   
   
   
2. Add the "Create Sharing Link" block from the Dropbox connector to the automation and configure it to use the path of the file from the output of the "Copy File on Dropbox" block in the Path parameter.
   
   
   
   
3. Add the "Send Mail" block from Microsoft Outlook 365 connector to the automation. Configure the Content Body parameter to use the public URL generated in the step above.
   
   
   
   

Microsoft OneDrive

The following steps explain how to create a public URL using the Microsoft OneDrive connector.

1. Store the generated report on Microsoft OneDrive using the "Copy File on Microsoft OneDrive" block. Configure it to use the output from the "Generate Report" block as the Source File and set the Destination connector to Microsoft OneDrive. Include the Path from the output of the "Generate Report" block in the Destination Path parameter to use the generated filename.
   
   
   
   
2. Add the "Get Item By Path" block from the Microsoft OneDrive connector to the automation. Configure it to use the file path from the output of the "Generate Report" block in the File Path parameter to use the generated filename.
   
   
   
   
3. Add the "Create Sharing Link" block from the Microsoft OneDrive connector to the automation. Configure it to use the ID of the file from the output of the "Get Item By Path" block in the Item Id parameter.
   
   
   
   
4. Add the "Send Mail" block from Microsoft Outlook 365 connector to the automation. Configure the Content Body parameter to use the public URL generated in the step above.
   
   
   
   

Microsoft SharePoint

The following steps explain how to create a public URL using the Microsoft SharePoint connector.

1. Store the generated report on Microsoft SharePoint using the "Copy File on Microsoft SharePoint" block. Configure it to use the output from the "Generate Report" block as the Source File and set the Destination connector to Microsoft SharePoint. Include the Path from the output of the "Generate Report" block in the Destination Path parameter to use the generated filename. Use the do lookup functionality to find a Drive Id or a Site Id,  but do not specify both.
   
   
   
   
2. Add the "List Items On Drive By Site" or "List Items On Drive" block from the Microsoft SharePoint connector to the automation to get all the items on a drive/site. Use the do lookup functionality to find a Drive Id or a Site Id.
   
   
   
   
3. Add a "Filter List" block to retrieve the generated report stored in SharePoint based on the report name. Note that the report name is equivalent to the Path obtained from the output of the "Generate Report" block. Set the condition as shown in the screenshot below.
   
   
   
   
4. Add the "Create Sharing Link" block from the Microsoft SharePoint connector to the automation. Configure it to use Drive Id and Id from the output of the "Filter List" block in the Drive Id and Item Id parameter. Set the Scope parameter value to anonymous to provide access to the sharing link to anyone (including individuals outside your organization) without needing to sign in. Keep in mind that this won't work if the anonymous support is disabled by the SharePoint admin. More info about enabling the external sharing option can be found here. Also, configure the Expiration Date Time Parameter which indicates the expiration time of permission.
   
   
   
   
5. Add the "Send Mail" block from Microsoft Outlook 365 connector to the automation. Configure the Content Body parameter to use the public URL generated in the step above.
   
   
   
   

Amazon S3

The following steps explain how to create a public URL using the Amazon S3 connector.

1. Store the generated report on Amazon S3 using the "Copy File on Amazon S3" block. Configure it to use the output from the "Generate Report" block as the Source File and set the Destination connector to Amazon S3. Include the Path from the output of the "Generate Report" block in the Destination Path parameter to use the generated filename. Also, configure Region and Bucket parameters.
   
   
   
   
2. Add the "Create Presigned Object URL" block from the Amazon S3 connector to the automation and configure it to use the path of the file from the output of the "Copy File on Amazon S3" block in the Key parameter. Also, configure Region, Bucket, and Expiration parameters.
   
   
   
   
3. Add the "Send Mail" block from Microsoft Outlook 365 connector to the automation. Configure the Content Body parameter to use the public URL generated in the step above.
   
   
   
   

Related Content

• Qlik Reporting
• Multi app reports
• Microsoft Outlook 365 Connector

Authentication and Authorization

Authentication and authorization with the Microsoft Excel Connector in Qlik Application Automation works through oAuth2. When a user attempts to connect a Microsoft Excel datasource, the user will be redirected to the Microsoft login page where a user is prompted to give permission for Qlik Application Automation to access their Office 365 tenant.

The following scopes are being requested by Qlik Application Automation:

• Offline_access
• User.Read
• Files.ReadWrite.All

Working with excel worksheets

The Microsoft Excel connector has blocks available to perform actions on worksheets, data inside worksheets and tables inside worksheets. When working with data, we always recommend to work with tables rather than data on the worksheet due to ease of use.

Listing rows from Excel File

This block obtains values from an excel file worksheet from a specified range. The following input parameters are used:

• Drive ID: This is a lookup that will query a user's drive.
•  Item ID: ID of an item. Using the lookup a user can query a specific folder to search for excel sheets
• Worksheet name: Can be selected using lookup or alternatively the worksheet name or ID can be manually provided.
• Start Cell: The upper left bound cell of the range to return.
• End Cell: The lower right bound cell of the range to return.

The block returns a double nested array. The first level contains a row, the second level contains the value of a column in that row.

The same block also exists for working with tables. Here the start cell and end cell are replaced by a Table ID.

Create a new worksheet

With the block Add Worksheet to Excel File a user can add a new worksheet to an Excel document. It takes the following input parameters:

• Item ID: ID of an item. Using the lookup a user can query a specific folder to search for excel sheets
• Name: Name for the new worksheet name.

Updating Rows in a Worksheet

Using the block Update Rows in Excel Sheet you can specify a range in which you want to overwrite existing data. The following input parameters are required:

• Drive ID: This is a lookup that will query a user's drive. A sharepoint drive ID can also be provided.
• Item ID: ID of an item. Using the lookup a user can query a specific folder to search for excel sheets
• Worksheet name: Can be selected using lookup or alternatively the worksheet name or ID can be manually provided.
• Start Cell: The upper left bound cell of the range to return.
• End Cell: The lower right bound cell of the range to return.
• Values: A double nested array containing data for each cell in the specified range. This has to match the range specified by the Start Cell and End Cell inputs. It is strongly recommended to work with variables and construct a listOfRows variable. This variable should be a list and it should be filled with multiple lists. An example could look like the following:

Create a new table

Using the block Create Excel Table With Headers a user can create a table in Excel. Using the block Add Table to Excel File Worksheet a user can also create a table without headers. The following input parameters are being requested:

• Drive ID: This is a lookup that will query a user's drive.  A sharepoint drive ID can also be provided.
• Item ID: ID of an item. Using the lookup a user can query a specific folder to search for excel sheets
• Worksheet: When item is selected, a lookup can be used to search for the different sheets in which the table has to be placed. You can also provide the name or ID of the worksheet.
• Start Row: The row where the headers will be placed. Indexes in Excel start from 1.
• Start Column: Use alphabetic letter.
• End column: Use alphabetic letter.
• Headers: Array of strings to use as header values. Keep in mind that the number of columns selected using the start column and end column have to match with the headers array. E.g. C-E as column start/end for an array of 3 headers.
• Name: Name of the table for easy references.

Aside from creating a table, we can also delete is by the use of the Delete Table from Excel File Worksheet block. This requires an item ID, worksheet and Table ID.

Find Row(s) in Excel Worksheet Table

Two blocks serve to find a matching row in an Excel Table. These blocks are Find Row in Excel Worksheet Table and Find Rows in Excel Worksheet Table. The first one will only return the first row found, the second one will return all rows found matching these criteria. The following input parameters are required:

• Drive ID: This is a lookup that will query a user's drive.
• Item ID: ID of an item. Using the lookup a user can query a specific folder to search for excel sheets
• Worksheet: When item is selected, a lookup can be used to search for the different sheets in which the table has to be placed. You can also provide the name or ID of the worksheet.
• Table ID: Can be obtained using the lookup once all above fields have been filled out.
• Column Index For Search: The index of the column that is being searched for a value. The indexes are zero based.
• Search Value: The value to search for. Only fully matching values will work.

Sharing workbooks

Functionality to share workbooks is not included in the Microsoft Excel connector. These actions can be done using the Microsoft Onedrive or Sharepoint connector.

Limitations

• Cannot create a new excel file.

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.

Getting data out of Qlik Sense Enterprise SaaS, and distributing it to different users in formatted Excel, has been a manual task until now.

Thanks to the release of Qlik Application Automation, it is now possible to automate this workflow 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. 

Video:

Considerations

• This example is built on distributing a SharePoint link. It is also possible to use attachments with the Mail block (see Reporting).
• 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

• Qlik Application Automation 
• Qlik Cloud 
• Microsoft Office 365

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.

With Salesforce Jobs API, you can insert, update, upsert, or delete large data sets. Prepare a comma-separated value (CSV) file representation of the data you want to upload, create a job, upload job data, and let the Qlik Application Automation handle these with the Salesforce API.

Here are the steps to use the Upload Jobs APIs:

1. Read the CSV file which needs to upload to the Salesforce Organization.
2. Firstly, Select the Jobs tag after selecting the Salesforce connector in the right rail to list out all the blocks related to Jobs API.
   Use the "Create Bulk Upload Job" block to create a job. Select appropriate 'operation' for the job. Each 'Operation' expects a differently formatted CSV file. This block creates a job that can be used for operation.
   • insert: Make sure to have added the mandatory fields of the object.
   • delete: Only the ID of the records is required in the CSV file.
   • hardDelete: Only the ID of the records is required in the CSV file.
   • update: The ID of the records should be specified in the CSV file.
   • upsert: External ID of the record should be specified in the 'External Id Field Name' parameter of the "Create Bulk Upload Job" block and all the Mandatory fields of the record should be specified in the CSV file.
     

      

3. Map the created job ID to the 'Upload Job data' block and upload the CSV file which was previously read in the same automation. Now run the Automation. This will upload the file to Salesforce.
   

    

4. After the Upload, use the "Close or Abort Upload Job" block to mark this job as 'completed'. Note that, the upload is only processed after marking the job as "UploadComplete".
5. We can get the result of each upload job using the following blocks:
   • "Get Upload Job Successful Record Results": Retrieves a list of successfully processed records for a completed job.
   • "Get Upload Job Failed Record Results": Retrieves a list of failed records for a completed job.
   • "Get Upload Job Unprocessed Records Results": Retrieves a list of unprocessed records for a completed job.

This article is intended to get started with the Microsoft Outlook 365 connector in Qlik Application Automation.

Authentication and Authorization

To authenticate with Microsoft Outlook 365 you create a new connection. The connector makes use of OAuth2 for authentication and authorization purposes. You will be prompted with a popup screen to consent a list of permissions for Qlik Application Automation to use. The Oauth scopes that are requested are:

• Mail.Send
• User.Read
• Offline_access

Sending Email with the Microsoft Outlook 365 connector

The scope of this connector has been limited to only sending emails. Currently, we do not enable sending email attachments and are looking to provide this functionality in the future. The suggested approach is to upload files to a different platform, e.g. Onedrive or Dropbox and create a sharing link that can be included in the email body.

The following parameters are available on the Send Email block:

• To: A comma separated list of recipients;
• CC: A comma separated list of recipients to which the email should be copied;
• BCC: A comma separated list of recipients to which the email should be blind copied;
• Subject: A title for your email;
• Type: Choose between plain text email or HTML;
• Body: The content of your email. You can use HTML for your email if chosen as the type.

Generating a report and sending an email with the report

As we do not currently support email attachments, we need to first generate a sharing link in Onedrive or an alternative file sharing service. The following automation shows how to generate a report from a Qlik Sense app, upload the report to Microsoft Onedrive, create a sharing link and send out an email with the sharing link in the body. This automation is also attached as JSON in the attachment to this post.

Environment:

Qlik Application Automation 

In this article, we will explain how to set up one automation as a source and keep multiple target automations in other tenants synced with the changes made to the original. We will be using an intermediary tool for this process, in our case Github, to store the changes made to the source automation so you could also say this article doubles as a version control article for your future automations.

Setting up the source automation and syncing changes to Github

To get the initial automation data inside a Github repository, all we need to do is construct a quick automation that contains the "Get Automation" block and run it in order to receive the much-needed data in the block history. We can then copy that data and paste it all into a file inside our working repository. For example purposes, our working file is called 'automation.json' and is situated in the main directory of the demo repository.

Now that our initial set-up is complete, we will list the steps needed to keep our repository up to date with all the changes to our automation. This is how the workflow will look:

You can see that our initial start block is replaced with a Qlik Cloud Services webhook that listens to all automations being updated in our tenant. We have also created a variable to keep the Github branch name in memory and keep it as a version number for the cases where it might be needed:

The formula used takes the date at which the automation was modified, transforming it to a UTC format, and attachs it to the branch name: version-{date: {$.AutomationUpdated.data.updatedAt}, 'U'}

We also need to attach a condition block to our flow to make sure only our source automation gets sent to our repository:

In this case, we are hardcoding the id of our automation in the condition input parameter. Next, on the 'yes' branch of the condition, comes the 'Get automation' block that will return the latest version of our source automation, followed by the flow that sends that data to a new branch in our Github repository. First, we create a new branch:

Secondly, we update the automation.json file we had in our main repository with the new data we received from the 'Get automation' block:

As you can see, Github platform only accepts BASE64 encoded file contents, so the JSON content received from 'Get automation' will be transformed to that format. To create the commit and pull request, we have one more step, which is to find out the branch TREE SHA information:

With this information, we can go ahead and create the commit:

The final step in our flow is to create the pull request :

Keeping your target automation up to date with the latest version

To do this, we now move to our target tenant and create a separate automation there. The workflow involved is a simple one:

Again, the start block will get replaced by the Github webhook that is listening to all new pull requests done in our repository:

We also create an empty object variable that will be later used to save the incoming information from the repository. We need to create a condition block as well with the following two conditions:

This lets the automation flow continue only if the pull request has been closed and its contents have been merged into the main branch of our repository. It tells us that the pull request has been approved and we can sync all changes into our target automations. For that, we query the file contents of the repository and save that information in our object variable after we have transformed it from a BASE64 encoding format to a text-based format:

The formula used when assigning the value to the variable is {object: '{base64decode: {$.getFileContent}}'} which just removes the base64 formatting and turns the string into a JSON object to better handle it in the next block. Now all that is left is to use the 'Update Automation' block to bring the new changes to our target automation:

As you can see, we hardcoded the GUID of our target automation, but the do lookup functionality can be used as well to select one of the automations in our tenant. Finally, we need to send the correct parameters from our object to the input parameters of the block. This can easily be done by selecting the Name, Description, Workspace and Schedule parts of the object.

This should let you keep your automations synced over multiple tenants. Your target automation history should not be impacted by the syncs in any way.

Please be aware that if the target automation is open during the sync process, the changes to it will not be reflected in real-time and a reopening of the automation will be needed in order to see them.

Attached to this article you can also find the JSON files that contain the automation flows presented.

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.

Related Content

• https://community.qlik.com/t5/Knowledge/Qlik-Application-Automation-How-to-use-the-create-update/ta-p/1978622

This article is meant to get users starting on the API key connector in Qlik Application Automation.
This connector is designed for advanced users to connect to platforms that do not have a dedicated connector

The connector employs the API key authentication flow, which requires only the API key to make requests to a specific API. 

Index:

• Limitations
• How to connect
• How to use in an automation
• An Example
• Using it in an automation

Limitations

• This connector will work with the majority of API key flows. In cases where there are additional fields to be sent such as additional query parameters in the authorize request, this connector will not work.
• APIs that send the result directly in an array is not supported in List Requests. You can use the Raw API request endpoint to run these.
  For example:
  Supported results type :
  

  {
  
  	"content": [{
  		"endDate": "2021-11-01T05:55:00.000+0000",
  		"status": "unpublished",
  		"created": "2021-06-16T18:40:12.000+0000",
  		"websiteUrl": "https://events.bizzabo.com/337104",
  		"modified": "2021-08-30T20:54:52.000+0000",
  		"timezone": "America/Denver",
  		"name": "Pluralsight LIVE 2021"
  
  	}]
  }

  Not Supported results type : 

  [{
  		"endDate": "2021-04-08T19:00:00.000+0000",
  		"status": "published",
  		"websiteUrl": "https://events.bizzabo.com/309250",
  		"modified": "2021-04-01T16:38:46.000+0000",
  		"timezone": "America/Denver",
  		"name": "Pluralsight Summit",
  		"id": 309250
  }]

How to connect

Begin by looking for the "API Key Connector" connector. When you click connect, the following input parameters are requested:

1. Connector name: A name for the connection. This can be used as an identifier for the user in the editor to differentiate between different API key connections.
2. Type: Here you can specify how the API key should be included in the request.
   
   1. HTTP header: If the API key is to be included in the HTTP header. 
      

      GET / HTTP/1.1  
      Host: example.com  
      Authorization: Basic <api_key>

   2. querystring : If the API key is to be sent as part of the URL as a query string
      

      curl -X GET "https://example.com/endpoint/?api_key=<api_key>"​

3.  Authentication name:  Enter the name of the field where the API key is sent here.
                  In the preceding examples, for example, Authorization is an authorization header and api_key is a query parameter.
4. API Key:  You must enter the API key that will be sent here. (When entering, include the Basic/Bearer prefix.)
                    Basic <api_key>= and <api_key>from the preceding examples.
5. Base URL: This should be the beginning of the URL to which you will be making REST calls. We recommend                including this as much as possible because it prevents calls to other URLs. (Omit the https://)
                   From the preceding examples, consider example.com.

How to use in an automation

When selecting the connector in automation you will notice that there are only a few blocks available, depending on the type of request and pagination involved in your request. These blocks have the following input variables:

1. RAW API REQUEST BLOCK
   • Path: This needs to be relative to the Base URL you provided when connecting.
   • Method: Enum choice between GET, POST, DELETE, PATCH or PUT. 
   • Query Parameters: Here you can provide a JSON object and the key/values will be added to the path as query parameters.
   • Request Body:  A JSON object can be provided for POST, PATCH or PUT operations that will be sent as a request body.
   • Headers: JSON object and these keys/values will be added as headers to the request.
2. RAW API LIST REQUEST BLOCK(s)
   • Method: Enum choice between GET, and POST in case of the block with No Paging Support is selected. The rest of the List Request blocks use the GET method by default.
   • Json Path for Output Records: This field is used to parse a section of the JSON returned by the API endpoint. This only returns a parsed VALUE.
   • Other Input Parameters: Other input parameters in the blocks are determined by the type of paging supported by the block. You can find more information about those under the input text boxes.

An Example

The following example will obtain list of events from the Tripletex application. We do not yet have a connector to do this and API key is used to obtain the value for the Authorization header/ Query string.  We first start by obtaining the Base URL and API Key. From the Tripletex documentation we can find these at https://api.tripletex.io/v2-docs/#/ . We use the following values: 

• Connector Name :  Tripletex
• Type : httpheader
• Authentication name : Authorization
• API Key : Basic {example API key}
• Base URL : api.tripletex.io/v2/

Once you fill the above details in the connector connection pop-up and click save, it would say the connector is connected. But since this is a generic connector, we don't have a way of verifying if the connector is actually connected. This will be evident when you run an endpoint.  

Using it in an automation

Create a new automation and drag the Raw API List Request Offset Based Paging block from the API Key connector on to the canvas.  Since the Tripletex connector supports offset based paging, we will be using the Offset Based Paging block to execute the list events api. 

Raw API List Request Offset Based Paging block inputs are as follows:

• Path : event  ( we have already provided "api.tripletex.io/v2/" during connection, "https://" is added by default)
• Query Parameters : (No query parameters required for this list events API)
• Headers : (No extra headers required, "Accept: application/json" is sent by default)
• Page Size Field Name : count 
• Page Size : 100
• Offset Field Name : from
• Offset Start : 0  
  These last four parameters enable the offset based paging. You can read more about this here.
• Json Path For Output Records : value ( The results for this list event API are sent inside a "value" array)
  
  

  
  
  

    Now when you execute this, it will return you the list of events in Tripletex.
  
  

   

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

This article gives an overview of the available blocks in the Google Sheets connector in Qlik Application Automation. It will also go over some basic examples of creating a new spreadsheet, adding new sheets to it, adding some information to the sheet as well as removing information from it.

You will find the mentioned example attached to this article in JSON format so you can upload it to your workspace and get started with it easily.

This connector supports operations to manage spreadsheets, adding/removing sheets from spreadsheets, manipulating sheet data, and a few other support operations.

Authentication for this connector is based on the oAuth2 Protocol. 

Let's start with the example prepared:

1. Create an automation;
2. From the left menu, select the Google Sheets connector;
3. Drag and drop the "Create Spreadsheet" block and fill in a title for the spreadsheet we will be adding
4. Let's go ahead and drag and drop the "Add Sheet to Spreadsheet" block and fill in the input parameters. (input parameter for the spreadsheet id can be filled manually by clicking the input and using the do lookup function for adding a new sheet to an existing spreadsheet or filled in from the output of the block before this one like shown in the picture)
   
   
   
   
5. Next step in our example was to append a couple of rows and columns to our newly added spreadsheet in one go by using the 'Append Rows to Sheet' block:
   
   
   For text values you can encase them between double quotes and for number you can simply send them as they are. The end result should look like the following picture in you document: 
   
   
   
6. Now in order to get the data from the spreadsheet we've used the block  'Get data from sheet with Headers' and the resulting output from that block will be a list object ( if there were multiple lines ) with the headers used as keys of the object and the values taken from the following lines.
7. Lastly we used the 'delete rows' block in order to delete 1 line from our sheet. Just as you can see on the block description, you are not allowed to delete all rows in a sheet.
   
   
   
   
8. In the presented JSON example, you will notice a disabled 'Delete Spreadsheet' block as well. That block is only available for accounts whose administrator has allowed the app beforehand in their G Suite administration panel, so not every user might have access to that blocks functionality. If you want to delete a spreadsheet and don't have the possibility from our Google Sheet connector, manually deleting the spreadsheets from your account is the only way to do it.

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.

Let's say you uploaded the wrong dataset, or want to clean up data files you've generated using AutoML.

These are the steps to delete a dataset from your working space in Qlik Catalog:

1. Navigate to the Catalog and set up filters on Space: your working space.
2. Click on Manage space
3. Choose Data Sources on the right-hand side.
   
   
   
   
   
   
   
   
4. Click the ... on the right-hand side of the dataset.
5. Click Delete
   
   

Environment

Qlik AutoML 

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.

Environment

• Qlik Sense Enterprise SaaS
• Qlik Application Automation

This article describes how an excel file can be converted to pdf file and send as an attachment via Send Mail block using Qlik Application Automation 

Automation Structure

The creation of an Excel report from a Qlik Sense straight table is explained in the first part of this article.

Let's walk through the blocks used in the automation workflow to convert the Excel file into a PDF file.

1. Use the 'Get File Download Url' block from Microsoft SharePoint Connector. You should set pdf as a value in the 'Format' parameter to convert the Excel file into a PDF file. This block returns the download URL of the PDF file in the response.
   
   
   
   

2. Open the PDF file using the 'Open File' block and select HTTP as the connector. Map the PDF file URL received from the above step in the 'URL' parameter.
   
   
   
   
   

3. Save the PDF file on Microsoft SharePoint using the 'Copy File on Microsoft SharePoint' block.
   
   
   
   
4. Open the PDF file created in Microsoft SharePoint using the 'Open File on Microsoft SharePoint' block.
   
   
   
   
    
5. Use the 'Send Mail' block from Mail Connector to send the PDF file as an attachment via email.
   
   
   
   

A JSON file containing the above automation is attached to this article.

Please follow the steps provided in the How to import automation from a JSON file article to import the automation.

Related Content 

• Generate Excel Report 
• Copy data from a Qlik Sense straight table to an Excel sheet 

This article explains how the Reporting connector in Qlik Application Automation can be used to generate multi-page PowerPoint reports. It also explains how to store the generated report on a cloud storage tool, like Microsoft SharePoint.

You can use the template that is available in the template picker. You can find it by navigating to Add new -> New automation -> Search templates and searching for 'Store a simple multi-page PowerPoint report to Microsoft SharePoint' in the search bar. Click on the Use template option to use it in the automation.

Environment

• Qlik Sense Enterprise SaaS
• Qlik Application Automation

Full Automation

Step-by-step description

The following steps describe how to build the demo automation. You will find a version of this automation attached to this article: "Store-a-simple-multi-page-PowerPoint-report-to-Microsoft-SharePoint.json".

1. Search for the 'Create Report' block in the Reporting connector, drag it to the automation editor, and attach it to the Start block. Configure it by specifying a name for the report and selecting Sheets mode = All option
2. Add the 'Generate Report' block to the automation and configure it to use the report we are building. Select PowerPoint as the format mode to generate a PowerPoint report.
   
   
   
   
3. Find the Cloud Storage connector in the Block Library and add the 'Copy File' block to the automation. Configure it to use the output from the 'Generate Report' block as the Source File and set the Destination connector to Microsoft SharePoint (or another destination of your choice). Include the Path from the output of the Generate Report block in the Destination Path parameter to use the generated filename. Use the 'do lookup' functionality to find a Drive Id or a Site Id,  but do not specify both.
   
   
   

Related Content

• Qlik Reporting Connector

This article contains information on how to use the “Split QVD Generation” template.

Content:

• How it works
• Configuring the apps
• Dimensional QVD App
• Parallel QVD Generator Template App
• Configuring the automation
• List Dimensional Values
• Copy App
• Debugging reloads
• Environment

While QVD files are optimized for loading speed, it is possible to further improve loading time during reloads by splitting a QVD file into several smaller ones. This will allow you to use Qlik Sense Cloud's ability to perform parallel reloads.

How it works

The template uses two Qlik Sense applications. Demo examples of what these applications may look like are attached to this article.

• The Dimensional QVD App is for selecting the dimension used for the splitting in the automation. Dimensions configured in this application can be used for the splitting of the data into multiple QVDs. The dimensional data can either be static or reloaded upon each run depending on need.
• The Parallel QVD Generator Template App is used to create a copy of the application for each dimensional value, resulting in QVD file splits that are based on the dimension selected from the Dimensional QVD App. Each copy will be prefixed with the same name as the dimensional value. After a reload, these temporary applications are deleted.

Configuring the apps

Dimensional QVD App

This app should have a table containing the dimensional values to be used for the splitting. Add the dimension on which you want to base the QVD split as a dimension in the application's Master Items. To visualize the values, you may also add the dimension to a table on a sheet. When considering what dimension to base the data split on, it is a good idea to select a dimension that has evenly distributed values, since this will improve performance. Having the data unevenly split, where most of the data is loaded into just one of the QVDs, would have minimal performance improvements compared to not splitting the QVD.

The attached demo uses sample data from Snowflake (TPCH_SF10) and "Region" is used as the dimension on which to base the data split.

Parallel QVD Generator Template App

This is the application that produces the data for the split QVD files. It is important that you filter on the dimension used for splitting in the WHERE clause in the load script of this application, as highlighted below in the example taken from the  attached demo application:

FROM "SNOWFLAKE_SAMPLE_DATA"."TPCH_SF10"."CUSTOMER","SNOWFLAKE_SAMPLE_DATA"."TPCH_SF10"."ORDERS","SNOWFLAKE_SAMPLE_DATA"."TPCH_SF10"."LINEITEM","SNOWFLAKE_SAMPLE_DATA"."TPCH_SF10"."REGION", "SNOWFLAKE_SAMPLE_DATA"."TPCH_SF10"."NATION","SNOWFLAKE_SAMPLE_DATA"."TPCH_SF10"."SUPPLIER"
where Year("O_ORDERDATE")=$(vYear) and Month("O_ORDERDATE")=$(vMonthNumber) and R_NAME = '$(vQVDName)' and "L_ORDERKEY"="O_ORDERKEY" and "C_CUSTKEY"="O_CUSTKEY" and "N_NATIONKEY"="C_NATIONKEY" and "N_REGIONKEY"="R_REGIONKEY" and "S_SUPPKEY"="L_SUPPKEY";

Configuring the automation

Once the applications have been set up, they can be used in the automation template. The template requires the configuration of two blocks:

List Dimensional Values

1. App Id - Use do lookup to find the Dimensional QVD App and set it as the value.
2. Dimension Id - Use do lookup to find the dimension on which  you want the base the QVD splits. This should match the statement included in the WHERE clause of the Parallel QVD Generator Template App's load script.

Copy App

1. App Id - Use do lookup to search for Parallel QVD Generator Template App and set it as the value.
2. Space Id - Use do lookup to find the space where you want to copy the temporary Apps.

Debugging reloads

If you wish to add debugging to the reloads you can add this after the “Wait For Reloads To Complete” block.

1. Add a list variable that takes the output from the “Wait For Reloads To Complete” block. The output will include information on the reload status per app. If a reload should fail you can also look at this output to find detailed information on the error in the log item.
   
   
   
   
2. The variable “reloadLog” now contains a list generated by the list variable block, where only the first item is populated. Select this list item and check the status for each reload using a “Filter List”.
   
   
   
3. Add a new list variable to store the App Ids of any failed reloads.
   
   
   
   
4.  Add a “Condition” block to check if there are any failed reloads.
   
   
   
5. Add an “Update Run Title” block and select a title that indicates that the run had failed reloads, for example, “Reload(s) failed”. This title can be used to filter out automation runs that failed in the automation’s history tab.
   
   
6. Add an “Error” block to stop the automation to output information on which reloads failed by applying the implode formula to the reloadErrors variable, which contains a list of App IDs for failed reloads. Finally, apply the text formula to turn this into a string.
   
   

If you can’t identify the app that failed based on its id, use the "Get App Information" to retrieve the app name.

Note that automation logs are removed after 30 days. If you wish to keep older logs, then you must add blocks to write the failed reload logs to a cloud storage destination.

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.

Environment

Qlik Application Automation

This article gives an overview of verifying the load script of the Qlik Sense app from new Github pull request using Qlik Application Automation. It explains a basic example of a template configured in Qlik Application Automation for this scenario

You can make use of the template which is already available in the template picker

You can find it by navigating to Add new -> New automation -> Search templates and search for 'Verify load script in Qlik Sense app from new GitHub pull request' in the search bar and click on the Use template option in order to use it in the automation

Environment

Before you begin, you'll need the following:

• Qlik Sense app in a shared space
• GitHub repository with a copy of the shared app's load script as a text file

Steps:

The following steps describe how to build the demo automation. You can also find an exported version of this automation attached to this article as "Verify-Qlik-Sense-app-load-script-from-new-GitHub-pull-request.json".

1. Configure new automation to be webhook triggered on every new pull request in the GitHub repo.
   
   
   
   
2. Add a condition block to check whether the new pull request was opened and stop automation if the pull request was not opened. You can change the condition to use other actions from the pull request (like 'synchronize'). Or use the 'Add condition' option for multiple actions. 
   
   
   
   
3. Get the load script from the file using the 'Get File Content' block. In this example, there's only one file so the first file from the List Pull Request Files block is used. If you have multiple files, use the Lookup Item In List block to find a specific file. 
   
   
   
4. Create a new (temporary) app and set the load script from the pull request as this app's load script. When retrieving files from GitHub, they're base64 encoded by default. Use the Base64 Decode formula from the formula picker to decode the file's content before setting the load script. Notice the use of the Save App block to make the updated load script available for the Reloads API.
   
   
   
   
5. Reload & delete the temporary app and evaluate the reload's outcome:
   1. If the reload is successful, alert your team via slack with a success message and use Add Pull Request Review block to comment on the pull request that the load script test has succeeded 
      
      
      
      
   2. If the reload fails, alert your team via slack with a failure message and use Add Pull Request Review block to request changes on the pull request
      
      
      

A JSON file containing the above automation is attached to this article.

Please follow the steps provided in How to import  automation from a JSON file  article to import the automation

Related Content 

• Manage load scripts in shared apps using GitHub and Qlik Application Automation
• How to get started with GitHub in Qlik Application Automation
• CI/CD pipelines for Qlik Sense apps and Qlik Application Automation
• How to use the Save App block in Qlik Application Automation

• A user subscribes to a sheet or chart that references a variable.
• When the variable changes, the report is still delivered with the old value.
• The Qlik Sense app, on the other hand, shows the correct value when opened.

Environment

• Qlik Cloud 

Resolution

This is a current limitation.
Variables are among the settings that are only saved (as value, not as formulas) when the subscription is saved.

The subscription report will continue to use first value.

We have an improvement planned to include variables as formulas planned in 2023, but currently no precise ETA.