Cell.Range

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

Remarks

Module: Spreadsheet · Office.cell(iframe) · namespace Cell
Obtained from: Worksheet.getRange(range), Worksheet.getSelection(), Worksheet.getUsedRange(), Table.getRange()
Every method returns a Promise; signatures use TypeScript notation.

Methods

Method	Description
`addConditionalFormat(rule)`	Adds a conditional format rule to the range.
`autofitColumns()`	Autofits the width of the columns spanned by the range to the content of each column.
`autofitRows()`	Autofits the height of the rows spanned by the range to the content of each row.
`clearAll()`	Clears all content of the range.
`clearConditionalFormats()`	Clears the conditional format rules that overlap the range.
`clearDataValidation()`	Clears the data validation rules that overlap the range.
`clearFormat()`	Clears the cell formats (font, fill, alignment, border, and number format) of the range and unmerges the merges in the range.
`decreaseIndent()`	Decreases the indent level of the range by 1.
`deleteColumn()`	Deletes the columns spanned by the range.
`deleteComment()`	Deletes all comments in the range.
`deleteRow()`	Deletes the rows spanned by the range.
`deleteValue()`	Clears the values and formulas of all cells in the range.
`getAddress(includeSheetName)`	Returns the A1 address of this range ("B2:C5", or "E10" for a single cell).
`getAlignment()`	Returns the alignment format of the top-left cell of the range in a single call.
`getBackgroundColor()`	Returns the background color of the top-left cell of the range.
`getBorder()`	Returns the borders of the top-left cell of the range, split into the four edges and the diagonal.
`getCellContent()`	Returns the value, displayed string, formula, and kind of value of the top-left cell of the range in a single call.
`getCellContents()`	Returns the values, displayed strings, formulas, and kinds of value of the whole range as a row-major two-dimensional array.
`getCellFormats(properties)`	Returns the cell formats of the whole range as a row-major two-dimensional array.
`getColumnWidth()`	Returns the width (px) of the first column of the range.
`getCommentList()`	Returns the list of comments on the cells in the range.
`getCoordinates()`	Returns the 0-based coordinates of this range.
`getDisplayValue()`	Returns the string displayed for the top-left cell of the range.
`getDisplayValues()`	Returns the displayed strings of the whole range as a row-major two-dimensional array.
`getFill()`	Returns the fill of the top-left cell of the range.
`getFont()`	Returns the font format of the top-left cell of the range in a single call.
`getFontColor()`	Returns the font color of the top-left cell of the range.
`getFontName()`	Returns the font name of the top-left cell of the range.
`getFontSize()`	Returns the font size (pt) of the top-left cell of the range.
`getFontUnderline()`	Returns the underline type of the top-left cell of the range.
`getFormula()`	Returns the formula string of the top-left cell of the range.
`getFormulas()`	Returns the formulas of the whole range as a row-major two-dimensional array.
`getHorizontalAlign()`	Returns the horizontal alignment of the top-left cell of the range.
`getHyperlinkList()`	Returns the list of hyperlinks that overlap the range.
`getIndent()`	Returns the indent level of the top-left cell of the range.
`getMergeCellList()`	Returns the addresses of the merged areas that overlap the range as an array of A1 strings.
`getNumberFormat()`	Returns the number format of the top-left cell of the range.
`getRowHeight()`	Returns the height (px) of the first row of the range.
`getValue()`	Returns the value of the top-left cell of the range.
`getValues()`	Returns the values of the whole range as a row-major two-dimensional array.
`getVerticalAlign()`	Returns the vertical alignment of the top-left cell of the range.
`increaseIndent()`	Increases the indent level of the range by 1.
`insertColumn(count)`	Inserts columns at the position of the first column of the range.
`insertRow(count)`	Inserts rows at the position of the first row of the range.
`isColumnVisible()`	Returns whether the first column of the range is visible.
`isFontBold()`	Returns whether the top-left cell of the range is bold.
`isFontItalic()`	Returns whether the top-left cell of the range is italic.
`isFontStrikethrough()`	Returns whether the top-left cell of the range has strikethrough.
`isRowVisible()`	Returns whether the first row of the range is visible.
`isSingleCell()`	Returns whether the range is a single cell.
`isWrapText()`	Returns whether text wrapping is applied to the top-left cell of the range.
`merge()`	Merges the cells of the range into one.
`removeHyperlink()`	Removes the hyperlinks that contain, or are contained in, the range.
`setBackgroundColor(color)`	Fills the range with a solid background color.
`setBorder(type, lineStyle, color)`	Applies or clears borders on the range.
`setColumnVisible(visible)`	Shows or hides the columns spanned by the range.
`setColumnWidth(width)`	Sets the width of the columns spanned by the range.
`setComment(text)`	Adds a comment to the top-left cell of the range.
`setCommentVisible(visible)`	Changes the visibility of the comment on the top-left cell of the range.
`setDataValidation(rule)`	Applies a data validation rule to the range.
`setFontBold(isOn)`	Applies or removes bold for the range.
`setFontColor(color)`	Changes the font color of the range.
`setFontItalic(isOn)`	Applies or removes italic for the range.
`setFontName(name)`	Changes the font of the range.
`setFontSize(size)`	Changes the font size of the range.
`setFontStrikethrough(isOn)`	Applies or removes strikethrough for the range.
`setFontUnderline(isOn, isDouble)`	Applies or removes underline for the range.
`setHorizontalAlign(value)`	Sets the horizontal alignment of the range.
`setHyperlink(location, display, type)`	Adds a hyperlink to the range.
`setNumberFormat(formatCode)`	Sets the number format of the range by format code.
`setRowHeight(height)`	Sets the height of the rows spanned by the range.
`setRowVisible(visible)`	Shows or hides the rows spanned by the range.
`setValue(value)`	Enters the same value into every cell of the range.
`setValues(values)`	Enters a different value into each cell of the range.
`setVerticalAlign(value)`	Sets the vertical alignment of the range.
`setWrapText(isOn)`	Applies or removes text wrapping for the range.
`sort(key, order)`	Sorts the range by the values of the specified column.
`unmerge()`	Unmerges all merged areas that overlap the range.

Method details

`addConditionalFormat(rule)`

Adds a conditional format rule to the range. The added rule has a higher priority than the existing rules.

addConditionalFormat(rule: ConditionalFormatRule): Promise<void>;

Parameters

Name	Type	Description
`rule`	`ConditionalFormatRule`	rule to add

Returns

none

`autofitColumns()`

Autofits the width of the columns spanned by the range to the content of each column. An adjusted column may be widened automatically whenever the content or format of a number cell changes afterward.

autofitColumns(): Promise<void>;

Returns

none

`autofitRows()`

Autofits the height of the rows spanned by the range to the content of each row. An adjusted row is autofitted again whenever the content or format of a cell changes afterward.

autofitRows(): Promise<void>;

Returns

none

`clearAll()`

Clears all content of the range. The targets are values, formulas, formats, merges, hyperlinks, comments, data validation, sparklines, filters, and tables. It cannot be used on a range that covers only part of a merged area.

clearAll(): Promise<void>;

Returns

none

`clearConditionalFormats()`

Clears the conditional format rules that overlap the range. If a rule extends beyond the range, only the overlapping part is removed from the applied range of the rule, and if the whole rule is inside the range, the rule itself is removed.

clearConditionalFormats(): Promise<void>;

Returns

none

`clearDataValidation()`

Clears the data validation rules that overlap the range.

clearDataValidation(): Promise<void>;

Returns

none

`clearFormat()`

Clears the cell formats (font, fill, alignment, border, and number format) of the range and unmerges the merges in the range. Values and formulas are kept. It cannot be used on a range that covers only part of a merged area.

clearFormat(): Promise<void>;

Returns

none

`decreaseIndent()`

Decreases the indent level of the range by 1. It does not go below 0.

decreaseIndent(): Promise<void>;

Returns

none

`deleteColumn()`

Deletes the columns spanned by the range. The columns cannot be deleted if all columns of the sheet or the last visible column would be removed, or if the entire area to the right of the freeze panes would be removed.

deleteColumn(): Promise<void>;

Returns

none

`deleteComment()`

Deletes all comments in the range.

deleteComment(): Promise<void>;

Returns

none

`deleteRow()`

Deletes the rows spanned by the range. If the range covers the entire data area of a table, one data row is kept so that the table does not disappear.

deleteRow(): Promise<void>;

Returns

none

`deleteValue()`

Clears the values and formulas of all cells in the range. Formats are kept.

deleteValue(): Promise<void>;

Returns

none

`getAddress(includeSheetName)`

Returns the A1 address of this range ("B2:C5", or "E10" for a single cell). If the sheet name is included, a name that contains spaces or special characters is enclosed in quotes.

getAddress(includeSheetName?: boolean): Promise<string>;

Parameters

Name	Type	Description
`includeSheetName` (optional)	`boolean`	true to include the sheet name, false to return only the address (default: false)

Returns

Promise<string>

`getAlignment()`

Returns the alignment format of the top-left cell of the range in a single call. Each item matches the getter of the same name.

getAlignment(): Promise<AlignmentInfo>;

Returns

Promise<AlignmentInfo>

`getBackgroundColor()`

Returns the background color of the top-left cell of the range. It is undefined when there is no fill, and also for a gradient fill because it cannot be expressed as a single color. Use getFill if the fill type is also needed.

getBackgroundColor(): Promise<string | undefined>;

Returns

Promise<string | undefined> - color in "#RRGGBB" format

`getBorder()`

Returns the borders of the top-left cell of the range, split into the four edges and the diagonal. Only lines that are set are included.

getBorder(): Promise<BorderInfo | undefined>;

Returns

Promise<BorderInfo | undefined> - undefined if the format of the cell has no border information

`getCellContent()`

Returns the value, displayed string, formula, and kind of value of the top-left cell of the range in a single call. The value, displayed string, and formula match getValue, getDisplayValue, and getFormula.

getCellContent(): Promise<CellContent>;

Returns

Promise<CellContent> - Formulas start with =. Array formula cells are in the form \{=...\}

`getCellContents()`

Returns the values, displayed strings, formulas, and kinds of value of the whole range as a row-major two-dimensional array. The array has the same size as the range.

getCellContents(): Promise<CellContent[][]>;

Returns

Promise<CellContent[][]> - Formulas start with =. Array formula cells are in the form \{=...\}

`getCellFormats(properties)`

Returns the cell formats of the whole range as a row-major two-dimensional array. The outer array holds rows and the inner arrays hold columns. The array has the same size as the range.

getCellFormats(properties?: CellFormatProperty[]): Promise<CellFormat[][]>;

Parameters

Name	Type	Description
`properties` (optional)	`CellFormatProperty[]`	format items to include for each cell. If omitted, all items are included

Returns

Promise<CellFormat[][]> - each cell's format holds only the items specified in properties

`getColumnWidth()`

Returns the width (px) of the first column of the range.

getColumnWidth(): Promise<number>;

Returns

Promise<number>

`getCommentList()`

Returns the list of comments on the cells in the range.

getCommentList(): Promise<CommentInfo[]>;

Returns

Promise<CommentInfo[]>

`getCoordinates()`

Returns the 0-based coordinates of this range. Use getAddress() if the A1 address is needed.

getCoordinates(): Promise<SheetRangeCoords>;

Returns

Promise<SheetRangeCoords>

`getDisplayValue()`

Returns the string displayed for the top-left cell of the range. It is the result after the number format is applied, so it can differ from the stored value.

getDisplayValue(): Promise<string>;

Returns

Promise<string> - empty string if the cell is empty

`getDisplayValues()`

Returns the displayed strings of the whole range as a row-major two-dimensional array. The array has the same size as the range.

getDisplayValues(): Promise<string[][]>;

Returns

Promise<string[][]> - positions of empty cells are empty strings

`getFill()`

Returns the fill of the top-left cell of the range.

getFill(): Promise<FillInfo | undefined>;

Returns

Promise<FillInfo | undefined> - undefined if there is no fill

`getFont()`

Returns the font format of the top-left cell of the range in a single call. Each item matches the getter of the same name.

getFont(): Promise<FontInfo>;

Returns

Promise<FontInfo>

`getFontColor()`

Returns the font color of the top-left cell of the range. A cell with no color specified holds the default color of the document.

getFontColor(): Promise<string>;

Returns

Promise<string> - color in "#RRGGBB" format

`getFontName()`

Returns the font name of the top-left cell of the range. A cell with no font specified holds the default font name of the document.

getFontName(): Promise<string>;

Returns

Promise<string>

`getFontSize()`

Returns the font size (pt) of the top-left cell of the range. A cell with no size specified holds the default size of the document.

getFontSize(): Promise<number>;

Returns

Promise<number>

`getFontUnderline()`

Returns the underline type of the top-left cell of the range. Accounting underlines (singleAccounting and doubleAccounting) can only be read and cannot be set with setFontUnderline.

getFontUnderline(): Promise<"single" | "double" | "singleAccounting" | "doubleAccounting" | "none">;

Returns

Promise<"single" | "double" | "singleAccounting" | "doubleAccounting" | "none"> - "none" if there is no underline

`getFormula()`

Returns the formula string of the top-left cell of the range. Returns null for a cell that is not a formula (a constant or an empty cell).

getFormula(): Promise<string | null>;

Returns

Promise<string | null> - formula starting with =. Array formula cells are in the form \{=...\}

`getFormulas()`

Returns the formulas of the whole range as a row-major two-dimensional array. The array has the same size as the range.

getFormulas(): Promise<(string | null)[][]>;

Returns

Promise<(string | null)[][]> - formulas starting with =, where array formula cells are in the form \{=...\}. Positions of cells that are not formulas are null

`getHorizontalAlign()`

Returns the horizontal alignment of the top-left cell of the range. Returns only an explicitly set value, and a value outside HorizontalAlign can be returned.

getHorizontalAlign(): Promise<string | undefined>;

Returns

Promise<string | undefined> - undefined if not set

`getHyperlinkList()`

Returns the list of hyperlinks that overlap the range. Links that overlap only partly are included.

getHyperlinkList(): Promise<HyperlinkInfo[]>;

Returns

Promise<HyperlinkInfo[]>

`getIndent()`

Returns the indent level of the top-left cell of the range.

getIndent(): Promise<number>;

Returns

Promise<number> - 0 if no indent is set

`getMergeCellList()`

Returns the addresses of the merged areas that overlap the range as an array of A1 strings. A merge that overlaps only partly is not cut to fit the range, and the address of the entire merged area is returned. Use Worksheet.getMergeCellList to get the merge list of the whole sheet.

getMergeCellList(): Promise<string[]>;

Returns

Promise<string[]>

`getNumberFormat()`

Returns the number format of the top-left cell of the range. A cell with no format specified also returns the code of the document default format.

getNumberFormat(): Promise<string>;

Returns

Promise<string> - number format code that can be passed as is to setNumberFormat

`getRowHeight()`

Returns the height (px) of the first row of the range.

getRowHeight(): Promise<number>;

Returns

Promise<number>

`getValue()`

Returns the value of the top-left cell of the range. For a formula cell, returns the calculated result.

getValue(): Promise<string | number | boolean | undefined>;

Returns

Promise<string | number | boolean | undefined> - undefined for an empty cell

`getValues()`

Returns the values of the whole range as a row-major two-dimensional array. The outer array holds rows and the inner arrays hold columns. The array has the same size as the range. Each value matches getValue.

getValues(): Promise<(string | number | boolean | undefined)[][]>;

Returns

Promise<(string | number | boolean | undefined)[][]> - positions of empty cells are filled with undefined

`getVerticalAlign()`

Returns the vertical alignment of the top-left cell of the range. Returns only an explicitly set value, and a value outside VerticalAlign can be returned.

getVerticalAlign(): Promise<string | undefined>;

Returns

Promise<string | undefined> - undefined if not set

`increaseIndent()`

Increases the indent level of the range by 1. If the horizontal alignment of the range is not uniformly left or right, the whole range is first changed to left alignment and then the indent level is increased.

increaseIndent(): Promise<void>;

Returns

none

`insertColumn(count)`

Inserts columns at the position of the first column of the range. The inserted columns follow the format of the column before. Inserting at the first column of the sheet inserts a column with no format.

insertColumn(count?: number | null): Promise<void>;

Parameters

Name	Type	Description
`count` (optional)	`number \| null`	number of columns to insert. If it is not 1 or greater, as many columns as the range spans are inserted

Returns

none

`insertRow(count)`

Inserts rows at the position of the first row of the range. The inserted rows follow the format of the row before. Inserting at the first row of the sheet inserts a row with no format.

insertRow(count?: number | null): Promise<void>;

Parameters

Name	Type	Description
`count` (optional)	`number \| null`	number of rows to insert. If it is not 1 or greater, as many rows as the range spans are inserted

Returns

none

`isColumnVisible()`

Returns whether the first column of the range is visible.

isColumnVisible(): Promise<boolean>;

Returns

Promise<boolean>

`isFontBold()`

Returns whether the top-left cell of the range is bold.

isFontBold(): Promise<boolean>;

Returns

Promise<boolean>

`isFontItalic()`

Returns whether the top-left cell of the range is italic.

isFontItalic(): Promise<boolean>;

Returns

Promise<boolean>

`isFontStrikethrough()`

Returns whether the top-left cell of the range has strikethrough.

isFontStrikethrough(): Promise<boolean>;

Returns

Promise<boolean>

`isRowVisible()`

Returns whether the first row of the range is visible.

isRowVisible(): Promise<boolean>;

Returns

Promise<boolean>

`isSingleCell()`

Returns whether the range is a single cell. A range that spans multiple rows or columns returns false.

isSingleCell(): Promise<boolean>;

Returns

Promise<boolean>

`isWrapText()`

Returns whether text wrapping is applied to the top-left cell of the range.

isWrapText(): Promise<boolean>;

Returns

Promise<boolean>

`merge()`

Merges the cells of the range into one. Only the value of the top-left cell remains in the merged cell, and the other values are lost. Does nothing if the range is a single cell or overlaps a table. If an existing merged area only partly overlaps the range, the cells cannot be merged and an error is thrown.

merge(): Promise<void>;

Returns

none

`removeHyperlink()`

Removes the hyperlinks that contain, or are contained in, the range. This covers links whose area is entirely inside the range and links whose area entirely contains the range. Links that overlap only partly are kept. The cell value is kept and only the link format (blue underline) is cleared with it, and the format of another link that overlapped a removed link is restored.

removeHyperlink(): Promise<void>;

Returns

none

`setBackgroundColor(color)`

Fills the range with a solid background color. If no color is given, the fill is cleared.

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

Parameters

Name	Type	Description
`color` (optional)	`string \| null`	color in "#RRGGBB" format. If null, undefined, or an empty string, the fill is cleared

Returns

none

`setBorder(type, lineStyle, color)`

Applies or clears borders on the range. If type is "none", all borders of the range are cleared, and for any other type, the border that type refers to is merged into the existing borders. If lineStyle is "none", only the line at the position that type refers to is cleared and color is ignored. If there is no inner line to draw, "center", "middle", and "cross" do nothing.

setBorder(
  type: BorderType,
  lineStyle?: BorderLineStyle | null,
  color?: string | null
): Promise<void>;

Parameters

Name	Type	Description
`type`	`BorderType`	position to draw the border
`lineStyle` (optional)	`BorderLineStyle \| null`	line style (default: "thin")
`color` (optional)	`string \| null`	line color in "#RRGGBB" format (default: "#000000")

Returns

none

`setColumnVisible(visible)`

Shows or hides the columns spanned by the range.

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

Parameters

Name	Type	Description
`visible`	`boolean`	true to show, false to hide

Returns

none

`setColumnWidth(width)`

Sets the width of the columns spanned by the range. Setting a width on a hidden column also unhides it. Passing 0 or less hides the columns without changing the width. The specified columns have a fixed width, so they are not widened automatically when the content changes.

setColumnWidth(width: number): Promise<void>;

Parameters

Name	Type	Description
`width`	`number`	column width (px)

Returns

none

`setComment(text)`

Adds a comment to the top-left cell of the range. If a comment already exists, only its text is changed, and its visibility and position are kept.

setComment(text: string): Promise<void>;

Parameters

Name	Type	Description
`text`	`string`	comment text. It is unformatted text, and a line break is "\n"

Returns

none

`setCommentVisible(visible)`

Changes the visibility of the comment on the top-left cell of the range.

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

Parameters

Name	Type	Description
`visible`	`boolean`	true to always show, false to show only on mouse hover

Returns

none

`setDataValidation(rule)`

Applies a data validation rule to the range. A range reference of the list type must start with =, and without = it is treated as a comma-separated list.

setDataValidation(rule: DataValidationRule): Promise<void>;

Parameters

Name	Type	Description
`rule`	`DataValidationRule`	rule to apply

Returns

none

`setFontBold(isOn)`

Applies or removes bold for the range.

setFontBold(isOn: boolean): Promise<void>;

Parameters

Name	Type	Description
`isOn`	`boolean`	true to apply, false to remove

Returns

none

`setFontColor(color)`

Changes the font color of the range.

setFontColor(color: string): Promise<void>;

Parameters

Name	Type	Description
`color`	`string`	color in "#RRGGBB" format

Returns

none

`setFontItalic(isOn)`

Applies or removes italic for the range.

setFontItalic(isOn: boolean): Promise<void>;

Parameters

Name	Type	Description
`isOn`	`boolean`	true to apply, false to remove

Returns

none

`setFontName(name)`

Changes the font of the range.

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

Parameters

Name	Type	Description
`name`	`string`	font name

Returns

none

`setFontSize(size)`

Changes the font size of the range.

setFontSize(size: number | string): Promise<void>;

Parameters

Name	Type	Description
`size`	`number \| string`	font size (pt)

Returns

none

`setFontStrikethrough(isOn)`

Applies or removes strikethrough for the range.

setFontStrikethrough(isOn: boolean): Promise<void>;

Parameters

Name	Type	Description
`isOn`	`boolean`	true to apply, false to remove

Returns

none

`setFontUnderline(isOn, isDouble)`

Applies or removes underline for the range.

setFontUnderline(isOn: boolean, isDouble?: boolean): Promise<void>;

Parameters

Name	Type	Description
`isOn`	`boolean`	true to apply, false to remove. If false, isDouble is ignored
`isDouble` (optional)	`boolean`	true for double underline, false for single underline (default: false)

Returns

none

`setHorizontalAlign(value)`

Sets the horizontal alignment of the range. Setting it also resets the indent of the range to 0.

setHorizontalAlign(value: HorizontalAlign): Promise<void>;

Parameters

Name	Type	Description
`value`	`HorizontalAlign`	horizontal alignment

Returns

none

`setHyperlink(location, display, type)`

Adds a hyperlink to the range. The link and the link format (blue underline) are applied to the whole range, but the display text is entered only in the top-left cell.

setHyperlink(
  location: string,
  display?: string | null,
  type?: HyperlinkType | null
): Promise<void>;

Parameters

Name	Type	Description
`location`	`string`	link target. It accepts a web address, an email address, a reference in the document ("Sheet1!A1"), or a defined name, and the link cannot be set if it is not in a format that matches type
`display` (optional)	`string \| null`	display text to enter in the top-left cell. If the cell value is a number or a string that contains a line break, display is ignored and the cell value is kept. If the cell value is any other string, it is replaced with display, and if the cell is empty, display is entered, and location is entered if display is omitted.
`type` (optional)	`HyperlinkType \| null`	link type. If omitted, it is determined from location.

Returns

none

`setNumberFormat(formatCode)`

Sets the number format of the range by format code. The given code is saved as a custom format.

setNumberFormat(formatCode: string): Promise<void>;

Parameters

Name	Type	Description
`formatCode`	`string`	Excel-compatible format code (such as "#,##0.00", "yyyy-mm-dd", or "0.00%")

Returns

none

`setRowHeight(height)`

Sets the height of the rows spanned by the range. Setting a height on a hidden row also unhides it. Passing 0 or less hides the rows without changing the height. The specified rows have a fixed height, so they are not adjusted automatically when the content changes.

setRowHeight(height: number): Promise<void>;

Parameters

Name	Type	Description
`height`	`number`	row height (px)

Returns

none

`setRowVisible(visible)`

Shows or hides the rows spanned by the range.

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

Parameters

Name	Type	Description
`visible`	`boolean`	true to show, false to hide

Returns

none

`setValue(value)`

Enters the same value into every cell of the range. The value is converted to a string and then interpreted, so a value starting with "=" becomes a formula, and a value in the form "{=...}" becomes an array formula that binds the whole range as one unit. If the number format of the top-left cell of the range is text, a value starting with "=" also does not become a formula and is entered as a string in the whole range. If the range is a single cell inside an existing array formula area, the entire area is entered again. An array formula cannot be entered into a merged cell or a table area. null becomes an empty cell, an object is entered in JSON notation, and an object that has a string notation, such as Date, is entered as that string. An array is entered by converting each element by the same rules and joining the results with commas.

setValue(value: string | number | boolean | null | Date | Record<string, unknown> | any[]): Promise<void>;

Parameters

Name	Type	Description
`value`	`string \| number \| boolean \| null \| Date \| Record<string, unknown> \| any[]`	value to enter into the cells. A value that cannot be converted to a string, such as an array or object with a circular structure, cannot be entered

Returns

none

`setValues(values)`

Enters a different value into each cell of the range. The outer array index corresponds to the row in the range and the inner array index corresponds to the column in the range, and the size must be exactly the same as the range. Each value is interpreted as in setValue, so a value starting with "=" becomes a formula, but an array formula cannot be entered. null and undefined become empty cells. It cannot be used on an entire-row or entire-column range.

setValues(values: (string | number | boolean | null | undefined | Date | Record<string, unknown> | any[])[][]): Promise<void>;

Parameters

Name	Type	Description
`values`	`(string \| number \| boolean \| null \| undefined \| Date \| Record<string, unknown> \| any[])[][]`	row-major two-dimensional array with the same size as the range

Returns

none

`setVerticalAlign(value)`

Sets the vertical alignment of the range.

setVerticalAlign(value: VerticalAlign): Promise<void>;

Parameters

Name	Type	Description
`value`	`VerticalAlign`	vertical alignment

Returns

none

`setWrapText(isOn)`

Applies or removes text wrapping for the range.

setWrapText(isOn: boolean): Promise<void>;

Parameters

Name	Type	Description
`isOn`	`boolean`	true to apply, false to remove

Returns

none

`sort(key, order)`

Sorts the range by the values of the specified column. If the range is a single cell inside a filter or table area, it is processed as the sort of that filter, so the sort state remains in the document. The header row is detected automatically. The range cannot be sorted if there are no values to sort or if the merges in the range vary in size.

sort(key: number, order: SortOrder): Promise<void>;

Parameters

Name	Type	Description
`key`	`number`	column to sort by. It is a 0-based offset where the first column of the range is 0 and cannot exceed the width of the range
`order`	`SortOrder`	sort order

Returns

none

`unmerge()`

Unmerges all merged areas that overlap the range.

unmerge(): Promise<void>;

Returns

none

Remarks
Methods
Method details

Remarks​

Methods​

Method details​

addConditionalFormat(rule)​

autofitColumns()​

autofitRows()​

clearAll()​

clearConditionalFormats()​

clearDataValidation()​

clearFormat()​

decreaseIndent()​

deleteColumn()​

deleteComment()​

deleteRow()​

deleteValue()​

getAddress(includeSheetName)​

getAlignment()​

getBackgroundColor()​

getBorder()​

getCellContent()​

getCellContents()​

getCellFormats(properties)​

getColumnWidth()​

getCommentList()​

getCoordinates()​

getDisplayValue()​

getDisplayValues()​

getFill()​

getFont()​

getFontColor()​

getFontName()​

getFontSize()​

getFontUnderline()​

getFormula()​

getFormulas()​

getHorizontalAlign()​

getHyperlinkList()​

getIndent()​

getMergeCellList()​

getNumberFormat()​

getRowHeight()​

getValue()​

getValues()​

getVerticalAlign()​

increaseIndent()​

insertColumn(count)​

insertRow(count)​

isColumnVisible()​

isFontBold()​

isFontItalic()​

isFontStrikethrough()​

isRowVisible()​

isSingleCell()​

isWrapText()​

merge()​

removeHyperlink()​

setBackgroundColor(color)​

setBorder(type, lineStyle, color)​

setColumnVisible(visible)​

setColumnWidth(width)​

setComment(text)​

setCommentVisible(visible)​

setDataValidation(rule)​

setFontBold(isOn)​

setFontColor(color)​

setFontItalic(isOn)​

setFontName(name)​

setFontSize(size)​

setFontStrikethrough(isOn)​

setFontUnderline(isOn, isDouble)​

setHorizontalAlign(value)​

setHyperlink(location, display, type)​

setNumberFormat(formatCode)​

setRowHeight(height)​

setRowVisible(visible)​

setValue(value)​

setValues(values)​

setVerticalAlign(value)​

setWrapText(isOn)​

sort(key, order)​

Remarks

Methods

Method details

`addConditionalFormat(rule)`

`autofitColumns()`

`autofitRows()`

`clearAll()`

`clearConditionalFormats()`

`clearDataValidation()`

`clearFormat()`

`decreaseIndent()`

`deleteColumn()`

`deleteComment()`

`deleteRow()`

`deleteValue()`

`getAddress(includeSheetName)`

`getAlignment()`

`getBackgroundColor()`

`getBorder()`

`getCellContent()`

`getCellContents()`

`getCellFormats(properties)`

`getColumnWidth()`

`getCommentList()`

`getCoordinates()`

`getDisplayValue()`

`getDisplayValues()`

`getFill()`

`getFont()`

`getFontColor()`

`getFontName()`

`getFontSize()`

`getFontUnderline()`

`getFormula()`

`getFormulas()`

`getHorizontalAlign()`

`getHyperlinkList()`

`getIndent()`

`getMergeCellList()`

`getNumberFormat()`

`getRowHeight()`

`getValue()`

`getValues()`

`getVerticalAlign()`

`increaseIndent()`

`insertColumn(count)`

`insertRow(count)`

`isColumnVisible()`

`isFontBold()`

`isFontItalic()`

`isFontStrikethrough()`

`isRowVisible()`

`isSingleCell()`

`isWrapText()`

`merge()`

`removeHyperlink()`

`setBackgroundColor(color)`

`setBorder(type, lineStyle, color)`

`setColumnVisible(visible)`

`setColumnWidth(width)`

`setComment(text)`

`setCommentVisible(visible)`

`setDataValidation(rule)`

`setFontBold(isOn)`

`setFontColor(color)`

`setFontItalic(isOn)`

`setFontName(name)`

`setFontSize(size)`

`setFontStrikethrough(isOn)`

`setFontUnderline(isOn, isDouble)`

`setHorizontalAlign(value)`

`setHyperlink(location, display, type)`

`setNumberFormat(formatCode)`

`setRowHeight(height)`

`setRowVisible(visible)`

`setValue(value)`

`setValues(values)`

`setVerticalAlign(value)`

`setWrapText(isOn)`

`sort(key, order)`