ExcelScript.NamedItem interface

Package:

ExcelScript

Represents a defined name for a range of cells or value. Names can be primitive named objects (as seen in the type below), range object, or a reference to a range. This object can be used to obtain range object associated with names.

Remarks

Examples

/**
 * This script creates a named formula and uses it in another part of the workbook.
 */
function main(workbook: ExcelScript.Workbook) {
  // Create a named item for a formula.
  // This formula is the sum of the cells F2:F21 on Sheet1.
  const namedItem: ExcelScript.NamedItem = workbook.addNamedItem(
    "GrandTotal", 
    "=SUM(Sheet1!$F$2:$F$21)", 
    "The sum of table sums."
  );

  // Add this named formula to a new sheet in the workbook.
  const otherSheet = workbook.addWorksheet();
  otherSheet.getRange("A1").setFormula(namedItem.getFormula());

  // Switch to the new worksheet.
  otherSheet.activate();
}

Methods

delete()	Deletes the given name.
getArrayValues()	Returns an object containing values and types of the named item.
getComment()	Specifies the comment associated with this name.
getFormula()	The formula of the named item. Formulas always start with an equal sign ("=").
getName()	The name of the object.
getRange()	Returns the range object that is associated with the name. If the named item's type is not a range, then this method returns undefined.
getScope()	Specifies if the name is scoped to the workbook or to a specific worksheet. Possible values are: Worksheet, Workbook.
getType()	Specifies the type of the value returned by the name's formula. See ExcelScript.NamedItemType for details.
getValue()	Represents the value computed by the name's formula. For a named range, it will return the range address. This API returns the #VALUE! error in the Excel UI if it refers to a user-defined function.
getVisible()	Specifies if the object is visible.
getWorksheet()	Returns the worksheet to which the named item is scoped. If the item is scoped to the workbook instead, then this method returns undefined.
setComment(comment)	Specifies the comment associated with this name.
setFormula(formula)	The formula of the named item. Formulas always start with an equal sign ("=").
setVisible(visible)	Specifies if the object is visible.

Method Details

delete()

Deletes the given name.

delete(): void;

Returns

void

getArrayValues()

Returns an object containing values and types of the named item.

getArrayValues(): NamedItemArrayValues;

Returns

ExcelScript.NamedItemArrayValues

getComment()

Specifies the comment associated with this name.

getComment(): string;

Returns

string

getFormula()

The formula of the named item. Formulas always start with an equal sign ("=").

getFormula(): string;

Returns

string

getName()

The name of the object.

getName(): string;

Returns

string

getRange()

Returns the range object that is associated with the name. If the named item's type is not a range, then this method returns undefined.

getRange(): Range;

Returns

ExcelScript.Range

getScope()

Specifies if the name is scoped to the workbook or to a specific worksheet. Possible values are: Worksheet, Workbook.

getScope(): NamedItemScope;

Returns

ExcelScript.NamedItemScope

getType()

Specifies the type of the value returned by the name's formula. See ExcelScript.NamedItemType for details.

getType(): NamedItemType;

Returns

ExcelScript.NamedItemType

Examples

/**
 * This script looks for every named range with "Review" in the name 
 * and marks the range with a yellow fill.
 */
function main(workbook: ExcelScript.Workbook) {
  // Look at every named item in the workbook.
  workbook.getNames().forEach((namedItem) => {
    // Find names containing "Review".
    if (namedItem.getName().includes("Review")) {
      // Only change the fill color if the named item is a range (not a formula).
      let itemType: ExcelScript.NamedItemType = namedItem.getType();
      if (itemType === ExcelScript.NamedItemType.range) {
        // Set the range's fill color to yellow.
        namedItem.getRange().getFormat().getFill().setColor("yellow");
      }
    }
  });
}

getValue()

Represents the value computed by the name's formula. For a named range, it will return the range address. This API returns the #VALUE! error in the Excel UI if it refers to a user-defined function.

getValue(): string | number;

Returns

string | number

getVisible()

Specifies if the object is visible.

getVisible(): boolean;

Returns

boolean

getWorksheet()

Returns the worksheet to which the named item is scoped. If the item is scoped to the workbook instead, then this method returns undefined.

getWorksheet(): Worksheet | undefined;

Returns

ExcelScript.Worksheet | undefined

setComment(comment)

Specifies the comment associated with this name.

setComment(comment: string): void;

Parameters

comment

string

Returns

void

setFormula(formula)

The formula of the named item. Formulas always start with an equal sign ("=").

setFormula(formula: string): void;

Parameters

formula

string

Returns

void

setVisible(visible)

Specifies if the object is visible.

setVisible(visible: boolean): void;

Parameters

visible

boolean

Returns

void