article How-to: Getting started with Microsoft Excel in Qlik Application Automation in Official Support Articles

How-to: Getting started with Microsoft Excel in Qlik Application Automation

MarkGeurtsen — Mon, 13 Mar 2023 08:57:41 GMT

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.

Re: How-to: Getting started with Microsoft Excel in Qlik Application Automation

Carl_Hunter — Thu, 30 Dec 2021 17:42:13 GMT

Can you help with "Add Row to Table" block of the Excel connector - I cannot get QAA to write a row to an existing table from a "Get Straight Table Data" block? The documentation is lacking in this area.

Re: How-to: Getting started with Microsoft Excel in Qlik Application Automation

MarkGeurtsen — Wed, 12 Jan 2022 13:11:00 GMT

The issue with writing data from the "Get Straight Table Data" block with "Add Row to Table" block is that the first block returns a list of Json objects where each object contains the header names as keys and the matching value for that row. The excel block expects a list of just values. You would have to convert the JSON objects to a list element containing only values.

Also answered in: https://community.qlik.com/t5/Qlik-Application-Automation/QAA-Update-Excel-rows-from-Table-Data/td-p/1877177

Re: How-to: Getting started with Microsoft Excel in Qlik Application Automation

crossroadsit — Wed, 12 Oct 2022 10:28:35 GMT

Is there a way to query for the column headers of a table in Excel?

I want to check the headers I have to ensure I'm matching them with the payload I want to insert/update.

I think I'm looking for something like this endpoint https://learn.microsoft.com/en-us/graph/api/table-list-columns?view=graph-rest-1.0&tabs=http

Re: How-to: Getting started with Microsoft Excel in Qlik Application Automation

MarkGeurtsen — Tue, 18 Oct 2022 11:53:19 GMT

Hi @crossroadsit ,

We do not yet have a block available for this.
This should be rather simple to implement, so I will add this as an enhancement to the connector and will update this message when that reaches live environments.

Thanks for your suggestion!

Re: How-to: Getting started with Microsoft Excel in Qlik Application Automation

Liam_Grant — Fri, 03 Mar 2023 14:08:39 GMT

Is this still limited to personal OneDrive areas? Or can it be used with any Sharepoint folder?

Re: How-to: Getting started with Microsoft Excel in Qlik Application Automation

Sonja_Bauernfeind — Mon, 13 Mar 2023 08:49:58 GMT

Hello @Liam_Grant Is this what you are a looking for? How to: Work with Excel files stored on Sharepoint using Qlik Application Automation

If not, I recommend posting your requirements in the Qlik Application Automation forum.

We'll also review the article itself though, so I appreciate your comment!

All the best,
Sonja

Re: How-to: Getting started with Microsoft Excel in Qlik Application Automation

luke_brady — Wed, 22 Mar 2023 11:23:22 GMT

I have an Excel file in Azure Blob Storage. When trying to read the contents of the excel file, obviously Drive ID isn't possible because the file is not saved locally.

Is there a workaround where I can enter e.g. the tenent ID for my Azure subscription where the drive ID is and Qlik Sense will be able to adjust?

I feel like only having the option to read from an excel file that is available locally would be quite limiting and not being proactive with current trends.

Thanks for your help.

Re: How-to: Getting started with Microsoft Excel in Qlik Application Automation

moshea — Sun, 10 Dec 2023 08:16:31 GMT

how can I know my Item ID?