Como optimizar la escritura en sheets con Google Apps Script


Artículo relacionado con la optimización de código de Google Apps Script

Una cosa muy típica que nos pasa a todos los que programamos en Google Apps Script al empezar es que tenemos que escribir información en un spreadsheet y está operación se vuelve horriblemente lenta.

De hecho aún recuerdo en alguno de mis primeros scripts en que físicamente podía ver como iba escribiendo casilla a casilla los valores en cada celda.

Si el tiempo no nos preocupa , ok, pero generalmente nos interesará que nuestros scripts se ejecuten en el mínimo tiempo posible.

Una manera muy sencilla de optimizar la escritura en spreadsheets es utilizar SetValues en vez de SetValue. Con un ejemplo lo veremos más claro.

Imaginemos que tenemos que hacer una función que nos imprima un contador incremental en una matriz de NxN , donde N es un parámetro que nos pasan a la función.

La forma "novata" de implementarlo sería la siguiente:

function TestVelocidadSetValue(n)
{
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var cont = 0;
  for(var i = 0 ; i < n ; ++i)
  {
     for(var j = 0 ; j < n ; ++j)
     {
        sheet.getRange(i+1, j+1).setValue(cont++);
     }
  }
}

Para valores pequeños de N no habría mucho problema, pero si N fuera grande (200 por ejemplo) la función tardaría bastante. Para solucionar esto podríamos usar un código un poco más "profesional":

function TestVelocidadSetValues(n)
{
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var cont = 0;
  var buffer = new Array(n);
  for(var i = 0 ; i < n ; ++i)
  {
    buffer[i] = new Array(n);
     for(var j = 0 ; j < n ; ++j)
     {
       buffer[i][j] = cont;
       cont++;
     }
  }

   sheet.getRange(1, 1,n,n).setValues(buffer);
}

Todo lo que hemos hecho es no imprimir directamente sobre el spreadsheet sino guardar todos los valores en un buffer (un array bidimensional) para luego volcar este en el spreadsheet.

Cual es la mejora? La siguiente tabla os dará la comparación de tiempos en segundos .

NSetValueSetValuesRatio
100,1250,0373,378378378
10010,0120,26837,35820896
20039,6762,38516,63563941

Como podéis observar para casos pequeños la mejora ayudará a optimizar el rendimiento general de la aplicación pero para casos grandes significará la diferencia entre poder hacer algo a tiempo real o no, o incluso podrá marcar la diferencia entre que nuestra función sea ejecutable o no (pensad que aquí solo imprimimos un número pero podría ser que tuviéramos que calcular algo más complejo y que el total de tiempo excediera el máximo de tiempo permitido por ejecución)

Espero que os sirva . Si tenéis dudas al respecto no vaciléis en poner un comentario.

Nos vemos


2 comentarios:

  1. Muchísimas gracias por la aportación!!

    Aquí os dejo un pequeño manual con otras dos formas de adaptar algunos procesos de escritura: https://www.linkedin.com/pulse/c%C3%B3mo-resolver-el-problema-de-m%C3%A1ximo-tiempo-ejecuci%C3%B3n-alcanzado-prada?trk=mp-reader-card

    Enhorabuena por el blog.
    Un saludo
    Javier

    ResponderEliminar
  2. Hola muy buenas, muchas gracias por el aporte, tengo este script para rellenarme una columna con la formula today() para establecer una fecha. Es super lento, va rellenando celda a celda, fila a fila y cuando lleva unas 150 se detiene porque ha superado el tiempo máximo de ejecución. He intentado acelerarla pero no soy capaz. No se si me podrías echar una mano.

    function Fechas_NUEVOS(){
    var app = SpreadsheetApp;
    var activeSheet = app.getActiveSpreadsheet().getActiveSheet();

    // Selecciona la columna en la que buscar la primera celda en blanco

    var columnToCheck = activeSheet.getRange("A:A").getValues();


    // Obtiene la última fila de la columna anterior

    var lastRow = getLastRowSpecial(columnToCheck);

    // Pone fecha de hoy + 15 días en productos que no tienen fecha de nuevo y que estan como publicados "1"

    // i es el indice de la fila actual
    for(var i=2;i<=lastRow;i++) {

    var workinCell = activeSheet.getRange(i, 32).getValue();
    var publicado = activeSheet.getRange(i, 44).getValue();

    if(workinCell == "" && publicado == 1){activeSheet.getRange(i, 32).setFormulaR1C1('=TODAY()+15');
    }
    }
    }

    function getLastRowSpecial(range){
    var rowNum = 0;
    var blank = false;
    for(var row = 0; row < range.length; row++){

    if(range[row][0] === "" && !blank){
    rowNum = row;
    blank = true;
    }else if(range[row][0] !== ""){
    blank = false;
    };
    };
    return rowNum;
    };


    Muchas gracias.

    ResponderEliminar

Tal vez te interese