ExcelScript.Range interface

Reference

Package:: ExcelScript

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

Remarks

Examples

/**
 * This script logs the address of the used range in the current worksheet.
 */
function main(workbook: ExcelScript.Workbook) {
  // Get the current, active worksheet.
  let currentWorksheet = workbook.getActiveWorksheet();

  // Get the range containing all the cells with data or formatting.
  let usedRange = currentWorksheet.getUsedRange();

  // Log the range's address to the console.
  console.log(usedRange.getAddress());
}

Methods

addConditionalFormat(type)	Adds a new conditional format to the collection at the first/top priority.
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.
calculate()	Calculates a range of cells on a worksheet.
clear(applyTo)	Clear range values and formatting, such as fill and border.
clearAllConditionalFormats()	Clears all conditional formats active on the current specified range.
convertDataTypeToText()	Converts the range cells with data types into text.
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 is expanded automatically if it's smaller than the source. Note: Like the copy functionality in the Excel UI, if the destination range is an exact multiple greater than the source range in either rows or columns, then the source content is replicated multiple times. For example, a 2x2 range copy into a 2x6 range will result in 3 copies of the original 2x2 range.
delete(shift)	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. If there are no matches, then this method returns `undefined`.
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.
getAddress()	Specifies the range reference in A1-style. Address value contains the sheet reference (e.g., "Sheet1!A1:B4").
getAddressLocal()	Represents the range reference for the specified range in the language of the user.
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.
getCellCount()	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).
getColumn(column)	Gets a column contained in the range.
getColumnCount()	Specifies the total number of columns in the range.
getColumnHidden()	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.
getColumnIndex()	Specifies the column number of the first cell in the range. Zero-indexed.
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.
getConditionalFormat(id)	Returns a conditional format identified by its ID. If the conditional format object does not exist, then this method returns `undefined`.
getConditionalFormats()	The collection of `ConditionalFormats` that intersect the range.
getDataValidation()	Returns a data validation 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.
getFormat()	Returns a format object, encapsulating the range's font, fill, borders, alignment, and other properties.
getFormula()	Represents the cell formula in A1-style notation. If the range contains multiple cells, the data from first cell (represented by row index of 0 and column index of 0) will be returned.
getFormulaLocal()	Represents the cell 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 the range contains multiple cells, the data from first cell (represented by row index of 0 and column index of 0) will be returned.
getFormulaR1C1()	Represents the cell formula in R1C1-style notation. If the range contains multiple cells, the data from first cell (represented by row index of 0 and column index of 0) will be returned.
getFormulas()	Represents the formula in A1-style notation. If a cell has no formula, its value is returned instead.
getFormulasLocal()	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.
getFormulasR1C1()	Represents the formula in R1C1-style notation. If a cell has no formula, its value is returned instead.
getHasSpill()	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.
getHeight()	Returns the distance in points, for 100% zoom, from the top edge of the range to the bottom edge of the range.
getHidden()	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.
getHyperlink()	Represents the hyperlink for the current range.
getImage()	Renders the range as a base64-encoded png image.
getIntersection(anotherRange)	Gets the range object that represents the rectangular intersection of the given ranges. If no intersection is found, then this method returns `undefined`.
getIsEntireColumn()	Represents if the current range is an entire column.
getIsEntireRow()	Represents if the current range is an entire row.
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".
getLeft()	Returns the distance in points, for 100% zoom, from the left edge of the worksheet to the left edge of the range.
getLinkedDataTypeState()	Represents the data type state of the cell.
getLinkedDataTypeStates()	Represents the data type state of each cell.
getMergedAreas()	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 returns `undefined`.
getNumberFormat()	Represents cell Excel number format code for the given range. If the range contains multiple cells, the data from first cell (represented by row index of 0 and column index of 0) will be returned.
getNumberFormatCategories()	Represents the category of number format of each cell.
getNumberFormatCategory()	Specifies the number format category of first cell in the range (represented by row index of 0 and column index of 0).
getNumberFormatLocal()	Represents cell Excel 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. If the range contains multiple cells, the data from first cell (represented by row index of 0 and column index of 0) will be returned.
getNumberFormats()	Represents Excel's number format code for the given range.
getNumberFormatsLocal()	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.
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.
getPredefinedCellStyle()	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.
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.
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.
getRowCount()	Returns the total number of rows in the range.
getRowHidden()	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.
getRowIndex()	Returns the row number of the first cell in the range. Zero-indexed.
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.
getSavedAsArray()	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.
getSort()	Represents the range sort of the current range.
getSpecialCells(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 `undefined`.
getSpillingToRange()	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 `undefined`.
getSpillParent()	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 `undefined`.
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.
getText()	Represents Text value of the specified range. The Text value will not depend on the cell width. The # sign substitution that happens in Excel UI will not affect the text value returned by the API. If the range contains multiple cells, the data from first cell (represented by row index of 0 and column index of 0) will be returned.
getTexts()	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.
getTop()	Returns the distance in points, for 100% zoom, from the top edge of the worksheet to the top edge of the range.
getUsedRange(valuesOnly)	Returns the used range of the given range object. If there are no used cells within the range, then this method returns `undefined`.
getValue()	Represents the raw value of the specified range. The data returned could be of type string, number, or a boolean. Cell that contain an error will return the error string. If the range contains multiple cells, the data from first cell (represented by row index of 0 and column index of 0) will be returned.
getValues()	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.
getValueType()	Represents the type of data in the cell. If the range contains multiple cells, the data from first cell (represented by row index of 0 and column index of 0) will be returned.
getValueTypes()	Specifies the type of data in each cell.
getVisibleView()	Represents the visible rows of the current range.
getWidth()	Returns the distance in points, for 100% zoom, from the left edge of the range to the right edge of the range.
getWorksheet()	The worksheet containing the current range.
group(groupOption)	Groups columns and rows for an outline.
hideGroupDetails(groupOption)	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.
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.
setColumnHidden(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.
setDirty()	Set a range to be recalculated when the next recalculation occurs.
setFormula(formula)	Sets the cell formula in A1-style notation. If the range contains multiple cells, each cell in the given range will be updated with the input data.
setFormulaLocal(formulaLocal)	Set the cell 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 the range contains multiple cells, each cell in the given range will be updated with the input data.
setFormulaR1C1(formulaR1C1)	Sets the cell formula in R1C1-style notation. If the range contains multiple cells, each cell in the given range will be updated with the input data.
setFormulas(formulas)	Represents the formula in A1-style notation. If a cell has no formula, its value is returned instead.
setFormulasLocal(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.
setFormulasR1C1(formulasR1C1)	Represents the formula in R1C1-style notation. If a cell has no formula, its value is returned instead.
setHyperlink(hyperlink)	Represents the hyperlink for the current range.
setNumberFormat(numberFormat)	Sets cell Excel number format code for the given range. If the range contains multiple cells, each cell in the given range will be updated with the input data.
setNumberFormatLocal(numberFormatLocal)	Sets cell Excel 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. If the range contains multiple cells, each cell in the given range will be updated with the input data.
setNumberFormats(numberFormats)	Represents Excel's number format code for the given range.
setNumberFormatsLocal(numberFormatsLocal)	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.
setPredefinedCellStyle(predefinedCellStyle)	Represents the style of the current range.
setRowHidden(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.
setValue(value)	Sets the raw value of the specified range. The data being set could be of type string, number, or a boolean. `null` value will be ignored (not set or overwritten in Excel). If the range contains multiple cells, each cell in the given range will be updated with the input data.
setValues(values)	Sets the raw values of the specified range. The data provided could be a string, number, or boolean. If the provided value starts with a plus ("+"), minus ("-"), or equal sign ("="), Excel interprets this value as a formula.
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.
ungroup(groupOption)	Ungroups columns and rows for an outline.
unmerge()	Unmerge the range cells into separate cells.

Method Details

addConditionalFormat(type)

Adds a new conditional format to the collection at the first/top priority.

addConditionalFormat(type: ConditionalFormatType): ConditionalFormat;

Parameters

type: ExcelScript.ConditionalFormatType

The type of conditional format being added. See ExcelScript.ConditionalFormatType for details.

Returns

ExcelScript.ConditionalFormat

Examples

/**
 * This sample applies conditional formatting to the currently used range in the worksheet. 
 * The conditional formatting is a green fill for the top 10% of values.
 */
function main(workbook: ExcelScript.Workbook) {
  // Get the current worksheet.
  let selectedSheet = workbook.getActiveWorksheet();

  // Get the used range in the worksheet.
  let range = selectedSheet.getUsedRange();

  // Set the fill color to green for the top 10% of values in the range.
  let conditionalFormat = range.addConditionalFormat(ExcelScript.ConditionalFormatType.topBottom)
  conditionalFormat.getTopBottom().getFormat().getFill().setColor("green");
  conditionalFormat.getTopBottom().setRule({
    rank: 10, // The percentage threshold.
    type: ExcelScript.ConditionalTopBottomCriterionType.topPercent // The type of the top/bottom condition.
  });
}

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.

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

Parameters

destinationRange: ExcelScript.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: ExcelScript.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

Examples

/**
 * This script uses the autofill feature to complete a table.
 * See https://support.microsoft.com/office/74e31bdd-d993-45da-aa82-35a236c5b5db
 * for examples of autofill scenarios.
 */
function main(workbook: ExcelScript.Workbook) {
  // Get the current, active worksheet.
  let currentWorksheet = workbook.getActiveWorksheet();

  // Get the data range that shows the pattern.
  let dataRange = currentWorksheet.getRange("C2:C3");

  // Autofill the connected range. C2:C3 are filled in. C4:C14 are blank.
  // This uses the default behavior to match a pattern with the table's contents.
  dataRange.autoFill("C2:C14");
}

calculate()

Calculates a range of cells on a worksheet.

calculate(): void;

Returns

void

Examples

/**
 * This script recalculates the used range of a specific worksheet.
 */
function main(workbook: ExcelScript.Workbook) {
  // Only recalculate if the calculation mode is not set to automatic.
  if (workbook.getApplication().getCalculationMode() !== ExcelScript.CalculationMode.automatic) {
    // Get the used range from a worksheet named "Monthly Report".
    const sheet = workbook.getWorksheet("Monthly Report");
    const range = sheet.getUsedRange();
    console.log(`Calculating ${range.getAddress()}`);

    // Force all the used cells in that worksheet to calculate.
    sheet.getUsedRange().calculate();
  }
}

clear(applyTo)

Clear range values and formatting, such as fill and border.

clear(applyTo?: ClearApplyTo): void;

Parameters

applyTo: ExcelScript.ClearApplyTo

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

Returns

void

Examples

/**
 * This script removes all the formatting from the selected range.
 */
function main(workbook: ExcelScript.Workbook) {
  // Get the selected range.
  let range = workbook.getSelectedRange();

  // Clear all the formatting in that range.
  range.clear(ExcelScript.ClearApplyTo.formats);
}

clearAllConditionalFormats()

Clears all conditional formats active on the current specified range.

clearAllConditionalFormats(): void;

Returns

void

convertDataTypeToText()

Converts the range cells with data types into text.

convertDataTypeToText(): void;

Returns

void

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 is expanded automatically if it's smaller than the source. Note: Like the copy functionality in the Excel UI, if the destination range is an exact multiple greater than the source range in either rows or columns, then the source content is replicated multiple times. For example, a 2x2 range copy into a 2x6 range will result in 3 copies of the original 2x2 range.

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

Parameters

sourceRange: ExcelScript.Range | ExcelScript.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: ExcelScript.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

Examples

/**
 * This script copies a table from one worksheet to a new worksheet.
 */
function main(workbook: ExcelScript.Workbook) {
  // Get the worksheet named "TableTemplate".
  let base = workbook.getWorksheet("TableTemplate");
  
  // Get the range to be copied based on the first table.
  let tableRange = base.getTables()[0].getRange();

  // Get the area in a new worksheet for the new table.
  let newWorksheet = workbook.addWorksheet();
  let newRange = newWorksheet.getRangeByIndexes(0,0, tableRange.getRowCount(), tableRange.getColumnCount());

  // Copy the existing data into the new range.
  newRange.copyFrom(tableRange);
}

delete(shift)

Deletes the cells associated with the range.

delete(shift: DeleteShiftDirection): void;

Parameters

shift: ExcelScript.DeleteShiftDirection

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

Returns

void

Examples

/**
 * This sample creates a sample range, then deletes
 * "A1" using different DeleteShiftDirection values.
 */
function main(workbook: ExcelScript.Workbook) {
  // Add sample data to better visualize the delete changes.
  const currentSheet = workbook.getActiveWorksheet();
  currentSheet.getRange("A1:D4").setValues([
    [1,2,3,4],
    [5,6,7,8],
    [9,10,11,12],
    [13,14,15,16]]);

  // Delete A1 and shift the cells from the right to fill the space.
  // The value being deleted is 1.
  currentSheet.getRange("A1").delete(ExcelScript.DeleteShiftDirection.left);

  // Delete A1 and shift the cells from the bottom to fill the space.
  // The value being deleted is 2.
  currentSheet.getRange("A1").delete(ExcelScript.DeleteShiftDirection.up);

  // Log the sample range. The values should be:
  /*
    5, 3, 4, "",
    9, 6, 7, 8,
    13, 10, 11, 12,
    "", 14, 15, 16
  */
  console.log(currentSheet.getRange("A1:D4").getValues()); 
}

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. If there are no matches, then this method returns undefined.

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

Parameters

text: string

The string to find.

criteria: ExcelScript.SearchCriteria

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

Returns

ExcelScript.Range

Examples

/**
 * This script searches through a table column and finds cells marked "no change". 
 * Those cells have "no change" replaced with the value from the cell to the left.
 * This script uses Range.find instead of Worksheet.findAll 
 * to limit the search to a specific range.
 */
function main(workbook: ExcelScript.Workbook) {
  // Get the range of a table named "Orders".
  let table = workbook.getTable("Orders");
  let range = table.getColumnByName("March").getRange();

  // Find all cells with the value "no change".
  let cellToOverwrite = range.find("no change", { completeMatch: true });
  while (cellToOverwrite) {
    let cellToCopyFrom = cellToOverwrite.getOffsetRange(0,-1);
    cellToOverwrite.setValue(cellToCopyFrom.getValue());
    cellToOverwrite = range.find("no change", { completeMatch: true });
  }
}

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

Examples

/**
 * This script uses the Flash Fill feature to complete a table.
 * See https://support.microsoft.com/office/3f9bcf1e-db93-4890-94a0-1578341f73f7
 * for the example table.
 */
function main(workbook: ExcelScript.Workbook) {
  // Get the current, active worksheet.
  let currentWorksheet = workbook.getActiveWorksheet();

  // Get the data range with a pattern and cells to fill. C2 is filled in. C3:C6 are blank.
  let dataRange = currentWorksheet.getRange("C2:C6");

  // Flash fill the connected range. 
  dataRange.flashFill();
}

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): 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

ExcelScript.Range

getAddress()

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

getAddress(): string;

Returns

string

Examples

/**
 * This script logs the address of the used range in each worksheet.
 */
function main(workbook: ExcelScript.Workbook) {
  // Iterate over every worksheet in the workbook.
  workbook.getWorksheets().forEach((sheet) => {
    // Get the used range for a single worksheet.
    let range = sheet.getUsedRange();

    // Print the address of the used range to the console.
    console.log(range.getAddress());
  });
}

getAddressLocal()

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

getAddressLocal(): string;

Returns

string

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): Range;

Parameters

anotherRange: ExcelScript.Range | string

The range object, address, or range name.

Returns

ExcelScript.Range

Examples

/**
 * This script gets the bounding range of two existing ranges and puts a border around it.
 */
function main(workbook: ExcelScript.Workbook) {
  // Get the current worksheet.
  let sheet = workbook.getActiveWorksheet();

  // Create two range objects for the sample.
  let range1 = sheet.getRange("B2:C5");
  let range2 = sheet.getRange("D10:E15");

  // Get the rectangular range that fully includes both ranges.
  let boundingRectangle = range1.getBoundingRect(range2);

  // Add a border around the whole bounding range (B2:E15).
  let format = boundingRectangle.getFormat();
  format.getRangeBorder(ExcelScript.BorderIndex.edgeTop).setStyle(ExcelScript.BorderLineStyle.continuous); // Top border
  format.getRangeBorder(ExcelScript.BorderIndex.edgeBottom).setStyle(ExcelScript.BorderLineStyle.continuous); // Bottom border
  format.getRangeBorder(ExcelScript.BorderIndex.edgeLeft).setStyle(ExcelScript.BorderLineStyle.continuous); // Left border
  format.getRangeBorder(ExcelScript.BorderIndex.edgeRight).setStyle(ExcelScript.BorderLineStyle.continuous); // Right border
}

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): 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

ExcelScript.Range

getCellCount()

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

getCellCount(): number;

Returns

number

getColumn(column)

Gets a column contained in the range.

getColumn(column: number): Range;

Parameters

column: number

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

Returns

ExcelScript.Range

getColumnCount()

Specifies the total number of columns in the range.

getColumnCount(): number;

Returns

number

Examples

/**
 * This sample provides the count of negative numbers that are present
 * in the used range of the current worksheet.
 */
function main(workbook: ExcelScript.Workbook) {
  // Get the working range.
  let usedRange = workbook.getActiveWorksheet().getUsedRange();
  let rowCount = usedRange.getRowCount();
  let columnCount = usedRange.getColumnCount();

  // Save the values locally to avoid repeatedly asking the workbook.
  let usedRangeValues = usedRange.getValues();

  // Start the negative number counter.
  let negativeCount = 0;

  // Iterate over the entire range looking for negative numbers.
  for (let i = 0; i < rowCount; i++) {
    for (let j = 0; j < columnCount; j++) {
      if (usedRangeValues[i][j] < 0) {
        negativeCount++;
      }
    }
  }

  // Log the negative number count to the console.
  console.log(negativeCount);
}

getColumnHidden()

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.

getColumnHidden(): boolean;

Returns

boolean

getColumnIndex()

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

getColumnIndex(): number;

Returns

number

getColumnsAfter(count)

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

getColumnsAfter(count?: number): 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

ExcelScript.Range

getColumnsBefore(count)

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

getColumnsBefore(count?: number): Range;

Parameters

count: number

Returns

ExcelScript.Range

getConditionalFormat(id)

Returns a conditional format identified by its ID. If the conditional format object does not exist, then this method returns undefined.

getConditionalFormat(id: string): ConditionalFormat | undefined;

Parameters

id: string

The ID of the conditional format.

Returns

ExcelScript.ConditionalFormat | undefined

getConditionalFormats()

The collection of ConditionalFormats that intersect the range.

getConditionalFormats(): ConditionalFormat[];

Returns

ExcelScript.ConditionalFormat[]

getDataValidation()

Returns a data validation object.

getDataValidation(): DataValidation;

Returns

ExcelScript.DataValidation

Examples

/**
 * This script creates a drop-down selection list for a cell. It uses the existing values of the selected range as the choices for the list.
 */
function main(workbook: ExcelScript.Workbook) {
  // Get the values for data validation.
  let selectedRange = workbook.getSelectedRange();
  let rangeValues = selectedRange.getValues();

  // Convert the values into a comma-delimited string.
  let dataValidationListString = "";
  rangeValues.forEach((rangeValueRow) => {
    rangeValueRow.forEach((value) => {
      dataValidationListString += value + ",";
    });
  });

  // Clear the old range.
  selectedRange.clear(ExcelScript.ClearApplyTo.contents);

  // Apply the data validation to the first cell in the selected range.
  let targetCell = selectedRange.getCell(0,0);
  let dataValidation = targetCell.getDataValidation();

  // Set the content of the drop-down list.
  dataValidation.setRule({
      list: {
        inCellDropDown: true,
        source: dataValidationListString
      }
    });
}

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(): WorkbookRangeAreas;

Returns

ExcelScript.WorkbookRangeAreas

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(): WorkbookRangeAreas;

Returns

ExcelScript.WorkbookRangeAreas

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(): WorkbookRangeAreas;

Returns

ExcelScript.WorkbookRangeAreas

Examples

/**
 * This script finds the direct precedents of the active cell.
 * It changes the font and color of those precedent cells. 
 */
function main(workbook: ExcelScript.Workbook) {
  // Get the active cell.
  const selected = workbook.getActiveCell();
  
  // Get the cells that are direct precedents of the current cell.
  const precedents : ExcelScript.WorkbookRangeAreas = selected.getDirectPrecedents();

  // Set the font to bold and the fill color to orange for all the precedent cells.
  precedents.getRanges().forEach(range => {
    range.getFormat().getFill().setColor("orange");
    range.getFormat().getFont().setBold(true);
  });
}

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(): Range;

Returns

ExcelScript.Range

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(): Range;

Returns

ExcelScript.Range

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: KeyboardDirection,
            activeCell?: Range | string
        ): Range;

Parameters

direction: ExcelScript.KeyboardDirection

The direction from the active cell.

activeCell: ExcelScript.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

ExcelScript.Range

Examples

/**
 * This script makes the font bold on all the contiguous cells between 
 * A1 and the bottom of the used range of the first column.
 */
function main(workbook: ExcelScript.Workbook)
{
  // Get the current worksheet.
  let selectedSheet = workbook.getActiveWorksheet();

  // Get every cell that's used between A1 and the end of the column.
  // This recreates the Ctrl+Shift+Down arrow key behavior.
  let firstCell = selectedSheet.getRange("A1");
  let firstColumn = firstCell.getExtendedRange(ExcelScript.KeyboardDirection.down);

  // Set the font to bold in that range.
  firstColumn.getFormat().getFont().setBold(true);
}

getFormat()

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

getFormat(): RangeFormat;

Returns

ExcelScript.RangeFormat

Examples

/**
 * This script gives the total row of a table a green color fill.
 */
function main(workbook: ExcelScript.Workbook) {
  // Get the first table in the workbook.
  let table = workbook.getTables()[0];

  // Get the range for the total row of the table.
  let totalRange = table.getTotalRowRange();

  // Set the fill color to green.
  totalRange.getFormat().getFill().setColor("green");
}

getFormula()

Represents the cell formula in A1-style notation. If the range contains multiple cells, the data from first cell (represented by row index of 0 and column index of 0) will be returned.

getFormula(): string;

Returns

string

Examples

/*
 * This script sets a cell's formula, 
 * then displays how Excel stores the cell's formula and value separately.
 */
function main(workbook: ExcelScript.Workbook) {
  let selectedSheet = workbook.getActiveWorksheet();

  // Set A1 to 2.
  let a1 = selectedSheet.getRange("A1");
  a1.setValue(2);

  // Set B1 to the formula =(2*A1), which should equal 4.
  let b1 = selectedSheet.getRange("B1")
  b1.setFormula("=(2*A1)");

  // Log the current results for `getFormula` and `getValue` at B1.
  console.log(`B1 - Formula: ${b1.getFormula()} | Value: ${b1.getValue()}`);
}

getFormulaLocal()

Represents the cell 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 the range contains multiple cells, the data from first cell (represented by row index of 0 and column index of 0) will be returned.

getFormulaLocal(): string;

Returns

string

getFormulaR1C1()

Represents the cell formula in R1C1-style notation. If the range contains multiple cells, the data from first cell (represented by row index of 0 and column index of 0) will be returned.

getFormulaR1C1(): string;

Returns

string

getFormulas()

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

getFormulas(): string[][];

Returns

string[][]

getFormulasLocal()

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.

getFormulasLocal(): string[][];

Returns

string[][]

getFormulasR1C1()

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

getFormulasR1C1(): string[][];

Returns

string[][]

getHasSpill()

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.

getHasSpill(): boolean;

Returns

boolean

getHeight()

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

getHeight(): number;

Returns

number

getHidden()

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.

getHidden(): boolean;

Returns

boolean

getHyperlink()

Represents the hyperlink for the current range.

getHyperlink(): RangeHyperlink;

Returns

ExcelScript.RangeHyperlink

Examples

/**
 * This sample clears all of the hyperlinks from the current worksheet
 * and removes the usual hyperlink formatting.
 */
function main(workbook: ExcelScript.Workbook, sheetName: string = 'Sheet1') {
  // Get the active worksheet. 
  let sheet = workbook.getWorksheet(sheetName);

  // Get the used range to operate on.
  // For large ranges (over 10000 entries), consider splitting the operation into batches for performance.
  const targetRange = sheet.getUsedRange(true);
  console.log(`Target Range to clear hyperlinks from: ${targetRange.getAddress()}`);

  const rowCount = targetRange.getRowCount();
  const colCount = targetRange.getColumnCount();
  console.log(`Searching for hyperlinks in ${targetRange.getAddress()} which contains ${(rowCount * colCount)} cells`);

  // Go through each individual cell looking for a hyperlink. 
  // This allows us to limit the formatting changes to only the cells with hyperlink formatting.
  let clearedCount = 0;
  for (let i = 0; i < rowCount; i++) {
    for (let j = 0; j < colCount; j++) {
      const cell = targetRange.getCell(i, j);
      const hyperlink = cell.getHyperlink();
      if (hyperlink) {
        cell.clear(ExcelScript.ClearApplyTo.hyperlinks);
        cell.getFormat().getFont().setUnderline(ExcelScript.RangeUnderlineStyle.none);
        cell.getFormat().getFont().setColor('Black');
        clearedCount++;
      }
    }
  }

  console.log(`Done. Cleared hyperlinks from ${clearedCount} cells`);
}

getImage()

Renders the range as a base64-encoded png image.

getImage(): string;

Returns

string

getIntersection(anotherRange)

Gets the range object that represents the rectangular intersection of the given ranges. If no intersection is found, then this method returns undefined.

getIntersection(anotherRange: Range | string): Range;

Parameters

anotherRange: ExcelScript.Range | string

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

Returns

ExcelScript.Range

getIsEntireColumn()

Represents if the current range is an entire column.

getIsEntireColumn(): boolean;

Returns

boolean

getIsEntireRow()

Represents if the current range is an entire row.

getIsEntireRow(): boolean;

Returns

boolean

getLastCell()

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

getLastCell(): Range;

Returns

ExcelScript.Range

getLastColumn()

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

getLastColumn(): Range;

Returns

ExcelScript.Range

getLastRow()

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

getLastRow(): Range;

Returns

ExcelScript.Range

getLeft()

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

getLeft(): number;

Returns

number

getLinkedDataTypeState()

Represents the data type state of the cell.

getLinkedDataTypeState(): LinkedDataTypeState;

Returns

ExcelScript.LinkedDataTypeState

getLinkedDataTypeStates()

Represents the data type state of each cell.

getLinkedDataTypeStates(): LinkedDataTypeState[][];

Returns

ExcelScript.LinkedDataTypeState[][]

getMergedAreas()

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 returns undefined.

getMergedAreas(): RangeAreas;

Returns

ExcelScript.RangeAreas

getNumberFormat()

Represents cell Excel number format code for the given range. If the range contains multiple cells, the data from first cell (represented by row index of 0 and column index of 0) will be returned.

getNumberFormat(): string;

Returns

string

getNumberFormatCategories()

Represents the category of number format of each cell.

getNumberFormatCategories(): NumberFormatCategory[][];

Returns

ExcelScript.NumberFormatCategory[][]

Examples

/**
 * This script finds cells in a table column that are not formatted as currency
 * and sets the fill color to red.
 */
function main(workbook: ExcelScript.Workbook) {
  // Get the "Cost" column from the "Expenses" table.
  const table = workbook.getTable("Expenses");
  const costColumn = table.getColumnByName("Cost");
  const costColumnRange = costColumn.getRangeBetweenHeaderAndTotal();

  // Get the number format categories for the column's range.
  const numberFormatCategories = costColumnRange.getNumberFormatCategories();

  // If any cell in the column doesn't have a currency format, make the cell red.
  numberFormatCategories.forEach((category, index) =>{
    if (category[0] != ExcelScript.NumberFormatCategory.currency) {
      costColumnRange.getCell(index, 0).getFormat().getFill().setColor("red");
    }
  }); 
}

getNumberFormatCategory()

Specifies the number format category of first cell in the range (represented by row index of 0 and column index of 0).

getNumberFormatCategory(): NumberFormatCategory;

Returns

ExcelScript.NumberFormatCategory

getNumberFormatLocal()

Represents cell Excel 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. If the range contains multiple cells, the data from first cell (represented by row index of 0 and column index of 0) will be returned.

getNumberFormatLocal(): string;

Returns

string

getNumberFormats()

Represents Excel's number format code for the given range.

getNumberFormats(): string[][];

Returns

string[][]

getNumberFormatsLocal()

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.

getNumberFormatsLocal(): string[][];

Returns

string[][]

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): 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

ExcelScript.Range

Examples

/**
 * This script gets adjacent cells using relative references.
 * Note that if the active cell is on the top row, part of the script fails, 
 * because it references the cell above the currently selected one.
 */
function main(workbook: ExcelScript.Workbook) {
  // Get the currently active cell in the workbook.
  let activeCell = workbook.getActiveCell();
  console.log(`The active cell's address is: ${activeCell.getAddress()}`);

  // Get the cell to the right of the active cell and set its value and color.
  let rightCell = activeCell.getOffsetRange(0,1);
  rightCell.setValue("Right cell");
  console.log(`The right cell's address is: ${rightCell.getAddress()}`);
  rightCell.getFormat().getFont().setColor("Magenta");
  rightCell.getFormat().getFill().setColor("Cyan");

  // Get the cell to the above of the active cell and set its value and color.
  // Note that this operation will fail if the active cell is in the top row.
  let aboveCell = activeCell.getOffsetRange(-1, 0);
  aboveCell.setValue("Above cell");
  console.log(`The above cell's address is: ${aboveCell.getAddress()}`);
  aboveCell.getFormat().getFont().setColor("White");
  aboveCell.getFormat().getFill().setColor("Black");
}

getPivotTables(fullyContained)

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

getPivotTables(fullyContained?: boolean): PivotTable[];

Parameters

fullyContained: boolean

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

Returns

ExcelScript.PivotTable[]

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(): WorkbookRangeAreas;

Returns

ExcelScript.WorkbookRangeAreas

getPredefinedCellStyle()

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.

getPredefinedCellStyle(): string;

Returns

string

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: KeyboardDirection,
            activeCell?: Range | string
        ): Range;

Parameters

direction: ExcelScript.KeyboardDirection

The direction from the active cell.

activeCell: ExcelScript.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

ExcelScript.Range

Examples

/**
 * This script adds the value "Total" after the end of the first column.
 */
function main(workbook: ExcelScript.Workbook)
{
  // Get the current worksheet.
  let selectedSheet = workbook.getActiveWorksheet();

  // Get the last used cell at the end of the column.
  // This recreates the Ctrl+Down arrow key behavior.
  let firstCell = selectedSheet.getRange("A1");
  let firstColumn = selectedSheet.getRange("A1").getRangeEdge(ExcelScript.KeyboardDirection.down);
  let cellAfter = firstColumn.getOffsetRange(1, 0);

  // Set the value of the cell after the current end of the used column to "Total".
  cellAfter.setValue("Total");
}

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): 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

ExcelScript.Range

Examples

/**
 * This script copies the formatting in the active cell to the neighboring cells.
 * Note that this script only works when the active cell isn't on an edge of the worksheet.
 */
function main(workbook: ExcelScript.Workbook) {
  // Get the active cell.
  let activeCell = workbook.getActiveCell();

  // Get the cell that's one row above and one column to the left of the active cell.
  let cornerCell = activeCell.getOffsetRange(-1,-1);

  // Get a range that includes all the cells surrounding the active cell.
  let surroundingRange = cornerCell.getResizedRange(2, 2)

  // Copy the formatting from the active cell to the new range.
  surroundingRange.copyFrom(
    activeCell, /* The source range. */
    ExcelScript.RangeCopyType.formats /* What to copy. */
  );
}

getRow(row)

Gets a row contained in the range.

getRow(row: number): Range;

Parameters

row: number

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

Returns

ExcelScript.Range

getRowCount()

Returns the total number of rows in the range.

getRowCount(): number;

Returns

number

Examples

/**
 * This sample provides the count of negative numbers that are present
 * in the used range of the current worksheet.
 */
function main(workbook: ExcelScript.Workbook) {
  // Get the working range.
  let usedRange = workbook.getActiveWorksheet().getUsedRange();
  let rowCount = usedRange.getRowCount();
  let columnCount = usedRange.getColumnCount();

  // Save the values locally to avoid repeatedly asking the workbook.
  let usedRangeValues = usedRange.getValues();

  // Start the negative number counter.
  let negativeCount = 0;

  // Iterate over the entire range looking for negative numbers.
  for (let i = 0; i < rowCount; i++) {
    for (let j = 0; j < columnCount; j++) {
      if (usedRangeValues[i][j] < 0) {
        negativeCount++;
      }
    }
  }

  // Log the negative number count to the console.
  console.log(negativeCount);
}

getRowHidden()

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.

getRowHidden(): boolean;

Returns

boolean

getRowIndex()

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

getRowIndex(): number;

Returns

number

getRowsAbove(count)

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

getRowsAbove(count?: number): 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

ExcelScript.Range

getRowsBelow(count)

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

getRowsBelow(count?: number): Range;

Parameters

count: number

Returns

ExcelScript.Range

getSavedAsArray()

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.

getSavedAsArray(): boolean;

Returns

boolean

getSort()

Represents the range sort of the current range.

getSort(): RangeSort;

Returns

ExcelScript.RangeSort

getSpecialCells(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 undefined.

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

Parameters

cellType: ExcelScript.SpecialCellType

The type of cells to include.

cellValueType: ExcelScript.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

ExcelScript.RangeAreas

Examples

/**
 * This sample gets all the blank cells in the current worksheet's used range. It then highlights all those cells with a yellow background.
 */
function main(workbook: ExcelScript.Workbook) {
    // Get the current used range.
    let range = workbook.getActiveWorksheet().getUsedRange();
    
    // Get all the blank cells.
    let blankCells = range.getSpecialCells(ExcelScript.SpecialCellType.blanks);
    // Highlight the blank cells with a yellow background.
    blankCells.getFormat().getFill().setColor("yellow");
}

getSpillingToRange()

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

getSpillingToRange(): Range;

Returns

ExcelScript.Range

getSpillParent()

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

getSpillParent(): Range;

Returns

ExcelScript.Range

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(): Range;

Returns

ExcelScript.Range

getTables(fullyContained)

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

getTables(fullyContained?: boolean): Table[];

Parameters

fullyContained: boolean

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

Returns

ExcelScript.Table[]

getText()

Represents Text value of the specified range. The Text value will not depend on the cell width. The # sign substitution that happens in Excel UI will not affect the text value returned by the API. If the range contains multiple cells, the data from first cell (represented by row index of 0 and column index of 0) will be returned.

getText(): string;

Returns

string

getTexts()

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.

getTexts(): string[][];

Returns

string[][]

getTop()

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

getTop(): number;

Returns

number

getUsedRange(valuesOnly)

Returns the used range of the given range object. If there are no used cells within the range, then this method returns undefined.

getUsedRange(valuesOnly?: boolean): Range;

Parameters

valuesOnly: boolean

Considers only cells with values as used cells.

Returns

ExcelScript.Range

getValue()

Represents the raw value of the specified range. The data returned could be of type string, number, or a boolean. Cell that contain an error will return the error string. If the range contains multiple cells, the data from first cell (represented by row index of 0 and column index of 0) will be returned.

getValue(): string | number | boolean;

Returns

string | number | boolean

Examples

/**
 * This sample reads the value of A1 and prints it to the console.
 */
function main(workbook: ExcelScript.Workbook) {
  // Get the current worksheet.
  let selectedSheet = workbook.getActiveWorksheet();

  // Get the value of cell A1.
  let range = selectedSheet.getRange("A1");
  
  // Print the value of A1.
  console.log(range.getValue());
}

getValues()

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.

getValues(): (string | number | boolean)[][];

Returns

(string | number | boolean)[][]

getValueType()

Represents the type of data in the cell. If the range contains multiple cells, the data from first cell (represented by row index of 0 and column index of 0) will be returned.

getValueType(): RangeValueType;

Returns

ExcelScript.RangeValueType

Examples

/**
 * This script formats rows in a worksheet based on the first value in that row. 
 * If it's the boolean value TRUE, the row is bolded. 
 * If it's FALSE, nothing is changed. 
 * If the value type isn't a boolean, the row is italicized.
 */
function main(workbook: ExcelScript.Workbook) {
  // Get the used range in the active worksheet.
  const sheet = workbook.getActiveWorksheet();
  const usedRange = sheet.getUsedRange();

  // Get the values in the first column.
  const firstColumnValues = usedRange.getColumn(0).getValues();

  // Look at the first cell in each row.
  const rowCount = usedRange.getRowCount();
  for (let i = 0; i < rowCount; i++) {
    // Get the type of the first cell to make sure it's a boolean.
    let firstValueType = usedRange.getCell(i, 0).getValueType();

    // Set the bold or italic of the row as described earlier.
    if (firstValueType === ExcelScript.RangeValueType.boolean) {
      if (firstColumnValues[i][0] as boolean === true) {
        usedRange.getRow(i).getFormat().getFont().setBold(true);
      } else {
        usedRange.getRow(i).getFormat().getFont().setBold(false);
      }
    } else {
      usedRange.getRow(i).getFormat().getFont().setItalic(true);
    }
  }
}

getValueTypes()

Specifies the type of data in each cell.

getValueTypes(): RangeValueType[][];

Returns

ExcelScript.RangeValueType[][]

getVisibleView()

Represents the visible rows of the current range.

getVisibleView(): RangeView;

Returns

ExcelScript.RangeView

Examples

/**
 * This script copies values and formatting from the 
 * visible range of a table in Sheet1 into Sheet2.
 */
function main(workbook: ExcelScript.Workbook) {
  // Get the filtered data from Sheet1.
  const currentSheet = workbook.getWorksheet("Sheet1");
  const table = currentSheet.getTables()[0];
  const visibleTableRange: ExcelScript.RangeView = table.getRange().getVisibleView();
  const source = currentSheet.getRanges(visibleTableRange.getCellAddresses().toString());

  // Copy the data into the other sheet.
  const otherSheet = workbook.getWorksheet("Sheet2");
  const otherRangeCorner = otherSheet.getRange("A1");
  otherRangeCorner.copyFrom(source, ExcelScript.RangeCopyType.all);
}

getWidth()

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

getWidth(): number;

Returns

number

getWorksheet()

The worksheet containing the current range.

getWorksheet(): Worksheet;

Returns

ExcelScript.Worksheet

group(groupOption)

Groups columns and rows for an outline.

group(groupOption: GroupOption): void;

Parameters

groupOption: ExcelScript.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

Examples

/**
 * This script creates a two-level column-based outline on Sheet1.
 */
function main(workbook: ExcelScript.Workbook) {
  // Group columns A-F in the worksheet named Sheet1.
  const sheet = workbook.getWorksheet("Sheet1");
  const firstLevel = sheet.getRange("A:F");
  firstLevel.group(ExcelScript.GroupOption.byColumns);
  
  // Create a second level to the outline by grouping subsections.
  sheet.getRange("A:B").group(ExcelScript.GroupOption.byColumns);
  sheet.getRange("D:E").group(ExcelScript.GroupOption.byColumns);
}

hideGroupDetails(groupOption)

Hides the details of the row or column group.

hideGroupDetails(groupOption: GroupOption): void;

Parameters

groupOption: ExcelScript.GroupOption

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

Returns

void

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: InsertShiftDirection): Range;

Parameters

shift: ExcelScript.InsertShiftDirection

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

Returns

ExcelScript.Range

Examples

/**
 * This script inserts headers at the top of the worksheet.
 */
function main(workbook: ExcelScript.Workbook)
{
  let currentSheet = workbook.getActiveWorksheet();

  // Create headers for 3 columns.
  let myHeaders = [["NAME", "ID", "ROLE"]];

  // Add a blank first row and push existing data down a row.
  let firstRow = currentSheet.getRange("1:1");
  firstRow.insert(ExcelScript.InsertShiftDirection.down);

  // Add the headers.
  currentSheet.getRange("A1:C1").setValues(myHeaders);
}

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

Examples

/**
 * This script merges a group of cells into a single region.
 */
function main(workbook: ExcelScript.Workbook) {
  // Get the active worksheet.
  const selectedSheet = workbook.getActiveWorksheet();

  // Merge cells A1 through A4.
  const range = selectedSheet.getRange("A1:A4");
  range.merge();
}

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: ExcelScript.Range | string

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

Returns

void

removeDuplicates(columns, includesHeader)

Removes duplicate values from the range specified by the columns.

removeDuplicates(
            columns: number[],
            includesHeader: boolean
        ): 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

ExcelScript.RemoveDuplicatesResult

Examples

/**
 * This script removes duplicate rows from a range.
 */
function main(workbook: ExcelScript.Workbook) {
  // Get the used range of the active worksheet.
  const usedRange = workbook.getActiveWorksheet().getUsedRange();

  // Remove any row that has a same value in the 0-indexed column as a previous row.
  const removedResults = usedRange.removeDuplicates([0], true);

  // Log the count of removed rows.
  console.log(`Rows removed: ${removedResults.getRemoved()}.`);
}

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: ReplaceCriteria
        ): number;

Parameters

text: string

String to find.

replacement: string

The string that replaces the original string.

criteria: ExcelScript.ReplaceCriteria

Additional replacement criteria.

Returns

number

Examples

/**
 * This script searches through a table column and replaces  
 * cells marked "monthly special" with "parsnip".
 * This script uses Range.replaceAll instead of Worksheet.replaceAll
 * to limit the search to a specific range.
 */
function main(workbook: ExcelScript.Workbook) {
  // Get the range of a table named "Orders".
  let table = workbook.getTable("Orders");
  let range = table.getColumnByName("Vegetable").getRange();
  
  // Change the value of any cells with the value "monthly special".
  range.replaceAll("monthly special", "parsnip", {completeMatch: true});
}

select()

Selects the specified range in the Excel UI.

select(): void;

Returns

void

Examples

/**
 * This script selects the first row of a table.
 */
function main(workbook: ExcelScript.Workbook) {
  // Get the first table on the current worksheet.
  const sheet = workbook.getActiveWorksheet()
  const table = sheet.getTables()[0];

  // Get the first data row in the table.
  const row = table.getRangeBetweenHeaderAndTotal().getRow(0);

  // Select the first data row.
  row.select();
}

setColumnHidden(columnHidden)

setColumnHidden(columnHidden: boolean): void;

Parameters

columnHidden: boolean

Returns

void

setDirty()

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

setDirty(): void;

Returns

void

setFormula(formula)

Sets the cell formula in A1-style notation. If the range contains multiple cells, each cell in the given range will be updated with the input data.

setFormula(formula: string): void;

Parameters

formula: string

Returns

void

Examples

/*
 * This script sets a cell's formula, 
 * then displays how Excel stores the cell's formula and value separately.
 */
function main(workbook: ExcelScript.Workbook) {
  let selectedSheet = workbook.getActiveWorksheet();

  // Set A1 to 2.
  let a1 = selectedSheet.getRange("A1");
  a1.setValue(2);

  // Set B1 to the formula =(2*A1), which should equal 4.
  let b1 = selectedSheet.getRange("B1")
  b1.setFormula("=(2*A1)");

  // Log the current results for `getFormula` and `getValue` at B1.
  console.log(`B1 - Formula: ${b1.getFormula()} | Value: ${b1.getValue()}`);
}

setFormulaLocal(formulaLocal)

Set the cell 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 the range contains multiple cells, each cell in the given range will be updated with the input data.

setFormulaLocal(formulaLocal: string): void;

Parameters

formulaLocal: string

Returns

void

setFormulaR1C1(formulaR1C1)

Sets the cell formula in R1C1-style notation. If the range contains multiple cells, each cell in the given range will be updated with the input data.

setFormulaR1C1(formulaR1C1: string): void;

Parameters

formulaR1C1: string

Returns

void

setFormulas(formulas)

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

setFormulas(formulas: string[][]): void;

Parameters

formulas: string[][]

Returns

void

Examples

/**
 * This script sets the values of a range, then adds SUM formulas to calculate
 * the totals for each row of that range. 
 */
function main(workbook: ExcelScript.Workbook)
{
  let currentSheet = workbook.getActiveWorksheet();

  // Set the values of a range.
  let values = [[1, 2, 4], [8, 16, 32], [64, 128, 256]];
  let valueRange = currentSheet.getRange("A1:C3");
  valueRange.setValues(values);

  // Set the formulas of a range.
  let formulas = [["=SUM(A1:C1)"], ["=SUM(A2:C2)"], ["=SUM(A3:C3)"]];
  let formulaRange = currentSheet.getRange("D1:D3");
  formulaRange.setFormulas(formulas);
}

setFormulasLocal(formulasLocal)

setFormulasLocal(formulasLocal: string[][]): void;

Parameters

formulasLocal: string[][]

Returns

void

setFormulasR1C1(formulasR1C1)

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

setFormulasR1C1(formulasR1C1: string[][]): void;

Parameters

formulasR1C1: string[][]

Returns

void

setHyperlink(hyperlink)

Represents the hyperlink for the current range.

setHyperlink(hyperlink: RangeHyperlink): void;

Parameters

hyperlink: ExcelScript.RangeHyperlink

Returns

void

Examples

/** 
 * This script inserts a hyperlink to the first cell of the last worksheet in the workbook.
 */
function main(workbook: ExcelScript.Workbook) {
  // Get the active cell.
  let cell = workbook.getActiveCell();

  // Get the last worksheet in the workbook.
  // Note that this might be the current sheet if there's only one worksheet.
  let lastSheet = workbook.getLastWorksheet();

  // Get sheet name. 
  let linkedSheetName = lastSheet.getName();
  console.log(`Setting hyperlink of ${cell.getAddress()} to the ${linkedSheetName} sheet's A1 cell`);

  // Set the text for the hyperlink.
  let value = `Click to go to: ${linkedSheetName}`;

  // Create the hyperlink using that cell's value.
  cell.setHyperlink({
    textToDisplay: value.toString(),
    screenTip: `Navigate to ${linkedSheetName}`,
    documentReference: `${linkedSheetName}!A1`
  });
}

setNumberFormat(numberFormat)

Sets cell Excel number format code for the given range. If the range contains multiple cells, each cell in the given range will be updated with the input data.

setNumberFormat(numberFormat: string): void;

Parameters

numberFormat: string

Returns

void

Examples

/**
 * This script sets the number format in column C to show the data as a percentage.
 */
function main(workbook: ExcelScript.Workbook) {
  const  selectedSheet = workbook.getActiveWorksheet();
  
  // Set number format for column C to a percentage that rounds to the nearest percentage point.
  selectedSheet.getRange("C:C").setNumberFormat("0%");
}

setNumberFormatLocal(numberFormatLocal)

Sets cell Excel 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. If the range contains multiple cells, each cell in the given range will be updated with the input data.

setNumberFormatLocal(numberFormatLocal: string): void;

Parameters

numberFormatLocal: string

Returns

void

Examples

/**
 * This script sets the number format in column D to show the data as a percentage with a decimal.
 */
function main(workbook: ExcelScript.Workbook) {
  const  selectedSheet = workbook.getActiveWorksheet();
  
  // Set number format for column D to a percentage that rounds to the nearest tenth of a percentage.
  selectedSheet.getRange("D:D").setNumberFormatLocal("0.0%");
}

setNumberFormats(numberFormats)

Represents Excel's number format code for the given range.

setNumberFormats(numberFormats: string[][]): void;

Parameters

numberFormats: string[][]

Returns

void

setNumberFormatsLocal(numberFormatsLocal)

setNumberFormatsLocal(numberFormatsLocal: string[][]): void;

Parameters

numberFormatsLocal: string[][]

Returns

void

setPredefinedCellStyle(predefinedCellStyle)

Represents the style of the current range.

setPredefinedCellStyle(predefinedCellStyle: string): void;

Parameters

predefinedCellStyle: string

Returns

void

setRowHidden(rowHidden)

setRowHidden(rowHidden: boolean): void;

Parameters

rowHidden: boolean

Returns

void

setValue(value)

Sets the raw value of the specified range. The data being set could be of type string, number, or a boolean. null value will be ignored (not set or overwritten in Excel). If the range contains multiple cells, each cell in the given range will be updated with the input data.

setValue(value: any): void;

Parameters

value: any

Returns

void

setValues(values)

Sets the raw values of the specified range. The data provided could be a string, number, or boolean. If the provided value starts with a plus ("+"), minus ("-"), or equal sign ("="), Excel interprets this value as a formula.

setValues(values: (string | number | boolean)[][]): void;

Parameters

values: (string | number | boolean)[][]

Returns

void

Examples

/**
 * This sample inserts some pre-loaded data into a range.
 * It also shows how to get a range that fits the data.
 */
 function main(workbook: ExcelScript.Workbook) {
   // Get the active cell.
   let currentCell = workbook.getActiveCell();
   
   // Calculate the range needed to fit the given data.
   let targetRange = currentCell.getResizedRange(DATA.length - 1, DATA[0].length - 1);

   // Set range values to the data.
   targetRange.setValues(DATA);

   // Autofit the columns so the worksheet is readable. 
   targetRange.getFormat().autofitColumns();
 }

 /* 
  * This sample's data is in a static 2-dimensional array.
  * You could also get the input from other ranges or sources.
  * Note that each row must have the same number of columns to be valid. 
  */
 const DATA = [
   ['Date', 'Salesperson', 'Product', 'Amount']
   , ['3/2/2020', 'Anne', 'Pizza', '$1400']
   , ['3/2/2020', 'Mariya', 'Pizza', '$1700']
   , ['3/7/2020', 'Mark', 'Sandwiches', '$1010']
   , ['3/24/2020', 'Anne', 'Pizza', '$750']
   , ['3/28/2020', 'Mark', 'Salads', '$510']
   , ['4/17/2020', 'Laura', 'Salads', '$900']
   , ['4/17/2020', 'Mariya', 'Salads', '$1600']
   , ['4/28/2020', 'Laura', 'Sandwiches', '$680']
 ];

showCard()

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

showCard(): void;

Returns

void

showGroupDetails(groupOption)

Shows the details of the row or column group.

showGroupDetails(groupOption: GroupOption): void;

Parameters

groupOption: ExcelScript.GroupOption

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

Returns

void

ungroup(groupOption)

Ungroups columns and rows for an outline.

ungroup(groupOption: GroupOption): void;

Parameters

groupOption: ExcelScript.GroupOption

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

Returns

void

unmerge()

Unmerge the range cells into separate cells.

unmerge(): void;

Returns

void

Examples

/**
 * This script unmerges every used cell in the current worksheet.
 */
function main(workbook: ExcelScript.Workbook) {
  // Get the active worksheet.
  const selectedSheet = workbook.getActiveWorksheet();

  // Separate all regions into single cells in the currently used range.
  const range = selectedSheet.getUsedRange();
  range.unmerge();
}

Share via

ExcelScript.Range interface

Remarks

Examples

Methods

Method Details

addConditionalFormat(type)

Parameters

Returns

Examples

autoFill(destinationRange, autoFillType)

Parameters

Returns

Examples

calculate()

Returns

Examples

clear(applyTo)

Parameters

Returns

Examples

clearAllConditionalFormats()

Returns

convertDataTypeToText()

Returns

copyFrom(sourceRange, copyType, skipBlanks, transpose)

Parameters

Returns

Examples

delete(shift)

Parameters

Returns

Examples

find(text, criteria)

Parameters

Returns

Examples

flashFill()

Returns

Examples

getAbsoluteResizedRange(numRows, numColumns)

Parameters

Returns

getAddress()

Returns

Examples

getAddressLocal()

Returns

getBoundingRect(anotherRange)

Parameters

Returns

Examples

getCell(row, column)

Parameters

Returns

getCellCount()

Returns

getColumn(column)

Parameters

Returns

getColumnCount()

Returns

Examples

getColumnHidden()

Returns

getColumnIndex()

Returns

getColumnsAfter(count)

Parameters

Returns

getColumnsBefore(count)

Parameters

Returns

getConditionalFormat(id)

Parameters

Returns

getConditionalFormats()

Returns

getDataValidation()

Returns