1

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.

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");
}
  • 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 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.