Add condition to short Office Script

[ChristineJ]

I'm using this code in Office Scripts so that cell E15 is not locked when the sheet is protected.

selectedSheet.getRange("E15").getFormat().getProtection().setLocked(false);

Cell B2 contains a text string that includes numbers, such as CJJS621597.

I'd like to update the Script so that cell E15 is set to unlocked only if the 7th character in the string in B2 is a 1 (possibly incorporating something like MID($B$2,7,1)="1" into the script?)

Can that be done? Thanks!

 

[ExcelToDAX]

it might be easier to put the =MID formula in a cell with an IF statement so that your Office Script code can say something like this (assuming you put this formula on the same sheet in cell E1000:

function main(workbook: ExcelScript.Workbook) {
    let selectedSheet = workbook.getActiveWorksheet();

    let cellVal = selectedSheet.getRange("E1000").getValue();

if (cellVal === "Yes") {
    return selectedSheet.getRange("E15").getFormat().getProtection().setLocked(false);
}

}

and cell E1000 should say something like:

=IF(MID($B$2,7,1)="1","Yes","No")

 

[ChristineJ]

Yes, I can use approach you're suggesting. Thanks!

Can I ask you one more thing? (I'm very new to Office Scripts.) I am trying to add a line to turn the protection on by adding the following code before the very last closing curly bracket in your code above:

selectedSheet.getProtection().protect();

Your script works, but the protection is not added to the sheet. (No password is necessary.) Am I putting it in the wrong place?

Thanks for the help!! C

 

[ExcelToDAX]

In that case you would get rid of the return statement and do the following:

function main(workbook: ExcelScript.Workbook) {
    let selectedSheet = workbook.getActiveWorksheet();

    let cellVal = selectedSheet.getRange("E1000").getValue();

    if (cellVal === "Yes") {
        selectedSheet.getRange("E15").getFormat().getProtection().setLocked(false);
        selectedSheet.getProtection().unprotect();
        selectedSheet.getProtection().protect();
    }

}

 

[ChristineJ]

EXCELLENT! Exactly what I need. I really appreciate your sticking with me on this. C