Friday 19 January 2024

Excel Online Javascript Api Add Allow Edit Range

I'm having trouble adding an allowed edit range to a worksheet protection object using Excel Javascript API. I keep getting an error Cannot read properties of undefined (reading 'add'). I believe I've added the property with statement

worksheet.load("protection/protected", "protection/allowEditRanges");

but maybe this is wrong?

I've referred to the API reference here https://learn.microsoft.com/en-us/javascript/api/excel/excel.alloweditrangecollection?view=excel-js-preview

async function protect(worksheetName) {
await Excel.run(async (context) => {
    worksheet = context.workbook.worksheets.getItem(worksheetName);
    worksheet.load("protection/protected", "protection/allowEditRanges");
    await context.sync();
    //can't add without pausing protection 
    worksheet.protection.unprotect("");
           
    var wholerange = worksheet.getRange();
    wholerange.format.protection.locked = true;                            

    worksheet.protection.allowEditRange.add({title: "Range1", rangeAddress: "A4:G500"});
    worksheet.protection.allowEditRange.add({title: "Range2", rangeAddress: "I4::L500"});        

    worksheet.protection.protect({
        allowFormatCells: true,
        allowAutoFilter: true,
        allowDeleteRows: true,
        allowEditObjects: true,
        //allowFormatColumns: true,
        allowFormatRows: true,
        allowInsertHyperlinks: true,
        allowInsertRows: true,
        allowPivotTables: true,
        allowSort: true
    }, "");

    await context.sync();

});

}



from Excel Online Javascript Api Add Allow Edit Range

No comments:

Post a Comment