javascriptgoogle-apps-scriptgoogle-sheetsgoogle-sheets-apidata-protection

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

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?

Solution

  • I believe your goal is as follows.

    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);
    }
  });
}

    References: