Worksheet Object (JavaScript API for Excel)

An Excel worksheet is a grid of cells. It can contain data, tables, charts, etc.

Properties

Property Type Description Req. Set
id string Returns a value that uniquely identifies the worksheet in a given workbook. The value of the identifier remains the same even when the worksheet is renamed or moved. Read-only. 1.1
name string The display name of the worksheet. 1.1
position int The zero-based position of the worksheet within the workbook. 1.1
visibility string The Visibility of the worksheet. Possible values are: Visible, Hidden, VeryHidden. 1.1

See property access examples.

Relationships

Relationship Type Description Req. Set
charts ChartCollection Returns collection of charts that are part of the worksheet. Read-only. 1.1
names NamedItemCollection Collection of names scoped to the current worksheet. Read-only. 1.4
pivotTables PivotTableCollection Collection of PivotTables that are part of the worksheet. Read-only. 1.3
protection WorksheetProtection Returns sheet protection object for a worksheet. Read-only. 1.2
tables TableCollection Collection of tables that are part of the worksheet. Read-only. 1.1

Methods

Method Return Type Description Req. Set
activate() void Activate the worksheet in the Excel UI. 1.1
delete() void Deletes the worksheet from the workbook. 1.1
getCell(row: number, column: number) Range 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's stays within the worksheet grid. 1.1
getRange(address: string) Range Gets the range object specified by the address or name. 1.1
getUsedRange(valuesOnly: bool) Range The used range is the smallest range that encompasses any cells that have a value or formatting assigned to them. If the entire worksheet is blank, this function will return the top left cell (i.e.,: it will not throw an error). 1.1
getUsedRangeOrNullObject(valuesOnly: bool) Range The used range is the smallest range that encompasses any cells that have a value or formatting assigned to them. If the entire worksheet is blank, this function will return a null object. 1.4

Method Details

activate()

Activate the worksheet in the Excel UI.

Syntax

worksheetObject.activate();

Parameters

None

Returns

void

Examples

Excel.run(function (ctx) { 
    var wSheetName = 'Sheet1';
    var worksheet = ctx.workbook.worksheets.getItem(wSheetName);
    worksheet.activate();
    return ctx.sync(); 
}).catch(function(error) {
        console.log("Error: " + error);
        if (error instanceof OfficeExtension.Error) {
            console.log("Debug info: " + JSON.stringify(error.debugInfo));
        }
});

delete()

Deletes the worksheet from the workbook.

Syntax

worksheetObject.delete();

Parameters

None

Returns

void

Examples

Excel.run(function (ctx) { 
    var wSheetName = 'Sheet1';
    var worksheet = ctx.workbook.worksheets.getItem(wSheetName);
    worksheet.delete();
    return ctx.sync(); 
}).catch(function(error) {
        console.log("Error: " + error);
        if (error instanceof OfficeExtension.Error) {
            console.log("Debug info: " + JSON.stringify(error.debugInfo));
        }
});

getCell(row: number, column: number)

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's stays within the worksheet grid.

Syntax

worksheetObject.getCell(row, column);

Parameters

Parameter Type Description
row number The row number of the cell to be retrieved. Zero-indexed.
column number the column number of the cell to be retrieved. Zero-indexed.

Returns

Range

Examples

Excel.run(function (ctx) { 
    var sheetName = "Sheet1";
    var rangeAddress = "A1:F8";
    var worksheet = ctx.workbook.worksheets.getItem(sheetName);
    var cell = worksheet.getCell(0,0);
    cell.load('address');
    return ctx.sync().then(function() {
        console.log(cell.address);
}).catch(function(error) {
        console.log("Error: " + error);
        if (error instanceof OfficeExtension.Error) {
            console.log("Debug info: " + JSON.stringify(error.debugInfo));
        }
});

getRange(address: string)

Gets the range object specified by the address or name.

Syntax

worksheetObject.getRange(address);

Parameters

Parameter Type Description
address string Optional. The address or the name of the range. If not specified, the entire worksheet range is returned.

Returns

Range

Examples

Below example uses range address to get the range object.

Excel.run(function (ctx) { 
    var sheetName = "Sheet1";
    var rangeAddress = "A1:F8";
    var worksheet = ctx.workbook.worksheets.getItem(sheetName);
    var range = worksheet.getRange(rangeAddress);
    range.load('cellCount');
    return ctx.sync().then(function() {
        console.log(range.cellCount);
    });
}).catch(function(error) {
        console.log("Error: " + error);
        if (error instanceof OfficeExtension.Error) {
            console.log("Debug info: " + JSON.stringify(error.debugInfo));
        }
});

Below example uses a named-range to get the range object.


Excel.run(function (ctx) { 
    var sheetName = "Sheet1";
    var rangeName = 'MyRange';
    var range = ctx.workbook.worksheets.getItem(sheetName).getRange(rangeName);
    range.load('address');
    return ctx.sync().then(function() {
        console.log(range.address);
    });
}).catch(function(error) {
        console.log("Error: " + error);
        if (error instanceof OfficeExtension.Error) {
            console.log("Debug info: " + JSON.stringify(error.debugInfo));
        }
});

getUsedRange(valuesOnly: bool)

The used range is the smallest range that encompasses any cells that have a value or formatting assigned to them. If the entire worksheet is blank, this function will return the top left cell (i.e.,: it will not throw an error).

Syntax

worksheetObject.getUsedRange(valuesOnly);

Parameters

Parameter Type Description
valuesOnly [ApiSet(Version Considers only cells with values as used cells (ignoring formatting).

Returns

Range

Examples

Excel.run(function (ctx) { 
    var wSheetName = 'Sheet1';
    var worksheet = ctx.workbook.worksheets.getItem(wSheetName);
    var usedRange = worksheet.getUsedRange();
    usedRange.load('address');
    return ctx.sync().then(function() {
            console.log(usedRange.address);
    });
}).catch(function(error) {
        console.log("Error: " + error);
        if (error instanceof OfficeExtension.Error) {
            console.log("Debug info: " + JSON.stringify(error.debugInfo));
        }
});

getUsedRangeOrNullObject(valuesOnly: bool)

The used range is the smallest range that encompasses any cells that have a value or formatting assigned to them. If the entire worksheet is blank, this function will return a null object.

Syntax

worksheetObject.getUsedRangeOrNullObject(valuesOnly);

Parameters

Parameter Type Description
valuesOnly bool Optional. Considers only cells with values as used cells.

Returns

Range

Property access examples

Get worksheet properties based on sheet name.

Excel.run(function (ctx) { 
    var wSheetName = 'Sheet1';
    var worksheet = ctx.workbook.worksheets.getItem(wSheetName);
    worksheet.load('position')
    return ctx.sync().then(function() {
            console.log(worksheet.position);
    });
}).catch(function(error) {
        console.log("Error: " + error);
        if (error instanceof OfficeExtension.Error) {
            console.log("Debug info: " + JSON.stringify(error.debugInfo));
        }
});

Set worksheet position.

Excel.run(function (ctx) { 
    var wSheetName = 'Sheet1';
    var worksheet = ctx.workbook.worksheets.getItem(wSheetName);
    worksheet.position = 2;
    return ctx.sync(); 
}).catch(function(error) {
        console.log("Error: " + error);
        if (error instanceof OfficeExtension.Error) {
            console.log("Debug info: " + JSON.stringify(error.debugInfo));
        }
});
; if (error instanceof OfficeExtension.Error) { console.log("Debug info: " + JSON.stringify(error.debugInfo)); } });
Page Content End -->