I have a function call settrigger, basically this is a function that will keep running until certain condition is fulfilled. However, I want to add a function which can stop the execution of the settrigger function manually instead of clicking Cancel to stop the running script. May I know how should the function look like? Appreciate your help!

This is my script:

function settrigger() {

  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var y = 1;
  var z = 1

  while ( y != 100000 ){
    var x = ss.getActiveSheet().getRange('A15').getValue();
    x=x 1;
    ss.getRange('A15').setValue(x);
    y=y 1;
    var toggle = ss.getRange('A1')
    if (x%2==0){
      toggle.setBackground("RED");
    } else if (x%2!=0) {
      toggle.setBackground("WHITE");
    }
  }

}

Stop Function

function stopfunc() {
  PropertiesService.getScriptProperties().setProperty("stop", "stop");
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var toggle = ss.getRange('A1')
  toggle.setBackground("WHITE")
}

CodePudding user response：

In that case, how about the following modification?

Modified script:

function settrigger() {
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var y = 1;
  var z = 1

  var p = PropertiesService.getScriptProperties();
  while (y != 100000 && p.getProperty("stop") == null){
    var x = ss.getActiveSheet().getRange('A15').getValue();
    x=x 1;
    ss.getRange('A15').setValue(x);
    y=y 1;
    var toggle = ss.getRange('A1')
    if (x%2==0){
      toggle.setBackground("RED");
    } else if (x%2!=0) {
      toggle.setBackground("WHITE");
    }
  }
  p.deleteProperty("stop");
}

// When you want to stop the function of `settrigger`, please run this.
function stopfunc() {
  PropertiesService.getScriptProperties().setProperty("stop", "stop");
}

• In this case, after the function of settrigger was run, when stopfunc is run, the key stop of PropertiesService is created. By this, y != 100000 && p.getProperty("stop") == null is false. By this, the loop is stopped.

Reference:

• Properties Service

Added:

From your additional question in your comment as follows,

I tried to add few lines of code to change the background back to white color again but seems like it doesn't work well, sometime still remain red color, it depends on luck.

In this case, the reason for your issue is due to that the function settrigger() is stopped after the function stopfunc() was run. So, please modify the above script as follows.

Modified script:

function settrigger() {
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var y = 1;
  var z = 1

  var p = PropertiesService.getScriptProperties();
  while (y != 100000 && p.getProperty("stop") == null){
    var x = ss.getActiveSheet().getRange('A15').getValue();
    x=x 1;
    ss.getRange('A15').setValue(x);
    y=y 1;
    var toggle = ss.getRange('A1')
    if (x%2==0){
      toggle.setBackground("RED");
    } else if (x%2!=0) {
      toggle.setBackground("WHITE");
    }
  }
  p.deleteProperty("stop");
  ss.getRange('A1').setBackground("WHITE");  // Added
}

// When you want to stop the function of `settrigger`, please run this.
function stopfunc() {
  PropertiesService.getScriptProperties().setProperty("stop", "stop");
}