Forum Discussion

Bkkendig's avatar
Bkkendig
Copper Contributor
Jun 17, 2023

Excel script to pause worksheet protection and allow data sort based on a specific column

I have a spreadsheet that is password protected.  It contains a matrix of data (not formatted as a table), and the first row of the matrix contains data heading labels.  The filter is turned on and a...
Excel for web
Office Scripts
Bkkendig's avatar
Bkkendig
Copper Contributor
Jun 17, 2023
Thank you. I shall give this a try. Is it possible that the sequence of "protect" and "unprotect" in your script should be reversed?
NikolinoDE's avatar
NikolinoDE
Gold Contributor
Jun 17, 2023

Bkkendig 

function main(workbook: ExcelScript.Workbook) {
  // Get the active sheet
  let sheet = workbook.getActiveWorksheet();

  // Unprotect worksheet
  sheet.unprotect();

  // Sort the data
  let dataRange = sheet.getUsedRange();
  let sortColumn = "A"; // Replace with the column letter you want to sort on
  let sortRange = dataRange.getRangeByIndexes(1, 1, dataRange.getRowCount(), dataRange.getColumnCount());
  sortRange.sort({ key: sortColumn, ascending: true });

  // Protect worksheet
  sheet.protect();
}

The worksheet is first unprotected using sheet.unprotect(), then the data is sorted, and finally, the worksheet is protected again using sheet.protect().

Please use this updated script to pause worksheet protection, sort the matrix based on a specific column, and then resume protection.

Resources