Cell.Worksheet
Cell.Worksheet is an object type of the Spreadsheet module (Office.cell(iframe)). 33 methods.
Remarks
- Module: Spreadsheet ·
Office.cell(iframe)· namespaceCell - Obtained from:
Workbook.getWorksheet(sheet) - Navigation methods (
getChart(),getFilter(),getRange()) return a child handle synchronously without a round trip; the request is sent by the next terminal call. Every other method returns aPromise.
Methods
| Method | Description |
|---|---|
addChart(chartType, dataRange, seriesBy) | Adds a chart to this sheet and returns the Chart. |
addTable(dataRange, hasHeaders) | Creates a table on this sheet and returns the Table. |
deleteConditionalFormatRules(priorities) | Deletes conditional format rules. |
getChart(oid) | Returns a Chart by ID. |
getChartList() | Returns the list of chart information on this sheet. |
getCommentList() | Returns the list of cell comments on this sheet. |
getConditionalFormatList() | Returns the list of conditional format rules on this sheet. |
getDataValidationList() | Returns the list of data validation rules on this sheet. |
getFilter() | Returns a Filter that manages the AutoFilter on this sheet. |
getFreezePanes() | Returns the freeze panes state. |
getHyperlinkList() | Returns the list of hyperlinks on this sheet. |
getMergeCellList() | Returns the list of merged cells on this sheet. |
getName() | Returns the sheet name. |
getRange(range) | Returns a Range for a range on this sheet. |
getSelection() | Returns a Range for the selection on this sheet. |
getSheetInfo() | Returns the sheet properties in a single call. |
getTabColor() | Returns the sheet tab color in "#RRGGBB" format. |
getTableList() | Returns the list of table summaries on this sheet. |
getUsedRange() | Returns the rectangular range that encloses all cells that have a value or cell format. |
getZoom() | Returns the zoom factor. |
goto(range) | Selects the specified range and scrolls the view so that the range is visible. |
isGridlinesVisible() | Returns whether gridlines are shown. |
isHidden() | Returns whether the sheet is hidden. |
moveConditionalFormatRule(priority, to) | Moves one conditional format rule to another priority position. |
setConditionalFormatRuleRanges(priority, ranges) | Replaces the applied ranges of one conditional format rule. |
setFreezePanes(rows, cols) | Sets freeze panes. |
setGridlinesVisible(visible) | Sets whether gridlines are shown. |
setHidden(hidden) | Hides the sheet or shows it again. |
setName(name) | Renames the sheet. |
setSelection(range) | Sets the selection. |
setTabColor(color) | Sets the sheet tab color. |
setZoom(zoomFactor) | Sets the zoom factor. |
updateConditionalFormatRules(descriptor) | Edits conditional format rules in a single call. |
Method details
addChart(chartType, dataRange, seriesBy)
Adds a chart to this sheet and returns the Chart. The title is taken from the data and set automatically. Up to 100 rows or columns can be used as series. Only data ranges on this sheet are accepted.
addChart(
chartType: ChartType,
dataRange: string | RangeCoords,
seriesBy?: ChartSeriesBy
): Promise<Chart>;
Parameters
| Name | Type | Description |
|---|---|---|
chartType | ChartType | chart type. An unsupported type throws an error |
dataRange | string | RangeCoords | data range used by the chart. An A1 notation string is also accepted ("A1:B2", or "E10" for a single cell) |
seriesBy (optional) | ChartSeriesBy | series direction. If omitted, the direction is determined by the shape of the data |
Returns
Promise<Chart>
addTable(dataRange, hasHeaders)
Creates a table on this sheet and returns the Table. The table name is assigned automatically. If headers are not used or the range is a single row, an empty header row is added at the top, so the content below is pushed down by one row and the table is one row larger than the range. A table cannot be created while the document is being co-edited, or if the range is entire rows or entire columns or includes the last row of the sheet.
addTable(dataRange: string | RangeCoords, hasHeaders?: boolean): Promise<Table>;
Parameters
| Name | Type | Description |
|---|---|---|
dataRange | string | RangeCoords | range to make into a table. An A1 notation string is also accepted ("A1:B2", or "E10" for a single cell) |
hasHeaders (optional) | boolean | whether the first row of the range is used as the header row (default: true) |
Returns
Promise<Table>
deleteConditionalFormatRules(priorities)
Deletes conditional format rules.
deleteConditionalFormatRules(priorities: number[]): Promise<void>;
Parameters
| Name | Type | Description |
|---|---|---|
priorities | number[] | priorities of the rules to delete. An empty list is not accepted |
Returns
none
getChart(oid)
Returns a Chart by ID. Only charts on this sheet are searched.
getChart(oid: string): Chart;
Parameters
| Name | Type | Description |
|---|---|---|
oid | string | unique ID that identifies the chart. It is the oid in the getChartList() result |
Returns
Chart
getChartList()
Returns the list of chart information on this sheet. To get a Chart, call getChart() with the oid of an item.
getChartList(): Promise<ChartInfo[]>;
Returns
Promise<ChartInfo[]>
getCommentList()
Returns the list of cell comments on this sheet.
getCommentList(): Promise<CommentInfo[]>;
Returns
Promise<CommentInfo[]>
getConditionalFormatList()
Returns the list of conditional format rules on this sheet. The font size and name, gradient, and number format of rule.style are currently read-only. A dataBar loaded from a file with a look that is not in the presets appears as colors, gradient, and cfvo, and these three are currently read-only.
getConditionalFormatList(): Promise<ConditionalFormatInfo[]>;
Returns
Promise<ConditionalFormatInfo[]> - formula1 and formula2 of a rule, and formula of a ConditionalFormatValue whose type is formula, start with =
getDataValidationList()
Returns the list of data validation rules on this sheet.
getDataValidationList(): Promise<DataValidationInfo[]>;
Returns
Promise<DataValidationInfo[]> - formula1 and formula2 of a rule start with =, except that the comma-separated list of the list type is given without =
getFilter()
Returns a Filter that manages the AutoFilter on this sheet. A Filter is returned even if no AutoFilter is set, so check whether one is set with enabled from getInfo() of the Filter, and set one with create().
getFilter(): Filter;
Returns
Filter
getFreezePanes()
Returns the freeze panes state.
getFreezePanes(): Promise<FreezePanes>;
Returns
Promise<FreezePanes>
getHyperlinkList()
Returns the list of hyperlinks on this sheet.
getHyperlinkList(): Promise<HyperlinkInfo[]>;
Returns
Promise<HyperlinkInfo[]>
getMergeCellList()
Returns the list of merged cells on this sheet. To get only the merges that overlap a specific range, use Range.getMergeCellList().
getMergeCellList(): Promise<string[]>;
Returns
Promise<string[]> - array of merged areas in A1 notation
getName()
Returns the sheet name.
getName(): Promise<string>;
Returns
Promise<string>
getRange(range)
Returns a Range for a range on this sheet.
getRange(range: string | RangeCoords): Range;
Parameters
| Name | Type | Description |
|---|---|---|
range | string | RangeCoords | target range. An A1 notation string is also accepted ("A1:B2", "E10" for a single cell, "5:5" for an entire row, or "A:C" for entire columns). An address with a sheet name ("Sheet1!A1") is not accepted |
Returns
Range
getSelection()
Returns a Range for the selection on this sheet. The selection is stored per sheet, so it can be read even on a sheet that is not the active sheet.
getSelection(): Promise<Range>;
Returns
Promise<Range>
getSheetInfo()
Returns the sheet properties in a single call. Each item matches the getter of the same name.
getSheetInfo(): Promise<SheetInfo>;
Returns
Promise<SheetInfo>
getTabColor()
Returns the sheet tab color in "#RRGGBB" format. It is an empty string for a sheet with no color set.
getTabColor(): Promise<string>;
Returns
Promise<string>
getTableList()
Returns the list of table summaries on this sheet. To get a Table, call Workbook.getTable() with the name in the summary.
getTableList(): Promise<TableInfo[]>;
Returns
Promise<TableInfo[]>
getUsedRange()
Returns the rectangular range that encloses all cells that have a value or cell format. Charts, shapes, row heights, and column widths are not reflected in this range.
getUsedRange(): Promise<Range>;
Returns
Promise<Range> - null if the sheet has no values and no cell formats
getZoom()
Returns the zoom factor.
getZoom(): Promise<number>;
Returns
Promise<number> - zoom factor where 1 is 100%. It is not converted to a percentage
goto(range)
Selects the specified range and scrolls the view so that the range is visible. If the target sheet is not the active sheet, it is made the active sheet first, and a hidden sheet cannot be navigated to.
goto(range: string | RangeCoords): Promise<SelectionInfo>;
Parameters
| Name | Type | Description |
|---|---|---|
range | string | RangeCoords | range to go to. An A1 notation string is also accepted ("A1:B2", or "E10" for a single cell) |
Returns
Promise<SelectionInfo>
isGridlinesVisible()
Returns whether gridlines are shown.
isGridlinesVisible(): Promise<boolean>;
Returns
Promise<boolean>
isHidden()
Returns whether the sheet is hidden.
isHidden(): Promise<boolean>;
Returns
Promise<boolean>
moveConditionalFormatRule(priority, to)
Moves one conditional format rule to another priority position. The rules in between shift by one position.
moveConditionalFormatRule(priority: number, to: number): Promise<void>;
Parameters
| Name | Type | Description |
|---|---|---|
priority | number | priority of the rule to move |
to | number | position to move to. Pass the priority of the rule currently at that position |
Returns
none
setConditionalFormatRuleRanges(priority, ranges)
Replaces the applied ranges of one conditional format rule.
setConditionalFormatRuleRanges(priority: number, ranges: string | RangeCoords[]): Promise<void>;
Parameters
| Name | Type | Description |
|---|---|---|
priority | number | priority of the target rule |
ranges | string | RangeCoords[] | new applied ranges |
Returns
none
setFreezePanes(rows, cols)
Sets freeze panes. A value of 0 or less, or null, unfreezes that direction.
setFreezePanes(rows?: number | null, cols?: number | null): Promise<void>;
Parameters
| Name | Type | Description |
|---|---|---|
rows (optional) | number | null | number of top rows to freeze (default: 0) |
cols (optional) | number | null | number of left columns to freeze (default: 0) |
Returns
none
setGridlinesVisible(visible)
Sets whether gridlines are shown.
setGridlinesVisible(visible: boolean): Promise<void>;
Parameters
| Name | Type | Description |
|---|---|---|
visible | boolean | true to show gridlines, false to hide them |
Returns
none
setHidden(hidden)
Hides the sheet or shows it again. If the active sheet is hidden, an adjacent visible sheet becomes the active sheet. The sheet cannot be hidden if it is the only visible sheet.
setHidden(hidden: boolean): Promise<void>;
Parameters
| Name | Type | Description |
|---|---|---|
hidden | boolean | true to hide the sheet, false to show it again |
Returns
none
setName(name)
Renames the sheet.
setName(name: string): Promise<void>;
Parameters
| Name | Type | Description |
|---|---|---|
name | string | new sheet name |
Returns
none
setSelection(range)
Sets the selection. If the target sheet is not the active sheet, it is made the active sheet first, and a hidden sheet cannot be activated, so an error is thrown. To scroll the view so that the selected range is visible, use goto().
setSelection(range: string | RangeCoords): Promise<SelectionInfo>;
Parameters
| Name | Type | Description |
|---|---|---|
range | string | RangeCoords | range to select. An A1 notation string is also accepted ("A1:B2", or "E10" for a single cell) |
Returns
Promise<SelectionInfo>
setTabColor(color)
Sets the sheet tab color. Passing an empty string or null, or omitting it, clears the tab color.
setTabColor(color?: string | null): Promise<void>;
Parameters
| Name | Type | Description |
|---|---|---|
color (optional) | string | null | tab color. In addition to "#RRGGBB", hex with 3, 4, or 8 digits, hex without "#", and color names such as "red" are also accepted |
Returns
none
setZoom(zoomFactor)
Sets the zoom factor. A value outside the allowed range throws an error.
setZoom(zoomFactor: number): Promise<void>;
Parameters
| Name | Type | Description |
|---|---|---|
zoomFactor | number | zoom factor where 1 is 100%. It must be greater than or equal to 0.5 and less than or equal to 1.5 |
Returns
none
updateConditionalFormatRules(descriptor)
Edits conditional format rules in a single call. Deleting rules, changing applied ranges, and moving priorities are processed as one batch, and if any of them cannot be applied, nothing is changed. To apply only one edit, use the method for that action. The condition of a rule cannot be changed, so to change a condition, delete the rule and add it again.
updateConditionalFormatRules(descriptor: ConditionalFormatUpdate): Promise<void>;
Parameters
| Name | Type | Description |
|---|---|---|
descriptor | ConditionalFormatUpdate | edits to apply |
Returns
none