Protect identical ranges across multiple Sheets

Posted on

Protect identical ranges across multiple Sheets – Whether you’re just starting out with Google Sheets or are a seasoned pro, sooner or later one of your formulas will give you a formula or script parse error message rather than the result you want. However, every Formula user, irrespective of whether it’s a beginner or expert, has invariably come across a formula parse error in Google Sheets at least once in their life (and if you haven’t. you soon will). It can be frustrating, especially if it’s a longer formula where the formula parse error may not be obvious. In this article, we will talk about some common script or formula parse errors in Google Sheets, like Protect identical ranges across multiple Sheets, and how to fix it.

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();
  }
}

Answer :

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")])
  })
}

Leave a Reply

Your email address will not be published.