Scripting and Macros

This script changes the structure of the JSON object for each worksheet to be unlocked

type wsPassword = { 
  wsName: string, 
  wsPassword: string 
} 

const options: ExcelScript.WorksheetProtectionOptions = { 
  allowAutoFilter: true, 
  allowSort: true, 
  allowFormatCells: false, 
  allowFormatColumns: false, 
  allowFormatRows: false, 
  allowInsertRows: false, 
  allowDeleteRows: false, 
  allowInsertColumns: false, 
  allowDeleteColumns: false 
}; 

function main( 
  workbook: ExcelScript.Workbook, 
  workbookPassword: string = "", 
  worksheetsToUnlock: wsPassword[] | undefined = undefined //Inform only the worksheets to be unlocked. Default: All. 
) { 

  const wbProtection = workbook.getProtection(); 
  const unlockedSheets: ExcelScript.Worksheet[] = []; 

  // Unprotect workbook structure if password provided 
  if (workbookPassword) { 
    wbProtection.unprotect(workbookPassword); 
  } 

  try { 
    // Unlock worksheets 
    if (worksheetsToUnlock && worksheetsToUnlock.length > 0) { 
      // Unlock only specified worksheets with their passwords 
      for (const item of worksheetsToUnlock) { 
        const ws = workbook.getWorksheet(item.wsName); 
        if (!ws) throw new Error(`Worksheet not found: ${item.wsName}`); 

        ws.getProtection().unprotect(item.wsPassword); 
        unlockedSheets.push(ws); 
      } 
    } else { 
      // Unlock all worksheets using workbookPassword 
      if (!workbookPassword) { 
        throw new Error("workbookPassword is required to unlock all worksheets."); 
      } 

      for (const ws of workbook.getWorksheets()) { 
        ws.getProtection().unprotect(workbookPassword); 
        unlockedSheets.push(ws); 
      } 
    } 

  } finally { 

    // Relock worksheets we unlocked 
    for (const ws of unlockedSheets) { 
      const pwd: string = worksheetsToUnlock?.find(x => x.wsName === ws.getName())?.wsPassword 
        ?? workbookPassword; 

      ws.getProtection().protect(options, pwd); 
    } 

    if (workbookPassword) { 
      workbook.getProtection().protect(workbookPassword); 
    } 

  } 
}