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?
CodePudding user response:
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.
- When a cell is edited,
LockCells
is run by the installable OnEdit trigger. - Put the a1Notation of edited cell and the date to Properties Service, and install the time-driven trigger after 10 minutes.
- When the time-driven trigger runs the function
lockCellsByTrigger
, the edited cells after 10 minutes are protected.
- When a cell is edited,