How to stop simultaneous functions triggering/conflicting in Google Sheets?

Question

I have a Google sheet that makes use of the onEdit(e) trigger in Google App script. I need this trigger to launch when I update a certain part of my Excel sheet. This particular function takes around 30 seconds to complete and makes some modifications to the Sheet.

My problem is that if I make multiple edits to the relevant range, the sheet will launch 4-5 simultaneous instances of that function. They then conflict with each other causing incorrect results.

Is there a way to block simultaneous instances, or perhaps put a cooldown on the onEditfunction so that it won't launch until 30 seconds afterwards the first launch?

Answer 1

Put a lock on the contents of your onEdit function

• The lock service allows to protect the spreadsheet from changes through more than one script simultaneously.
• When one instance of the onEdit trigger is being fired - the first thing the function does is to try to acquire a lock.
• The script will obtain a lock, if no other script currently accesses the spreasheet.
• If the script cannot ubtain the lock straightaway, it will wait for the specified amount of time until the already running script finishes executing.
• If the script succeeds in obtaining a lock - no other script will obtain access to the spreadsheet until the lock is realesed.

Sample:

function onEdit(e) {
  var lock = LockService.getDocumentLock();
  lock.waitLock(20000);
  // DO SOMETHING
  SpreadsheetApp.flush();
  lock.releaseLock();
}

Answer 2

You could make use of the Properties service to implement the "cooldown" by tracking the last edit timestamp

var editCooldownKey            = 'onEditCooldown';
var editCooldownInMilliseconds = 30 * 1000;
var currentTimestamp           = new Date().getTime();
var scriptProperties           = PropertiesService.getScriptProperties();
var lastEditTimestamp          = scriptProperties.getProperty( editCooldownKey );

// If not set or have passed the cooldown, reset the cooldown. Otherwise, do not proceed
if ( lastEditTimestamp === null || currentTimestamp - lastEditTimestamp > editCooldownInMilliseconds ) {
    scriptProperties.setProperty( editCooldownKey, currentTimestamp );
} else {
    return;
}

// The rest of your logic goes here