Excel.Range class

Range represents a set of one or more contiguous cells such as a cell, a row, a column, block of cells, etc.

[ API set: ExcelApi 1.1 ]

Extends

Properties

address

Represents the range reference in A1-style. Address value will contain the Sheet reference (e.g. "Sheet1!A1:B4"). Read-only.

[ API set: ExcelApi 1.1 ]

addressLocal

Represents range reference for the specified range in the language of the user. Read-only.

[ API set: ExcelApi 1.1 ]

cellCount

Number of cells in the range. This API will return -1 if the cell count exceeds 2^31-1 (2,147,483,647). Read-only.

[ API set: ExcelApi 1.1 ]

columnCount

Represents the total number of columns in the range. Read-only.

[ API set: ExcelApi 1.1 ]

columnHidden

Represents if all columns of the current range are hidden.

[ API set: ExcelApi 1.2 ]

columnIndex

Represents the column number of the first cell in the range. Zero-indexed. Read-only.

[ API set: ExcelApi 1.1 ]

conditionalFormats

Collection of ConditionalFormats that intersect the range. Read-only.

[ API set: ExcelApi 1.6 ]

context

The request context associated with the object. This connects the add-in's process to the Office host application's process.

dataValidation

Returns a data validation object.

[ API set: ExcelApi 1.8 ]

format

Returns a format object, encapsulating the range's font, fill, borders, alignment, and other properties. Read-only.

[ API set: ExcelApi 1.1 ]

formulas

Represents the formula in A1-style notation. When setting formulas to a range, the value argument can be either a single value (a string) or a two-dimensional array. If the argument is a single value, it will be applied to all cells in the range.

[ API set: ExcelApi 1.1 ]

formulasLocal

Represents the formula in A1-style notation, in the user's language and number-formatting locale. For example, the English "=SUM(A1, 1.5)" formula would become "=SUMME(A1; 1,5)" in German. When setting formulas to a range, the value argument can be either a single value (a string) or a two-dimensional array. If the argument is a single value, it will be applied to all cells in the range.

[ API set: ExcelApi 1.1 ]

formulasR1C1

Represents the formula in R1C1-style notation. When setting formulas to a range, the value argument can be either a single value (a string) or a two-dimensional array. If the argument is a single value, it will be applied to all cells in the range.

[ API set: ExcelApi 1.2 ]

hasSpill

Represents if all cells have a spill border. Returns true if all cells have a spill border, or false if all cells do not have a spill border. Returns null if there are cells both with and without spill borders within the range.

[ API set: ExcelApi BETA (PREVIEW ONLY) ]

hidden

Represents if all cells of the current range are hidden. Read-only.

[ API set: ExcelApi 1.2 ]

hyperlink

Represents the hyperlink for the current range.

[ API set: ExcelApi 1.7 ]

isEntireColumn

Represents if the current range is an entire column. Read-only.

[ API set: ExcelApi 1.7 ]

isEntireRow

Represents if the current range is an entire row. Read-only.

[ API set: ExcelApi 1.7 ]

linkedDataTypeState

Represents the data type state of each cell. Read-only.

[ API set: ExcelApi BETA (PREVIEW ONLY) ]

numberFormat

Represents Excel's number format code for the given range. When setting number format to a range, the value argument can be either a single value (string) or a two-dimensional array. If the argument is a single value, it will be applied to all cells in the range.

[ API set: ExcelApi 1.1 ]

numberFormatLocal

Represents Excel's number format code for the given range as a string in the language of the user. When setting number format local to a range, the value argument can be either a single value (string) or a two-dimensional array. If the argument is a single value, it will be applied to all cells in the range.

[ API set: ExcelApi 1.7 ]

rowCount

Returns the total number of rows in the range. Read-only.

[ API set: ExcelApi 1.1 ]

rowHidden

Represents if all rows of the current range are hidden.

[ API set: ExcelApi 1.2 ]

rowIndex

Returns the row number of the first cell in the range. Zero-indexed. Read-only.

[ API set: ExcelApi 1.1 ]

sort

Represents the range sort of the current range. Read-only.

[ API set: ExcelApi 1.2 ]

style

Represents the style of the current range. If the styles of the cells are inconsistent, null will be returned. For custom styles, the style name will be returned. For built-in styles, a string representing a value in the BuiltInStyle enum will be returned.

[ API set: ExcelApi 1.7 ]

text

Text values of the specified range. The Text value will not depend on the cell width. The # sign substitution that happens in Excel UI will not affect the text value returned by the API. Read-only.

[ API set: ExcelApi 1.1 ]

values

Represents the raw values of the specified range. The data returned could be of type string, number, or a boolean. Cells that contain an error will return the error string. When setting values to a range, the value argument can be either a single value (string, number or boolean) or a two-dimensional array. If the argument is a single value, it will be applied to all cells in the range.

[ API set: ExcelApi 1.1 ]

valueTypes

Represents the type of data of each cell. Read-only.

[ API set: ExcelApi 1.1 ]

worksheet

The worksheet containing the current range. Read-only.

[ API set: ExcelApi 1.1 ]

Methods

autoFill(destinationRange, autoFillType)
autoFill(destinationRange, autoFillTypeString)
calculate()

Calculates a range of cells on a worksheet.

[ API set: ExcelApi 1.6 ]

clear(applyTo)

Clear range values, format, fill, border, etc.

[ API set: ExcelApi 1.1 ]

clear(applyToString)

Clear range values, format, fill, border, etc.

[ API set: ExcelApi 1.1 ]

convertDataTypeToText()

Converts the range cells with datatypes into text.

[ API set: ExcelApi BETA (PREVIEW ONLY) ]

convertToLinkedDataType(serviceID, languageCulture)

Converts the range cells into linked datatype in the worksheet.

[ API set: ExcelApi BETA (PREVIEW ONLY) ]

copyFrom(sourceRange, copyType, skipBlanks, transpose)

Copies cell data or formatting from the source range or RangeAreas to the current range. The destination range can be of different size than the source range or RangeAreas. The destination will be expanded automatically if it is smaller than the source.

[ API set: ExcelApi BETA (PREVIEW ONLY) ]

copyFrom(sourceRange, copyTypeString, skipBlanks, transpose)

Copies cell data or formatting from the source range or RangeAreas to the current range. The destination range can be of different size than the source range or RangeAreas. The destination will be expanded automatically if it is smaller than the source.

[ API set: ExcelApi BETA (PREVIEW ONLY) ]

delete(shift)

Deletes the cells associated with the range.

[ API set: ExcelApi 1.1 ]

delete(shiftString)

Deletes the cells associated with the range.

[ API set: ExcelApi 1.1 ]

find(text, criteria)

Finds the given string based on the criteria specified. If the current range is larger than a single cell, then the search will be limited to that range, else the search will cover the entire sheet starting after that cell.

[ API set: ExcelApi BETA (PREVIEW ONLY) ]

findOrNullObject(text, criteria)

Finds the given string based on the criteria specified. If the current range is larger than a single cell, then the search will be limited to that range, else the search will cover the entire sheet starting after that cell. If there are no matches, this function will return a null object.

[ API set: ExcelApi BETA (PREVIEW ONLY) ]

getAbsoluteResizedRange(numRows, numColumns)

Gets a Range object with the same top-left cell as the current Range object, but with the specified numbers of rows and columns.

[ API set: ExcelApi 1.7 ]

getBoundingRect(anotherRange)

Gets the smallest range object that encompasses the given ranges. For example, the GetBoundingRect of "B2:C5" and "D10:E15" is "B2:E15".

[ API set: ExcelApi 1.1 ]

getCell(row, column)

Gets the range object containing the single cell based on row and column numbers. The cell can be outside the bounds of its parent range, so long as it stays within the worksheet grid. The returned cell is located relative to the top left cell of the range.

[ API set: ExcelApi 1.1 ]

getCellProperties(cellPropertiesLoadOptions)

Returns a 2D array, encapsulating the data for each cell's font, fill, borders, alignment, and other properties.

[ API set: ExcelApi BETA (PREVIEW ONLY) ]

getColumn(column)

Gets a column contained in the range.

[ API set: ExcelApi 1.1 ]

getColumnProperties(columnPropertiesLoadOptions)

Returns a single-dimensional array, encapsulating the data for each column's font, fill, borders, alignment, and other properties. For properties that are not consistent across each cell within a given column, null will be returned.

[ API set: ExcelApi BETA (PREVIEW ONLY) ]

getColumnsAfter(count)

Gets a certain number of columns to the right of the current Range object.

[ API set: ExcelApi 1.2 ]

getColumnsBefore(count)

Gets a certain number of columns to the left of the current Range object.

[ API set: ExcelApi 1.2 ]

getEntireColumn()

Gets an object that represents the entire column of the range (for example, if the current range represents cells "B4:E11", its getEntireColumn is a range that represents columns "B:E").

[ API set: ExcelApi 1.1 ]

getEntireRow()

Gets an object that represents the entire row of the range (for example, if the current range represents cells "B4:E11", its GetEntireRow is a range that represents rows "4:11").

[ API set: ExcelApi 1.1 ]

getImage()

Renders the range as a base64-encoded png image.

[ API set: ExcelApi 1.7 ]

getIntersection(anotherRange)

Gets the range object that represents the rectangular intersection of the given ranges.

[ API set: ExcelApi 1.1 ]

getIntersectionOrNullObject(anotherRange)

Gets the range object that represents the rectangular intersection of the given ranges. If no intersection is found, will return a null object.

[ API set: ExcelApi 1.4 ]

getLastCell()

Gets the last cell within the range. For example, the last cell of "B2:D5" is "D5".

[ API set: ExcelApi 1.1 ]

getLastColumn()

Gets the last column within the range. For example, the last column of "B2:D5" is "D2:D5".

[ API set: ExcelApi 1.1 ]

getLastRow()

Gets the last row within the range. For example, the last row of "B2:D5" is "B5:D5".

[ API set: ExcelApi 1.1 ]

getOffsetRange(rowOffset, columnOffset)

Gets an object which represents a range that's offset from the specified range. The dimension of the returned range will match this range. If the resulting range is forced outside the bounds of the worksheet grid, an error will be thrown.

[ API set: ExcelApi 1.1 ]

getResizedRange(deltaRows, deltaColumns)

Gets a Range object similar to the current Range object, but with its bottom-right corner expanded (or contracted) by some number of rows and columns.

[ API set: ExcelApi 1.2 ]

getRow(row)

Gets a row contained in the range.

[ API set: ExcelApi 1.1 ]

getRowProperties(rowPropertiesLoadOptions)

Returns a single-dimensional array, encapsulating the data for each row's font, fill, borders, alignment, and other properties. For properties that are not consistent across each cell within a given row, null will be returned.

[ API set: ExcelApi BETA (PREVIEW ONLY) ]

getRowsAbove(count)

Gets a certain number of rows above the current Range object.

[ API set: ExcelApi 1.2 ]

getRowsBelow(count)

Gets a certain number of rows below the current Range object.

[ API set: ExcelApi 1.2 ]

getSpecialCells(cellType, cellValueType)

Gets the RangeAreas object, comprising one or more rectangular ranges, that represents all the cells that match the specified type and value. If no special cells are found, an ItemNotFound error will be thrown.

[ API set: ExcelApi BETA (PREVIEW ONLY) ]

getSpecialCells(cellTypeString, cellValueType)

Gets the RangeAreas object, comprising one or more rectangular ranges, that represents all the cells that match the specified type and value. If no special cells are found, an ItemNotFound error will be thrown.

[ API set: ExcelApi BETA (PREVIEW ONLY) ]

getSpecialCellsOrNullObject(cellType, cellValueType)

Gets the RangeAreas object, comprising one or more ranges, that represents all the cells that match the specified type and value. If no special cells are found, a null object will be returned.

[ API set: ExcelApi BETA (PREVIEW ONLY) ]

getSpecialCellsOrNullObject(cellTypeString, cellValueType)

Gets the RangeAreas object, comprising one or more ranges, that represents all the cells that match the specified type and value. If no special cells are found, a null object will be returned.

[ API set: ExcelApi BETA (PREVIEW ONLY) ]

getSpillingToRange()

Gets the range object containing the spill range when called on an anchor cell. Fails if applied to a range with more than one cell. Read only.

[ API set: ExcelApi BETA (PREVIEW ONLY) ]

getSpillParent()

Gets the range object containing the anchor cell for a cell getting spilled into. Fails if applied to a range with more than one cell. Read only.

[ API set: ExcelApi BETA (PREVIEW ONLY) ]

getSurroundingRegion()

Returns a Range object that represents the surrounding region for the top-left cell in this range. A surrounding region is a range bounded by any combination of blank rows and blank columns relative to this range.

[ API set: ExcelApi 1.7 ]

getTables(fullyContained)

Gets a scoped collection of tables that overlap with the range.

[ API set: ExcelApi BETA (PREVIEW ONLY) ]

getUsedRange(valuesOnly)

Returns the used range of the given range object. If there are no used cells within the range, this function will throw an ItemNotFound error.

[ API set: ExcelApi 1.1 ]

getUsedRangeOrNullObject(valuesOnly)

Returns the used range of the given range object. If there are no used cells within the range, this function will return a null object.

[ API set: ExcelApi 1.4 ]

getVisibleView()

Represents the visible rows of the current range.

[ API set: ExcelApi 1.3 ]

insert(shift)

Inserts a cell or a range of cells into the worksheet in place of this range, and shifts the other cells to make space. Returns a new Range object at the now blank space.

[ API set: ExcelApi 1.1 ]

insert(shiftString)

Inserts a cell or a range of cells into the worksheet in place of this range, and shifts the other cells to make space. Returns a new Range object at the now blank space.

[ API set: ExcelApi 1.1 ]

load(option)

Queues up a command to load the specified properties of the object. You must call "context.sync()" before reading the properties.

load(propertyNames)

Queues up a command to load the specified properties of the object. You must call context.sync() before reading the properties.

load(propertyNamesAndPaths)

Queues up a command to load the specified properties of the object. You must call context.sync() before reading the properties.

merge(across)

Merge the range cells into one region in the worksheet.

[ API set: ExcelApi 1.2 ]

removeDuplicates(columns, includesHeader)

Removes duplicate values from the range specified by the columns.

[ API set: ExcelApi BETA (PREVIEW ONLY) ]

replaceAll(text, replacement, criteria)

Finds and replaces the given string based on the criteria specified within the current range.

[ API set: ExcelApi BETA (PREVIEW ONLY) ]

select()

Selects the specified range in the Excel UI.

[ API set: ExcelApi 1.1 ]

set(properties, options)

Sets multiple properties of an object at the same time. You can pass either a plain object with the appropriate properties, or another API object of the same type.

set(properties)

Sets multiple properties on the object at the same time, based on an existing loaded object.

setCellProperties(cellPropertiesData)

Updates the range based on a 2D array of cell properties , encapsulating things like font, fill, borders, alignment, and so forth.

[ API set: ExcelApi BETA (PREVIEW ONLY) ]

setColumnProperties(columnPropertiesData)

Updates the range based on a single-dimensional array of column properties, encapsulating things like font, fill, borders, alignment, and so forth.

[ API set: ExcelApi BETA (PREVIEW ONLY) ]

setDirty()

Set a range to be recalculated when the next recalculation occurs.

[ API set: ExcelApi BETA (PREVIEW ONLY) ]

setRowProperties(rowPropertiesData)

Updates the range based on a single-dimensional array of row properties, encapsulating things like font, fill, borders, alignment, and so forth.

[ API set: ExcelApi BETA (PREVIEW ONLY) ]

showCard()

Displays the card for an active cell if it has rich value content.

[ API set: ExcelApi 1.7 ]

toJSON()

Overrides the JavaScript toJSON() method in order to provide more useful output when an API object is passed to JSON.stringify(). (JSON.stringify, in turn, calls the toJSON method of the object that is passed to it.) Whereas the original Excel.Range object is an API object, the toJSON method returns a plain JavaScript object (typed as Excel.Interfaces.RangeData) that contains shallow copies of any loaded child properties from the original object.

track()

Track the object for automatic adjustment based on surrounding changes in the document. This call is a shorthand for context.trackedObjects.add(thisObject). If you are using this object across ".sync" calls and outside the sequential execution of a ".run" batch, and get an "InvalidObjectPath" error when setting a property or invoking a method on the object, you needed to have added the object to the tracked object collection when the object was first created.

unmerge()

Unmerge the range cells into separate cells.

[ API set: ExcelApi 1.2 ]

untrack()

Release the memory associated with this object, if it has previously been tracked. This call is shorthand for context.trackedObjects.remove(thisObject). Having many tracked objects slows down the host application, so please remember to free any objects you add, once you're done using them. You will need to call "context.sync()" before the memory release takes effect.

Property Details

address

Represents the range reference in A1-style. Address value will contain the Sheet reference (e.g. "Sheet1!A1:B4"). Read-only.

[ API set: ExcelApi 1.1 ]

readonly address: string;

Property Value

string

addressLocal

Represents range reference for the specified range in the language of the user. Read-only.

[ API set: ExcelApi 1.1 ]

readonly addressLocal: string;

Property Value

string

cellCount

Number of cells in the range. This API will return -1 if the cell count exceeds 2^31-1 (2,147,483,647). Read-only.

[ API set: ExcelApi 1.1 ]

readonly cellCount: number;

Property Value

number

columnCount

Represents the total number of columns in the range. Read-only.

[ API set: ExcelApi 1.1 ]

readonly columnCount: number;

Property Value

number

columnHidden

Represents if all columns of the current range are hidden.

[ API set: ExcelApi 1.2 ]

columnHidden: boolean;

Property Value

boolean

columnIndex

Represents the column number of the first cell in the range. Zero-indexed. Read-only.

[ API set: ExcelApi 1.1 ]

readonly columnIndex: number;

Property Value

number

conditionalFormats

Collection of ConditionalFormats that intersect the range. Read-only.

[ API set: ExcelApi 1.6 ]

readonly conditionalFormats: Excel.ConditionalFormatCollection;

Property Value

context

The request context associated with the object. This connects the add-in's process to the Office host application's process.

context: RequestContext;

Property Value

RequestContext

dataValidation

Returns a data validation object.

[ API set: ExcelApi 1.8 ]

readonly dataValidation: Excel.DataValidation;

Property Value

format

Returns a format object, encapsulating the range's font, fill, borders, alignment, and other properties. Read-only.

[ API set: ExcelApi 1.1 ]

readonly format: Excel.RangeFormat;

Property Value

formulas

Represents the formula in A1-style notation. When setting formulas to a range, the value argument can be either a single value (a string) or a two-dimensional array. If the argument is a single value, it will be applied to all cells in the range.

[ API set: ExcelApi 1.1 ]

formulas: any[][];

Property Value

any[][]

formulasLocal

Represents the formula in A1-style notation, in the user's language and number-formatting locale. For example, the English "=SUM(A1, 1.5)" formula would become "=SUMME(A1; 1,5)" in German. When setting formulas to a range, the value argument can be either a single value (a string) or a two-dimensional array. If the argument is a single value, it will be applied to all cells in the range.

[ API set: ExcelApi 1.1 ]

formulasLocal: any[][];

Property Value

any[][]

formulasR1C1

Represents the formula in R1C1-style notation. When setting formulas to a range, the value argument can be either a single value (a string) or a two-dimensional array. If the argument is a single value, it will be applied to all cells in the range.

[ API set: ExcelApi 1.2 ]

formulasR1C1: any[][];

Property Value

any[][]

hasSpill

Note

This API is provided as a preview for developers and may change based on feedback that we receive. Do not use this API in a production environment.

Represents if all cells have a spill border. Returns true if all cells have a spill border, or false if all cells do not have a spill border. Returns null if there are cells both with and without spill borders within the range.

[ API set: ExcelApi BETA (PREVIEW ONLY) ]

readonly hasSpill: boolean;

Property Value

boolean

hidden

Represents if all cells of the current range are hidden. Read-only.

[ API set: ExcelApi 1.2 ]

readonly hidden: boolean;

Property Value

boolean

Represents the hyperlink for the current range.

[ API set: ExcelApi 1.7 ]

hyperlink: Excel.RangeHyperlink;

Property Value

Examples

await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getItem("Orders");

    let productsRange = sheet.getRange("A3:A5");
    productsRange.load("values");

    await context.sync();

    // Create a hyperlink to a URL 
    // for each product name in the first table.
    for (let i = 0; i < productsRange.values.length; i++) {
        let cellRange = productsRange.getCell(i, 0);
        let cellText = productsRange.values[i][0];

        let hyperlink = {
            textToDisplay: cellText,
            screenTip: "Search Bing for '" + cellText + "'",
            address: "https://www.bing.com?q=" + cellText
        }
        cellRange.hyperlink = hyperlink;
    }

    await context.sync();
});

isEntireColumn

Represents if the current range is an entire column. Read-only.

[ API set: ExcelApi 1.7 ]

readonly isEntireColumn: boolean;

Property Value

boolean

isEntireRow

Represents if the current range is an entire row. Read-only.

[ API set: ExcelApi 1.7 ]

readonly isEntireRow: boolean;

Property Value

boolean

linkedDataTypeState

Note

This API is provided as a preview for developers and may change based on feedback that we receive. Do not use this API in a production environment.

Represents the data type state of each cell. Read-only.

[ API set: ExcelApi BETA (PREVIEW ONLY) ]

readonly linkedDataTypeState: Excel.LinkedDataTypeState[][];

Property Value

Excel.LinkedDataTypeState[][]

numberFormat

Represents Excel's number format code for the given range. When setting number format to a range, the value argument can be either a single value (string) or a two-dimensional array. If the argument is a single value, it will be applied to all cells in the range.

[ API set: ExcelApi 1.1 ]

numberFormat: any[][];

Property Value

any[][]

numberFormatLocal

Represents Excel's number format code for the given range as a string in the language of the user. When setting number format local to a range, the value argument can be either a single value (string) or a two-dimensional array. If the argument is a single value, it will be applied to all cells in the range.

[ API set: ExcelApi 1.7 ]

numberFormatLocal: any[][];

Property Value

any[][]

rowCount

Returns the total number of rows in the range. Read-only.

[ API set: ExcelApi 1.1 ]

readonly rowCount: number;

Property Value

number

rowHidden

Represents if all rows of the current range are hidden.

[ API set: ExcelApi 1.2 ]

rowHidden: boolean;

Property Value

boolean

rowIndex

Returns the row number of the first cell in the range. Zero-indexed. Read-only.

[ API set: ExcelApi 1.1 ]

readonly rowIndex: number;

Property Value

number

sort

Represents the range sort of the current range. Read-only.

[ API set: ExcelApi 1.2 ]

readonly sort: Excel.RangeSort;

Property Value

style

Represents the style of the current range. If the styles of the cells are inconsistent, null will be returned. For custom styles, the style name will be returned. For built-in styles, a string representing a value in the BuiltInStyle enum will be returned.

[ API set: ExcelApi 1.7 ]

style: string;

Property Value

string

Examples

await Excel.run(async (context) => {
    let worksheet = context.workbook.worksheets.getItem("Sample");
    let range = worksheet.getRange("A1:E1");

    // Apply built-in style. 
    // Styles are in the Home tab ribbon.
    range.style = Excel.BuiltInStyle.neutral;
    range.format.horizontalAlignment = "Right";

    await context.sync();
});

text

Text values of the specified range. The Text value will not depend on the cell width. The # sign substitution that happens in Excel UI will not affect the text value returned by the API. Read-only.

[ API set: ExcelApi 1.1 ]

readonly text: string[][];

Property Value

string[][]

values

Represents the raw values of the specified range. The data returned could be of type string, number, or a boolean. Cells that contain an error will return the error string. When setting values to a range, the value argument can be either a single value (string, number or boolean) or a two-dimensional array. If the argument is a single value, it will be applied to all cells in the range.

[ API set: ExcelApi 1.1 ]

values: any[][];

Property Value

any[][]

valueTypes

Represents the type of data of each cell. Read-only.

[ API set: ExcelApi 1.1 ]

readonly valueTypes: Excel.RangeValueType[][];

Property Value

Excel.RangeValueType[][]

worksheet

The worksheet containing the current range. Read-only.

[ API set: ExcelApi 1.1 ]

readonly worksheet: Excel.Worksheet;

Property Value

Method Details

autoFill(destinationRange, autoFillType)

autoFill(destinationRange: Range | string, autoFillType?: Excel.AutoFillType): void;

Parameters

destinationRange
Range | string
autoFillType
Excel.AutoFillType

Returns

void

autoFill(destinationRange, autoFillTypeString)

autoFill(destinationRange: Range | string, autoFillTypeString?: "FillDefault" | "FillCopy" | "FillSeries" | "FillFormats" | "FillValues" | "FillDays" | "FillWeekdays" | "FillMonths" | "FillYears" | "LinearTrend" | "GrowthTrend" | "FlashFill"): void;

Parameters

destinationRange
Range | string
autoFillTypeString
"FillDefault" | "FillCopy" | "FillSeries" | "FillFormats" | "FillValues" | "FillDays" | "FillWeekdays" | "FillMonths" | "FillYears" | "LinearTrend" | "GrowthTrend" | "FlashFill"

Returns

void

calculate()

Calculates a range of cells on a worksheet.

[ API set: ExcelApi 1.6 ]

calculate(): void;

Returns

void

clear(applyTo)

Clear range values, format, fill, border, etc.

[ API set: ExcelApi 1.1 ]

clear(applyTo?: Excel.ClearApplyTo): void;

Parameters

applyTo
Excel.ClearApplyTo

Optional. Determines the type of clear action. See Excel.ClearApplyTo for details.

Returns

void

Examples

// Below example clears format and contents of the range. 
Excel.run(function (ctx) { 
    var sheetName = "Sheet1";
    var rangeAddress = "D:F";
    var range = ctx.workbook.worksheets.getItem(sheetName).getRange(rangeAddress);
    range.clear();
    return ctx.sync(); 
}).catch(function(error) {
    console.log("Error: " + error);
    if (error instanceof OfficeExtension.Error) {
        console.log("Debug info: " + JSON.stringify(error.debugInfo));
    }
});

clear(applyToString)

Clear range values, format, fill, border, etc.

[ API set: ExcelApi 1.1 ]

clear(applyToString?: "All" | "Formats" | "Contents" | "Hyperlinks" | "RemoveHyperlinks"): void;

Parameters

applyToString
"All" | "Formats" | "Contents" | "Hyperlinks" | "RemoveHyperlinks"

Optional. Determines the type of clear action. See Excel.ClearApplyTo for details.

Returns

void

convertDataTypeToText()

Note

This API is provided as a preview for developers and may change based on feedback that we receive. Do not use this API in a production environment.

Converts the range cells with datatypes into text.

[ API set: ExcelApi BETA (PREVIEW ONLY) ]

convertDataTypeToText(): void;

Returns

void

convertToLinkedDataType(serviceID, languageCulture)

Note

This API is provided as a preview for developers and may change based on feedback that we receive. Do not use this API in a production environment.

Converts the range cells into linked datatype in the worksheet.

[ API set: ExcelApi BETA (PREVIEW ONLY) ]

convertToLinkedDataType(serviceID: number, languageCulture: string): void;

Parameters

serviceID
number

The Service ID which will be used to query the data.

languageCulture
string

Language Culture to query the service for.

Returns

void

copyFrom(sourceRange, copyType, skipBlanks, transpose)

Note

This API is provided as a preview for developers and may change based on feedback that we receive. Do not use this API in a production environment.

Copies cell data or formatting from the source range or RangeAreas to the current range. The destination range can be of different size than the source range or RangeAreas. The destination will be expanded automatically if it is smaller than the source.

[ API set: ExcelApi BETA (PREVIEW ONLY) ]

copyFrom(sourceRange: Range | RangeAreas | string, copyType?: Excel.RangeCopyType, skipBlanks?: boolean, transpose?: boolean): void;

Parameters

sourceRange
Range | RangeAreas | string

The source range or RangeAreas to copy from. When the source RangeAreas has multiple ranges, it must in the outline form which can be created by removing full rows or columns from a rectangular range.

copyType
Excel.RangeCopyType

The type of cell data or formatting to copy over. Default is "All".

skipBlanks
boolean

True if to skip blank cells in the source range. Default is false.

transpose
boolean

True if to transpose the cells in the destination range. Default is false.

Returns

void

copyFrom(sourceRange, copyTypeString, skipBlanks, transpose)

Note

This API is provided as a preview for developers and may change based on feedback that we receive. Do not use this API in a production environment.

Copies cell data or formatting from the source range or RangeAreas to the current range. The destination range can be of different size than the source range or RangeAreas. The destination will be expanded automatically if it is smaller than the source.

[ API set: ExcelApi BETA (PREVIEW ONLY) ]

copyFrom(sourceRange: Range | RangeAreas | string, copyTypeString?: "All" | "Formulas" | "Values" | "Formats", skipBlanks?: boolean, transpose?: boolean): void;

Parameters

sourceRange
Range | RangeAreas | string

The source range or RangeAreas to copy from. When the source RangeAreas has multiple ranges, it must in the outline form which can be created by removing full rows or columns from a rectangular range.

copyTypeString
"All" | "Formulas" | "Values" | "Formats"

The type of cell data or formatting to copy over. Default is "All".

skipBlanks
boolean

True if to skip blank cells in the source range. Default is false.

transpose
boolean

True if to transpose the cells in the destination range. Default is false.

Returns

void

delete(shift)

Deletes the cells associated with the range.

[ API set: ExcelApi 1.1 ]

delete(shift: Excel.DeleteShiftDirection): void;

Parameters

shift
Excel.DeleteShiftDirection

Specifies which way to shift the cells. See Excel.DeleteShiftDirection for details.

Returns

void

delete(shiftString)

Deletes the cells associated with the range.

[ API set: ExcelApi 1.1 ]

delete(shiftString: "Up" | "Left"): void;

Parameters

shiftString
"Up" | "Left"

Specifies which way to shift the cells. See Excel.DeleteShiftDirection for details.

Returns

void

Examples

Excel.run(function (ctx) { 
    var sheetName = "Sheet1";
    var rangeAddress = "D:F";
    var range = ctx.workbook.worksheets.getItem(sheetName).getRange(rangeAddress);
    range.delete("Left");
    return ctx.sync(); 
}).catch(function(error) {
    console.log("Error: " + error);
    if (error instanceof OfficeExtension.Error) {
        console.log("Debug info: " + JSON.stringify(error.debugInfo));
    }
});

find(text, criteria)

Note

This API is provided as a preview for developers and may change based on feedback that we receive. Do not use this API in a production environment.

Finds the given string based on the criteria specified. If the current range is larger than a single cell, then the search will be limited to that range, else the search will cover the entire sheet starting after that cell.

[ API set: ExcelApi BETA (PREVIEW ONLY) ]

find(text: string, criteria: Excel.SearchCriteria): Excel.Range;

Parameters

text
string

String to find.

criteria
Excel.SearchCriteria

Additional Criteria.

Returns

The Range which matched the search criteria.

findOrNullObject(text, criteria)

Note

This API is provided as a preview for developers and may change based on feedback that we receive. Do not use this API in a production environment.

Finds the given string based on the criteria specified. If the current range is larger than a single cell, then the search will be limited to that range, else the search will cover the entire sheet starting after that cell. If there are no matches, this function will return a null object.

[ API set: ExcelApi BETA (PREVIEW ONLY) ]

findOrNullObject(text: string, criteria: Excel.SearchCriteria): Excel.Range;

Parameters

text
string

String to find.

criteria
Excel.SearchCriteria

Additional Criteria.

Returns

The Range which matched the search criteria.

getAbsoluteResizedRange(numRows, numColumns)

Gets a Range object with the same top-left cell as the current Range object, but with the specified numbers of rows and columns.

[ API set: ExcelApi 1.7 ]

getAbsoluteResizedRange(numRows: number, numColumns: number): Excel.Range;

Parameters

numRows
number

The number of rows of the new range size.

numColumns
number

The number of columns of the new range size.

Returns

getBoundingRect(anotherRange)

Gets the smallest range object that encompasses the given ranges. For example, the GetBoundingRect of "B2:C5" and "D10:E15" is "B2:E15".

[ API set: ExcelApi 1.1 ]

getBoundingRect(anotherRange: Range | string): Excel.Range;

Parameters

anotherRange
Range | string

The range object or address or range name.

Returns

Examples

Excel.run(function (ctx) { 
    var sheetName = "Sheet1";
    var rangeAddress = "D4:G6";
    var range = ctx.workbook.worksheets.getItem(sheetName).getRange(rangeAddress);
    var range = range.getBoundingRect("G4:H8");
    range.load('address');
    return ctx.sync().then(function() {
        console.log(range.address); // Prints Sheet1!D4:H8
    });
}).catch(function(error) {
    console.log("Error: " + error);
    if (error instanceof OfficeExtension.Error) {
        console.log("Debug info: " + JSON.stringify(error.debugInfo));
    }
});

getCell(row, column)

Gets the range object containing the single cell based on row and column numbers. The cell can be outside the bounds of its parent range, so long as it stays within the worksheet grid. The returned cell is located relative to the top left cell of the range.

[ API set: ExcelApi 1.1 ]

getCell(row: number, column: number): Excel.Range;

Parameters

row
number

Row number of the cell to be retrieved. Zero-indexed.

column
number

Column number of the cell to be retrieved. Zero-indexed.

Returns

Examples

Excel.run(function (ctx) { 
    var sheetName = "Sheet1";
    var rangeAddress = "A1:F8";
    var worksheet = ctx.workbook.worksheets.getItem(sheetName);
    var range = worksheet.getRange(rangeAddress);
    var cell = range.cell(0,0);
    cell.load('address');
    return ctx.sync().then(function() {
        console.log(cell.address);
    });
}).catch(function(error) {
    console.log("Error: " + error);
    if (error instanceof OfficeExtension.Error) {
        console.log("Debug info: " + JSON.stringify(error.debugInfo));
    }
});

getCellProperties(cellPropertiesLoadOptions)

Note

This API is provided as a preview for developers and may change based on feedback that we receive. Do not use this API in a production environment.

Returns a 2D array, encapsulating the data for each cell's font, fill, borders, alignment, and other properties.

[ API set: ExcelApi BETA (PREVIEW ONLY) ]

getCellProperties(cellPropertiesLoadOptions: CellPropertiesLoadOptions): OfficeExtension.ClientResult<CellProperties[][]>;

Parameters

cellPropertiesLoadOptions
CellPropertiesLoadOptions

Returns

OfficeExtension.ClientResult<CellProperties[][]>

getColumn(column)

Gets a column contained in the range.

[ API set: ExcelApi 1.1 ]

getColumn(column: number): Excel.Range;

Parameters

column
number

Column number of the range to be retrieved. Zero-indexed.

Returns

Examples

Excel.run(function (ctx) { 
    var sheetName = "Sheet19";
    var rangeAddress = "A1:F8";
    var range = ctx.workbook.worksheets.getItem(sheetName).getRange(rangeAddress).getColumn(1);
    range.load('address');
    return ctx.sync().then(function() {
        console.log(range.address); // prints Sheet1!B1:B8
    });
}).catch(function(error) {
    console.log("Error: " + error);
    if (error instanceof OfficeExtension.Error) {
        console.log("Debug info: " + JSON.stringify(error.debugInfo));
    }
});

getColumnProperties(columnPropertiesLoadOptions)

Note

This API is provided as a preview for developers and may change based on feedback that we receive. Do not use this API in a production environment.

Returns a single-dimensional array, encapsulating the data for each column's font, fill, borders, alignment, and other properties. For properties that are not consistent across each cell within a given column, null will be returned.

[ API set: ExcelApi BETA (PREVIEW ONLY) ]

getColumnProperties(columnPropertiesLoadOptions: ColumnPropertiesLoadOptions): OfficeExtension.ClientResult<ColumnProperties[]>;

Parameters

columnPropertiesLoadOptions
ColumnPropertiesLoadOptions

Returns

OfficeExtension.ClientResult<ColumnProperties[]>

getColumnsAfter(count)

Gets a certain number of columns to the right of the current Range object.

[ API set: ExcelApi 1.2 ]

getColumnsAfter(count?: number): Excel.Range;

Parameters

count
number

Optional. The number of columns to include in the resulting range. In general, use a positive number to create a range outside the current range. You can also use a negative number to create a range within the current range. The default value is 1.

Returns

getColumnsBefore(count)

Gets a certain number of columns to the left of the current Range object.

[ API set: ExcelApi 1.2 ]

getColumnsBefore(count?: number): Excel.Range;

Parameters

count
number

Optional. The number of columns to include in the resulting range. In general, use a positive number to create a range outside the current range. You can also use a negative number to create a range within the current range. The default value is 1.

Returns

getEntireColumn()

Gets an object that represents the entire column of the range (for example, if the current range represents cells "B4:E11", its getEntireColumn is a range that represents columns "B:E").

[ API set: ExcelApi 1.1 ]

getEntireColumn(): Excel.Range;

Returns

Examples

// Note: the grid properties of the Range (values, numberFormat, formulas) 
// contains null since the Range in question is unbounded.
Excel.run(function (ctx) { 
    var sheetName = "Sheet1";
    var rangeAddress = "D:F";
    var range = ctx.workbook.worksheets.getItem(sheetName).getRange(rangeAddress);
    var rangeEC = range.getEntireColumn();
    rangeEC.load('address');
    return ctx.sync().then(function() {
        console.log(rangeEC.address);
    });
}).catch(function(error) {
    console.log("Error: " + error);
    if (error instanceof OfficeExtension.Error) {
        console.log("Debug info: " + JSON.stringify(error.debugInfo));
    }
});

getEntireRow()

Gets an object that represents the entire row of the range (for example, if the current range represents cells "B4:E11", its GetEntireRow is a range that represents rows "4:11").

[ API set: ExcelApi 1.1 ]

getEntireRow(): Excel.Range;

Returns

Examples

// Gets an object that represents the entire row of the range 
// (for example, if the current range represents cells "B4:E11", 
// its GetEntireRow is a range that represents rows "4:11").
Excel.run(function (ctx) {
    var sheetName = "Sheet1";
    var rangeAddress = "D:F"; 
    var range = ctx.workbook.worksheets.getItem(sheetName).getRange(rangeAddress);
    var rangeER = range.getEntireRow();
    rangeER.load('address');
    return ctx.sync().then(function() {
        console.log(rangeER.address);
    });
}).catch(function(error) {
    console.log("Error: " + error);
    if (error instanceof OfficeExtension.Error) {
        console.log("Debug info: " + JSON.stringify(error.debugInfo));
    }
});

getImage()

Renders the range as a base64-encoded png image.

[ API set: ExcelApi 1.7 ]

getImage(): OfficeExtension.ClientResult<string>;

Returns

OfficeExtension.ClientResult<string>

getIntersection(anotherRange)

Gets the range object that represents the rectangular intersection of the given ranges.

[ API set: ExcelApi 1.1 ]

getIntersection(anotherRange: Range | string): Excel.Range;

Parameters

anotherRange
Range | string

The range object or range address that will be used to determine the intersection of ranges.

Returns

Examples

Excel.run(function (ctx) { 
    var sheetName = "Sheet1";
    var rangeAddress = "A1:F8";
    var range = 
        ctx.workbook.worksheets.getItem(sheetName).getRange(rangeAddress).getIntersection("D4:G6");
    range.load('address');
    return ctx.sync().then(function() {
        console.log(range.address); // prints Sheet1!D4:F6
    });
}).catch(function(error) {
    console.log("Error: " + error);
    if (error instanceof OfficeExtension.Error) {
        console.log("Debug info: " + JSON.stringify(error.debugInfo));
    }
});

getIntersectionOrNullObject(anotherRange)

Gets the range object that represents the rectangular intersection of the given ranges. If no intersection is found, will return a null object.

[ API set: ExcelApi 1.4 ]

getIntersectionOrNullObject(anotherRange: Range | string): Excel.Range;

Parameters

anotherRange
Range | string

The range object or range address that will be used to determine the intersection of ranges.

Returns

Examples

await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getItem("Sample");
    const salesTable = sheet.tables.getItem("SalesTable");
    const dataRange = salesTable.getDataBodyRange();

    // We want the most recent quarter that has data, so
    // exclude quarters without data and get the last of
    // the remaining columns.
    const usedDataRange = dataRange.getUsedRange(true /* valuesOnly */);
    const currentQuarterRange = usedDataRange.getLastColumn();

    // Asian and European teams have separate contests.
    const asianSalesRange = sheet.getRange("A2:E4");
    const europeanSalesRange = sheet.getRange("A5:E7");

    // The data for each chart is the intersection of the
    // current quarter column and the rows for the continent.
    const asianContestRange = asianSalesRange.getIntersectionOrNullObject(currentQuarterRange);
    const europeanContestRange = europeanSalesRange.getIntersectionOrNullObject(currentQuarterRange);

    // Must sync before you can test the output of *OrNullObject
    // method/property.
    await context.sync();

    if (asianContestRange.isNullObject) {
        // See the declaration of this method for how to
        // test this code path.
        reportMissingData("Asian");
    } else {
        createContinentChart(
            sheet,
            "Asian",
            asianContestRange,
            "A9",
            "F24"
        );
    }

    if (europeanContestRange.isNullObject) {
        // See the declaration of this method for how to
        // test this code path.
        reportMissingData("European");
    } else {
        createContinentChart(
            sheet,
            "European",
            europeanContestRange,
            "A25",
            "F40"
        );
    }

    await context.sync();
});

getLastCell()

Gets the last cell within the range. For example, the last cell of "B2:D5" is "D5".

[ API set: ExcelApi 1.1 ]

getLastCell(): Excel.Range;

Returns

Examples

Excel.run(function (ctx) { 
    var sheetName = "Sheet1";
    var rangeAddress = "A1:F8";
    var range = ctx.workbook.worksheets.getItem(sheetName).getRange(rangeAddress).getLastCell();
    range.load('address');
    return ctx.sync().then(function() {
        console.log(range.address); // prints Sheet1!F8
    });
}).catch(function(error) {
    console.log("Error: " + error);
    if (error instanceof OfficeExtension.Error) {
        console.log("Debug info: " + JSON.stringify(error.debugInfo));
    }
});

getLastColumn()

Gets the last column within the range. For example, the last column of "B2:D5" is "D2:D5".

[ API set: ExcelApi 1.1 ]

getLastColumn(): Excel.Range;

Returns

Examples

Excel.run(function (ctx) { 
    var sheetName = "Sheet1";
    var rangeAddress = "A1:F8";
    var range = ctx.workbook.worksheets.getItem(sheetName).getRange(rangeAddress).getLastColumn();
    range.load('address');
    return ctx.sync().then(function() {
        console.log(range.address); // prints Sheet1!F1:F8
    });
}).catch(function(error) {
    console.log("Error: " + error);
    if (error instanceof OfficeExtension.Error) {
        console.log("Debug info: " + JSON.stringify(error.debugInfo));
    }
});

getLastRow()

Gets the last row within the range. For example, the last row of "B2:D5" is "B5:D5".

[ API set: ExcelApi 1.1 ]

getLastRow(): Excel.Range;

Returns

Examples

Excel.run(function (ctx) { 
    var sheetName = "Sheet1";
    var rangeAddress = "A1:F8";
    var range = ctx.workbook.worksheets.getItem(sheetName).getRange(rangeAddress).getLastRow();
    range.load('address');
    return ctx.sync().then(function() {
        console.log(range.address); // prints Sheet1!A8:F8
    });
}).catch(function(error) {
    console.log("Error: " + error);
    if (error instanceof OfficeExtension.Error) {
        console.log("Debug info: " + JSON.stringify(error.debugInfo));
    }
});

getOffsetRange(rowOffset, columnOffset)

Gets an object which represents a range that's offset from the specified range. The dimension of the returned range will match this range. If the resulting range is forced outside the bounds of the worksheet grid, an error will be thrown.

[ API set: ExcelApi 1.1 ]

getOffsetRange(rowOffset: number, columnOffset: number): Excel.Range;

Parameters

rowOffset
number

The number of rows (positive, negative, or 0) by which the range is to be offset. Positive values are offset downward, and negative values are offset upward.

columnOffset
number

The number of columns (positive, negative, or 0) by which the range is to be offset. Positive values are offset to the right, and negative values are offset to the left.

Returns

Examples

Excel.run(function (ctx) { 
    var sheetName = "Sheet1";
    var rangeAddress = "D4:F6";
    var range = 
        ctx.workbook.worksheets.getItem(sheetName).getRange(rangeAddress).getOffsetRange(-1,4);
    range.load('address');
    return ctx.sync().then(function() {
        console.log(range.address); // prints Sheet1!H3:K5
    });
}).catch(function(error) {
    console.log("Error: " + error);
    if (error instanceof OfficeExtension.Error) {
        console.log("Debug info: " + JSON.stringify(error.debugInfo));
    }
});

getResizedRange(deltaRows, deltaColumns)

Gets a Range object similar to the current Range object, but with its bottom-right corner expanded (or contracted) by some number of rows and columns.

[ API set: ExcelApi 1.2 ]

getResizedRange(deltaRows: number, deltaColumns: number): Excel.Range;

Parameters

deltaRows
number

The number of rows by which to expand the bottom-right corner, relative to the current range. Use a positive number to expand the range, or a negative number to decrease it.

deltaColumns
number

The number of columns by which to expand the bottom-right corner, relative to the current range. Use a positive number to expand the range, or a negative number to decrease it.

Returns

getRow(row)

Gets a row contained in the range.

[ API set: ExcelApi 1.1 ]

getRow(row: number): Excel.Range;

Parameters

row
number

Row number of the range to be retrieved. Zero-indexed.

Returns

Examples

Excel.run(function (ctx) { 
    var sheetName = "Sheet1";
    var rangeAddress = "A1:F8";
    var range = ctx.workbook.worksheets.getItem(sheetName).getRange(rangeAddress).getRow(1);
    range.load('address');
    return ctx.sync().then(function() {
        console.log(range.address); // prints Sheet1!A2:F2
    });
}).catch(function(error) {
    console.log("Error: " + error);
    if (error instanceof OfficeExtension.Error) {
        console.log("Debug info: " + JSON.stringify(error.debugInfo));
    }
});

getRowProperties(rowPropertiesLoadOptions)

Note

This API is provided as a preview for developers and may change based on feedback that we receive. Do not use this API in a production environment.

Returns a single-dimensional array, encapsulating the data for each row's font, fill, borders, alignment, and other properties. For properties that are not consistent across each cell within a given row, null will be returned.

[ API set: ExcelApi BETA (PREVIEW ONLY) ]

getRowProperties(rowPropertiesLoadOptions: RowPropertiesLoadOptions): OfficeExtension.ClientResult<RowProperties[]>;

Parameters

rowPropertiesLoadOptions
RowPropertiesLoadOptions

Returns

OfficeExtension.ClientResult<RowProperties[]>

getRowsAbove(count)

Gets a certain number of rows above the current Range object.

[ API set: ExcelApi 1.2 ]

getRowsAbove(count?: number): Excel.Range;

Parameters

count
number

Optional. The number of rows to include in the resulting range. In general, use a positive number to create a range outside the current range. You can also use a negative number to create a range within the current range. The default value is 1.

Returns

getRowsBelow(count)

Gets a certain number of rows below the current Range object.

[ API set: ExcelApi 1.2 ]

getRowsBelow(count?: number): Excel.Range;

Parameters

count
number

Optional. The number of rows to include in the resulting range. In general, use a positive number to create a range outside the current range. You can also use a negative number to create a range within the current range. The default value is 1.

Returns

getSpecialCells(cellType, cellValueType)

Note

This API is provided as a preview for developers and may change based on feedback that we receive. Do not use this API in a production environment.

Gets the RangeAreas object, comprising one or more rectangular ranges, that represents all the cells that match the specified type and value. If no special cells are found, an ItemNotFound error will be thrown.

[ API set: ExcelApi BETA (PREVIEW ONLY) ]

getSpecialCells(cellType: Excel.SpecialCellType, cellValueType?: Excel.SpecialCellValueType): Excel.RangeAreas;

Parameters

cellType
Excel.SpecialCellType

The type of cells to include.

cellValueType
Excel.SpecialCellValueType

If cellType is either Constants or Formulas, this argument is used to determine which types of cells to include in the result. These values can be combined together to return more than one type. The default is to select all constants or formulas, no matter what the type.

Returns

getSpecialCells(cellTypeString, cellValueType)

Note

This API is provided as a preview for developers and may change based on feedback that we receive. Do not use this API in a production environment.

Gets the RangeAreas object, comprising one or more rectangular ranges, that represents all the cells that match the specified type and value. If no special cells are found, an ItemNotFound error will be thrown.

[ API set: ExcelApi BETA (PREVIEW ONLY) ]

getSpecialCells(cellTypeString: "ConditionalFormats" | "DataValidations" | "Blanks" | "Comments" | "Constants" | "Formulas" | "SameConditionalFormat" | "SameDataValidation" | "Visible", cellValueType?: "All" | "Errors" | "ErrorsLogical" | "ErrorsNumbers" | "ErrorsText" | "ErrorsLogicalNumber" | "ErrorsLogicalText" | "ErrorsNumberText" | "Logical" | "LogicalNumbers" | "LogicalText" | "LogicalNumbersText" | "Numbers" | "NumbersText" | "Text"): Excel.RangeAreas;

Parameters

cellTypeString
"ConditionalFormats" | "DataValidations" | "Blanks" | "Comments" | "Constants" | "Formulas" | "SameConditionalFormat" | "SameDataValidation" | "Visible"

The type of cells to include.

cellValueType
"All" | "Errors" | "ErrorsLogical" | "ErrorsNumbers" | "ErrorsText" | "ErrorsLogicalNumber" | "ErrorsLogicalText" | "ErrorsNumberText" | "Logical" | "LogicalNumbers" | "LogicalText" | "LogicalNumbersText" | "Numbers" | "NumbersText" | "Text"

If cellType is either Constants or Formulas, this argument is used to determine which types of cells to include in the result. These values can be combined together to return more than one type. The default is to select all constants or formulas, no matter what the type.

Returns

getSpecialCellsOrNullObject(cellType, cellValueType)

Note

This API is provided as a preview for developers and may change based on feedback that we receive. Do not use this API in a production environment.

Gets the RangeAreas object, comprising one or more ranges, that represents all the cells that match the specified type and value. If no special cells are found, a null object will be returned.

[ API set: ExcelApi BETA (PREVIEW ONLY) ]

getSpecialCellsOrNullObject(cellType: Excel.SpecialCellType, cellValueType?: Excel.SpecialCellValueType): Excel.RangeAreas;

Parameters

cellType
Excel.SpecialCellType

The type of cells to include.

cellValueType
Excel.SpecialCellValueType

If cellType is either Constants or Formulas, this argument is used to determine which types of cells to include in the result. These values can be combined together to return more than one type. The default is to select all constants or formulas, no matter what the type.

Returns

getSpecialCellsOrNullObject(cellTypeString, cellValueType)

Note

This API is provided as a preview for developers and may change based on feedback that we receive. Do not use this API in a production environment.

Gets the RangeAreas object, comprising one or more ranges, that represents all the cells that match the specified type and value. If no special cells are found, a null object will be returned.

[ API set: ExcelApi BETA (PREVIEW ONLY) ]

getSpecialCellsOrNullObject(cellTypeString: "ConditionalFormats" | "DataValidations" | "Blanks" | "Comments" | "Constants" | "Formulas" | "SameConditionalFormat" | "SameDataValidation" | "Visible", cellValueType?: "All" | "Errors" | "ErrorsLogical" | "ErrorsNumbers" | "ErrorsText" | "ErrorsLogicalNumber" | "ErrorsLogicalText" | "ErrorsNumberText" | "Logical" | "LogicalNumbers" | "LogicalText" | "LogicalNumbersText" | "Numbers" | "NumbersText" | "Text"): Excel.RangeAreas;

Parameters

cellTypeString
"ConditionalFormats" | "DataValidations" | "Blanks" | "Comments" | "Constants" | "Formulas" | "SameConditionalFormat" | "SameDataValidation" | "Visible"

The type of cells to include.

cellValueType
"All" | "Errors" | "ErrorsLogical" | "ErrorsNumbers" | "ErrorsText" | "ErrorsLogicalNumber" | "ErrorsLogicalText" | "ErrorsNumberText" | "Logical" | "LogicalNumbers" | "LogicalText" | "LogicalNumbersText" | "Numbers" | "NumbersText" | "Text"

If cellType is either Constants or Formulas, this argument is used to determine which types of cells to include in the result. These values can be combined together to return more than one type. The default is to select all constants or formulas, no matter what the type.

Returns

getSpillingToRange()

Note

This API is provided as a preview for developers and may change based on feedback that we receive. Do not use this API in a production environment.

Gets the range object containing the spill range when called on an anchor cell. Fails if applied to a range with more than one cell. Read only.

[ API set: ExcelApi BETA (PREVIEW ONLY) ]

getSpillingToRange(): Excel.Range;

Returns

getSpillParent()

Note

This API is provided as a preview for developers and may change based on feedback that we receive. Do not use this API in a production environment.

Gets the range object containing the anchor cell for a cell getting spilled into. Fails if applied to a range with more than one cell. Read only.

[ API set: ExcelApi BETA (PREVIEW ONLY) ]

getSpillParent(): Excel.Range;

Returns

getSurroundingRegion()

Returns a Range object that represents the surrounding region for the top-left cell in this range. A surrounding region is a range bounded by any combination of blank rows and blank columns relative to this range.

[ API set: ExcelApi 1.7 ]

getSurroundingRegion(): Excel.Range;

Returns

getTables(fullyContained)

Note

This API is provided as a preview for developers and may change based on feedback that we receive. Do not use this API in a production environment.

Gets a scoped collection of tables that overlap with the range.

[ API set: ExcelApi BETA (PREVIEW ONLY) ]

getTables(fullyContained?: boolean): Excel.TableScopedCollection;

Parameters

fullyContained
boolean

If true, returns only tables that are fully contained within the range bounds. The default value is false.

Returns

getUsedRange(valuesOnly)

Returns the used range of the given range object. If there are no used cells within the range, this function will throw an ItemNotFound error.

[ API set: ExcelApi 1.1 ]

getUsedRange(valuesOnly?: boolean): Excel.Range;

Parameters

valuesOnly
boolean

Considers only cells with values as used cells. [Api set: ExcelApi 1.2]

Returns

Examples

Excel.run(function (ctx) { 
    var sheetName = "Sheet1";
    var rangeAddress = "D:F";
    var range = ctx.workbook.worksheets.getItem(sheetName).getRange(rangeAddress);
    var rangeUR = range.getUsedRange();
    rangeUR.load('address');
    return ctx.sync().then(function() {
        console.log(rangeUR.address);
    });
}).catch(function(error) {
    console.log("Error: " + error);
    if (error instanceof OfficeExtension.Error) {
        console.log("Debug info: " + JSON.stringify(error.debugInfo));
    }
});
await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getItem("Sample");
    const salesTable = sheet.tables.getItem("SalesTable");
    const dataRange = salesTable.getDataBodyRange();

    // We want the most recent quarter that has data, so
    // exclude quarters without data and get the last of
    // the remaining columns.
    const usedDataRange = dataRange.getUsedRange(true /* valuesOnly */);
    const currentQuarterRange = usedDataRange.getLastColumn();

    // Asian and European teams have separate contests.
    const asianSalesRange = sheet.getRange("A2:E4");
    const europeanSalesRange = sheet.getRange("A5:E7");

    // The data for each chart is the intersection of the
    // current quarter column and the rows for the continent.
    const asianContestRange = asianSalesRange.getIntersectionOrNullObject(currentQuarterRange);
    const europeanContestRange = europeanSalesRange.getIntersectionOrNullObject(currentQuarterRange);

    // Must sync before you can test the output of *OrNullObject
    // method/property.
    await context.sync();

    if (asianContestRange.isNullObject) {
        // See the declaration of this method for how to
        // test this code path.
        reportMissingData("Asian");
    } else {
        createContinentChart(
            sheet,
            "Asian",
            asianContestRange,
            "A9",
            "F24"
        );
    }

    if (europeanContestRange.isNullObject) {
        // See the declaration of this method for how to
        // test this code path.
        reportMissingData("European");
    } else {
        createContinentChart(
            sheet,
            "European",
            europeanContestRange,
            "A25",
            "F40"
        );
    }

    await context.sync();
});

getUsedRangeOrNullObject(valuesOnly)

Returns the used range of the given range object. If there are no used cells within the range, this function will return a null object.

[ API set: ExcelApi 1.4 ]

getUsedRangeOrNullObject(valuesOnly?: boolean): Excel.Range;

Parameters

valuesOnly
boolean

Considers only cells with values as used cells.

Returns

Examples

await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getItem("Sample");
    const salesTable = sheet.tables.getItem("SalesTable");
    const dataRange = salesTable.getDataBodyRange();

    // Pass true so only cells with values count as used
    const usedDataRange = dataRange.getUsedRangeOrNullObject(
        true /* valuesOnly */
    );

    //Must sync before reading value returned from *OrNullObject method/property.
    await context.sync();

    if (usedDataRange.isNullObject) {
        console.log("Need Data to Make Chart");
        console.log("To create a meaningful chart, press 'Fill the table' (or add names to the Product column and numbers to some of the other cells). Then press 'Try to create chart' again.");
    } else {
        const chart = sheet.charts.add(
            Excel.ChartType.columnClustered,
            dataRange,
            "Columns"
        );
        chart.setPosition("A15", "F30");
        chart.title.text = "Quarterly sales chart";
        chart.legend.position = "Right";
        chart.legend.format.fill.setSolidColor("white");
        chart.dataLabels.format.font.size = 15;
        chart.dataLabels.format.font.color = "black";
    }

    await context.sync();
});

getVisibleView()

Represents the visible rows of the current range.

[ API set: ExcelApi 1.3 ]

getVisibleView(): Excel.RangeView;

Returns

insert(shift)

Inserts a cell or a range of cells into the worksheet in place of this range, and shifts the other cells to make space. Returns a new Range object at the now blank space.

[ API set: ExcelApi 1.1 ]

insert(shift: Excel.InsertShiftDirection): Excel.Range;

Parameters

shift
Excel.InsertShiftDirection

Specifies which way to shift the cells. See Excel.InsertShiftDirection for details.

Returns

Examples

Excel.run(function (ctx) { 
    var sheetName = "Sheet1";
    var rangeAddress = "F5:F10";
    var range = ctx.workbook.worksheets.getItem(sheetName).getRange(rangeAddress);
    range.insert();
    return ctx.sync(); 
    });
}).catch(function(error) {
    console.log("Error: " + error);
    if (error instanceof OfficeExtension.Error) {
        console.log("Debug info: " + JSON.stringify(error.debugInfo));
    }
});

insert(shiftString)

Inserts a cell or a range of cells into the worksheet in place of this range, and shifts the other cells to make space. Returns a new Range object at the now blank space.

[ API set: ExcelApi 1.1 ]

insert(shiftString: "Down" | "Right"): Excel.Range;

Parameters

shiftString
"Down" | "Right"

Specifies which way to shift the cells. See Excel.InsertShiftDirection for details.

Returns

load(option)

Queues up a command to load the specified properties of the object. You must call "context.sync()" before reading the properties.

load(option?: Excel.Interfaces.RangeLoadOptions): Excel.Range;

Parameters

Returns

Remarks

In addition to this signature, this method has the following signatures:

load(option?: string | string[]): Excel.Range - Where option is a comma-delimited string or an array of strings that specify the properties to load.

load(option?: { select?: string; expand?: string; }): Excel.Range - Where option.select is a comma-delimited string that specifies the properties to load, and options.expand is a comma-delimited string that specifies the navigation properties to load.

load(option?: { select?: string; expand?: string; top?: number; skip?: number }): Excel.Range - Only available on collection types. It is similar to the preceding signature. Option.top specifies the maximum number of collection items that can be included in the result. Option.skip specifies the number of items that are to be skipped and not included in the result. If option.top is specified, the result set will start after skipping the specified number of items.

load(propertyNames)

Queues up a command to load the specified properties of the object. You must call context.sync() before reading the properties.

load(propertyNames?: string | string[]): Excel.Range;

Parameters

propertyNames
string | string[]

A comma-delimited string or an array of strings that specify the properties to load.

Returns

Examples

// Below example uses range address to get the range object.
Excel.run(function (ctx) {
    var sheetName = "Sheet1";
    var rangeAddress = "A1:F8"; 
    var worksheet = ctx.workbook.worksheets.getItem(sheetName);
    var range = worksheet.getRange(rangeAddress);
    range.load('cellCount');
    return ctx.sync().then(function() {
        console.log(range.cellCount);
    });
}).catch(function(error) {
    console.log("Error: " + error);
    if (error instanceof OfficeExtension.Error) {
        console.log("Debug info: " + JSON.stringify(error.debugInfo));
    }
});
// Below example uses a named-range to get the range object.
Excel.run(function (ctx) { 
    var rangeName = 'MyRange';
    var range = ctx.workbook.names.getItem(rangeName).range;
    range.load('cellCount');
    return ctx.sync().then(function() {
        console.log(range.cellCount);
    });
}).catch(function(error) {
    console.log("Error: " + error);
    if (error instanceof OfficeExtension.Error) {
        console.log("Debug info: " + JSON.stringify(error.debugInfo));
    }
});
// The example below sets number-format, values and formulas on a grid that contains 2x3 grid.
Excel.run(function (ctx) { 
    var sheetName = "Sheet1";
    var rangeAddress = "F5:G7";
    var numberFormat = [[null, "d-mmm"], [null, "d-mmm"], [null, null]]
    var values = [["Today", 42147], ["Tomorrow", "5/24"], ["Difference in days", null]];
    var formulas = [[null,null], [null,null], [null,"=G6-G5"]];
    var range = ctx.workbook.worksheets.getItem(sheetName).getRange(rangeAddress);
    range.numberFormat = numberFormat;
    range.values = values;
    range.formulas= formulas;
    range.load('text');
    return ctx.sync().then(function() {
        console.log(range.text);
    });
}).catch(function(error) {
    console.log("Error: " + error);
    if (error instanceof OfficeExtension.Error) {
        console.log("Debug info: " + JSON.stringify(error.debugInfo));
    }
});

load(propertyNamesAndPaths)

Queues up a command to load the specified properties of the object. You must call context.sync() before reading the properties.

load(propertyNamesAndPaths?: { select?: string; expand?: string; }): Excel.Range;

Parameters

propertyNamesAndPaths
{ select?: string; expand?: string; }

Where propertyNamesAndPaths.select is a comma-delimited string that specifies the properties to load, and propertyNamesAndPaths.expand is a comma-delimited string that specifies the navigation properties to load.

Returns

merge(across)

Merge the range cells into one region in the worksheet.

[ API set: ExcelApi 1.2 ]

merge(across?: boolean): void;

Parameters

across
boolean

Optional. Set true to merge cells in each row of the specified range as separate merged cells. The default value is false.

Returns

void

Examples

Excel.run(function (ctx) { 
    var sheetName = "Sheet1";
    var rangeAddress = "A1:C3";
    var range = ctx.workbook.worksheets.getItem(sheetName).getRange(rangeAddress);
    range.merge(true);
    return ctx.sync(); 
}).catch(function(error) {
    console.log("Error: " + error);
    if (error instanceof OfficeExtension.Error) {
        console.log("Debug info: " + JSON.stringify(error.debugInfo));
    }
});
Excel.run(function (ctx) { 
    var sheetName = "Sheet1";
    var rangeAddress = "A1:C3";
    var range = ctx.workbook.worksheets.getItem(sheetName).getRange(rangeAddress);
    range.unmerge();
    return ctx.sync(); 
}).catch(function(error) {
    console.log("Error: " + error);
    if (error instanceof OfficeExtension.Error) {
        console.log("Debug info: " + JSON.stringify(error.debugInfo));
    }
});

removeDuplicates(columns, includesHeader)

Note

This API is provided as a preview for developers and may change based on feedback that we receive. Do not use this API in a production environment.

Removes duplicate values from the range specified by the columns.

[ API set: ExcelApi BETA (PREVIEW ONLY) ]

removeDuplicates(columns: number[], includesHeader: boolean): Excel.RemoveDuplicatesResult;

Parameters

columns
number[]

The columns inside the range that may contain duplicates. At least one column needs to be specified. Zero-indexed.

includesHeader
boolean

True if the input data contains header. Default is false.

Returns

The resulting object that contains the number of rows removed and the number of remaining unique rows.

replaceAll(text, replacement, criteria)

Note

This API is provided as a preview for developers and may change based on feedback that we receive. Do not use this API in a production environment.

Finds and replaces the given string based on the criteria specified within the current range.

[ API set: ExcelApi BETA (PREVIEW ONLY) ]

replaceAll(text: string, replacement: string, criteria: Excel.ReplaceCriteria): OfficeExtension.ClientResult<number>;

Parameters

text
string

String to find.

replacement
string

String to replace the original with.

criteria
Excel.ReplaceCriteria

Additional Replace Criteria.

Returns

OfficeExtension.ClientResult<number>

The number of replacements performed.

select()

Selects the specified range in the Excel UI.

[ API set: ExcelApi 1.1 ]

select(): void;

Returns

void

Examples

Excel.run(function (ctx) {
    var sheetName = "Sheet1";
    var rangeAddress = "F5:F10"; 
    var range = ctx.workbook.worksheets.getItem(sheetName).getRange(rangeAddress);
    range.select();
    return ctx.sync(); 
}).catch(function(error) {
    console.log("Error: " + error);
    if (error instanceof OfficeExtension.Error) {
        console.log("Debug info: " + JSON.stringify(error.debugInfo));
    }
});

set(properties, options)

Sets multiple properties of an object at the same time. You can pass either a plain object with the appropriate properties, or another API object of the same type.

set(properties: Interfaces.RangeUpdateData, options?: OfficeExtension.UpdateOptions): void;

Parameters

properties
Interfaces.RangeUpdateData

A JavaScript object with properties that are structured isomorphically to the properties of the object on which the method is called.

options
OfficeExtension.UpdateOptions

Provides an option to suppress errors if the properties object tries to set any read-only properties.

Returns

void

Remarks

This method has the following additional signature:

set(properties: Excel.Range): void

Examples

await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getItem("Sample");

    const range = sheet.getRange("B2:E2");
    range.set({
        format: {
            fill: {
                color: "#4472C4"
            },
            font: {
                name: "Verdana",
                color: "white"
            }
        }
    })
    range.format.autofitColumns();
    await context.sync();
});
await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getItem("Sample");

    const sourceRange = sheet.getRange("B2:E2");
    sourceRange.load("format/fill/color, format/font/name, format/font/color");
    await context.sync();

    // Set properties based on the loaded and synced 
    // source range.
    const targetRange = sheet.getRange("B7:E7");
    targetRange.set(sourceRange); 
    targetRange.format.autofitColumns();
    await context.sync();
});

set(properties)

Sets multiple properties on the object at the same time, based on an existing loaded object.

set(properties: Excel.Range): void;

Parameters

properties
Excel.Range

Returns

void

setCellProperties(cellPropertiesData)

Note

This API is provided as a preview for developers and may change based on feedback that we receive. Do not use this API in a production environment.

Updates the range based on a 2D array of cell properties , encapsulating things like font, fill, borders, alignment, and so forth.

[ API set: ExcelApi BETA (PREVIEW ONLY) ]

setCellProperties(cellPropertiesData: SettableCellProperties[][]): void;

Parameters

cellPropertiesData
SettableCellProperties[][]

Returns

void

setColumnProperties(columnPropertiesData)

Note

This API is provided as a preview for developers and may change based on feedback that we receive. Do not use this API in a production environment.

Updates the range based on a single-dimensional array of column properties, encapsulating things like font, fill, borders, alignment, and so forth.

[ API set: ExcelApi BETA (PREVIEW ONLY) ]

setColumnProperties(columnPropertiesData: SettableColumnProperties[]): void;

Parameters

columnPropertiesData
SettableColumnProperties[]

Returns

void

setDirty()

Note

This API is provided as a preview for developers and may change based on feedback that we receive. Do not use this API in a production environment.

Set a range to be recalculated when the next recalculation occurs.

[ API set: ExcelApi BETA (PREVIEW ONLY) ]

setDirty(): void;

Returns

void

setRowProperties(rowPropertiesData)

Note

This API is provided as a preview for developers and may change based on feedback that we receive. Do not use this API in a production environment.

Updates the range based on a single-dimensional array of row properties, encapsulating things like font, fill, borders, alignment, and so forth.

[ API set: ExcelApi BETA (PREVIEW ONLY) ]

setRowProperties(rowPropertiesData: SettableRowProperties[]): void;

Parameters

rowPropertiesData
SettableRowProperties[]

Returns

void

showCard()

Displays the card for an active cell if it has rich value content.

[ API set: ExcelApi 1.7 ]

showCard(): void;

Returns

void

toJSON()

Overrides the JavaScript toJSON() method in order to provide more useful output when an API object is passed to JSON.stringify(). (JSON.stringify, in turn, calls the toJSON method of the object that is passed to it.) Whereas the original Excel.Range object is an API object, the toJSON method returns a plain JavaScript object (typed as Excel.Interfaces.RangeData) that contains shallow copies of any loaded child properties from the original object.

toJSON(): Excel.Interfaces.RangeData;

Returns

track()

Track the object for automatic adjustment based on surrounding changes in the document. This call is a shorthand for context.trackedObjects.add(thisObject). If you are using this object across ".sync" calls and outside the sequential execution of a ".run" batch, and get an "InvalidObjectPath" error when setting a property or invoking a method on the object, you needed to have added the object to the tracked object collection when the object was first created.

track(): Excel.Range;

Returns

unmerge()

Unmerge the range cells into separate cells.

[ API set: ExcelApi 1.2 ]

unmerge(): void;

Returns

void

Examples

Excel.run(function (ctx) { 
    var sheetName = "Sheet1";
    var rangeAddress = "A1:C3";
    var range = ctx.workbook.worksheets.getItem(sheetName).getRange(rangeAddress);
    range.unmerge();
    return ctx.sync(); 
}).catch(function(error) {
    console.log("Error: " + error);
    if (error instanceof OfficeExtension.Error) {
        console.log("Debug info: " + JSON.stringify(error.debugInfo));
    }
});

untrack()

Release the memory associated with this object, if it has previously been tracked. This call is shorthand for context.trackedObjects.remove(thisObject). Having many tracked objects slows down the host application, so please remember to free any objects you add, once you're done using them. You will need to call "context.sync()" before the memory release takes effect.

untrack(): Excel.Range;

Returns

Examples

Excel.run(async (context) => {
    const largeRange = context.workbook.getSelectedRange();
    largeRange.load(["rowCount", "columnCount"]);
    await context.sync();

    for (let i = 0; i < largeRange.rowCount; i++) {
        for (let j = 0; j < largeRange.columnCount; j++) {
            let cell = largeRange.getCell(i, j);
            cell.values = [[i *j]];

            // call untrack() to release the range from memory
            cell.untrack();
        }
    }

    await context.sync();
});