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
Properties
| address | Specifies the range reference in A1-style. Address value contains the sheet reference (e.g., "Sheet1!A1:B4"). |
| address |
Represents the range reference for the specified range in the language of the user. |
| cell |
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). |
| column |
Specifies the total number of columns in the range. |
| column |
Represents if all columns in the current range are hidden. Value is |
| column |
Specifies the column number of the first cell in the range. Zero-indexed. |
| conditional |
The collection of |
| context | The request context associated with the object. This connects the add-in's process to the Office host application's process. |
| 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. |
| formulas |
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. |
| hidden | Represents if all cells in the current range are hidden. Value is |
| number |
Represents Excel's number format code for the given range. For more information about Excel number formatting, see Number format codes. |
| row |
Returns the total number of rows in the range. |
| row |
Represents if all rows in the current range are hidden. Value is |
| row |
Returns the row number of the first cell in the range. Zero-indexed. |
| sort | Represents the range sort of the current range. |
| 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. |
| 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. |
| value |
Specifies the type of data in each cell. |
| worksheet | The worksheet containing the current range. |
Methods
| calculate() | Calculates a range of cells on a worksheet. |
| clear(apply |
Clear range values, format, fill, border, etc. |
| clear(apply |
Clear range values, format, fill, border, etc. |
| delete(shift) | Deletes the cells associated with the range. |
| delete(shift |
Deletes the cells associated with the range. |
| get |
Gets the smallest range object that encompasses the given ranges. For example, the |
| get |
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. |
| get |
Gets a column contained in the range. |
| get |
Gets a certain number of columns to the right of the current |
| get |
Gets a certain number of columns to the left of the current |
| get |
Gets an object that represents the entire column of the range (for example, if the current range represents cells "B4:E11", its |
| get |
Gets an object that represents the entire row of the range (for example, if the current range represents cells "B4:E11", its |
| get |
Gets the range object that represents the rectangular intersection of the given ranges. |
| get |
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 |
| get |
Gets the last cell within the range. For example, the last cell of "B2:D5" is "D5". |
| get |
Gets the last column within the range. For example, the last column of "B2:D5" is "D2:D5". |
| get |
Gets the last row within the range. For example, the last row of "B2:D5" is "B5:D5". |
| get |
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. |
| get |
Gets a |
| get |
Gets a row contained in the range. |
| get |
Gets a certain number of rows above the current |
| get |
Gets a certain number of rows below the current |
| get |
Returns the used range of the given range object. If there are no used cells within the range, this function will throw an |
| get |
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 |
| get |
Represents the visible rows of the current range. |
| 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 |
| 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 |
| load(options) | Queues up a command to load the specified properties of the object. You must call |
| load(property |
Queues up a command to load the specified properties of the object. You must call |
| load(property |
Queues up a command to load the specified properties of the object. You must call |
| merge(across) | Merge the range cells into one region in the worksheet. |
| 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. |
| toJSON() | Overrides the JavaScript |
| 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 |
| 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 |
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
addressLocal
Represents the range reference for the specified range in the language of the user.
readonly addressLocal: string;Property Value
string
Remarks
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
columnCount
Specifies the total number of columns in the range.
readonly columnCount: number;Property Value
number
Remarks
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
columnIndex
Specifies the column number of the first cell in the range. Zero-indexed.
readonly columnIndex: number;Property Value
number
Remarks
conditionalFormats
The collection of ConditionalFormats that intersect the range.
readonly conditionalFormats: Excel.ConditionalFormatCollection;Property Value
Remarks
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
format
Returns a format object, encapsulating the range's font, fill, borders, alignment, and other properties.
readonly format: Excel.RangeFormat;Property Value
Remarks
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
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
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
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
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
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);
});
rowCount
Returns the total number of rows in the range.
readonly rowCount: number;Property Value
number
Remarks
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
rowIndex
Returns the row number of the first cell in the range. Zero-indexed.
readonly rowIndex: number;Property Value
number
Remarks
sort
Represents the range sort of the current range.
readonly sort: Excel.RangeSort;Property Value
Remarks
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();
});
}
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
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
valueTypes
Specifies the type of data in each cell.
readonly valueTypes: Excel.RangeValueType[][];Property Value
Remarks
worksheet
The worksheet containing the current range.
readonly worksheet: Excel.Worksheet;Property Value
Remarks
Method Details
calculate()
Calculates a range of cells on a worksheet.
calculate(): void;Returns
void
Remarks
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
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
delete(shift)
Deletes the cells associated with the range.
delete(shift: Excel.DeleteShiftDirection): void;Parameters
Specifies which way to shift the cells. See Excel.DeleteShiftDirection for details.
Returns
void
Remarks
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
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
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
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);
});
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
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
});
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
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
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
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
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);
});
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
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
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
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
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
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
});
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
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
});
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
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
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
});
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
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
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
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
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
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
Specifies which way to shift the cells. See Excel.InsertShiftDirection for details.
Returns
Remarks
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
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
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
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();
});
select()
Selects the specified range in the Excel UI.
select(): void;Returns
void
Remarks
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
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
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 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();
});
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
unmerge()
Unmerge the range cells into separate cells.
unmerge(): void;Returns
void
Remarks
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();
});
Office Add-ins
Feedback
Coming soon: Throughout 2024 we will be phasing out GitHub Issues as the feedback mechanism for content and replacing it with a new feedback system. For more information see: https://aka.ms/ContentUserFeedback.
Submit and view feedback for