Home / Questions / 260432
Accepted
Iván Montero
Asked: 2020-05-03 23:43:47 +0800 CST 2020-05-03 23:43:47 +0800 CST

Optimize script or increase execution time in a google spreadsheet

  • 772

This would be the second part of a post that I made recently: Script traversal of rows and columns

In this part I have added an array, so that I go through certain sheets and later make a loop over them and execute the previous code.

function myFunction() {

  var hojas = [

    "Calvià",
    "Inca",
    "Manacor",
    "LLucmajor",
    "Sóller",
    "Artà",
    "Alcudia",
    "Felaitx"

  ]

  var nombres = 2;
  var parque = 2;
  var posiciong = 2;
  var posiciongr = 2;

  for (i=0; i<hojas.length + 1; i++){

    var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(hojas[i]);
    var lastrow = ss.getLastRow();

    for (r=7; r<lastrow+1; r++){

        var G = 0;
        var GR = 0;
        var range = ss.getRange(r,1).getValue();

        if (range!=0){

          var range2 = ss.getRange(r,4).getValue();
          SpreadsheetApp.getActiveSpreadsheet().getSheetByName("ContadorHoras").getRange(nombres, 1).setValue(range);
          SpreadsheetApp.getActiveSpreadsheet().getSheetByName("ContadorHoras").getRange(parque, 2).setValue(range2);
          nombres = nombres + 1;
          parque = parque +1;

          for (c=6; c<93; c++){

            var rangec = ss.getRange(r,c).getValue().toString();

            if (rangec=="G"){

              G = G + 1;

            }if (rangec=="GR"){

              GR = GR + 1;

            }

          }

          SpreadsheetApp.getActiveSpreadsheet().getSheetByName("ContadorHoras").getRange(posiciong, 3).setValue(G);
          SpreadsheetApp.getActiveSpreadsheet().getSheetByName("ContadorHoras").getRange(posiciongr, 4).setValue(GR);
          posiciong = posiciong + 1;
          posiciongr = posiciongr + 1;

        }

    }

  }

}

I think the code is fine, but the problem is that it takes more than 5 minutes to execute completely and it gives me execution time finished. The question is if it would be possible to optimize the script so that it takes less or increase the execution time, I have read that this second option cannot be done.

google-apps-script

1 Answers

  1. Best Answer

    Google Apps Script services like SpreadsheetApp are "expensive" in terms of runtime so it is advised whenever possible to use methods that return arrays like setValuesinstead of those that return point values ​​like setValue.

    In particular, consider instead of using a 2D Array setValuein your statement and passing all the values ​​with only one after that statement.forsetValues

    Another thing that might help is that instead of calling SpreadsheetApp.getActiveSpreadsheet().getSheetByName("ContadorHoras")every time you call getRangeassign this to a variable.

    My practice is to minimize the use of strings, to some extent because this helps code debugging when needed and because having short statements makes the code easier to read. I think that this also helps the execution optimization that Google Apps Script performs internally, but I have not tested it.

    As for increasing the time limit, G Suite accounts are limited to 30 minutes.

    Related

    • 1