CODEWITHSUNDEEP
office-js
Tobias J.
Tobias J.

Reputation: 1053

How to lock only dedicated cells via Excel JavaScript API

As documented here I tried to lock only some cells (all cells of a column in my case) but all cells of the whole document are locked and not only the cells of the range. I already tried to lock only some cells like "D1:D5" but again all cells are locked and not only these 5 cells.

This is my code:

function lockColumnHandler() {
    lockColumn("Sheet1", "D:D");
}

function lockColumn(sheetName, columnRange) {
    Excel.run(function (ctx) {
        var sheet = ctx.workbook.worksheets.getItem(sheetName);
        var range = sheet.getRange(columnRange);

        if (range) {
            range.format.protection.locked = true;
        }

        sheet.protection.protect({
            allowAutoFilter: true,
            allowDeleteColumns: true,
            allowDeleteRows: true,
            allowFormatCells: true,
            allowFormatColumns: true,
            allowFormatRows: true,
            allowInsertColumns: true,
            allowInsertHyperlinks: true,
            allowInsertRows: true,
            allowPivotTables: true,
            allowSort: true
        });

        return ctx.sync();
    })
    .catch(errorHandler);
}

Upvotes: 3

Views: 5404

Answers (2)

Kim Brandl
Kim Brandl

Reputation: 13480

If I create a new sheet in Excel, populate it with some test data, and run the following code, it looks like the initial state of cells in the range columnRange is locked:

function lockColumn(sheetName, columnRange) {

    Excel.run(function (ctx) {
        var sheet = ctx.workbook.worksheets.getItem(sheetName);
        var range = sheet.getRange(columnRange);

        if (range) {
            range.load(['address', 'format/protection/locked']);
            ctx.sync()
                .then(function () {
                    console.log(`The range address is: "${range.address}".`);
                    console.log('Initial value of range.format.protection.locked: ' + range.format.protection.locked);
                });
        };
        return ctx.sync();
    })
    .catch(errorHandler);
}

The console output from running this code in a new sheet is:

console output

Based on these findings, I'd suspect that perhaps the outcome you've described is due to the fact that the initial state of all cells in a new sheet is locked (i.e., you think your code is just locking certain cells in the sheet, but in reality, your code is having no effect since all cells in the sheet are already locked by default). Perhaps try first unlocking all cells in the sheet, then after that, explicitly locking only the cells that you want to lock?

Upvotes: 1

Philip Rueker
Philip Rueker

Reputation: 948

The default state in Excel is that all cells are marked as locked (you can right-click on any cell in Excel, go to Format Cells->Protection and you will find "Locked" is ticked by default).

So in order to lock only these cells, you would have to unlock all cells of the sheet, except the ones you want to lock.

Upvotes: 4

Related Questions