Trigger to get around "Exceeded maximum execution time" in google-apps-script

Question

I am trying to run the following script to get around Google script's maximum execution time. I have the function run for 4 minutes and set a trigger for it to run again in 5 minutes starting where it left off in processing. The first time through it runs smoothly and sets the trigger. When the trigger runs the first time, it processes very slow and then does not run again. Could anyone tell me what I'm doing wrong and if there is a way around this?

function updateAll() {

  var startTime= (new Date()).getTime();
  var startRow = ScriptProperties.getProperty("start_row");

  //Start on row 2 if null
  if (!startRow) {
    startRow = "2";
  }

  // This code deletes all the triggers
  var triggers = ScriptApp.getProjectTriggers();
  for(var i in triggers) {
    ScriptApp.deleteTrigger(triggers[i]);
  }

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];

  for (var i = startRow; i < 3000; i++) {
    var currTime = (new Date()).getTime();

    if(currTime - startTime >= 240000) {

      ScriptProperties.setProperty("start_row", i)
      //Set new trigger to run in 5 minutes
      ScriptApp.newTrigger("updateAll")
               .timeBased()
               .at(new Date(currTime+300000))
               .create();
      break;

    } else {

      //Do logic and set values in spreadsheet
      //Run sleep to avoid maximum script execution error
      Utilities.sleep(100);

    }

  };
}

Henrique G. Abreu Henrique G. Abreu · Accepted Answer · 2014-01-03T17:09:36

How do you know it's running slowly? Is the spreadsheet your working on the new version? It seems there's a lot of issues with it yet.

Anyway, I'd not remove the trigger and re-set it every time. I'd just set it to run every 5 minutes and remove it when I detect the job is fully finished. Also there's no need to call Utilities.sleep because of the "maximum execution time error". It's only useful if you're hitting a "calls per second" type of error.

As I was finishing this answer I saw that your problem might on your ScriptProperties usage. It can only save strings, and if you pass something else to it, it will just try to parse it to string. So, when you get your startRow back from ScriptProperties you should parseInt it back to a number.

var startRow = parseInt(ScriptProperties.getProperty("start_row"));

Trigger to get around "Exceeded maximum execution time" in google-apps-script

1 Answers