Forum Discussion
Bkkendig
May 30, 2023Copper Contributor
Excel script to disable password / unprotect, then re-apply at end of script
I am using excel scripts. My challenge is to remove protection from a password protected worksheet, apply changes, then re-apply password protection. I'd be grateful for help with the portions of t...
mtarler
May 30, 2023Silver Contributor
Bkkendig Here is a sample I did:
/**
* This script filters table.
*/
function main(workbook: ExcelScript.Workbook) {
//Unlock worksheet
let wksht = workbook.getActiveWorksheet()
wksht.getProtection().unprotect()
//Get table
let tbl = workbook.getTable("PRs")
console.log(tbl.getRowCount())
// Sort the table based on the first column.
if ( tbl.getAutoFilter().getIsDataFiltered() ) {
tbl.clearFilters()
} else {
tbl.getColumn(6).getFilter().applyValuesFilter([""]);
}
wksht.getProtection().protect()
}
this script Unlocks the sheet, then toggles a filter on column 6 on/off, then ReLocks the sheet
Bkkendig
May 31, 2023Copper Contributor
Thanks. However, I am also looking for the code that hardcodes the password to disable password protection, then turns it back on at the end.
- mtarlerMay 31, 2023Silver Contributor
Yes look at lines 6,7,17
I edited above comment to indicate that it unlocks the sheet, THEN toggles, then Relocks the sheet