Cell.Worksheet

Cell.Worksheet is an object type of the Spreadsheet module (Office.cell(iframe)). 30 methods.

Generated from the SDK type definitions

Spreadsheet · 6 object types · 156 methods · namespace Cell. This reference is generated from the API document artifact (sdk-api-doc.html) produced with the SDK build. Descriptions are the editor's API comments as published. Pin the SDK and Office versions together and regenerate when either changes.

Remarks

• Module: Spreadsheet · Office.cell(iframe) · namespace Cell
• 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 a Promise.

Methods

Method	Description
addChart(chartType, dataRange, opts)	Adds a chart to this sheet and returns its handle.
addTable(dataRange, hasHeaders)	Creates a table on this sheet and returns its handle.
getChart(oid)	Returns a chart handle by ID.
getChartList()	Returns the chart summaries of this sheet.
getCommentList()	Returns the cell comments of this sheet.
getConditionalFormatList()	Returns the conditional formatting rules of this sheet.
getDataValidationList()	Returns the data validation rules of this sheet.
getFilter()	Returns the auto-filter handle of this sheet.
getFreezePanes()	Returns the freeze panes state.
getHyperlinkList()	Returns the hyperlinks of this sheet.
getMergeCellList()	Returns the merged cells of this sheet.
getName()	Returns the sheet name.
getRange(range)	Returns a Range handle for an area of this sheet.
getSelection()	Returns a Range handle for the selection of this sheet.
getSheetInfo()	Returns the sheet properties at once.
getTabColor()	Returns the sheet tab color as "#RRGGBB".
getTableList()	Returns the table summaries of this sheet.
getUsedRange()	Returns the rectangular range that encloses every cell with a value or cell formatting.
getZoom()	Returns the zoom factor.
goto(range)	Selects the given area and scrolls it into view.
isGridlinesVisible()	Returns whether gridlines are shown.
isHidden()	Returns whether the sheet is hidden.
setFreezePanes(rows, cols)	Sets freeze panes.
setGridlinesVisible(visible)	Sets gridline visibility.
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 formatting rules at once.

Method details

addChart(chartType, dataRange, opts)

Adds a chart to this sheet and returns its handle. The title is taken from the data automatically. Up to 100 rows or columns are supported as series.

addChart(
  chartType: ChartType,
  dataRange: string | RangeCoords,
  opts?: AddChartOptions
): Promise<Chart>;

Parameters

Name	Type	Description
chartType	ChartType	chart type; an unsupported type raises an error
dataRange	string | RangeCoords	data range the chart uses; an A1-notation string also works ("A1:B2", or "E10" for a single cell)
opts (optional)	AddChartOptions	Additional options

Returns

Promise<Chart>

addTable(dataRange, hasHeaders)

Creates a table on this sheet and returns its handle. The table name is assigned automatically. Without a header row, or with a single-row range, an empty header row is inserted at the top, so the content below shifts down one row and the table is one row taller than the range. It cannot be created during co-editing, or when the range covers whole rows or whole 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 turn into a table; an A1-notation string also works ("A1:B2", or "E10" for a single cell)
hasHeaders (optional)	boolean	whether to use the first row of the range as the header (default: true)

Returns

Promise<Table>

getChart(oid)

Returns a chart handle 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; the oid in the getChartList() result

Returns

Chart

getChartList()

Returns the chart summaries of this sheet. When a handle is needed, call getChart() with the summary's oid.

getChartList(): Promise<ChartInfo[]>;

Returns

Promise<ChartInfo[]>

getCommentList()

Returns the cell comments of this sheet.

getCommentList(): Promise<CommentInfo[]>;

Returns

Promise<CommentInfo[]>

getConditionalFormatList()

Returns the conditional formatting rules of this sheet. Each item can be passed back as-is when re-adding or moving a rule.

getConditionalFormatList(): Promise<ConditionalFormatInfo[]>;

Returns

Promise<ConditionalFormatInfo[]>

getDataValidationList()

Returns the data validation rules of this sheet.

getDataValidationList(): Promise<DataValidationRule[]>;

Returns

Promise<DataValidationRule[]>

getFilter()

Returns the auto-filter handle of this sheet. A handle is returned even when no auto filter is set, so check enabled from the handle's getInfo() and set one with create().

getFilter(): Filter;

Returns

Filter

getFreezePanes()

Returns the freeze panes state.

getFreezePanes(): Promise<FreezePanes>;

Returns

Promise<FreezePanes>

getHyperlinkList()

Returns the hyperlinks of this sheet.

getHyperlinkList(): Promise<HyperlinkInfo[]>;

Returns

Promise<HyperlinkInfo[]>

getMergeCellList()

Returns the merged cells of this sheet. Use Range.getMergeCellList() when only the merges that overlap a range are needed.

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 handle for an area of this sheet. The handle keeps the coordinates it was created with even when rows or columns are inserted or deleted.

getRange(range: string | RangeCoords): Range;

Parameters

Name	Type	Description
range	string | RangeCoords	target area; an A1-notation string also works ("A1:B2", "E10" for a single cell, "5:5" for a whole row, "A:C" for whole columns)

Returns

Range

getSelection()

Returns a Range handle for the selection of this sheet. The selection is kept per sheet, so it can be read on a sheet that is not active.

getSelection(): Promise<Range>;

Returns

Promise<Range>

getSheetInfo()

Returns the sheet properties at once. Each item is the same value as the individual getter of the same name.

getSheetInfo(): Promise<SheetInfo>;

Returns

Promise<SheetInfo>

getTabColor()

Returns the sheet tab color as "#RRGGBB". A sheet with no tab color returns an empty string.

getTabColor(): Promise<string>;

Returns

Promise<string>

getTableList()

Returns the table summaries of this sheet. When a handle is needed, call Workbook.getTable() with the summary's name.

getTableList(): Promise<TableInfo[]>;

Returns

Promise<TableInfo[]>

getUsedRange()

Returns the rectangular range that encloses every cell with a value or cell formatting. Charts, shapes, row heights, and column widths are not reflected in it.

getUsedRange(): Promise<Range>;

Returns

Promise<Range> - null on a sheet with no values and no cell formatting

getZoom()

Returns the zoom factor.

getZoom(): Promise<number>;

Returns

Promise<number> - factor where 1 is 100%; it is not converted to a percentage

goto(range)

Selects the given area and scrolls it into view. When the target sheet is not active it is activated first, and a hidden sheet is replaced by another visible sheet to move to.

goto(range: string | RangeCoords): Promise<SelectionInfo>;

Parameters

Name	Type	Description
range	string | RangeCoords	area to move to; an A1-notation string also works ("A1:B2", or "E10" for a single cell)

Returns

Promise<SelectionInfo> - carries the name of the sheet moved to instead, when a hidden sheet was given

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>

setFreezePanes(rows, cols)

Sets freeze panes. A value of 0 or less, or null, releases the freeze in that direction.

setFreezePanes(rows?: number | null, cols?: number | null): Promise<void>;

Parameters

Name	Type	Description
rows (optional)	number | null	number of rows to freeze at the top (default: 0)
cols (optional)	number | null	number of columns to freeze on the left (default: 0)

Returns

Promise<void>

setGridlinesVisible(visible)

Sets gridline visibility.

setGridlinesVisible(visible: boolean): Promise<void>;

Parameters

Name	Type	Description
visible	boolean	true shows gridlines, false hides them

Returns

Promise<void>

setHidden(hidden)

Hides the sheet or shows it again. It does nothing when this is the only visible sheet. Hiding the active sheet makes an adjacent visible sheet active.

setHidden(hidden: boolean): Promise<void>;

Parameters

Name	Type	Description
hidden	boolean	true hides it, false shows it again

Returns

Promise<void>

setName(name)

Renames the sheet.

setName(name: string): Promise<void>;

Parameters

Name	Type	Description
name	string	name of the new sheet

Returns

Promise<void>

setSelection(range)

Sets the selection. When the target sheet is not active it is activated first; a hidden sheet cannot be activated and raises an error. Use goto() to scroll the selected area into view.

setSelection(range: string | RangeCoords): Promise<SelectionInfo>;

Parameters

Name	Type	Description
range	string | RangeCoords	area to select; an A1-notation string also works ("A1:B2", or "E10" for a single cell)

Returns

Promise<SelectionInfo>

setTabColor(color)

Sets the sheet tab color. An empty string, null, or omitting it clears the tab color.

setTabColor(color?: string | null): Promise<void>;

Parameters

Name	Type	Description
color (optional)	string | null	tab color; besides "#RRGGBB" it accepts 3-, 4-, and 8-digit hex, hex without "#", and color names such as "red"

Returns

Promise<void>

setZoom(zoomFactor)

Sets the zoom factor. A value outside the allowed range raises an error instead of being clamped.

setZoom(zoomFactor: number): Promise<void>;

Parameters

Name	Type	Description
zoomFactor	number	factor where 1 is 100%; it must be between 0.5 and 1.5

Returns

Promise<void>

updateConditionalFormatRules(descriptor)

Edits conditional formatting rules at once. Rule deletion, applied-range changes, and priority moves are handled as one batch, so nothing changes when any one of them cannot be applied. A rule's condition itself cannot be changed; to change it, delete the rule and add it again.

updateConditionalFormatRules(descriptor: ConditionalFormatUpdate): Promise<void>;

Parameters

Name	Type	Description
descriptor	ConditionalFormatUpdate	edits to apply

Returns

Promise<void>