Setting multiple protected ranges with one range value inputted

Question

Id like to set protected ranges over several sheets and ranges of the same spreadsheet in a Google spreadsheet.

The normal way to do a range is 'Sheet One'!A:L

What id like to do is be able to write something like 'Sheet One'!A:L, 'Sheet Two'!A:L and also 'Sheet One'!A:L, M:N, Q.

Is there a way to do something like this, when i input these above ranges into the range input i get an error saying invalid range.

Answer 1

The following piece of code makes it is possible to create different protected ranges throughout a spreadsheet at once.

Code

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

Explained

The first two variables are usable throughout the code. The onOpen() function creates a menu item upon file opening and adds two items. The setRanges() function initially creates a popup box (prompt) to capture the string. The response is then split using the comma as delimiter.

Now we need to iterate though the different ranges in order to prepare the protected ranges. Within the iteration, the response is validated once more by splitting it using the exclamation mark as delimiter. This will yield either one or more results. The switch method will determine the buildup of the sheet name and the range.

Now that the sheet has been defined, the range (getRange()) can be protected (protect()) and the try / catch allows for some error handling.

Screenshot

Note

This script can also cope with the following sheet name examples:

• This is a test
• Test!Test

Example

I've created an example file for you: setting up multiple protected ranges
Add this script in the script editor (Tools > Script editor) and press the bug button:
Now you can use the script.

Answer 2

This is actually possible by protecting the entire sheet, rather than protecting a set range, then once you have set the sheet you can add exceptions.

So for instance you could restrict sheetA, the set an exception for A:B and J:K. The effect of this is that C:I and K:Z.. would be protected

Answer 3

At this time it's not possible from the Google Sheets built-in user interface to protect non-continuous ranges in single step. Instead do the procedure manually for each range. It's worth to say that it's possible to use Google Apps Script to automate certain tasks.

References
Protect a range or sheet from being edited - Docs editor Help
Extend Google Docs, Sheets, and Forms with Apps Script - Docs editor Help
protect() - Class Range - Google Apps Script