GAS: Range Protection?

Question

I've looked everywhere and it seems that GAS hasn't caught up with Google Spreadsheet. Is there a brute force method for setting protection on certain ranges in a sheet..? (And making a protected sheet with all the formulas and referring to them won't help me.)

I found this through google: https://code.google.com/p/google-apps-script-issues/issues/detail?id=1721

I even commented at the bottom. (More of a complaint than anything useful.) But the guy above me there posted this code:

//Function to Protect Target Sheet
function ProtectTargetSheet() {

      //Enter ID for each Worksheet
      var IDs = ["Sheeet_1", "Sheet_2"]

      //Enter Page to protect, in order of WorkSheets
      var Sheet_names = ["Page_1", "Page_2"]

      //For each sheet in the array
      for ( i = 0; i < IDs.length; i ++) {

        //id of sheet
        var sheet = IDs[i]

        //activate dedicated sheet
        var ActiveSheet = SpreadsheetApp.openById(sheet)

        //Find last row and column for each sheet
        var LastRow = ActiveSheet.getSheetByName(Sheet_names[i]).getLastRow();
        var LastCol = ActiveSheet.getSheetByName(Sheet_names[i]).getLastColumn();

        //Name of protectSheet
        var Name = "Protect_Sheet";

        //Range for Protection
        var Named_Range = ActiveSheet.getSheetByName(Sheet_names[i]).getRange(1, 1, LastRow, LastCol);

        //Impletment Protect Range
        var protected_Range = ActiveSheet.setNamedRange(Name, Named_Range);
      }
}

I don't see how this can work to give protection to a range when shared. It seems that it would just create a Named Range. He does say to set the permissions manually first. But I can't figure what exactly he meant.

Anyways, I was hoping that someone had found a way by now to do this until Google syncs GAS with its counterpart.

My wish is to, through 100% code, select a range in a sheet, within a spreadsheet, and make it so that when I share this whole spreadsheet to a person, that he or she can't edit that range. There will be other parts in that sheet that they have to be able to edit. Just not that range. It is easy to do this manually, but when having to create 100s of spreadsheets, it would be help to be able to do this through GAS.

Thanks.

Answer 1

Part of your question asked about protecting a sheet. Please have a look here: setProtected(protection)

As for programmatically protecting a range no. However, you could protect a sheet, does not need to be in the same spreadsheet and then create an onEdit trigger that would replace any change in your "protected" range with the original source data.

Something like this:

function onLoad() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var protected = ss.getSheets()[1].getRange("A1:B2").getValues(); //You could use openByID to get from a different ss
  var target = ss.getSheets()[0].getRange("A1:B2").setValues(protected);
}

function onEdit(){ 
 onLoad();
}

Every time a change is made to the spreadsheet the script will rewrite the data in the sheet for the range you specify.

Answer 2

The easiest approach I've found is to use Data Validation: that is, write a script which will examine each cell to be 'protected' and create and apply a validation rule which enforces entry of the existing content and rejects anything else. [Of course, this also implies that you have designed your spreadsheet such that all entered data is on sheet or sheets separate from those which have formula embedded. On these you use normal sheet protection.]

Answer 3

If you are sending 100s of copies of the same sheet. Then create a template sheet, protect the ranges in it manually and send a copy of the template. It will retain the protection.

Sorry but as others have said there is not script method of setting protection at a sub-sheet level.

Answer 4

According to Control protected ranges and sheets in Google Sheets with Apps Script, posted on February 19, 2015, now is possible to protect a Google Sheets range.

From https://developers.google.com/apps-script/reference/spreadsheet/protection

Class Protection

Access and modify protected ranges and sheets. A protected range can protect either a static range of cells or a named range. A protected sheet may include unprotected regions. For spreadsheets created with the older version of Google Sheets, use the PageProtection class instead.

 // Protect range A1:B10, then remove all other users from the list of editors.
 var ss = SpreadsheetApp.getActive();
 var range = ss.getRange('A1:B10');
 var protection = range.protect().setDescription('Sample protected range');

 // Ensure the current user is an editor before removing others. Otherwise, if the user's edit
 // permission comes from a group, the script will throw an exception upon removing the group.
 var me = Session.getEffectiveUser();
 protection.addEditor(me);
 protection.removeEditors(protection.getEditors());
 if (protection.canDomainEdit()) {
   protection.setDomainEdit(false);
 }