0

I currently have a multitude of docs each containing 30-60+ sheets. Each sheet is formatted identically with the same headers/column labels. In doing research I found its not possible to mass protect across multiple sheet within GUI. Bulk Sheet Manager addon can mass protect the entire sheets but not specific ranges. Can anyone help with the required script to protect all columns excluding K:M?

I think this script gets me close but without more indepth script knowledge it might as well be arabic for me.

var app = SpreadsheetApp.getUi();
var ss = SpreadsheetApp.getActive();

function onOpen() {
  app.createMenu("Ranges")
    .addItem("Set ranges", "setRanges")
    .addItem("Delete ranges", "deleteRanges")
    .addToUi();
}

function setRanges() {
  var text = app.prompt("Give string", "Like A1:A10, Sheet2!A4:B5, This is a test!A:A", app.ButtonSet.OK_CANCEL); 
  if(text.getSelectedButton() == app.Button.OK) {
    var ranges = text.getResponseText().split(",");  

    for(var i = 0, iLen = ranges.length; i < iLen; i++) {
      var value, range, index, sname, split, sh;
      value = ranges[i].trim(), split = value.split("!");

      switch(split.length) {
        case 1:
          range = value;
          sh = ss.getActiveSheet();
          break;
        case 2:
          range = value;
          sh = ss.getSheetByName(String(split[0].trim()));
          break;
        default:
          index = value.lastIndexOf("!");
          range = "'" + value.slice(0, index) + "'!" + split[split.length - 1].trim();
          sh = ss.getSheetByName(String(value.slice(0, index)));
          break; 
      }

      try {
        sh.getRange(String(range))
          .protect()
          .setDescription("Range" + i);
      } catch(e) {
        throw "Range: " + (i) + " doesn't exist";
      }
    }
  }
}

function deleteRanges() {
  var protections = ss.getProtections(SpreadsheetApp.ProtectionType.RANGE);
  for (var j = 0, jLen = protections.length; j < jLen; j++) {
    protections[j].remove();
  }
}
2
  • 1
    Welcome to Web Applications. Please show what you tried to protect the columns that were mentioned and a brief description of your search efforts as is suggested in How to Ask.
    – Rubén
    Apr 8, 2021 at 21:39
  • Welcome. May I try to clarify? Please correct me if I am wrong. You have many spreadsheets; each spreadsheet contains many sheets; each sheet has identical formatting. You are trying to write a script for a given spreadsheet that will protect all columns excluding K:M. You have included script for two functions: setRanges and deleteRanges. Did you write these or were they sourced elsewhere? You said that they get you "close"; are you able to describe what is required in order to complete the functions? AAMOI, do you have a plan for applying these functions to your other spreadsheets?
    – Tedinoz
    Apr 11, 2021 at 6:51

1 Answer 1

0

To protect all sheets inside the active spreadsheet, except the columns K:M, use

function protectAllExceptKM() {
  const sheets = SpreadsheetApp.getActive().getSheets()
  sheets.forEach(sheet=>{
    sheet.protect().setUnprotectedRanges([sheet.getRange("K:M")])
  })
}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.