2

In my Google spreadsheet, I want to protect all cells below a checkbox if it's value is true and unprotect if it's value is false. Every cell in row 3 is a checkbox that this has to work for.

Example Sheet:

    +------------+------------+------------+------------+------------+-----
    |  Column A  |  Column B  |  Column C  |  Column D  |  Column E  | ...
+---+------------+------------+------------+------------+------------+-----
| 1 |            |            |            |            |            |  
+---+------------+------------+------------+------------+------------+-----
| 2 |            |            |            |            |            |  
+---+------------+------------+------------+------------+------------+-----
| 3 | checkbox1  | checkbox2  | checkbox3  | checkbox4  | checkbox5  | ...
+---+------------+------------+------------+------------+------------+-----
| 4 | value1.1   | value2.1   | ...        | ...        | ...        | ...
+---+------------+------------+------------+------------+------------+-----
| 5 | value1.2   | value2.2   | ...        | ...        | ...        | ... 
+---+------------+------------+------------+------------+------------+-----
| 6 |            |            |            |            |            |  
+---+------------+------------+------------+------------+------------+-----
|   |            |            |            |            |            |  

So if I set checkbox1 to true, A3:A50 should be protected and unprotected if I set it to false
...same for checkbox2 -> B3:B50 ...checkbox3 -> C3:C50 ...

This should also only work in one specific sheet (let's call it 'sheet1'). I know how I can set protection using a script but I don't know how to create the dynamic ranges.

I hope you can help me with this.

2

I expect you have already solved this, but still wanted to post my solution for others who might have the same issue. I edited user0's answer to allow for dynamic criteria

function onEdit(event){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getSheets()[0]; // 0 == first sheet.
  var cellRange = SpreadsheetApp.getActiveSpreadsheet().getActiveRange();

  if (isWatched(cellRange)) { // Check if edited cell is the one we're watching.
    if (event.value == "TRUE"){ // If the new value is true, protect the data
      setProtected(cellRange)
    } else { // If the new value isn't true we remove the protection (this allows you to remove the protection by unchecking the checkbox)
      var protections = sh.getProtections(SpreadsheetApp.ProtectionType.RANGE);
      for (var i = 0; i < protections.length; i++) {
        if (protections[i].getRange().getColumn() == cellRange.getColumn()) { // We only check if the column of a protection is the same
          protections[i].remove(); // remove protection.
        }
      }
    }
  }
}

function setProtected(cellRange){
  // Sets the desired range to protected
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getSheets()[0];
  var protectedRows = sh.getRange(cellRange.getRow() + 1, cellRange.getColumn(), 7); // We protect the 7 rows underneath the checkbox

  protectedRows.protect().setDescription("Protected cell");
}

function isWatched(cellRange) {
  // Check if cell is watched
  if (cellRange.getRow() == 3){
    return true
  } else {
    return false
  }
}
| improve this answer | |
0

for A column:

function onEdit(event){

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getSheets()[0]; // 0 == first sheet.
  var ee = SpreadsheetApp.getActiveSpreadsheet().getActiveRange().getA1Notation();
  if (ee == "A3") { // Check if edited cell is the one we're watching.
    if (SpreadsheetApp.value == "true"){ // If the value == "true", do stuff.
      var protections = sh.getProtections(SpreadsheetApp.ProtectionType.RANGE);
      for (var i = 0; i < protections.length; i++) {
        if (protections[i].getDescription() == 'Test1') { // If protect. descript. == "Test1"
          protections[i].remove(); // then remove protection.
        }
      }
    } 
  }  
}

function setProtected(){
  // Sets the desired range to protected with "Test1" description for demonstration.
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getSheets()[0];
  var protectedRows = sh.getRange("A4:A50");
  protectedRows.protect().setDescription("Test1");
}
| improve this answer | |
  • As I said I know how to set a protection using a script. The problem is I don't want to copy&paste this function for 50+ columns. Is it possible to do this in one function? – Shaedo Jan 5 '19 at 16:34

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.