Excel.Range class

Range represents a set of one or more contiguous cells such as a cell, a row, a column, or a block of cells. To learn more about how ranges are used throughout the API, start with Ranges in the Excel JavaScript API.

Extends

Remarks

[ API set: ExcelApi 1.1 ]

Properties

address

Specifies the range reference in A1-style. Address value contains the sheet reference (e.g., "Sheet1!A1:B4").

addressLocal

Represents the range reference for the specified range in the language of the user.

cellCount

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

columnCount

Specifies the total number of columns in the range.

columnHidden

Represents if all columns in the current range are hidden. Value is true when all columns in a range are hidden. Value is false when no columns in the range are hidden. Value is null when some columns in a range are hidden and other columns in the same range are not hidden.

columnIndex

Specifies the column number of the first cell in the range. Zero-indexed.

conditionalFormats

The collection of ConditionalFormats that intersect the range.

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.

format

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

formulas

Represents the formula in A1-style notation. If a cell has no formula, its value is returned instead.

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. If a cell has no formula, its value is returned instead.

formulasR1C1

Represents the formula in R1C1-style notation. If a cell has no formula, its value is returned instead.

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.

height

Returns the distance in points, for 100% zoom, from the top edge of the range to the bottom edge of the range.

hidden

Represents if all cells in the current range are hidden. Value is true when all cells in a range are hidden. Value is false when no cells in the range are hidden. Value is null when some cells in a range are hidden and other cells in the same range are not hidden.

hyperlink

Represents the hyperlink for the current range.

isEntireColumn

Represents if the current range is an entire column.

isEntireRow

Represents if the current range is an entire row.

left

Returns the distance in points, for 100% zoom, from the left edge of the worksheet to the left edge of the range.

linkedDataTypeState

Represents the data type state of each cell.

numberFormat

Represents Excel's number format code for the given range. For more information about Excel number formatting, see Number format codes.

numberFormatCategories

Represents the category of number format of each cell.

numberFormatLocal

Represents Excel's number format code for the given range, based on the language settings of the user. Excel does not perform any language or format coercion when getting or setting the numberFormatLocal property. Any returned text uses the locally-formatted strings based on the language specified in the system settings.

rowCount

Returns the total number of rows in the range.

rowHidden

Represents if all rows in the current range are hidden. Value is true when all rows in a range are hidden. Value is false when no rows in the range are hidden. Value is null when some rows in a range are hidden and other rows in the same range are not hidden.

rowIndex

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

savedAsArray

Represents if all the cells would be saved as an array formula. Returns true if all cells would be saved as an array formula, or false if all cells would not be saved as an array formula. Returns null if some cells would be saved as an array formula and some would not be.

sort

Represents the range sort of the current range.

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.

text

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

top

Returns the distance in points, for 100% zoom, from the top edge of the worksheet to the top edge of the range.

values

Represents the raw values of the specified range. The data returned could be a string, number, or boolean. Cells that contain an error will return the error string. If the returned value starts with a plus ("+"), minus ("-"), or equal sign ("="), Excel interprets this value as a formula.

valuesAsJson

A JSON representation of the values in the cells in this range. Unlike Range.values, Range.valuesAsJson supports all data types which can be in a cell. Examples include formatted number values and web images, in addition to the standard boolean, number, and string values. Data returned from this API always aligns with the en-US locale. To retrieve data in the user's display locale, use Range.valuesAsJsonLocal.

valuesAsJsonLocal

A JSON representation of the values in the cells in this range. Unlike Range.values, Range.valuesAsJsonLocal supports all data types which can be in a cell. Examples include formatted number values and web images, in addition to the standard boolean, number, and string values. Data returned from this API always aligns with the user's display locale. To retrieve data independent of locale, use Range.valuesAsJson.

valueTypes

Specifies the type of data in each cell.

width

Returns the distance in points, for 100% zoom, from the left edge of the range to the right edge of the range.

worksheet

The worksheet containing the current range.

Methods

autoFill(destinationRange, autoFillType)

Fills a range from the current range to the destination range using the specified AutoFill logic. The destination range can be null or can extend the source range either horizontally or vertically. Discontiguous ranges are not supported.

For more information, see Use AutoFill and Flash Fill.

autoFill(destinationRange, autoFillTypeString)

Fills a range from the current range to the destination range using the specified AutoFill logic. The destination range can be null or can extend the source range either horizontally or vertically. Discontiguous ranges are not supported.

For more information, see Use AutoFill and Flash Fill.

calculate()

Calculates a range of cells on a worksheet.

clear(applyTo)

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

clear(applyToString)

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

convertDataTypeToText()

Converts the range cells with data types into text.

convertToLinkedDataType(serviceID, languageCulture)

Converts the range cells into linked data types in the worksheet.

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 a different size than the source range or RangeAreas. The destination will be expanded automatically if it is smaller than the source.

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 a different size than the source range or RangeAreas. The destination will be expanded automatically if it is smaller than the source.

delete(shift)

Deletes the cells associated with the range.

delete(shiftString)

Deletes the cells associated with the range.

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.

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, then this method returns an object with its isNullObject property set to true. For further information, see *OrNullObject methods and properties.

flashFill()

Does a Flash Fill to the current range. Flash Fill automatically fills data when it senses a pattern, so the range must be a single column range and have data around it in order to find a pattern.

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.

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".

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.

getCellProperties(cellPropertiesLoadOptions)

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

getColumn(column)

Gets a column contained in the range.

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.

getColumnsAfter(count)

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

getColumnsBefore(count)

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

getDependents()

Returns a WorkbookRangeAreas object that represents the range containing all the dependent cells of a specified range in the same worksheet or across multiple worksheets.

getDirectDependents()

Returns a WorkbookRangeAreas object that represents the range containing all the direct dependent cells of a specified range in the same worksheet or across multiple worksheets.

getDirectPrecedents()

Returns a WorkbookRangeAreas object that represents the range containing all the direct precedent cells of a specified range in the same worksheet or across multiple worksheets.

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").

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").

getExtendedRange(direction, activeCell)

Returns a range object that includes the current range and up to the edge of the range, based on the provided direction. This matches the Ctrl+Shift+Arrow key behavior in the Excel on Windows UI.

getExtendedRange(directionString, activeCell)

Returns a range object that includes the current range and up to the edge of the range, based on the provided direction. This matches the Ctrl+Shift+Arrow key behavior in the Excel on Windows UI.

getImage()

Renders the range as a base64-encoded png image. Important*: This API is currently unsupported in Excel for Mac. Visit OfficeDev/office-js Issue #235 for the current status.

getIntersection(anotherRange)

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

getIntersectionOrNullObject(anotherRange)

Gets the range object that represents the rectangular intersection of the given ranges. If no intersection is found, then this method returns an object with its isNullObject property set to true. For further information, see *OrNullObject methods and properties.

getLastCell()

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

getLastColumn()

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

getLastRow()

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

getMergedAreasOrNullObject()

Returns a RangeAreas object that represents the merged areas in this range. Note that if the merged areas count in this range is more than 512, then this method will fail to return the result. If the RangeAreas object doesn't exist, then this function will return an object with its isNullObject property set to true. For further information, see *OrNullObject methods and properties.

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.

getPivotTables(fullyContained)

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

getPrecedents()

Returns a WorkbookRangeAreas object that represents the range containing all the precedent cells of a specified range in the same worksheet or across multiple worksheets.

getRangeEdge(direction, activeCell)

Returns a range object that is the edge cell of the data region that corresponds to the provided direction. This matches the Ctrl+Arrow key behavior in the Excel on Windows UI.

getRangeEdge(directionString, activeCell)

Returns a range object that is the edge cell of the data region that corresponds to the provided direction. This matches the Ctrl+Arrow key behavior in the Excel on Windows UI.

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.

getRow(row)

Gets a row contained in the range.

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.

getRowsAbove(count)

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

getRowsBelow(count)

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

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.

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.

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, then this method returns an object with its isNullObject property set to true. For further information, see *OrNullObject methods and properties.

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, then this method returns an object with its isNullObject property set to true. For further information, see *OrNullObject methods and properties.

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.

getSpillingToRangeOrNullObject()

Gets the range object containing the spill range when called on an anchor cell. If the range isn't an anchor cell or the spill range can't be found, then this method returns an object with its isNullObject property set to true. For further information, see *OrNullObject methods and properties.

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.

getSpillParentOrNullObject()

Gets the range object containing the anchor cell for the cell getting spilled into. If it's not a spilled cell, or more than one cell is given, then this method returns an object with its isNullObject property set to true. For further information, see *OrNullObject methods and properties.

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.

getTables(fullyContained)

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

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.

getUsedRangeOrNullObject(valuesOnly)

Returns the used range of the given range object. If there are no used cells within the range, then this method returns an object with its isNullObject property set to true. For further information, see *OrNullObject methods and properties.

getVisibleView()

Represents the visible rows of the current range.

group(groupOption)

Groups columns and rows for an outline.

group(groupOptionString)

Groups columns and rows for an outline.

hideGroupDetails(groupOption)

Hides the details of the row or column group.

hideGroupDetails(groupOptionString)

Hides the details of the row or column group.

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.

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.

load(options)

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.

moveTo(destinationRange)

Moves cell values, formatting, and formulas from current range to the destination range, replacing the old information in those cells. The destination range will be expanded automatically if it is smaller than the current range. Any cells in the destination range that are outside of the original range's area are not changed.

removeDuplicates(columns, includesHeader)

Removes duplicate values from the range specified by the columns.

replaceAll(text, replacement, criteria)

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

select()

Selects the specified range in the Excel UI.

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, and alignment.

setColumnProperties(columnPropertiesData)

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

setDirty()

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

setRowProperties(rowPropertiesData)

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

showCard()

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

showGroupDetails(groupOption)

Shows the details of the row or column group.

showGroupDetails(groupOptionString)

Shows the details of the row or column group.

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 need to add the object to the tracked object collection when the object was first created.

ungroup(groupOption)

Ungroups columns and rows for an outline.

ungroup(groupOptionString)

Ungroups columns and rows for an outline.

unmerge()

Unmerge the range cells into separate cells.

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

Specifies the range reference in A1-style. Address value contains the sheet reference (e.g., "Sheet1!A1:B4").

readonly address: string;

Property Value

string

Remarks

[ API set: ExcelApi 1.1 ]

addressLocal

Represents the range reference for the specified range in the language of the user.

readonly addressLocal: string;

Property Value

string

Remarks

[ API set: ExcelApi 1.1 ]

cellCount

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

readonly cellCount: number;

Property Value

number

Remarks

[ API set: ExcelApi 1.1 ]

columnCount

Specifies the total number of columns in the range.

readonly columnCount: number;

Property Value

number

Remarks

[ API set: ExcelApi 1.1 ]

columnHidden

Represents if all columns in the current range are hidden. Value is true when all columns in a range are hidden. Value is false when no columns in the range are hidden. Value is null when some columns in a range are hidden and other columns in the same range are not hidden.

columnHidden: boolean;

Property Value

boolean

Remarks

[ API set: ExcelApi 1.2 ]

columnIndex

Specifies the column number of the first cell in the range. Zero-indexed.

readonly columnIndex: number;

Property Value

number

Remarks

[ API set: ExcelApi 1.1 ]

conditionalFormats

The collection of ConditionalFormats that intersect the range.

readonly conditionalFormats: Excel.ConditionalFormatCollection;

Property Value

Remarks

[ 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.

context: RequestContext;

Property Value

dataValidation

Returns a data validation object.

readonly dataValidation: Excel.DataValidation;

Property Value

Remarks

[ API set: ExcelApi 1.8 ]

format

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

readonly format: Excel.RangeFormat;

Property Value

Remarks

[ API set: ExcelApi 1.1 ]

formulas

Represents the formula in A1-style notation. If a cell has no formula, its value is returned instead.

formulas: any[][];

Property Value

any[][]

Remarks

[ 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. If a cell has no formula, its value is returned instead.

formulasLocal: any[][];

Property Value

any[][]

Remarks

[ API set: ExcelApi 1.1 ]

formulasR1C1

Represents the formula in R1C1-style notation. If a cell has no formula, its value is returned instead.

formulasR1C1: any[][];

Property Value

any[][]

Remarks

[ 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.

readonly hasSpill: boolean;

Property Value

boolean

Remarks

[ API set: ExcelApi 1.12 ]

height

Returns the distance in points, for 100% zoom, from the top edge of the range to the bottom edge of the range.

readonly height: number;

Property Value

number

Remarks

[ API set: ExcelApi 1.10 ]

hidden

Represents if all cells in the current range are hidden. Value is true when all cells in a range are hidden. Value is false when no cells in the range are hidden. Value is null when some cells in a range are hidden and other cells in the same range are not hidden.

readonly hidden: boolean;

Property Value

boolean

Remarks

[ API set: ExcelApi 1.2 ]

Represents the hyperlink for the current range.

hyperlink: Excel.RangeHyperlink;

Property Value

Remarks

[ API set: ExcelApi 1.7 ]

Examples

// Link to full sample: https://raw.githubusercontent.com/OfficeDev/office-js-snippets/prod/samples/excel/42-range/range-hyperlink.yaml
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.

readonly isEntireColumn: boolean;

Property Value

boolean

Remarks

[ API set: ExcelApi 1.7 ]

isEntireRow

Represents if the current range is an entire row.

readonly isEntireRow: boolean;

Property Value

boolean

Remarks

[ API set: ExcelApi 1.7 ]

left

Returns the distance in points, for 100% zoom, from the left edge of the worksheet to the left edge of the range.

readonly left: number;

Property Value

number

Remarks

[ API set: ExcelApi 1.10 ]

linkedDataTypeState

Represents the data type state of each cell.

readonly linkedDataTypeState: Excel.LinkedDataTypeState[][];

Property Value

Remarks

[ API set: ExcelApi 1.9 ]

numberFormat

Represents Excel's number format code for the given range. For more information about Excel number formatting, see Number format codes.

numberFormat: any[][];

Property Value

any[][]

Remarks

[ API set: ExcelApi 1.1 ]

Examples

// Set the text of the chart title to "My Chart" and display it as an overlay on the chart.
await Excel.run(async (context) => { 
    const sheetName = "Sheet1";
    const rangeAddress = "F5:G7";
    const numberFormat = [[null, "d-mmm"], [null, "d-mmm"], [null, null]]
    const values = [["Today", 42147], ["Tomorrow", "5/24"], ["Difference in days", null]];
    const formulas = [[null,null], [null,null], [null,"=G6-G5"]];
    const range = context.workbook.worksheets.getItem(sheetName).getRange(rangeAddress);
    range.numberFormat = numberFormat;
    range.values = values;
    range.formulas= formulas;
    range.load('text');
    await context.sync();
    
    console.log(range.text);
});

numberFormatCategories

Represents the category of number format of each cell.

readonly numberFormatCategories: Excel.NumberFormatCategory[][];

Property Value

Remarks

[ API set: ExcelApi 1.12 ]

numberFormatLocal

Represents Excel's number format code for the given range, based on the language settings of the user. Excel does not perform any language or format coercion when getting or setting the numberFormatLocal property. Any returned text uses the locally-formatted strings based on the language specified in the system settings.

numberFormatLocal: any[][];

Property Value

any[][]

Remarks

[ API set: ExcelApi 1.7 ]

rowCount

Returns the total number of rows in the range.

readonly rowCount: number;

Property Value

number

Remarks

[ API set: ExcelApi 1.1 ]

rowHidden

Represents if all rows in the current range are hidden. Value is true when all rows in a range are hidden. Value is false when no rows in the range are hidden. Value is null when some rows in a range are hidden and other rows in the same range are not hidden.

rowHidden: boolean;

Property Value

boolean

Remarks

[ API set: ExcelApi 1.2 ]

rowIndex

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

readonly rowIndex: number;

Property Value

number

Remarks

[ API set: ExcelApi 1.1 ]

savedAsArray

Represents if all the cells would be saved as an array formula. Returns true if all cells would be saved as an array formula, or false if all cells would not be saved as an array formula. Returns null if some cells would be saved as an array formula and some would not be.

readonly savedAsArray: boolean;

Property Value

boolean

Remarks

[ API set: ExcelApi 1.12 ]

sort

Represents the range sort of the current range.

readonly sort: Excel.RangeSort;

Property Value

Remarks

[ API set: ExcelApi 1.2 ]

Examples

// Link to full sample: https://raw.githubusercontent.com/OfficeDev/office-js-snippets/prod/samples/excel/30-events/event-column-and-row-sort.yaml
async function sortTopToBottom(criteria: string) {
    await Excel.run(async (context) => {
        const sheet = context.workbook.worksheets.getActiveWorksheet();
        const range = sheet.getRange("A1:E5");

        // Find the column header that provides the sort criteria.
        const header = range.find(criteria, {});
        header.load("columnIndex");
        await context.sync();

        range.sort.apply(
            [
                {
                    key: header.columnIndex,
                    sortOn: Excel.SortOn.value
                }
            ],
            false /*matchCase*/,
            true /*hasHeaders*/,
            Excel.SortOrientation.rows
        );
        await context.sync();
    });
}

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.

style: string;

Property Value

string

Remarks

[ API set: ExcelApi 1.7 ]

Examples

// Link to full sample: https://raw.githubusercontent.com/OfficeDev/office-js-snippets/prod/samples/excel/42-range/style.yaml
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 number sign (#) substitution that happens in the Excel UI will not affect the text value returned by the API.

readonly text: string[][];

Property Value

string[][]

Remarks

[ API set: ExcelApi 1.1 ]

top

Returns the distance in points, for 100% zoom, from the top edge of the worksheet to the top edge of the range.

readonly top: number;

Property Value

number

Remarks

[ API set: ExcelApi 1.10 ]

values

Represents the raw values of the specified range. The data returned could be a string, number, or boolean. Cells that contain an error will return the error string. If the returned value starts with a plus ("+"), minus ("-"), or equal sign ("="), Excel interprets this value as a formula.

values: any[][];

Property Value

any[][]

Remarks

[ API set: ExcelApi 1.1 ]

valuesAsJson

A JSON representation of the values in the cells in this range. Unlike Range.values, Range.valuesAsJson supports all data types which can be in a cell. Examples include formatted number values and web images, in addition to the standard boolean, number, and string values. Data returned from this API always aligns with the en-US locale. To retrieve data in the user's display locale, use Range.valuesAsJsonLocal.

valuesAsJson: CellValue[][];

Property Value

Remarks

[ API set: ExcelApi 1.16 ]

Examples

// Link to full sample: https://raw.githubusercontent.com/OfficeDev/office-js-snippets/prod/samples/excel/20-data-types/data-types-formatted-number.yaml
// This function creates a formatted number data type,
// and sets the format of this data type as a date.
await Excel.run(async (context) => {
  // Get the Sample worksheet and a range on that sheet.
  const sheet = context.workbook.worksheets.getItemOrNullObject("Sample");
  const dateRange = sheet.getRange("A1");

  // Write a number formatted as a date to cell A1.
  dateRange.valuesAsJson = [
    [
      {
        type: Excel.CellValueType.formattedNumber,
        basicValue: 32889.0,
        numberFormat: "m/d/yyyy"
      }
    ]
  ];
  await context.sync();
});

valuesAsJsonLocal

A JSON representation of the values in the cells in this range. Unlike Range.values, Range.valuesAsJsonLocal supports all data types which can be in a cell. Examples include formatted number values and web images, in addition to the standard boolean, number, and string values. Data returned from this API always aligns with the user's display locale. To retrieve data independent of locale, use Range.valuesAsJson.

valuesAsJsonLocal: CellValue[][];

Property Value

Remarks

[ API set: ExcelApi 1.16 ]

valueTypes

Specifies the type of data in each cell.

readonly valueTypes: Excel.RangeValueType[][];

Property Value

Remarks

[ API set: ExcelApi 1.1 ]

width

Returns the distance in points, for 100% zoom, from the left edge of the range to the right edge of the range.

readonly width: number;

Property Value

number

Remarks

[ API set: ExcelApi 1.10 ]

worksheet

The worksheet containing the current range.

readonly worksheet: Excel.Worksheet;

Property Value

Remarks

[ API set: ExcelApi 1.1 ]

Method Details

autoFill(destinationRange, autoFillType)

Fills a range from the current range to the destination range using the specified AutoFill logic. The destination range can be null or can extend the source range either horizontally or vertically. Discontiguous ranges are not supported.

For more information, see Use AutoFill and Flash Fill.

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

Parameters

destinationRange

Excel.Range | string

The destination range to AutoFill. If the destination range is null, data is filled out based on the surrounding cells (which is the behavior when double-clicking the UI’s range fill handle).

autoFillType
Excel.AutoFillType

The type of AutoFill. Specifies how the destination range is to be filled, based on the contents of the current range. Default is "FillDefault".

Returns

void

Remarks

[ API set: ExcelApi 1.9, ExcelApi Preview for null destinationRange ]

Examples

// Link to full sample: https://raw.githubusercontent.com/OfficeDev/office-js-snippets/prod/samples/excel/42-range/range-auto-fill.yaml
await Excel.run(async (context) => {
  const sheet = context.workbook.worksheets.getActiveWorksheet();
  const sumCell = sheet.getRange("P4");
  
  // Copy everything. The formulas will be contextually updated based on their new locations.
  sumCell.autoFill("P4:P7", Excel.AutoFillType.fillCopy);
  sumCell.format.autofitColumns();
  await context.sync();
});

autoFill(destinationRange, autoFillTypeString)

Fills a range from the current range to the destination range using the specified AutoFill logic. The destination range can be null or can extend the source range either horizontally or vertically. Discontiguous ranges are not supported.

For more information, see Use AutoFill and Flash Fill.

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

Parameters

destinationRange

Excel.Range | string

The destination range to AutoFill. If the destination range is null, data is filled out based on the surrounding cells (which is the behavior when double-clicking the UI’s range fill handle).

autoFillTypeString

"FillDefault" | "FillCopy" | "FillSeries" | "FillFormats" | "FillValues" | "FillDays" | "FillWeekdays" | "FillMonths" | "FillYears" | "LinearTrend" | "GrowthTrend" | "FlashFill"

The type of AutoFill. Specifies how the destination range is to be filled, based on the contents of the current range. Default is "FillDefault".

Returns

void

Remarks

[ API set: ExcelApi 1.9, ExcelApi Preview for null destinationRange ]

calculate()

Calculates a range of cells on a worksheet.

calculate(): void;

Returns

void

Remarks

[ API set: ExcelApi 1.6 ]

clear(applyTo)

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

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

Parameters

applyTo
Excel.ClearApplyTo

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

Returns

void

Remarks

[ API set: ExcelApi 1.1 ]

Examples

// Clear the format and contents of the range.
await Excel.run(async (context) => { 
    const sheetName = "Sheet1";
    const rangeAddress = "D:F";
    const range = context.workbook.worksheets.getItem(sheetName).getRange(rangeAddress);
    range.clear();
    await context.sync(); 
});

clear(applyToString)

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

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

Remarks

[ API set: ExcelApi 1.1 ]

convertDataTypeToText()

Converts the range cells with data types into text.

convertDataTypeToText(): void;

Returns

void

Remarks

[ API set: ExcelApi 1.9 ]

convertToLinkedDataType(serviceID, languageCulture)

Converts the range cells into linked data types in the worksheet.

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

Remarks

[ API set: ExcelApi 1.9 ]

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 a different size than the source range or RangeAreas. The destination will be expanded automatically if it is smaller than the source.

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

Parameters

sourceRange

Excel.Range | Excel.RangeAreas | string

The source range or RangeAreas to copy from. When the source RangeAreas has multiple ranges, their form must be able to 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

Remarks

[ API set: ExcelApi 1.9 ]

Examples

// Link to full sample: https://raw.githubusercontent.com/OfficeDev/office-js-snippets/prod/samples/excel/42-range/range-copyfrom.yaml
await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getItem("Sample");
    // Place a label in front of the copied data.
    sheet.getRange("F2").values = [["Copied Formula"]];

    // Copy a range preserving the formulas.
    // Note: non-formula values are copied over as is.
    sheet.getRange("G2").copyFrom("A1:E1", Excel.RangeCopyType.formulas);
    await context.sync();
});

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 a different size than the source range or RangeAreas. The destination will be expanded automatically if it is smaller than the source.

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

Parameters

sourceRange

Excel.Range | Excel.RangeAreas | string

The source range or RangeAreas to copy from. When the source RangeAreas has multiple ranges, their form must be able to 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

Remarks

[ API set: ExcelApi 1.9 ]

delete(shift)

Deletes the cells associated with the range.

delete(shift: Excel.DeleteShiftDirection): void;

Parameters

shift
Excel.DeleteShiftDirection

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

Returns

void

Remarks

[ API set: ExcelApi 1.1 ]

Examples

await Excel.run(async (context) => { 
    const sheetName = "Sheet1";
    const rangeAddress = "D:F";
    const range = context.workbook.worksheets.getItem(sheetName).getRange(rangeAddress);
    range.delete("Left");
    await context.sync(); 
});

delete(shiftString)

Deletes the cells associated with the range.

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

Parameters

shiftString

"Up" | "Left"

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

Returns

void

Remarks

[ 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.

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

Parameters

text

string

The string to find.

criteria
Excel.SearchCriteria

Additional search criteria, including the search direction and whether the search needs to match the entire cell or be case-sensitive.

Returns

The Range object representing the first cell that contains a value matching the search text and criteria.

Remarks

[ API set: ExcelApi 1.9 ]

Examples

// Link to full sample: https://raw.githubusercontent.com/OfficeDev/office-js-snippets/prod/samples/excel/42-range/range-find.yaml
await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getItem("Sample");
    const table = sheet.tables.getItem("ExpensesTable");
    const searchRange = table.getRange();

    // NOTE: If no match is found, an ItemNotFound error
    // is thrown when Range.find is evaluated.
    const foundRange = searchRange.find($("#searchText").val().toString(), {
        completeMatch: isCompleteMatchToggle,
        matchCase: isMatchCaseToggle,
        searchDirection: searchDirectionToggle
    });
    
    foundRange.load("address");
    await context.sync();


    console.log(foundRange.address);
});

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, then this method returns an object with its isNullObject property set to true. For further information, see *OrNullObject methods and properties.

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

Parameters

text

string

The string to find.

criteria
Excel.SearchCriteria

Additional search criteria, including the search direction and whether the search needs to match the entire cell or be case-sensitive.

Returns

The Range which matched the search criteria.

Remarks

[ API set: ExcelApi 1.9 ]

Examples

// Link to full sample: https://raw.githubusercontent.com/OfficeDev/office-js-snippets/prod/samples/excel/42-range/range-find.yaml
await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getItem("Sample");
    const table = sheet.tables.getItem("ExpensesTable");
    const searchRange = table.getRange();
    const foundRange = searchRange.findOrNullObject($("#searchText").val().toString(), {
        completeMatch: isCompleteMatchToggle,
        matchCase: isMatchCaseToggle,
        searchDirection: searchDirectionToggle
    });
    
    foundRange.load("address");
    await context.sync();

    if (foundRange.isNullObject) {
        console.log("Text not found");
    } else {
        console.log(foundRange.address);
    }
});

flashFill()

Does a Flash Fill to the current range. Flash Fill automatically fills data when it senses a pattern, so the range must be a single column range and have data around it in order to find a pattern.

flashFill(): void;

Returns

void

Remarks

[ API set: ExcelApi 1.9 ]

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.

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

Remarks

[ 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".

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

Parameters

anotherRange

Excel.Range | string

The range object, address, or range name.

Returns

Remarks

[ API set: ExcelApi 1.1 ]

Examples

await Excel.run(async (context) => { 
    const sheetName = "Sheet1";
    const rangeAddress = "D4:G6";
    let range = context.workbook.worksheets.getItem(sheetName).getRange(rangeAddress);
    range = range.getBoundingRect("G4:H8");
    range.load('address');
    await context.sync();
    
    console.log(range.address); // Prints Sheet1!D4:H8
});

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.

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

Remarks

[ API set: ExcelApi 1.1 ]

Examples

await Excel.run(async (context) => { 
    const sheetName = "Sheet1";
    const rangeAddress = "A1:F8";
    const worksheet = context.workbook.worksheets.getItem(sheetName);
    const range = worksheet.getRange(rangeAddress);
    const cell = range.getCell(0,0);
    cell.load('address');
    await context.sync();
    
    console.log(cell.address);
});

getCellProperties(cellPropertiesLoadOptions)

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

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

Parameters

cellPropertiesLoadOptions
Excel.CellPropertiesLoadOptions

An object that represents which cell properties to load.

Returns

A 2D array where each item represents the requested properties of the corresponding cell.

Remarks

[ API set: ExcelApi 1.9 ]

Examples

// Link to full sample: https://raw.githubusercontent.com/OfficeDev/office-js-snippets/prod/samples/excel/42-range/cell-properties.yaml
await Excel.run(async (context) => {
    const cell = context.workbook.getActiveCell();

    // Define the cell properties to get by setting the matching LoadOptions to true.
    const propertiesToGet = cell.getCellProperties({
        address: true,
        format: {
            fill: {
                color: true
            },
            font: {
                color: true
            }
        },
        style: true
    });

    // Sync to get the data from the workbook.
    await context.sync();
    const cellProperties = propertiesToGet.value[0][0];
    console.log(
        `Address: ${cellProperties.address}\nStyle: ${cellProperties.style}\nFill Color: ${cellProperties.format.fill.color}\nFont Color: ${cellProperties.format.font.color}`);
});

getColumn(column)

Gets a column contained in the range.

getColumn(column: number): Excel.Range;

Parameters

column

number

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

Returns

Remarks

[ API set: ExcelApi 1.1 ]

Examples

await Excel.run(async (context) => { 
    const sheetName = "Sheet19";
    const rangeAddress = "A1:F8";
    const range = context.workbook.worksheets.getItem(sheetName).getRange(rangeAddress).getColumn(1);
    range.load('address');
    await context.sync();

    console.log(range.address); // prints Sheet1!B1:B8
});

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.

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

Parameters

columnPropertiesLoadOptions
Excel.ColumnPropertiesLoadOptions

An object that represents which column properties to load.

Returns

An array where each item represents the requested properties of the corresponding column.

Remarks

[ API set: ExcelApi 1.9 ]

getColumnsAfter(count)

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

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

Remarks

[ API set: ExcelApi 1.2 ]

getColumnsBefore(count)

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

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

Remarks

[ API set: ExcelApi 1.2 ]

getDependents()

Returns a WorkbookRangeAreas object that represents the range containing all the dependent cells of a specified range in the same worksheet or across multiple worksheets.

getDependents(): Excel.WorkbookRangeAreas;

Returns

Remarks

[ API set: ExcelApi 1.15 ]

Examples

// Link to full sample: https://raw.githubusercontent.com/OfficeDev/office-js-snippets/prod/samples/excel/42-range/range-dependents.yaml
// This function highlights all the dependent cells of the active cell.
// Dependent cells contain formulas that refer to other cells.
await Excel.run(async (context) => {
  // Get addresses of the active cell's dependent cells.
  const range = context.workbook.getActiveCell();
  const dependents = range.getDependents();
  range.load("address");
  dependents.areas.load("address");
  await context.sync();

  console.log(`All dependent cells of ${range.address}:`);

  // Use the dependents API to loop through dependents of the active cell.
  for (let i = 0; i < dependents.areas.items.length; i++) {
    // Highlight and print out the address of each dependent cell.
    dependents.areas.items[i].format.fill.color = "Orange";
    console.log(`  ${dependents.areas.items[i].address}`);
  }
  await context.sync();
});

getDirectDependents()

Returns a WorkbookRangeAreas object that represents the range containing all the direct dependent cells of a specified range in the same worksheet or across multiple worksheets.

getDirectDependents(): Excel.WorkbookRangeAreas;

Returns

Remarks

[ API set: ExcelApi 1.13 ]

Examples

// Link to full sample: https://raw.githubusercontent.com/OfficeDev/office-js-snippets/prod/samples/excel/42-range/range-direct-dependents.yaml
await Excel.run(async (context) => {
  // Direct dependents are cells that contain formulas that refer to other cells.
  let range = context.workbook.getActiveCell();
  let directDependents = range.getDirectDependents();
  range.load("address");
  directDependents.areas.load("address");
  await context.sync();
  
  console.log(`Direct dependent cells of ${range.address}:`);
  
  // Use the direct dependents API to loop through direct dependents of the active cell.
  for (let i = 0; i < directDependents.areas.items.length; i++) {
    // Highlight and print the address of each dependent cell.
    directDependents.areas.items[i].format.fill.color = "Yellow";
    console.log(`  ${directDependents.areas.items[i].address}`);
  }
  await context.sync();
});

getDirectPrecedents()

Returns a WorkbookRangeAreas object that represents the range containing all the direct precedent cells of a specified range in the same worksheet or across multiple worksheets.

getDirectPrecedents(): Excel.WorkbookRangeAreas;

Returns

Remarks

[ API set: ExcelApi 1.12 ]

Examples

// Link to full sample: https://raw.githubusercontent.com/OfficeDev/office-js-snippets/prod/samples/excel/42-range/precedents.yaml
await Excel.run(async (context) => {
  // Precedents are cells referenced by the formula in a cell.
  // A "direct precedent" is a cell directly referenced by the selected formula.
  let range = context.workbook.getActiveCell();
  let directPrecedents = range.getDirectPrecedents();
  range.load("address");
  directPrecedents.areas.load("address");
  await context.sync();

  console.log(`Direct precedent cells of ${range.address}:`);

  // Use the direct precedents API to loop through precedents of the active cell.
  for (let i = 0; i < directPrecedents.areas.items.length; i++) {
    // Highlight and console the address of each precedent cell.
    directPrecedents.areas.items[i].format.fill.color = "Yellow";
    console.log(`  ${directPrecedents.areas.items[i].address}`);
  }
  await context.sync();
});

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").

getEntireColumn(): Excel.Range;

Returns

Remarks

[ API set: ExcelApi 1.1 ]

Examples

// Note: the grid properties of the Range (values, numberFormat, formulas) 
// contains null since the Range in question is unbounded.
await Excel.run(async (context) => { 
    const sheetName = "Sheet1";
    const rangeAddress = "D:F";
    const range = context.workbook.worksheets.getItem(sheetName).getRange(rangeAddress);
    const rangeEC = range.getEntireColumn();
    rangeEC.load('address');
    await context.sync();
    
    console.log(rangeEC.address);
});

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").

getEntireRow(): Excel.Range;

Returns

Remarks

[ API set: ExcelApi 1.1 ]

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").
await Excel.run(async (context) => {
    const sheetName = "Sheet1";
    const rangeAddress = "D:F"; 
    const range = context.workbook.worksheets.getItem(sheetName).getRange(rangeAddress);
    const rangeER = range.getEntireRow();
    rangeER.load('address');
    await context.sync();
    
    console.log(rangeER.address);
});

getExtendedRange(direction, activeCell)

Returns a range object that includes the current range and up to the edge of the range, based on the provided direction. This matches the Ctrl+Shift+Arrow key behavior in the Excel on Windows UI.

getExtendedRange(direction: Excel.KeyboardDirection, activeCell?: Range | string): Excel.Range;

Parameters

direction
Excel.KeyboardDirection

The direction from the active cell.

activeCell

Excel.Range | string

The active cell in this range. By default, the active cell is the top-left cell of the range. An error is thrown if the active cell is not in this range.

Returns

Remarks

[ API set: ExcelApi 1.13 ]

Examples

// Link to full sample: https://raw.githubusercontent.com/OfficeDev/office-js-snippets/prod/samples/excel/42-range/range-get-range-edge.yaml
await Excel.run(async (context) => {
  // Get the selected range.
  const range = context.workbook.getSelectedRange();

  // Specify the direction with the `KeyboardDirection` enum.
  const direction = Excel.KeyboardDirection.down;

  // Get the active cell in the workbook.
  const activeCell = context.workbook.getActiveCell();

  // Get all the cells from the currently selected range to the bottom-most edge of the used range.
  // This method acts like the Ctrl+Shift+Arrow key keyboard shortcut while a range is selected.
  const extendedRange = range.getExtendedRange(
    direction,
    activeCell // If the selected range contains more than one cell, the active cell must be defined.
  );
  extendedRange.select();

  await context.sync();
});

getExtendedRange(directionString, activeCell)

Returns a range object that includes the current range and up to the edge of the range, based on the provided direction. This matches the Ctrl+Shift+Arrow key behavior in the Excel on Windows UI.

getExtendedRange(directionString: "Left" | "Right" | "Up" | "Down", activeCell?: Range | string): Excel.Range;

Parameters

directionString

"Left" | "Right" | "Up" | "Down"

The direction from the active cell.

activeCell

Excel.Range | string

The active cell in this range. By default, the active cell is the top-left cell of the range. An error is thrown if the active cell is not in this range.

Returns

Remarks

[ API set: ExcelApi 1.13 ]

getImage()

Renders the range as a base64-encoded png image. Important*: This API is currently unsupported in Excel for Mac. Visit OfficeDev/office-js Issue #235 for the current status.

getImage(): OfficeExtension.ClientResult<string>;

Returns

Remarks

[ API set: ExcelApi 1.7 ]

getIntersection(anotherRange)

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

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

Parameters

anotherRange

Excel.Range | string

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

Returns

Remarks

[ API set: ExcelApi 1.1 ]

Examples

await Excel.run(async (context) => { 
    const sheetName = "Sheet1";
    const rangeAddress = "A1:F8";
    const range = 
        context.workbook.worksheets.getItem(sheetName).getRange(rangeAddress).getIntersection("D4:G6");
    range.load('address');
    await context.sync();
    
    console.log(range.address); // prints Sheet1!D4:F6
});

getIntersectionOrNullObject(anotherRange)

Gets the range object that represents the rectangular intersection of the given ranges. If no intersection is found, then this method returns an object with its isNullObject property set to true. For further information, see *OrNullObject methods and properties.

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

Parameters

anotherRange

Excel.Range | string

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

Returns

Remarks

[ API set: ExcelApi 1.4 ]

Examples

// Link to full sample: https://raw.githubusercontent.com/OfficeDev/office-js-snippets/prod/samples/excel/42-range/range-relationships.yaml
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 function for how to
        // test this code path.
        reportMissingData("Asian");
    } else {
        createContinentChart(
            sheet,
            "Asian",
            asianContestRange,
            "A9",
            "F24"
        );
    }

    if (europeanContestRange.isNullObject) {
        // See the declaration of this function 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".

getLastCell(): Excel.Range;

Returns

Remarks

[ API set: ExcelApi 1.1 ]

Examples

await Excel.run(async (context) => { 
    const sheetName = "Sheet1";
    const rangeAddress = "A1:F8";
    const range = context.workbook.worksheets.getItem(sheetName).getRange(rangeAddress).getLastCell();
    range.load('address');
    await context.sync();
    
    console.log(range.address); // prints Sheet1!F8
});

getLastColumn()

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

getLastColumn(): Excel.Range;

Returns

Remarks

[ API set: ExcelApi 1.1 ]

Examples

await Excel.run(async (context) => { 
    const sheetName = "Sheet1";
    const rangeAddress = "A1:F8";
    const range = context.workbook.worksheets.getItem(sheetName).getRange(rangeAddress).getLastColumn();
    range.load('address');
    await context.sync();
    
    console.log(range.address); // prints Sheet1!F1:F8
});

getLastRow()

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

getLastRow(): Excel.Range;

Returns

Remarks

[ API set: ExcelApi 1.1 ]

Examples

await Excel.run(async (context) => { 
    const sheetName = "Sheet1";
    const rangeAddress = "A1:F8";
    const range = context.workbook.worksheets.getItem(sheetName).getRange(rangeAddress).getLastRow();
    range.load('address');
    await context.sync();
    
    console.log(range.address); // prints Sheet1!A8:F8
});

getMergedAreasOrNullObject()

Returns a RangeAreas object that represents the merged areas in this range. Note that if the merged areas count in this range is more than 512, then this method will fail to return the result. If the RangeAreas object doesn't exist, then this function will return an object with its isNullObject property set to true. For further information, see *OrNullObject methods and properties.

getMergedAreasOrNullObject(): Excel.RangeAreas;

Returns

Remarks

[ API set: ExcelApi 1.13 ]

Examples

// Link to full sample: https://raw.githubusercontent.com/OfficeDev/office-js-snippets/prod/samples/excel/42-range/range-merged-ranges.yaml
await Excel.run(async (context) => {
  // Retrieve the worksheet and the table in that worksheet.
  const sheet = context.workbook.worksheets.getActiveWorksheet();
  const tableRange = sheet.getRange("B2:E6");

  // Retrieve the merged range within the table and load its details.
  const mergedAreas = tableRange.getMergedAreasOrNullObject();
  mergedAreas.load("address");
  mergedAreas.load("cellCount");

  // Select the merged range.
  const range = mergedAreas.areas.getItemAt(0);
  range.select();
  await context.sync();

  // Print out the details of the `mergedAreas` range object.
  console.log(`Address of the merged range: ${mergedAreas.address}`);
  console.log(`Number of cells in the merged range: ${mergedAreas.cellCount}`);

  await context.sync();
});

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.

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

Remarks

[ API set: ExcelApi 1.1 ]

Examples

await Excel.run(async (context) => { 
    const sheetName = "Sheet1";
    const rangeAddress = "D4:F6";
    const range = 
        context.workbook.worksheets.getItem(sheetName).getRange(rangeAddress).getOffsetRange(-1,4);
    range.load('address');
    await context.sync();
    
    console.log(range.address); // prints Sheet1!H3:J5
});

getPivotTables(fullyContained)

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

getPivotTables(fullyContained?: boolean): Excel.PivotTableScopedCollection;

Parameters

fullyContained

boolean

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

Returns

Remarks

[ API set: ExcelApi 1.12 ]

Examples

// Link to full sample: https://raw.githubusercontent.com/OfficeDev/office-js-snippets/prod/samples/excel/38-pivottable/pivottable-get-pivottables.yaml
await Excel.run(async (context) => {
  const activeRange = context.workbook.getSelectedRange();

  // Get all the PivotTables that intersect with this range.
  const partiallyContainedPivotTables = activeRange.getPivotTables();
  // Get all the PivotTables that are completely contained within this range.
  const fullyContainedPivotTables = activeRange.getPivotTables(true);

  partiallyContainedPivotTables.load("name");
  fullyContainedPivotTables.load("name");
  await context.sync();

  // Display the names in the console.
  console.log("PivotTables in the current range:")
  partiallyContainedPivotTables.items.forEach((pivotTable) => {
    console.log(`\t${pivotTable.name}`);
  });
  console.log("PivotTables completely contained in the current range:")
  fullyContainedPivotTables.items.forEach((pivotTable) => {
    console.log(`\t${pivotTable.name}`);
  });
});

getPrecedents()

Returns a WorkbookRangeAreas object that represents the range containing all the precedent cells of a specified range in the same worksheet or across multiple worksheets.

getPrecedents(): Excel.WorkbookRangeAreas;

Returns

Remarks

[ API set: ExcelApi 1.14 ]

Examples

// Link to full sample: https://raw.githubusercontent.com/OfficeDev/office-js-snippets/prod/samples/excel/42-range/precedents.yaml
await Excel.run(async (context) => {
  // Precedents are cells referenced by the formula in a cell.
  let range = context.workbook.getActiveCell();
  let precedents = range.getPrecedents();
  range.load("address");
  precedents.areas.load("address");
  await context.sync();

  console.log(`All precedent cells of ${range.address}:`);

  // Use the precedents API to loop through precedents of the active cell.
  for (let i = 0; i < precedents.areas.items.length; i++) {
    // Highlight and console the address of each precedent cell.
    precedents.areas.items[i].format.fill.color = "Orange";
    console.log(`  ${precedents.areas.items[i].address}`);
  }
  await context.sync();
});

getRangeEdge(direction, activeCell)

Returns a range object that is the edge cell of the data region that corresponds to the provided direction. This matches the Ctrl+Arrow key behavior in the Excel on Windows UI.

getRangeEdge(direction: Excel.KeyboardDirection, activeCell?: Range | string): Excel.Range;

Parameters

direction
Excel.KeyboardDirection

The direction from the active cell.

activeCell

Excel.Range | string

The active cell in this range. By default, the active cell is the top-left cell of the range. An error is thrown if the active cell is not in this range.

Returns

Remarks

[ API set: ExcelApi 1.13 ]

Examples

// Link to full sample: https://raw.githubusercontent.com/OfficeDev/office-js-snippets/prod/samples/excel/42-range/range-get-range-edge.yaml
await Excel.run(async (context) => {
  // Get the selected range.
  const range = context.workbook.getSelectedRange();

  // Specify the direction with the `KeyboardDirection` enum.
  const direction = Excel.KeyboardDirection.up;

  // Get the active cell in the workbook.
  const activeCell = context.workbook.getActiveCell();

  // Get the top-most cell of the current used range.
  // This method acts like the Ctrl+Arrow key keyboard shortcut while a range is selected.
  const rangeEdge = range.getRangeEdge(
    direction,
    activeCell // If the selected range contains more than one cell, the active cell must be defined.
  );
  rangeEdge.select();

  await context.sync();
});

getRangeEdge(directionString, activeCell)

Returns a range object that is the edge cell of the data region that corresponds to the provided direction. This matches the Ctrl+Arrow key behavior in the Excel on Windows UI.

getRangeEdge(directionString: "Left" | "Right" | "Up" | "Down", activeCell?: Range | string): Excel.Range;

Parameters

directionString

"Left" | "Right" | "Up" | "Down"

The direction from the active cell.

activeCell

Excel.Range | string

The active cell in this range. By default, the active cell is the top-left cell of the range. An error is thrown if the active cell is not in this range.

Returns

Remarks

[ API set: ExcelApi 1.13 ]

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.

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

Remarks

[ API set: ExcelApi 1.2 ]

getRow(row)

Gets a row contained in the range.

getRow(row: number): Excel.Range;

Parameters

row

number

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

Returns

Remarks

[ API set: ExcelApi 1.1 ]

Examples

await Excel.run(async (context) => { 
    const sheetName = "Sheet1";
    const rangeAddress = "A1:F8";
    const range = context.workbook.worksheets.getItem(sheetName).getRange(rangeAddress).getRow(1);
    range.load('address');
    await context.sync();
    
    console.log(range.address); // prints Sheet1!A2:F2
});

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.

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

Parameters

rowPropertiesLoadOptions
Excel.RowPropertiesLoadOptions

An object that represents which row properties to load.

Returns

An array where each item represents the requested properties of the corresponding row.

Remarks

[ API set: ExcelApi 1.9 ]

getRowsAbove(count)

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

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

Remarks

[ API set: ExcelApi 1.2 ]

getRowsBelow(count)

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

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

Remarks

[ 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.

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

Remarks

[ API set: ExcelApi 1.9 ]

Examples

// Link to full sample: https://raw.githubusercontent.com/OfficeDev/office-js-snippets/prod/samples/excel/42-range/range-areas.yaml
await Excel.run(async (context) => {

    const sheet = context.workbook.worksheets.getActiveWorksheet();
    const usedRange = sheet.getUsedRange();

    // Find the ranges with either text or logical (boolean) values.
    const formulaRanges = usedRange.getSpecialCells("Constants", "LogicalText");
    formulaRanges.format.fill.color = "orange";

    return context.sync();
});

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.

getSpecialCells(cellTypeString: "ConditionalFormats" | "DataValidations" | "Blanks" | "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" | "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

Remarks

[ API set: ExcelApi 1.9 ]

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, then this method returns an object with its isNullObject property set to true. For further information, see *OrNullObject methods and properties.

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

Remarks

[ API set: ExcelApi 1.9 ]

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, then this method returns an object with its isNullObject property set to true. For further information, see *OrNullObject methods and properties.

getSpecialCellsOrNullObject(cellTypeString: "ConditionalFormats" | "DataValidations" | "Blanks" | "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" | "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

Remarks

[ API set: ExcelApi 1.9 ]

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.

getSpillingToRange(): Excel.Range;

Returns

Remarks

[ API set: ExcelApi 1.12 ]

Examples

// Link to full sample: https://raw.githubusercontent.com/OfficeDev/office-js-snippets/prod/samples/excel/42-range/dynamic-arrays.yaml
await Excel.run(async (context) => {
  const sheet = context.workbook.worksheets.getItem("Sample");

  // Set G4 to a formula that returns a dynamic array.
  const targetCell = sheet.getRange("G4");
  targetCell.formulas = [["=A4:D4"]];

  // Get the address of the cells that the dynamic array spilled into.
  const spillRange = targetCell.getSpillingToRange();
  spillRange.load("address");

  // Fit the columns for readability.
  sheet.getUsedRange().format.autofitColumns();
  await context.sync();

  console.log(`Copying the table headers spilled into ${spillRange.address}.`);
});

getSpillingToRangeOrNullObject()

Gets the range object containing the spill range when called on an anchor cell. If the range isn't an anchor cell or the spill range can't be found, then this method returns an object with its isNullObject property set to true. For further information, see *OrNullObject methods and properties.

getSpillingToRangeOrNullObject(): Excel.Range;

Returns

Remarks

[ API set: ExcelApi 1.12 ]

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.

getSpillParent(): Excel.Range;

Returns

Remarks

[ API set: ExcelApi 1.12 ]

getSpillParentOrNullObject()

Gets the range object containing the anchor cell for the cell getting spilled into. If it's not a spilled cell, or more than one cell is given, then this method returns an object with its isNullObject property set to true. For further information, see *OrNullObject methods and properties.

getSpillParentOrNullObject(): Excel.Range;

Returns

Remarks

[ API set: ExcelApi 1.12 ]

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.

getSurroundingRegion(): Excel.Range;

Returns

Remarks

[ API set: ExcelApi 1.7 ]

getTables(fullyContained)

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

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

Remarks

[ API set: ExcelApi 1.9 ]

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.

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

Parameters

valuesOnly

boolean

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

Returns

Remarks

[ API set: ExcelApi 1.1 ]

Examples

// Link to full sample: https://raw.githubusercontent.com/OfficeDev/office-js-snippets/prod/samples/excel/42-range/range-relationships.yaml
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 function for how to
        // test this code path.
        reportMissingData("Asian");
    } else {
        createContinentChart(
            sheet,
            "Asian",
            asianContestRange,
            "A9",
            "F24"
        );
    }

    if (europeanContestRange.isNullObject) {
        // See the declaration of this function 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, then this method returns an object with its isNullObject property set to true. For further information, see *OrNullObject methods and properties.

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

Parameters

valuesOnly

boolean

Considers only cells with values as used cells.

Returns

Remarks

[ API set: ExcelApi 1.4 ]

Examples

// Link to full sample: https://raw.githubusercontent.com/OfficeDev/office-js-snippets/prod/samples/excel/42-range/used-range.yaml
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.

getVisibleView(): Excel.RangeView;

Returns

Remarks

[ API set: ExcelApi 1.3 ]

group(groupOption)

Groups columns and rows for an outline.

group(groupOption: Excel.GroupOption): void;

Parameters

groupOption
Excel.GroupOption

Specifies how the range can be grouped by rows or columns. An InvalidArgument error is thrown when the group option differs from the range's isEntireRow or isEntireColumn property (i.e., range.isEntireRow is true and groupOption is "ByColumns" or range.isEntireColumn is true and groupOption is "ByRows").

Returns

void

Remarks

[ API set: ExcelApi 1.10 ]

Examples

// Link to full sample: https://raw.githubusercontent.com/OfficeDev/office-js-snippets/prod/samples/excel/42-range/outline.yaml
Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getActiveWorksheet();
    
    // Group the larger, main level. Note that the outline controls
    // will be on row 10, meaning 4-9 will collapse and expand.
    sheet.getRange("4:9").group(Excel.GroupOption.byRows);

    // Group the smaller, sublevels. Note that the outline controls
    // will be on rows 6 and 9, meaning 4-5 and 7-8 will collapse and expand.
    sheet.getRange("4:5").group(Excel.GroupOption.byRows);
    sheet.getRange("7:8").group(Excel.GroupOption.byRows);
    await context.sync();
});

group(groupOptionString)

Groups columns and rows for an outline.

group(groupOptionString: "ByRows" | "ByColumns"): void;

Parameters

groupOptionString

"ByRows" | "ByColumns"

Specifies how the range can be grouped by rows or columns. An InvalidArgument error is thrown when the group option differs from the range's isEntireRow or isEntireColumn property (i.e., range.isEntireRow is true and groupOption is "ByColumns" or range.isEntireColumn is true and groupOption is "ByRows").

Returns

void

Remarks

[ API set: ExcelApi 1.10 ]

hideGroupDetails(groupOption)

Hides the details of the row or column group.

hideGroupDetails(groupOption: Excel.GroupOption): void;

Parameters

groupOption
Excel.GroupOption

Specifies whether to hide the details of grouped rows or grouped columns.

Returns

void

Remarks

[ API set: ExcelApi 1.10 ]

hideGroupDetails(groupOptionString)

Hides the details of the row or column group.

hideGroupDetails(groupOptionString: "ByRows" | "ByColumns"): void;

Parameters

groupOptionString

"ByRows" | "ByColumns"

Specifies whether to hide the details of grouped rows or grouped columns.

Returns

void

Remarks

[ API set: ExcelApi 1.10 ]

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.

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

Parameters

shift
Excel.InsertShiftDirection

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

Returns

Remarks

[ API set: ExcelApi 1.1 ]

Examples

await Excel.run(async (context) => {
    const sheetName = "Sheet1";
    const rangeAddress = "F5:F10";
    const range = context.workbook.worksheets.getItem(sheetName).getRange(rangeAddress);
    range.insert(Excel.InsertShiftDirection.down);
    await context.sync();
});

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.

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

Parameters

shiftString

"Down" | "Right"

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

Returns

Remarks

[ API set: ExcelApi 1.1 ]

load(options)

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

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

Parameters

options
Excel.Interfaces.RangeLoadOptions

Provides options for which properties of the object to load.

Returns

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

// Use the range address to get the range object.
await Excel.run(async (context) => {
    const sheetName = "Sheet1";
    const rangeAddress = "A1:F8"; 
    const worksheet = context.workbook.worksheets.getItem(sheetName);
    const range = worksheet.getRange(rangeAddress);
    range.load('cellCount');
    await context.sync();
    
    console.log(range.cellCount);
});

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; }

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.

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

Remarks

[ API set: ExcelApi 1.2 ]

Examples

await Excel.run(async (context) => { 
    const sheetName = "Sheet1";
    const rangeAddress = "A1:C3";
    const range = context.workbook.worksheets.getItem(sheetName).getRange(rangeAddress);
    range.merge(true);
    await context.sync(); 
});
// Link to full sample: https://raw.githubusercontent.com/OfficeDev/office-js-snippets/prod/samples/excel/42-range/range-merged-ranges.yaml
await Excel.run(async (context) => {
  // Retrieve the worksheet and the table in that worksheet.
  const sheet = context.workbook.worksheets.getActiveWorksheet();
  const tableRange = sheet.getRange("B2:E6");

  // Create a merged range in the first row of the table.
  const chartTitle = tableRange.getRow(0);
  chartTitle.merge(true);

  // Format the merged range.
  chartTitle.format.horizontalAlignment = "Center";

  await context.sync();
});

moveTo(destinationRange)

Moves cell values, formatting, and formulas from current range to the destination range, replacing the old information in those cells. The destination range will be expanded automatically if it is smaller than the current range. Any cells in the destination range that are outside of the original range's area are not changed.

moveTo(destinationRange: Range | string): void;

Parameters

destinationRange

Excel.Range | string

destinationRange Specifies the range to where the information in this range will be moved.

Returns

void

Remarks

[ API set: ExcelApi 1.11 ]

Examples

// Link to full sample: https://raw.githubusercontent.com/OfficeDev/office-js-snippets/prod/samples/excel/42-range/range-copyfrom.yaml
await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getItem("Sample");
    // Place a label in front of the moved data.
    sheet.getRange("F12").values = [["Moved Range:"]];

    // Move the range from A1:E1 to G12:K12.
    sheet.getRange("A1:E1").moveTo("G12");
    await context.sync();
});

removeDuplicates(columns, includesHeader)

Removes duplicate values from the range specified by the columns.

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.

Remarks

[ API set: ExcelApi 1.9 ]

Examples

// Link to full sample: https://raw.githubusercontent.com/OfficeDev/office-js-snippets/prod/samples/excel/42-range/range-remove-duplicates.yaml
await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getItem("Sample");
    const range = sheet.getRange("B2:D11");

    const deleteResult = range.removeDuplicates([0],true);    
    deleteResult.load();    
    await context.sync();

    console.log(deleteResult.removed + " entries with duplicate names removed.");
    console.log(deleteResult.uniqueRemaining + " entries with unique names remain in the range.");
});

replaceAll(text, replacement, criteria)

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

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

Parameters

text

string

String to find.

replacement

string

The string that replaces the original string.

criteria
Excel.ReplaceCriteria

Additional replacement criteria.

Returns

The number of replacements performed.

Remarks

[ API set: ExcelApi 1.9 ]

select()

Selects the specified range in the Excel UI.

select(): void;

Returns

void

Remarks

[ API set: ExcelApi 1.1 ]

Examples

await Excel.run(async (context) => {
    const sheetName = "Sheet1";
    const rangeAddress = "F5:F10"; 
    const range = context.workbook.worksheets.getItem(sheetName).getRange(rangeAddress);
    range.select();
    await context.sync(); 
});

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
Excel.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

Examples

// Link to full sample: https://raw.githubusercontent.com/OfficeDev/office-js-snippets/prod/samples/excel/90-scenarios/multiple-property-set.yaml
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();
});
// Link to full sample: https://raw.githubusercontent.com/OfficeDev/office-js-snippets/prod/samples/excel/90-scenarios/multiple-property-set.yaml
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)

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

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

Parameters

cellPropertiesData

Excel.SettableCellProperties[][]

A 2D array that represents which properties to set in each cell.

Returns

void

Remarks

[ API set: ExcelApi 1.9 ]

Examples

// Link to full sample: https://raw.githubusercontent.com/OfficeDev/office-js-snippets/prod/samples/excel/42-range/cell-properties.yaml
await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getActiveWorksheet();

    // Creating the SettableCellProperties objects to use for the range.
    // In your add-in, these should be created once, outside the function.
    const topHeaderProps: Excel.SettableCellProperties = {
        // The style property takes a string matching the name of an Excel style.
        // Built-in style names are listed in the `BuiltInStyle` enum.
        // Note that a style will overwrite any formatting,
        // so do not use the format property with the style property.
        style: "Heading1"
    };

    const headerProps: Excel.SettableCellProperties = {
        // Any subproperties of format that are not set will not be changed when these cell properties are set.
        format: {
            fill: {
                color: "Blue"
            },
            font: {
                color: "White",
                bold: true
            }
        }
    };

    const nonApplicableProps: Excel.SettableCellProperties = {
        format: {
            fill: {
                pattern: Excel.FillPattern.gray25
            },
            font: {
                color: "Gray",
                italic: true
            }
        }
    };

    const matchupScoreProps: Excel.SettableCellProperties = {
        format: {
            borders: {
                bottom: {
                    style: Excel.BorderLineStyle.continuous
                },
                left: {
                    style: Excel.BorderLineStyle.continuous
                },
                right: {
                    style: Excel.BorderLineStyle.continuous
                },
                top: {
                    style: Excel.BorderLineStyle.continuous
                }
            }
        }
    };

    const range = sheet.getRange("A1:E5");

    // You can use empty JSON objects to avoid changing a cell's properties.
    range.setCellProperties([
        [topHeaderProps, {}, {}, {}, {}],
        [{}, {}, headerProps, headerProps, headerProps],
        [{}, headerProps, nonApplicableProps, matchupScoreProps, matchupScoreProps],
        [{}, headerProps, matchupScoreProps, nonApplicableProps, matchupScoreProps],
        [{}, headerProps, matchupScoreProps, matchupScoreProps, nonApplicableProps]
    ]);

    sheet.getUsedRange().format.autofitColumns();
    await context.sync();
});

setColumnProperties(columnPropertiesData)

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

setColumnProperties(columnPropertiesData: SettableColumnProperties[]): void;

Parameters

columnPropertiesData

Excel.SettableColumnProperties[]

An array that represents which properties to set in each column.

Returns

void

Remarks

[ API set: ExcelApi 1.9 ]

setDirty()

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

setDirty(): void;

Returns

void

Remarks

[ API set: ExcelApi 1.9 ]

setRowProperties(rowPropertiesData)

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

setRowProperties(rowPropertiesData: SettableRowProperties[]): void;

Parameters

rowPropertiesData

Excel.SettableRowProperties[]

An array that represents which properties to set in each row.

Returns

void

Remarks

[ API set: ExcelApi 1.9 ]

showCard()

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

showCard(): void;

Returns

void

Remarks

[ API set: ExcelApi 1.7 ]

showGroupDetails(groupOption)

Shows the details of the row or column group.

showGroupDetails(groupOption: Excel.GroupOption): void;

Parameters

groupOption
Excel.GroupOption

Specifies whether to show the details of grouped rows or grouped columns.

Returns

void

Remarks

[ API set: ExcelApi 1.10 ]

showGroupDetails(groupOptionString)

Shows the details of the row or column group.

showGroupDetails(groupOptionString: "ByRows" | "ByColumns"): void;

Parameters

groupOptionString

"ByRows" | "ByColumns"

Specifies whether to show the details of grouped rows or grouped columns.

Returns

void

Remarks

[ API set: ExcelApi 1.10 ]

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 need to add the object to the tracked object collection when the object was first created.

track(): Excel.Range;

Returns

ungroup(groupOption)

Ungroups columns and rows for an outline.

ungroup(groupOption: Excel.GroupOption): void;

Parameters

groupOption
Excel.GroupOption

Specifies how the range can be ungrouped by rows or columns.

Returns

void

Remarks

[ API set: ExcelApi 1.10 ]

Examples

// Link to full sample: https://raw.githubusercontent.com/OfficeDev/office-js-snippets/prod/samples/excel/42-range/outline.yaml
Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getActiveWorksheet();
    
    // This removes two levels of groups from the "A1-R10" range.
    // Any groups at the same level on the same dimension will be removed by a single call.
    sheet.getRange("A1:R10").ungroup(Excel.GroupOption.byRows);
    sheet.getRange("A1:R10").ungroup(Excel.GroupOption.byRows);
    sheet.getRange("A1:R10").ungroup(Excel.GroupOption.byColumns);
    sheet.getRange("A1:R10").ungroup(Excel.GroupOption.byColumns);
    await context.sync();
});

ungroup(groupOptionString)

Ungroups columns and rows for an outline.

ungroup(groupOptionString: "ByRows" | "ByColumns"): void;

Parameters

groupOptionString

"ByRows" | "ByColumns"

Specifies how the range can be ungrouped by rows or columns.

Returns

void

Remarks

[ API set: ExcelApi 1.10 ]

unmerge()

Unmerge the range cells into separate cells.

unmerge(): void;

Returns

void

Remarks

[ API set: ExcelApi 1.2 ]

Examples

await Excel.run(async (context) => { 
    const sheetName = "Sheet1";
    const rangeAddress = "A1:C3";
    const range = context.workbook.worksheets.getItem(sheetName).getRange(rangeAddress);
    range.unmerge();
    await context.sync(); 
});

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

await 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++) {
            const cell = largeRange.getCell(i, j);
            cell.values = [[i *j]];

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

    await context.sync();
});