Google script stops after 5mins of execution

Question

I am running this code and the code needs to be run for a very long period of time, but google script shows an error "exceeds maximum execution time". I read somewhere that using triggers this can be implemented where the script turns off and resumes where it left. But I am not able to do so. How to make it resume where it left??

function myFunction() {

  var app = SpreadsheetApp.getActive();

  var sheet = app.getSheets()[2];

  sheet.getRange(1, 1).setBackground('green');
  var data = sheet.getDataRange().getValues();

  for(var x=0; x<data.length; x++){


    if(data[x][1] == 1) {


        var name1 = sheet.getRange((x+1),(4)).getValue();

        var comp1 = sheet.getRange((x+1),(8)).setValue(name1);

    }
    else if(data[x][1] == 2){
      var name2 = sheet.getRange((x+1),(4)).getValue();
      var comp2 = sheet.getRange((x+1),(9)).setValue(name2);}
    else if(data[x][1] == 3){
      var name3 = sheet.getRange((x+1),(4)).getValue();
      var comp3 = sheet.getRange((x+1),(10)).setValue(name3);}
    else if(data[x][1] == 4){
      var name4 = sheet.getRange((x+1),(4)).getValue();
      var comp4 = sheet.getRange((x+1),(11)).setValue(name4);}
      else if(data[x][1] != 4 && data[x][1] != 3 && data[x][1] != 2 && data[x][1] != 1){
        comp1 = sheet.getRange((x+1),(8)).setValue(name1);
        comp2 = sheet.getRange((x+1),(9)).setValue(name2);
        comp3 = sheet.getRange((x+1),(10)).setValue(name3);
        comp4 = sheet.getRange((x+1),(11)).setValue(name4);
      }

var word = sheet.getRange((x+1),(3)).getValue();
var nextword = sheet.getRange((x+2),(3)).getValue();
comp1 = sheet.getRange((x+1),(8)).setValue(name1);
comp2 = sheet.getRange((x+1),(9)).setValue(name2);
comp3 = sheet.getRange((x+1),(10)).setValue(name3);
comp4 = sheet.getRange((x+1),(11)).setValue(name4);    

    if(word != nextword){
      name1 = "";
      name2 = "";
      name3 = "";
      name4 = "";
    }    

}
}

You need to remove all those `getValue` and `setValue` calls. `sheet.getRange((x+1),(4)).getValue()` is basically `data[x][3]` — TheMaster, May 19 '20 at 04:11
Does this answer your question? [Google app script timeout ~ 5 minutes?](https://stackoverflow.com/questions/14450819/google-app-script-timeout-5-minutes) — TheMaster, May 19 '20 at 04:13

Stykes · Accepted Answer · 2020-05-19T12:23:50.960

You can manage triggers programmatically using Script Service, for what you're trying to do, this will involve checking the runtime regularly using something like:

taskStartTime = new Date();

at the beginning of the script and preiodically checking how long the script has been running with something like:

endTime = new Date(); duration = Math.round((endTime.getTime() - taskStartTime.getTime())/1000); tasks_Processed_Array[tasks_Processed_Array.length - 1].duration = duration;

And if the duration is close to the max, then create a trigger for later and start where you left off.

However, there's no need to do this for your script. You are overusing .getValue() and .setValue() and this is slowing down the script significantly. Instead use getValues() to gather all data from a sheet at once, and then manipulate it in the script. Use setValues() to paste all the values at once. This post explains it more.

Every time you use getValue() the script needs to wait for Google to respond. It can be frustrating working with the Arrays that getValues() returns, but used correctly it will take a fraction of the execution time.

Here are a few examples: var name1 = data[x][3] var name2 = data[x][4]

Better yet, define name once before the if statement.

The setValues part is a little more challenging. Define an array outside the loop var ary = [] and then add elements to the array using ary.push([colToPasteIn, name]) that way you have a reference for where it should be pasted.

But getValue() slows down the script much more than setValue() so I think fixing the first part will be sufficient. More explanation here.

The process is going on in a loop, it checks few cells of each row and append other cells of the same row, so I don't think it is possible to set values at once. and how do I save the row number before I break, so that I can resume from the same row? — Shivam Bhasin, May 18 '20 at 22:07
You certainly can, see the revised answer. Going the triggers route might seem simpler, but it will result in a much more complicated solution that will take exponentially more time to execute... but you can save the row variable in a cell in the spreadsheet.... — Stykes, May 18 '20 at 22:34
Thanks, just fixing the get value did it for me, I didn't need to form an array or something. It is now processing 10k+ rows in 3 mins or so, just by eliminating get value. — Shivam Bhasin, May 19 '20 at 16:44

Google script stops after 5mins of execution

1 Answers1