Protect Worksheet in Office Scripts with Options

Question

How can I protect a worksheet but allow the user to format the columns in Office Scripts? I have tried a few things but haven't had any success.

function main(workbook: ExcelScript.Workbook) {
    let sheet = workbook.getWorksheet("By Item");
    sheet.getProtection().protect(ExcelScript.WorksheetProtectionOptions.allowFormatColumns);
}

Please see the attached link

https://learn.microsoft.com/en-us/javascript/api/office-scripts/excelscript/excelscript.worksheetprotection?view=office-scripts#protect-options--password-

Answer 1

The protect() method takes an object as argument for the 1st argument. See below. I noticed that cell background/fill doesn't work even with this setting. All other formatting works such as font color, border, etc. That may be a bug that we'll follow-up on.

function main(workbook: ExcelScript.Workbook) {
    let sheet = workbook.getWorksheet("By Item");
    sheet.getProtection().unprotect();
    sheet.getProtection().protect({
        allowFormatCells: true
    });
}

Answer 2

Had the same problem. Setting ranges to unlocked solved the problem. Like this sheet.getRange("A1:A20").getFormat().getProtection().setLocked(false);

function main(workbook: ExcelScript.Workbook) {
let sheet = workbook.getWorkSheet("name");

sheet.getProtection().unprotect();

sheet.getProtection().protect({
    allowAutoFilter: true,
    allowDeleteRows: false,
    allowDeleteColumns: false,
    allowEditObjects: true,
    allowEditScenarios: true,
    allowFormatCells: true,
    allowFormatColumns: true,
    allowFormatRows: true,
    allowInsertColumns: false,
    allowInsertHyperlinks: true,
    allowInsertRows: false,
    allowPivotTables: false,
    allowSort: true,
    selectionMode: ExcelScript.ProtectionSelectionMode.normal
  });

sheet.getRange("A1:A20").getFormat().getProtection().setLocked(false);
}