Automatically protect / un-protect sheets or ranges when value is change from another source (google sheets)

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/1st1BjweeahAYrIziAibJgTj-_R_LEfOL5_CwTKg0Pzg/edit#gid=0

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.
4. Value at Sheet2!A1 is imported from another file with IMPORTRANGE formula. So that I (as administrator) don't have to edit every single Sheet2!A1 from every students to lock their work.
5. Here is the link to the other master file for me to change the value at every Sheet2!A1 from every student's file: https://docs.google.com/spreadsheets/d/1qXct3QKHyYt-hQR_4-ySjrcrNSPeoai7aogKHuE-14c/edit#gid=0
6. 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 there is "change" (maybe with on change trigger) at Sheet2!A1 (with value imported from another source)?

Note: 1 value at Sheet2!A1 is to lock Sheet1 and 0 value at Sheet2!A1 is to unlock Sheet1.

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

Answer 1

Issue and solution:

• There's no way to trigger a function when the value returned by a formula gets updated (onEdit trigger runs when a user changes the value of a cell).
• As an alternative, I'd suggest using a time-based trigger, that will fire periodically (with 1 minute being the highest frequency available).
• To do that, you have to install the trigger. You can do that manually, by following these steps, or programmatically, by executing the function installTrigger (see below) once (while logged in with the owner account, who will always have access to the protected range).
• This trigger will then fire a function that will check whether Sheet2!A1 is 1, and protect/unprotect the range Sheet1!A:B accordingly (function updateProtection below). See Range.protect to protect the range, and getProtections and Protection.remove to unprotect it.

Code sample:

const SPREADSHEET_ID = "YOUR_SPREADSHEET_ID";
const PROTECTED_RANGE = "A:B";

function updateProtection() {
  const ss = SpreadsheetApp.openById(SPREADSHEET_ID);
  const sheet1 = ss.getSheetByName("Sheet1");
  const protections = sheet1.getProtections(SpreadsheetApp.ProtectionType.RANGE);
  const sheet2 = ss.getSheetByName("Sheet2");
  const condition = sheet2.getRange("A1").getValue();
  if (condition == 1) { // If formula returns 1, protect
    const isRangeProtected = protections.some(p => p.getRange().getA1Notation() === PROTECTED_RANGE);
    if (!isRangeProtected) { // Avoid duplicate protections
      const range = sheet1.getRange(PROTECTED_RANGE);
      const protection = range.protect();
      const me = Session.getEffectiveUser();
      protection.addEditor(me);
      protection.removeEditors(protection.getEditors());
      if (protection.canDomainEdit()) {
        protection.setDomainEdit(false);
      }
    }
  } else { // Unprotect
    protections.filter(p => p.canEdit()).forEach(p => p.remove());
  }
}

function installTrigger() {
  ScriptApp.newTrigger("updateProtection")
           .timeBased()
           .everyMinutes(1)
           .create();
}