ExcelScript.RangeView interface

Package:

ExcelScript

RangeView represents a set of visible cells of the parent range.

Remarks

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

Methods

getCellAddresses()	Represents the cell addresses of the RangeView.
getColumnCount()	The number of visible columns.
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.
getIndex()	Returns a value that represents the index of the RangeView.
getNumberFormat()	Represents Excel's number format code for the given cell.
getRange()	Gets the parent range associated with the current RangeView.
getRowCount()	The number of visible rows.
getRows()	Represents a collection of range views associated with the range.
getText()	Text values of the specified range. The text value will not depend on the cell width. The # sign substitution that happens in Excel UI will not affect the text value returned by the API.
getValues()	Represents the raw values of the specified range view. The data returned could be of type string, number, or a boolean. Cells that contain an error will return the error string.
getValueTypes()	Represents the type of data of each cell.
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.
setNumberFormat(numberFormat)	Represents Excel's number format code for the given cell.
setValues(values)	Represents the raw values of the specified range view. The data returned could be of type string, number, or a boolean. Cells that contain an error will return the error string.

Method Details

getCellAddresses()

Represents the cell addresses of the RangeView.

getCellAddresses(): string[][];

Returns

string[][]

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

getColumnCount()

The number of visible columns.

getColumnCount(): number;

Returns

number

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

getIndex()

Returns a value that represents the index of the RangeView.

getIndex(): number;

Returns

number

getNumberFormat()

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

getNumberFormat(): string[][];

Returns

string[][]

getRange()

Gets the parent range associated with the current RangeView.

getRange(): Range;

Returns

ExcelScript.Range

getRowCount()

The number of visible rows.

getRowCount(): number;

Returns

number

getRows()

Represents a collection of range views associated with the range.

getRows(): RangeView[];

Returns

ExcelScript.RangeView[]

getText()

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

getText(): string[][];

Returns

string[][]

getValues()

Represents the raw values of the specified range view. The data returned could be of type string, number, or a boolean. Cells that contain an error will return the error string.

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

Returns

(string | number | boolean)[][]

getValueTypes()

Represents the type of data of each cell.

getValueTypes(): RangeValueType[][];

Returns

ExcelScript.RangeValueType[][]

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

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.

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

setNumberFormat(numberFormat)

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

setNumberFormat(numberFormat: string[][]): void;

Parameters

numberFormat

string[][]

Returns

void

setValues(values)

Represents the raw values of the specified range view. The data returned could be of type string, number, or a boolean. Cells that contain an error will return the error string.

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

Parameters

values

(string | number | boolean)[][]

Returns

void