Worksheet Object (JavaScript API for Excel)

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

Properties

See property access examples.

Relationships

Methods

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

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

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

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

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

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

Returns

Worksheet

getRange(address: string)

Gets the range object specified by the address or name.

Syntax

worksheetObject.getRange(address);

Parameters

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

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

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