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
calculate(markAllDirty: bool) void Calculates all cells on a worksheet. 1.6
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
getNext(visibleOnly: bool) Worksheet Gets the worksheet that follows this one. If there are no worksheets following this one, this method will throw an error. 1.5
getNextOrNullObject(visibleOnly: bool) Worksheet Gets the worksheet that follows this one. If there are no worksheets following this one, this method will return a null object. 1.5
getPrevious(visibleOnly: bool) Worksheet Gets the worksheet that precedes this one. If there are no previous worksheets, this method will throw an error. 1.5
getPreviousOrNullObject(visibleOnly: bool) Worksheet Gets the worksheet that precedes this one. If there are no previous worksheets, this method will return a null objet. 1.5
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));
        }
});

calculate(markAllDirty: bool)

Calculates all cells on a worksheet.

Syntax

worksheetObject.calculate(markAllDirty);

Parameters

Parameter Type Description
markAllDirty bool Boolean to mark as dirty.

Returns

void

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

getNext(visibleOnly: bool)

Gets the worksheet that follows this one. If there are no worksheets following this one, this method will throw an error.

Syntax

worksheetObject.getNext(visibleOnly);

Parameters

Parameter Type Description
visibleOnly bool Optional. If true, considers only visible worksheets, skipping over any hidden ones.

Returns

Worksheet

getNextOrNullObject(visibleOnly: bool)

Gets the worksheet that follows this one. If there are no worksheets following this one, this method will return a null object.

Syntax

worksheetObject.getNextOrNullObject(visibleOnly);

Parameters

Parameter Type Description
visibleOnly bool Optional. If true, considers only visible worksheets, skipping over any hidden ones.

Returns

Worksheet

getPrevious(visibleOnly: bool)

Gets the worksheet that precedes this one. If there are no previous worksheets, this method will throw an error.

Syntax

worksheetObject.getPrevious(visibleOnly);

Parameters

Parameter Type Description
visibleOnly bool Optional. If true, considers only visible worksheets, skipping over any hidden ones.

Returns

Worksheet

getPreviousOrNullObject(visibleOnly: bool)

Gets the worksheet that precedes this one. If there are no previous worksheets, this method will return a null objet.

Syntax

worksheetObject.getPreviousOrNullObject(visibleOnly);

Parameters

Parameter Type Description
visibleOnly bool Optional. If true, considers only visible worksheets, skipping over any hidden ones.

Returns

Worksheet

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 bool Optional. 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));
        }
});