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

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:

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.

Update: Using onEdit Trigger

References: