CODEWITHSUNDEEP
javascriptgoogle-apps-scripttriggersgoogle-sheets
Christopher Mauer
Christopher Mauer

Reputation: 365

Temporarily disabling triggers

I have been tasked with creating buttons inside of cells using Google Sheets. Since GAS does not natively allow for buttons aside from using images, I decided to use data validation to get the job done. I created data validations instead of buttons with singular entries, and used the onEdit trigger to fire a script whenever that cell's value is changed to the value in the list.

The script fires as expected when the cell initially changes. However, I cannot find a way to change the value in the pseudobutton back to its original value without triggering the onEdit trigger again. Here is the backing code to a relatively simple button from my sheet:

// If Delete Standard button is pressed
} else if (cell.getColumn() == 1 
           && cell.getRow() >= 8 
           && SpreadsheetApp.getActiveSheet().getName() == analysisSheet.getName() 
           && cell.getDisplayValue != "X") {
  Browser.msgBox("Deleted Standard: " + analysisSheet.getRange(cell.getRow(), 3).getDisplayValue(), Browser.Buttons.OK_CANCEL);
  analysisSheet.deleteRow(cell.getRow());

The cells in the column that contains the data validation have a value of "X", and that value is changed to "Remove this standard". This triggers an onEdit function, which deletes the selected row. Sometimes this only deletes one row, but most of the time, it also deletes the following row, and in a few cases, three rows. I tried to restrict access to the function by stating that the selected cell value cannot be "X", but it appears that on the second trigger, it still has access to this portion of the code. Yes, Browser.msgBox gets triggered for each row that is deleted.

My question is, is there a way to temporarily disable triggers to prevent this behavior from happening? I have tried using the following without luck:

var lock = LockService.getScriptLock();
lock.waitLock(3000);

Edit: Looks like I forgot parenthesis after the getDisplayValue method which fixes this issue. However, I'd still like some sort of workaround for temporarily disabling triggers.

Upvotes: 1

Views: 2115

Answers (1)

toddmo
toddmo

Reputation: 22416

You can add / remove your triggers via script. Luckily this doesn't delete the execution history. It's functionally equivalent to disabling the trigger. Here's an example of creating and deleting triggers, although I know yours is an onedit:

function createTimers() {
  ScriptApp.newTrigger('calendar_test_occurred')
      .timeBased()
      .atHour(10)
      .everyMinutes(1) 
      .create();

}

function deleteTimers() {
  var allTriggers = ScriptApp.getProjectTriggers();
  for (var i = 0; i < allTriggers.length; i++) {
    if (triggers[i].getEventType() == ScriptApp.EventType.CLOCK) {
      ScriptApp.deleteTrigger(allTriggers[i]);
    }
  }
}

Also, I use check-box validation for buttons. I only run the code if the value is true, and then the script sets the range back to false at the end.

Upvotes: 1

Related Questions