Skip to main content
Announcements
July 15, NEW Customer Portal: Initial launch will improve how you submit Support Cases. READ MORE

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

No ratings
cancel
Showing results for 
Search instead for 
Did you mean: 
MarkGeurtsen
Support
Support

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

Last Update:

Mar 13, 2023 4:57:41 AM

Updated By:

MarkGeurtsen

Created date:

Sep 22, 2021 7:42:09 AM

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:

MarkGeurtsen_0-1632310780764.png

 

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.

Labels (1)
Comments
Carl_Hunter
Partner Ambassador
Partner Ambassador

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. 

MarkGeurtsen
Support
Support

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...

crossroadsit
Contributor II
Contributor II

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

MarkGeurtsen
Support
Support

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!

Liam_Grant
Partner - Contributor
Partner - Contributor

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

Sonja_Bauernfeind
Digital Support
Digital Support

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 

Elarbe
Contributor III
Contributor III

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.

moshea
Contributor III
Contributor III

how can I know my Item ID?

Version history
Last update:
‎2023-03-13 04:57 AM
Updated by: