Protect Sheet when condition is met by change (Google Sheet)

Question

There is a case where test is given to a certain student. The student can only work with the answer for the test before a given period.

Here is the sample case: https://docs.google.com/spreadsheets/d/1xS3SMrSh2vHCb-ShP96IzzKQGRXYK7ACKO0Ua4_OHOY/edit?usp=sharing

1. Sheet1!A:A is where the questions are given
2. Sheet1!B:B is where the answer must be written by the student
3. Sheet2!A1 is the condition to protect or unprotect Sheet1. When the date is after 15th, this cell become 1, otherwise it stays 0. This cell change automatically since it contains TODAY() formula.
4. When Sheet2!A1 change into 1, Sheet1!A:B need to be protected from editing by anyone unless the owner of the file

Is it possible to automatically protect sheet or range (in the example case is Sheet1!A:B) when a condition met?

For instance, if cell A1 in Sheet2 is 0, then Sheet1 is protected from being edited by anyone (excluding me). If cell A1 in Sheet2 is no 0, then Sheet1 is not locked anymore.

Is there any formula or google sheet script that can solve this problem?

Answer 1

You can actually implement that on Sheet using Time Driven Trigger of Apps Script. Time Driven Trigger executes at a particular time. In your case, you have to use Month timer as Type of time based trigger. Month timer has day of month and time of day properties which you can set to determine which day and time the script will run.

Example:

Follow these steps:

1. Copy and paste the code below to your code editor and save.

function testFunc(){
    var e = {
    'day-of-month':16
    }
    lockUnlockSheet(e)
}


function lockUnlockSheet(e) {
    var sheet1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
    if (e['day-of-month'] >= 16) {
    var protection = sheet1.protect().setDescription("Protect Sheet1");
    var me = Session.getEffectiveUser();
    protection.addEditor(me);
    protection.removeEditors(protection.getEditors());
    if (protection.canDomainEdit()) {
        protection.setDomainEdit(false);
    }
    } else {
    sheet1.protect().remove();
    }
}

2. Once you paste the code, click Save
3. Go to the Triggers tab in your editor (Located on the left side of the editor with alarm clock symbol)
4. Click Add Trigger
5. Copy the configuration of the trigger below (You should have 2 triggers generated)

It should look like this:

All set now. But to test the function, we need the help of another function to mimic the behavior of Trigger. This is where we use the testFunc().

In your editor click the dropdown besides the Debug button and change it to testFunc then Click Run.

If you set the value of day-of-month to 16-31, it will lock the Sheet1.

If you change it to 1 - 15 it will unlock it.

Note: Installable triggers always run under the account of the person who created them. The getEffectiveUser() is always you.

---

Update: Using onEdit Trigger

Code:

function onEditLock(e) {
  var range = e.range;
  var sheet = range.getSheet();
  var sheet1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
  if (range.getA1Notation() == "A1" && sheet.getName() == "Sheet2") {
    if (e.value == 1) {
      var protection = sheet1.protect().setDescription("Protect Sheet1");
      var me = Session.getEffectiveUser();
      protection.addEditor(me);
      protection.removeEditors(protection.getEditors());
      if (protection.canDomainEdit()) {
        protection.setDomainEdit(false);
      }
    } else if(e.value == 0) {
      sheet1.protect().remove();
    }
  }
}

Trigger Setup:

Output:

Note: onEdit() only runs when a user changes a value in a spreadsheet.

References:

• Time-driven events
• onEdit events
• Installable Triggers
• Class Protection
• Class Sheet
• Class Range