Microsoft Excel
Overview of the Microsoft Excel integration.
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
Steps
Follow these steps to connect the integration in Kognitos:
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.
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.
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.
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 all filters on the table
Clear all filters on a table.
2. Clear the filter on the table's column
Clear the filter on a specific table column.
3. Clear the worksheet range
Delete the contents of a range of cells in an Excel worksheet.
4. Copy the sheet with a name
Copy a worksheet's content to a new sheet in the same workbook.
5. Create a new sheet in the file
Create a new worksheet in an Excel workbook.
6. Create a table on a worksheet range
Create a table from a specified range of cells in an Excel worksheet.
7. Create a worksheet range in a sheet
Create a range reference for a set of cells in an Excel worksheet, defined by start and end cell addresses. This does not modify the spreadsheet; it creates a local reference that can be used with other procedures to read, write, or clear data.
8. Create a workbook in a folder
Create a new empty Excel workbook in a SharePoint or OneDrive folder.
9. Delete a column from the table
Delete a column from a table in an Excel worksheet.
10. Delete a row from the table
Delete a row from a table in an Excel worksheet.
11. Delete the sheet
Delete a worksheet from an Excel workbook.
12. Filter the table's column by a filter value
Apply a value-based filter to a table column.
13. Get the cell's color
Get the background color of a cell in an Excel worksheet.
14. Get the cell's formula
Get the formula of a cell in an Excel worksheet.
15. Get the cell's number format
Get the number format string of a cell in an Excel worksheet.
16. Get the cell's value
Get the value of a cell in an Excel worksheet.
17. Get the column count in a table
Get the number of columns in a table in an Excel worksheet.
18. Get the column count in a worksheet range
Get the number of columns in an Excel worksheet range.
19. Get the column's cells from the table
Get the cells from a column in a table in an Excel worksheet.
20. Get the file's sheets
Get the worksheets of an Excel file.
21. Get the following row range in a worksheet range
Get a range representing the row immediately below the given range, spanning the same columns. Useful for appending data below an existing range or table.
22. Get the row count in a table
Get the number of rows in a table in an Excel worksheet.
23. Get the row count in a worksheet range
Get the number of rows in an Excel worksheet range.
24. Get the row's cells from the table
Get the cells from a row in a table in an Excel worksheet.
25. Get the row's cells from the worksheet range
Get the cells from a row in an Excel worksheet range.
26. Get the sheet's pivot tables
Get the pivot tables from an Excel worksheet.
27. Get the sheet's protection status
Get the protection status of a worksheet.
28. Get the sheet's tables
Get the tables from an Excel worksheet.
29. Get the sheet's used range
Get the used range of an Excel worksheet.
30. Get the worksheet range's rows
Get the rows from a range in an Excel worksheet.
31. Get the worksheet range from the table
Get the range of a table in an Excel worksheet.
32. Insert a new column in the table
Insert a new column within a table in an Excel worksheet.
33. Insert a new row in the table
Insert a new row in a table in an Excel worksheet.
34. Open a workbook at a url
Open an Excel workbook by its SharePoint or OneDrive URL, returning its sheets.
35. Protect the sheet
Protect a worksheet from editing.
36. Read the content from a table
Read the contents of a table in an Excel worksheet.
37. Read the content from a worksheet range
Read the contents of a range in an Excel worksheet.
38. Recalculate the workbook
Force a full recalculation of all formulas in the workbook.
39. Refresh all pivot tables in the sheet
Refresh all pivot tables in a worksheet.
40. Refresh the pivot table
Refresh a specific pivot table.
41. Rename the sheet to a new name
Rename a worksheet in an Excel workbook.
42. Retrieve the columns from the table
Get the columns from a table in an Excel worksheet.
43. Retrieve the rows from the table
Get the rows from a table in an Excel worksheet.
44. Set the cell's color to a color
Set the background fill color of a cell in an Excel worksheet.
45. Set the cell's content to a value
Update the value of a cell in an Excel worksheet.
46. Set the cell's font color to a color
Set the font color of a cell in an Excel worksheet.
47. Set the cell's formula to a formula value
Update the formula of a cell in an Excel worksheet.
48. Set the cell's number format to a format string
Set the number format of a cell in an Excel worksheet.
49. Set the range's color to a color
Set the background fill color of all cells in a range.
50. Set the range's font color to a color
Set the font color of all cells in a range.
51. Set the worksheet range's number format to a format string
Set a uniform number format on all cells in a range.
52. Sort the table by a column index
Sort a table by a specified column.
53. Sort the worksheet range by a column index
Sort a range by a specified column.
54. Unprotect the sheet
Remove protection from a worksheet.
55. Write the content in a table
Update the contents of an Excel table with new data.
56. Write the content in a worksheet range
Update the contents of a range in an Excel worksheet.
Concepts
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.
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.
id
The unique identifier for the column.
text
index
The 0-based index of the column.
number
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.
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 0-based 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 0-based 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.
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
Sharepoint file reference
A Sharepoint File Reference is a reference to a file in a SharePoint document library.
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]
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]
e_tag
Version-specific entity tag. Combined with id, uniquely identifies a file version (useful for deduplicating trigger events).
optional[text]
Sharepoint folder reference
A Sharepoint Folder Reference is a reference to a folder in a SharePoint document library.
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]
is_folder
Boolean flag indicating whenever this item is a folder or not.
optional[boolean]
folder_name
The name of the folder. Same as name.
optional[text]
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.
id
The unique identifier for the row.
text
index
The 0-based index of the row.
number
Excel pivot table reference
ExcelPivotTableRef represents a reference to a pivot table within an Excel worksheet.Graph API is read-only for pivot tables: list and refresh only. Cannot create, modify, or delete pivot tables via the API.
id
The unique identifier for the pivot table.
text
name
The name of the pivot table.
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 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.
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 0-based row index of the cell (A1 = row 0).
number
column_index
The 0-based column index of the cell (A = 0, B = 1, ...).
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)
id
optional[text]
drive_id
optional[text]
parent_reference (sharepoint folder reference)
id
optional[text]
drive_id
optional[text]
Last updated
Was this helpful?