"Exceeded maximum execution time in Google Apps Script" stops my script

I am worknig on one project using google sheets and I need to create one Script using google Apps Script.

I need to rewrite every row in sheet, that contain certain number. I have following code now:

function AddDuplicate() {
  var app = SpreadsheetApp;
  var ActiveSheet = app.getActiveSpreadsheet().getActiveSheet();
  //var data = sheet.getDataRange().getRow();

  var Zdroj = app.getActiveSpreadsheet().getSheetByName("ZDROJ");
  var Vysledek = app.getActiveSpreadsheet().getSheetByName("VYSLEDKY");
  var PocetRadkuVysledku = 1;
  for(var i= AktualniPoziceY ;i<500;i++)
  {
    var pocet = Zdroj.getRange(i,7).getValue();
   for(var y = 1; y<pocet+1;y++)
     {
     for(var o = AktualniPoziceX ; o < 10; o++)
       {
         var Pom = Zdroj.getRange(i,o).getValue();
         Vysledek.getRange(PocetRadkuVysledku, o).setValue(Pom);
        }
        PocetRadkuVysledku++;
     }
   }
}

It works, but only problem I got is "Exceeded maximum execution time in Google Apps Script" error, that will stop my script on some point. Can someone help, how to overcome this?

1 answer

  • answered 2020-03-25 14:32 ziganotschka

    The reason you exceed maximum execution time is that your code is slow, see Apps Script Best Practices

    How to apply it to your case:

    Rather than retrieving a range and setting values within each iteration of a nested for loop (which is inefficient due to the multiple calls to the SpreadsheetApp service), use arrays and make only one call after finishing the iteration.

    Use getValues() and setValues() instead of getValue() and setValue().

    Sample (you mind need to adapt the range dimensions to your needs, what is important is that the destination range has the same amount of rows and columns as the original one):

    function AddDuplicate() {
      var app = SpreadsheetApp;
      var ss = app.getActive();
      var Zdroj = ss.getSheetByName("ZDROJ");
      var Vysledek = ss.getSheetByName("VYSLEDKY");
      var pocetArray = Zdroj.getRange(1,7, 500, 1).getValues();   
      Vysledek.getRange(1, AktualniPoziceX, pocet, 500, 1).setValues(pocetArray);
     }