How to lock a range in excel via office add-in

Question

I'm developing an excel add-in using js. I want to lock some cells or rows or cols, while other cells can still be edited. I haven't found a document about it.

I tried the following code but the range is not locked.

await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getActiveWorksheet();
    const range = sheet.getRange("B2:E5");
    range.format.protection.locked = true;

    await context.sync();
});

I found the solution:

await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getActiveWorksheet();
    var wholerange = sheet.getRange();
    wholerange.format.protection.locked = false;

    var range = sheet.getRange("A2:D5");
    range.format.protection.locked = true;

    sheet.protection.protect({ allowInsertRows: true });
    await context.sync();
});

Answer 1

I found a solution, refer to How to lock only dedicated cells via Excel JavaScript API

await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getActiveWorksheet();
    var wholerange = sheet.getRange();
    wholerange.format.protection.locked = false;

    var range = sheet.getRange("A2:D5");
    range.format.protection.locked = true;

    sheet.protection.protect({ allowInsertRows: true });
    await context.sync();
});

Answer 2

You can do this by using the Range.format.protection property. See for example Range.format and FormatProjection.

UPDATE: Locking ranges works in conjunction with Worksheet protection. The locking/unlocking of ranges has no effect until the worksheet is protected. See WorksheetProtection.

Also, the logic of range locking is the same as when an end-user locks ranges in the Excel UI: All cells will be locked by default when the worksheet is protected. So before you protect the worksheet, you UNlock the cells that should not be locked. Please work with locking and protection as an end-user until you are familiar with how it works and what the options are. For example, see this page Lock cells Excel.