Excel script to disable password / unprotect, then re-apply at end of script

Copper Contributor

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 the script that disables the password, unprotects the worksheet, then later in the same script, re-applies the protection and re-applies the password.

3 Replies

@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

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.

Yes look at lines 6,7,17

I edited above comment to indicate that it unlocks the sheet, THEN toggles, then Relocks the sheet