CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
user3447653
user3447653

Reputation: 4158

Set up a timer in google apps script

I am trying to set up a timer for 24 hours when a cell is edited in the sheets. If it hasn't been edited for 24 hrs, that row has to be locked; else timer has to be reset to 24 hrs when it is edited before 24 hrs. New to google apps script programming; any suggestions would be appreciated

function onEdit(e) {

var range = e.range;
range.setNote('Last modified: ' + new Date());
ScriptApp.newTrigger("functionToLockCells").timeBased().after(24 * 60 *60 * 1000).create()

var triggers = ScriptApp.getProjectTriggers();
for (var i = 0; i < triggers.length; i++) {
  if (   triggers[i].getHandlerFunction() == "functionToLockCells") {
  ScriptApp.deleteTrigger(triggers[i]);
}}
}

function functionToLockCells() {
  //First cell to lock
  var col = 1;
  // Get last row with data in sheet
  var lastRow = sheet.getLastRow();
  //Loop until last row in sheet is passed
  lockRange(lastRow, col);
}

Upvotes: 0

Views: 1135

Answers (1)

Marios
Marios

Reputation: 27400

You can add to your onEdit() function a code that creates a scheduled one-time trigger to execute the function that locks your cells in 24 hours after the cell is edited.

ScriptApp.newTrigger("functionToLockCells")
.timeBased()
.after(24 * 60 *60 * 1000)
.create()
}

If this cell is edited within the 24 hours span, then you can delete this trigger using this code :

var triggers = ScriptApp.getProjectTriggers();
for (var i = 0; i < triggers.length; i++) {
  if (   triggers[i].getHandlerFunction() == "functionToLockCells") {
    ScriptApp.deleteTrigger(triggers[i]);
  }}

and create a new one using again the first code snippet in this answer.

The process could be as follows: when the cell of interest is edited, delete the active trigger and create a new one that will execute the function after 24 hours. If 24 hours have passed, the cell will be locked and thus it can/should no longer be edited. So simply put the trigger deletion part (second code snippet) before the trigger creation code (first code snippet) and it will presumably work.

Upvotes: 1

Related Questions