Microsoft Excel

Overview of the Microsoft Excel integration.

The following documentation is for Microsoft Excel v2.2.3.

Overview

Microsoft Excel is the industry-standard spreadsheet application with advanced data analysis and visualization capabilities. This integration enables automated data processing, report generation, and spreadsheet management workflows. Leverage Excel's powerful features for automated data analysis and business intelligence.

Setup

The following integrations need to be connected to your Kognitos workspace:

• Microsoft Excel

• Microsoft Office 365

Note: The Microsoft Excel integration depends on Microsoft Office 365 for core Microsoft capabilities.

Steps

Follow these steps to connect the integration in Kognitos:

1

Navigate

Using the left navigation menu, go to Integrations → Explore Integrations.

2

Find

Search for the integration and click on it.

3

Connect

Click on Connect to add a connection to the integration.

4

Configure

Add a name for the connection. You'll be prompted for authentication details if needed. Then, click on Connect.

Authentication

Use one of the following authentication methods to connect this integration in Kognitos. Each method has its own configuration requirements.

Connect using Client ID, Client Secret and Tenant ID

Connect to the Microsoft Graph API using the provided client credentials.

Label	Description	Type
Client ID	The client ID of the application registered in Azure AD.	text
Client Secret	The client secret of the application registered in Azure AD.	sensitive
Tenant ID	The tenant ID of the Azure AD directory.	text

Connect using Client ID, Certificate and Tenant ID

Connect to the Microsoft Graph API using certificate credentials.

Label	Description	Type
Client ID	The client ID of the application registered in Azure AD.	text
Certificate	PEM-encoded X.509 certificate string containing both the certificate and private key.	sensitive
Tenant ID	The tenant ID of the Azure AD directory.	text

Connect using Client ID, Certificate, Private Key and Tenant ID

Connect to the Microsoft Graph API using certificate and private key.

Label	Description	Type
Client ID	The client ID of the application registered in Azure AD.	text
Certificate	PEM-encoded certificate string.	sensitive
Private Key	PEM-encoded private key string.	sensitive
Tenant ID	The tenant ID of the Azure AD directory.	text

Actions

The following actions are available in the Microsoft Excel integration:

1. Clear the worksheet range

Delete the contents of a range of cells in an Excel worksheet.

2. Create a table on a worksheet range

Create a table from a specified range of cells in an Excel worksheet.

3. Create a worksheet range in a sheet

Create a range object representing a set of cells in an Excel worksheet, defined by start and end addresses.

4. Delete a column from the table

Delete a column from a table in an Excel worksheet.

5. Delete a row from the table

Delete a row from a table in an Excel worksheet.

6. Get the cell's color

Get the background color of a cell in an Excel worksheet.

7. Get the cell's formula

Get the formula of a cell in an Excel worksheet.

8. Get the cell's value

Get the value of a cell in an Excel worksheet.

9. Get the column count in a table

Get the number of columns in a table in an Excel worksheet.

10. Get the column count in a worksheet range

Get the number of columns in an Excel worksheet range.

11. Get the column's cells from the table

Get the cells from a column in a table in an Excel worksheet.

12. Get the file's sheets

Get the worksheets of an Excel file.

13. Get the following row range in a worksheet range

Get the following row in an Excel worksheet.

14. Get the row count in a table

Get the number of rows in a table in an Excel worksheet.

15. Get the row count in a worksheet range

Get the number of rows in an Excel worksheet range.

16. Get the row's cells from the table

Get the cells from a row in a table in an Excel worksheet.

17. Get the row's cells from the worksheet range

Get the cells from a row in an Excel worksheet range.

18. Get the sheet's tables

Get the tables from an Excel worksheet.

19. Get the sheet's used range

Get the used range of an Excel worksheet.

20. Get the worksheet range's rows

Get the rows from a range in an Excel worksheet.

21. Get the worksheet range from the table

Get the range of a table in an Excel worksheet.

22. Insert a new column in the table

Create a new column within a table in an Excel worksheet.

23. Insert a new row in the table

Create a new row in a table in an Excel worksheet.

24. Read the content from a table

Read the contents of a table in an Excel worksheet.

25. Read the content from a worksheet range

Read the contents of a range in an Excel worksheet.

26. Retrieve the columns from the table

Get the columns from a table in an Excel worksheet.

27. Retrieve the rows from the table

Get the rows from a table in an Excel worksheet.

28. Retrieve the worksheet range from a table

Get the range of a table in an Excel worksheet.

29. Set the cell's content to a value

Update the value of a cell in an Excel worksheet.

30. Set the cell's formula to a formula value

Update the formula of a cell in an Excel worksheet.

31. Write the content in a table

Update the contents of a table in an Excel worksheet.

32. Write the content in a worksheet range

Update the contents of a range in an Excel worksheet.

Concepts

Excel range reference

ExcelRangeRef represents a reference to a range of cells within an Excel worksheet. It provides detailed information about a contiguous set of cells and is designed for use in applications that require precise range-based operations, such as data extraction, modification, and analysis within spreadsheets.

Field Name	Description	Type
drive_id	The unique identifier for the drive containing the workbook.	text
file_id	The unique identifier for the workbook.	text
sheet_id	The unique identifier for the worksheet.	text
address	The address of the range.	text
address_local	The local address of the range.	optional[text]
cell_count	The number of cells in the range.	optional[number]
column_count	The number of columns in the range.	optional[number]
column_hidden	Whether the columns in the range are hidden.	optional[boolean]
column_index	The index of the first column in the range.	optional[number]
hidden	Whether the range is hidden.	optional[boolean]
row_count	The number of rows in the range.	optional[number]
row_hidden	Whether the rows in the range are hidden.	optional[boolean]
row_index	The index of the first row in the range.	optional[number]

Excel sheet reference

ExcelSheetRef represents a reference to a worksheet within an Excel workbook, providing details to uniquely identify and interact with a specific sheet. It facilitates precise data manipulation and integration, useful for data processing, automated workflows, and integration across multiple files.

Field Name	Description	Type
id	The unique identifier for the worksheet.	text
name	The name of the worksheet.	text
drive_id	The unique identifier for the drive containing the workbook.	text
file_id	The unique identifier for the workbook.	text

Excel table reference

ExcelTableRef represents a reference to a table within an Excel worksheet, providing essential details to uniquely identify and interact with specific tables. This utility is used to facilitate data management, structured data handling, and integration within larger workflows.

Field Name	Description	Type
id	The unique identifier for the worksheet.	text
name	The name of the worksheet.	text
drive_id	The unique identifier for the drive containing the workbook.	text
file_id	The unique identifier for the workbook.	text
sheet_id	The unique identifier for the worksheet.	text

Excel column reference

ExcelColumnRef represents a reference to a specific column within an Excel worksheet. It serves as a utility to uniquely identify and interact with data in a column, providing essential metadata that allows for efficient data manipulation and retrieval.

Field Name	Description	Type
id	The unique identifier for the column.	text
index	The index of the column.	number

Excel row reference

ExcelRowRef represents a reference to a specific row within an Excel worksheet. This utility enables efficient access, manipulation, and reference of data within rows, providing essential information to uniquely identify and interact with a row's content.

Field Name	Description	Type
id	The unique identifier for the row.	text
index	The index of the row.	number

Sharepoint file reference

A Sharepoint File Reference is a reference to a file in a SharePoint document library.

Field Name	Description	Type
id	The unique identifier for the document library.	optional[text]
name	The name of the document library.	optional[text]
web_url	URL that either displays the resource in the browser (for Office file formats), or is a direct link to the file (for other formats).	optional[text]
parent_reference	Parent information, if the item has a parent.	optional[json]
is_folder	Boolean flag indicating whenever this item is a folder or not.	optional[boolean]
file_name	The name of the file. Same as name.	optional[text]

Excel cell reference

ExcelCellRef represents a reference to a specific cell within an Excel worksheet, serving as a utility to uniquely identify and manipulate data within the cell. It provides essential details to locate the cell precisely.

Field Name	Description	Type
drive_id	The unique identifier for the drive.	text
file_id	The unique identifier for the file.	text
sheet_id	The unique identifier for the sheet.	text
row_index	The row of the cell.	number
column_index	The column of the cell.	number
id	The unique identifier for the cell.	optional[text]
row_id	The unique identifier for the row of the cell.	optional[text]
column_id	The unique identifier for the column of the cell.	optional[text]
address	The address of the cell.	optional[text]
table_id	The unique identifier for the table.	optional[text]

Concept attribute specifications

parent_reference (sharepoint file reference)

Name	Type
id	optional[text]
drive_id	optional[text]