Clock Trigger Builder Not calling function when scheduled - Google sheets app Script

Question

I am using the app script provided by Google to access their prediction API through sheets. I am trying to predict thousands of rows at once, however, after 6 minutes the maximum execution time is reached at the code stops.

I implemented a solution that I found using clock trigger builder. Once I run the function it goes for 5 mins, then it stops sets a trigger to recall the function within 2 mins.

The major problem is that the function is not called when scheduled. I see it in the current triggers list, but it never gets called again. Can you please explain why this is occurring.

My intention is to predict as many lines as possible in 5 min then stop set a trigger to call the predict function again within a few minutes start where it left off and continue until ever element has been predicted.

I also need to know how would I store then values in cache so that it would know all the information that it needs when the function is called again.

//This is the function that is used to predict a selection of data
function predict() {
  try {
    clearOutput();
    var startTime= (new Date()).getTime();
    var sheet = SpreadsheetApp.getActiveSheet();
    var selection = sheet.getActiveSelection();
    var instances = selection.getValues();
    var project_number = getProjectNumber();
    var model_name = getModelName();
    var startRow = stRow; 
    var MAX_RUNNING_TIME = 300000;
    var REASONABLE_TIME_TO_WAIT = 60000;
    for (var i = startRow; i < instances.length; ++i) {
      var currTime = (new Date()).getTime();
      if(currTime - startTime >= MAX_RUNNING_TIME) {
        var builder = ScriptApp.newTrigger('predict').timeBased().after(REASONABLE_TIME_TO_WAIT);
    builder.create();
    break;
  } else {  
    var result = predictSingleRow(project_number, model_name, instances[i]);
    selection.getCell(i + 1, 1).setValue(result);
  }
    }
  } catch(e) {
    Browser.msgBox('ERROR:' + e, Browser.Buttons.OK);
  }
}

Answer 1

Few things as to why your code is not functioning as intended:

1) Since you mentioned,"I see it in the current triggers list, but it never gets called again" and looking at your code, I am unsure whether you intended to call the function again after it's execution has completed. If you do, this is because your for loop runs for a while until the length of the instances is obtained. Nothing in the script suggests that the function needs to be run again once it has finished iterating through instances. Refer to this link to see how to Manage Trigger Programmatically.

2) var builder = ScriptApp.newTrigger('predict').timeBased().after(REASONABLE_TIME_TO_WAIT);

This line of your code falls under an if condition which stops the execution for 1 minute (value is 60000). Hence, adding 1 minute to the time since execution started. Nowhere are you resetting the startTime counter to the time after the waiting time since once the value of currTime - startTime has exceeded MAX_RUNNING_TIME, the function will keep calling the if loop for all iterations of the for loop after that. Simply put, if startTime was 9:35 and currTime was 9:40, after waiting for 1 minute the currTime is 9:41 which is still more than the MAX_RUNNING_TIME(5 minutes) because value of startTime still remains 9:35. Resetting it to 9:41 at this point should resolve your problem.

3) Loosing the break in the if loop would probably help fix that as well.

EDIT:

Add a function as shown in the link I mentioned above:

function callTrigger(){
  ScriptApp.newTrigger('predict')
      .timeBased()
      .everyMinutes(30)
      .create();
}

Run the function callTrigger once from your editor and you should be good to go. Remember, for minutes you can only pass values 1,5,15 or 30.