How to delay cell protection when value is entered in a cell?

Question

I have used following OnEdit() trigger code to lock cell after entering data first time:

 function LockCells(event){
 
  var range = event.range;

  var description = 'Protected';   // + stringDate;
  var protection = range.protect().setDescription(description);
  
  var me = Session.getEffectiveUser();
  protection.addEditor(me);
  protection.removeEditors(protection.getEditors());
  
  if (protection.canDomainEdit()) {
  protection.setDomainEdit(false);
}

}

And when I enter a value in a cell as a user (not sheet admin), it instantaneously blocks the cell from re-entering value. Can we delay this process? I mean if we enter value now but the protection on that cell is applied after 10 minutes or one hour but not immediately?

Answer 1

I believe your goal is as follows.

• Your function of LockCells is executed by the OnEdit installable trigger.
• You want to run the script in the function LockCells after the OnEdit trigger is run.

In this case, how about the following modified script?

Modified script 1:

For example, when the OnEdit trigger is run, when you want to run the script in the function LockCells after about 6 minutes, the modified script can be a bit simple as follows.

function LockCells(event) {
  Utilities.sleep(5 * 60 * 1000); // For example, after 5 minutes, the script is run.
  var range = event.range;
  var description = 'Protected';   // + stringDate;
  var protection = range.protect().setDescription(description);
  var me = Session.getEffectiveUser();
  protection.addEditor(me);
  protection.removeEditors(protection.getEditors());
  if (protection.canDomainEdit()) {
    protection.setDomainEdit(false);
  }
}

Modified script 2:

When you want to run the script in the function LockCells after more than 6 minutes, the modified script is as follows. Please copy and paste the following script to the script editor of Spreadsheet. And, please reinstall the OnEdit installable trigger to the function LockCells. By this, when you edit the cell, the edited cell is protected after 10 minutes in this sample script.

var time = 10 * 60 * 1000; // 10 minutes

function LockCells(event) {
  var date = new Date().getTime();
  var range = event.range;
  var a1Notation = `'${range.getSheet().getSheetName()}'!${range.getA1Notation()}`;
  var p = PropertiesService.getScriptProperties();
  var ranges = p.getProperty("ranges");
  ranges = ranges ? JSON.parse(ranges).concat({ date, a1Notation }) : [{ date, a1Notation }];
  p.setProperty("ranges", JSON.stringify(ranges));
  ScriptApp.newTrigger("lockCellsByTrigger").timeBased().after(time).create();
}

function lockCellsByTrigger(e) {
  ScriptApp.getScriptTriggers().forEach(t => {
    if (t.getUniqueId() == e.triggerUid) ScriptApp.deleteTrigger(t);
  });
  var limit = time;
  var now = new Date().getTime();
  var p = PropertiesService.getScriptProperties();
  var ranges = p.getProperty("ranges");
  if (!ranges) return;
  ranges = JSON.parse(ranges);
  var {rranges, r} = ranges.reduce((o, e) => {
    o[e.date + limit < now ? "rranges" : "r"].push(e);
    return o;
  }, {rranges: [], r: []});
  if (rranges.length == 0) return;
  p.setProperty("ranges", JSON.stringify(r));
  var description = 'Protected';
  var me = Session.getEffectiveUser();
  rranges.forEach(({a1Notation}) => {
    var protection = SpreadsheetApp.getActiveSpreadsheet().getRange(a1Notation).protect().setDescription(description);
    protection.addEditor(me);
    protection.removeEditors(protection.getEditors());
    if (protection.canDomainEdit()) {
      protection.setDomainEdit(false);
    }
  });
}

• When you want to change the time, please modify time. In the current stage, after 10 minutes, the edited cell is protected.
• The flow of this script is as follows.
  1. When a cell is edited, LockCells is run by the installable OnEdit trigger.
  2. Put the a1Notation of edited cell and the date to Properties Service, and install the time-driven trigger after 10 minutes.
  3. When the time-driven trigger runs the function lockCellsByTrigger, the edited cells after 10 minutes are protected.

References:

• Properties Service
• newTrigger(functionName)