What's new in Excel JavaScript API 1.14
The ExcelApi 1.14 added objects to control the data table feature of a chart, a method to locate all the precedent cells of a formula, and worksheet protection events to track changes to the protection state of a worksheet. It also added multiple getItemOrNullObject methods for objects like CommentCollection, ShapeCollection, and StyleCollection to improve error handling.
| Feature area | Description | Relevant objects |
|---|---|---|
| Chart data tables | Control appearance, formatting, and visibility of data tables on charts. | Chart, ChartDataTable, ChartDataTableFormat |
| Formula precedents | Return all the precedent cells of a formula. | Range |
| Queries | Retrieve Power Query attributes like name, refresh date, and query count. | Query, QueryCollection |
| Worksheet protection events | Track changes to the protection state of a worksheet and the source of those changes. | WorksheetProtectionChangedEventArgs, Worksheet, WorksheetCollection |
API list
The following table lists the APIs in Excel JavaScript API requirement set 1.14. To view API reference documentation for all APIs supported by Excel JavaScript API requirement set 1.14 or earlier, see Excel APIs in requirement set 1.14 or earlier.
| Class | Fields | Description |
|---|---|---|
| AutoFilter | clearColumnCriteria(columnIndex: number) | Clears the column filter criteria of the AutoFilter. |
| ChangeDirectionState | deleteShiftDirection | Represents the direction (such as up or to the left) that the remaining cells will shift when a cell or cells are deleted. |
| insertShiftDirection | Represents the direction (such as down or to the right) that the existing cells will shift when a new cell or cells are inserted. | |
| Chart | getDataTable() | Gets the data table on the chart. |
| getDataTableOrNullObject() | Gets the data table on the chart. | |
| ChartDataTable | format | Represents the format of a chart data table, which includes fill, font, and border format. |
| showHorizontalBorder | Specifies whether to display the horizontal border of the data table. | |
| showLegendKey | Specifies whether to show the legend key of the data table. | |
| showOutlineBorder | Specifies whether to display the outline border of the data table. | |
| showVerticalBorder | Specifies whether to display the vertical border of the data table. | |
| visible | Specifies whether to show the data table of the chart. | |
| ChartDataTableFormat | border | Represents the border format of chart data table, which includes color, line style, and weight. |
| fill | Represents the fill format of an object, which includes background formatting information. | |
| font | Represents the font attributes (such as font name, font size, and color) for the current object. | |
| CommentCollection | getItemOrNullObject(commentId: string) | Gets a comment from the collection based on its ID. |
| CommentReplyCollection | getItemOrNullObject(commentReplyId: string) | Returns a comment reply identified by its ID. |
| ConditionalFormatCollection | getItemOrNullObject(id: string) | Returns a conditional format identified by its ID. |
| GroupShapeCollection | getItemOrNullObject(key: string) | Gets a shape using its name or ID. |
| Query | error | Gets the query error message from when the query was last refreshed. |
| loadedTo | Gets the query loaded to object type. | |
| loadedToDataModel | Specifies if the query loaded to the data model. | |
| name | Gets the name of the query. | |
| refreshDate | Gets the date and time when the query was last refreshed. | |
| rowsLoadedCount | Gets the number of rows that were loaded when the query was last refreshed. | |
| QueryCollection | getCount() | Gets the number of queries in the workbook. |
| getItem(key: string) | Gets a query from the collection based on its name. | |
| items | Gets the loaded child items in this collection. | |
| Range | getPrecedents() | Returns a WorkbookRangeAreas object that represents the range containing all the precedent cells of a specified range in the same worksheet or across multiple worksheets. |
| ShapeCollection | getItemOrNullObject(key: string) | Gets a shape using its name or ID. |
| StyleCollection | getItemOrNullObject(name: string) | Gets a style by name. |
| TableScopedCollection | getItemOrNullObject(key: string) | Gets a table by name or ID. |
| Workbook | queries | Returns a collection of Power Query queries that are part of the workbook. |
| Worksheet | onProtectionChanged | Occurs when the worksheet protection state is changed. |
| tabId | Returns a value representing this worksheet that can be read by Open Office XML. | |
| WorksheetChangedEventArgs | changeDirectionState | Represents a change to the direction that the cells in a worksheet will shift when a cell or cells are deleted or inserted. |
| triggerSource | Represents the trigger source of the event. | |
| WorksheetCollection | onProtectionChanged | Occurs when the worksheet protection state is changed. |
| WorksheetProtectionChangedEventArgs | isProtected | Gets the current protection status of the worksheet. |
| source | The source of the event. | |
| type | Gets the type of the event. | |
| worksheetId | Gets the ID of the worksheet in which the protection status is changed. |