5

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.

  • You could build a small UI in GAS to input the ranges into? – Jacob Jan Tuinstra Jun 29 '15 at 20:21
  • Hi sam, just out of curiosity, did my solution work? – Jacob Jan Tuinstra Jul 20 '15 at 17:56
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

enter image description here

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: enter image description here
Now you can use the script.

2
+50

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

0

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

  • Hi Sam, just out of curiosity, did my solution work? Your solution still needs multiple inputs, contrary to your request. – Jacob Jan Tuinstra Aug 13 '15 at 18:07
  • Hi Jacob, it was actually for my colleague ill check with them and see how they got on, your correct my answer does contradict my question, but wanted to add it in as another option. – sam Aug 13 '15 at 19:22
  • Hi Sam, it has been a while and I was wondering if your colleague was able to test my solution? – Jacob Jan Tuinstra Nov 2 '15 at 22:28
  • @JacobJanTuinstra - i spoke with him and he said it worked perfectly, thanks very much for this ! – sam Nov 2 '15 at 23:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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