Excel Office Add-In API Worksheet Protection Password

Question

I have a question relating to Excel's worksheet protection...

The context is that I need to have different worksheets available for different user groups to edit but all groups must at least see all sheets e.g. usergroup1 can edit sheets two and three and parts of sheet one, usergroup2 can edit only sheet one.

I am able to set the FormatProtection (range.format.protection.locked = false;) accordingly and WorksheetProtection (worksheet.protection.protect();) to enable this but I don't appear to have the ability to set a password through the API against the Worksheet Protection? This means for example, that either group can simply click the Unprotect Sheet option in the review ribbon and edit the sheets that I don't want them to.

I've tried going through the below documentation but to no avail unfortunately.

• http://dev.office.com/reference/add-ins/excel/worksheetprotection
• https://github.com/OfficeDev/office-js-docs/blob/master/reference/excel/worksheetprotection.md

As an example, here is a function that I'd like to complete:

function CopyWorksheet() {

        var newAddress;

        Excel.run(function (ctx) {

            var worksheet = ctx.workbook.worksheets.getActiveWorksheet();
            var range = worksheet.getUsedRange();
            range.load();

            // insert new worksheet
            var newWorksheetName = "Copied_Sheet";              
            var newWorksheet = ctx.workbook.worksheets.add(newWorksheetName);

            return ctx.sync().then(function () {

                // copy the old values to the new worksheet
                newAddress = range.address.substring(range.address.indexOf("!") + 1);
                newWorksheet.getRange(newAddress).values = range.values;
                newWorksheet.getRange(newAddress).formulas = range.formulas;
                newWorksheet.getRange(newAddress).text = range.text;

                // protect both worksheets
                worksheet.protection.protect();
                newWorksheet.protection.protect();

                // requirement here to set a password so that no one can 
                // edit the worksheets by selecting 'Unprotect Sheet' in excel
                // ...
            })
            .then(ctx.sync)})
            .catch(function(error) {
                    console.log("Error: " + error);                     
            });
        }

Currently, I'm using Excel 2016 (desktop version). Is this possible to implement or have I missed some functionality that exists which can achieve the same result?

Thanks for your help.

Answer 1

Password-protection is not available in our APIs. You can protect the sheet to avoid casual edits, but you can't password-protect. The reason is that password-protection is not available on all endpoints (IIRC, there was an issue with Excel Online).

If you want to file a suggestion bug on UserVoice, you can see if we'd consider doing password-protection as a Desktop-only API. We have so far avoided doing those in Excel, but I do know that Word has done a few "WordApiDesktop" APIs. So depending on how much it's blocking your (and others') scenario, that might be an option. In which case you'd be able to password-protect and unprotect on desktop, but wouldn't be able to take those actions online.