Cómo optimizar la lectura de spreadsheets con Google Apps Script

• <<<< Como optimizar la escritura en spreadsheets mediante SetValues de Google Apps Script
• Como autorellenar campos de un spreadsheet con Google Apps Script >>>>

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

No hace mucho expliqué como optimizar las escrituras dentro de un spreadsheet, pero cuando hacemos una web dinámica con google apps script realmente lo que necesitamos es optimizar las lecturas dado que generalmente las webs que haremos serán extracciones de datos de hojas de cálculo de google.

La idea para optimizar las lecturas de hojas de cálculo es muy parecida a la usada para optimizar las escrituras, en vez de hacer la operación x veces la hacemos 1 sola vez y en vez de recoger los datos de toda la hoja , obtendremos los datos solo de la columnas y filas que nos interesen.

La lectura como tal mediante getDataRange es bastante rápida así que no podemos esperar ratios tan escandalosos como cuando optimizábamos las escrituras pero tal vez este truco pueda dar ese pequeño empujón a la aplicación para que vaya más fluida.

El código en versión "novata" y en versión optimizada lo tenéis aquí:

Versión "novata":

function TestVelocidadGetDataRange()
{
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var range = sheet.getDataRange();
  var values = range.getValues();

  //Aquí pondriamos nuestro código que trabaja con los valores de la hoja de cálculo
}

Versión optimizada:

function TestVelocidadGetRange()
{
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var range = sheet.getRange(2, 1, 10);
  var values = range.getValues();

  //Aquí pondriamos nuestro código que trabaja con los valores de la hoja de cálculo
}

Esta optimización se tiene que revisar bien para el tipo de datos que uses dado que la mejora de rendimiento depende del número de columnas de nuestra hoja. Si tenemos pocas columnas casi puede resultar más beneficioso usar el acceso "no óptimo".

Esta optimización solo tiene sentido si de una hoja muy grande solo queremos leer una columna (buscar ID's, ordenar datos, etc...) cosa que se usa bastante al hacer desplegables dentro de una web dinámica. Si nuestro objetivo es trabajar con bastantes columnas a la vez o bien el documento tiene pocas columnas se tiene que probar bien que realmente de beneficio.

A nivel de ejemplo, imaginando que tenemos una hoja de cálculo con 30 columnas de las que solo nos interesa 1 podéis ver la mejora de tiempos de esta optimización:

LECTURAS EN LAS QUE SOLO INTERESA UNA COLUMNA DE 30
N	GetDataRange	GetRange	Ratio
10	0,114	0,043	2,651162791
100	0,152	0,074	2,054054054
200	0,188	0,096	1,958333333
1000	1,687	0,75	2,249333333

Como os he dicho la mejora no es abismal, pero da un poco de margen para que nuestros tiempos de carga disminuyan

Cualquier cosa, ya lo sabéis.

Nos vemos

NOTA: Si te ha gustado este tutorial tal vez también te interese el tutorial sobre las diferentes maneras de obtener una fila de un spreadsheet con Google Apps Script

Como optimizar la escritura en sheets con Google Apps Script

• <<<< Cómo poner imagenes y tablas en la cabecera de docs con Google Apps Script
• Cómo optimizar la lectura de spreadsheets mediante GetValues de 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 .

N	SetValue	SetValues	Ratio
10	0,125	0,037	3,378378378
100	10,012	0,268	37,35820896
200	39,676	2,385	16,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