Crear informes automáticos con Data Studio y planillas en la nube

La entrega de informes periódicos es una de las tareas típicas en todos los niveles de organización. La consistencia de los mismos es crucial para evitar que cada informe tengas diferencias que nos hacen dudar del mismo y gastar tiempo en entenderlo en lugar de analizar.

Una buena forma de sistematizar la entrega de informes es utilizar las siguientes herramientas combinadas para automatizar la generación de informes:

  1. Planillas en la nube con una estructura definida de antemano.
  2. Apps Script para importar informacion desde APIs, leer correos desde nuestras casillas en forma automatica o importar desde otras planillas o Excel de sistemas offline.
  3. Data Studio para leer la planilla como si fuera una base de datos.

El primer paso es definir una estructura general de planilla, definiendo las categorias o areas y el periodo de tiempo, por ejemplo un año dividido en meses o semanas. Hecho esto debemos generar hojas aparte que alimenten a ese cuadro, estas hojas aparte será donde importaremos informacion desde otras fuentes.

El segundo paso es utilizar formulas como IMPORTRANGE (que tiene sus limitaciones, es para algunos cientos de celdas) o bien Apps Script para copiar rangos o leer archivos XLSX. Es importante definir controles de consistencia de datos, para evitar que por ejemplo los XLSX subidos sean distintos cada vez.

Por último una vez que nuestra planilla de Drive es consistente pasamos a Data Studio para leer la misma como si fuera una base de datos, con la ventaja de que podemos ver la información desplegada y modificarla para hacer pruebas.

En Data Studio vamos al menu «Recurso» y accedemos a «Administrar las fuentes de datos…». Clic en «Añadir una fuente de datos» y buscamos la opción «Hojas de cálculo de Google». Buscamos la Hoja de Cálculo e indicamos la hoja en particular que vamos a usar. Podemos agregar distintas hojas como distintas fuentes.

Una vez agregada la fuente de datos debemos verificar que cada campo tiene el Tipo adecuado. Por ejemplo los campos con los cuales queremos graficar valores, sumar, promediar, debemos inficar Tipo: Numero. Para los campos por los cuales vamos a distribuir en los ejes, por grupos o periodos de tiempo debemos indicar Tipo: Texto o alguno de los de Fecha según nuestros datos.

Una buena idea es aplicar cálculos a los campos como números para redondear o pasar a millones o miles si los números son muy grandes, esto lo hacemos agregando campos calculados:

Los campos calculados se indican con un fx, clic en el icono para editarlo.
Podemos usar formulas y calculos.

Luego utilizamos estos campos calculados en las gráficas:

Dimension del periodo: las fechas. Dimension: las categorias. Métrica: los numeros a sumar.

Cada vez que actualizamos los datos de la planilla fuente de datos hacemos clic arriba a la derecha para actualizar y listo:

Lo interesante es que el informe se visualiza como una página web simple y no requiere de utilizar formulas avanzadas. Podemos aplicar filtros a las gáficas simplemente agrupando los mismos para indicar cuales filtros aplican a cuales gráficas:

Por último para los campos calculados podemos utilizar la función CASE para realizar un Si lógico, es decir presentar la información que viene de la planilla con un texto distinto de acuerdo a los valores. Por ejemplo si creamos un campo calculado podemos tomar otro campo y presentar distintas opciones para mejorar la lectura:

Así en las graficas y filtros podemos ver textos en lugar de números o códigos.

Copiar rangos de datos entre planillas de Google Spreadsheets

Una de las funciones que suelen atraer a muchos a utilizar planillas en la nube es IMPORTRANGE que nos permite importar un rango de celdas desde otra planilla. Cuando la fuente remota se actualiza la planilla que usa IMPORTRANGE «ve los cambios» e importa el rango actualizado.

Es análogo a los orígenes de datos de Excel pero solo que en la nube.

Las dos limitaciones que suelen aparecer usando IMPORTRANGE se dan cuando:

  1. Se quieren importar rangos demasiado grandes y se alcanza el limite de la función IMPORTRANGE.
  2. El origen de datos va cambiando muy seguido y nuestra planilla no termina nunca de cambiar…

Cuando suceden estos problemas es probable que sea conveniente utilizar un script para copiar los datos entre planillas, podemos crear un menu para iniciar la ejecución a demanda.

Al usar Apps script para copiar entre planillas tenemos la capacidad de copiar rangos practicamente ilimitados con mejor performance que usando la formula IMPORTRANGE. Definamos una planilla de origen por su link y las columnas a copiar:


// Link a planilla desde la cual vamos a copiar hacia la planilla actual
var linkPlanillaOrigenDatos = ‘https://…’;


// Columnas a copiar desde la hoja de origen, para poder cambiar el orden, indice inicia en 1
var arrColsOrigen = [3, 1, 20, 6];

// Cada hoja de la planilla de origen

for (i in sheets){
  var sheetName = sheets[i].getSheetName().substring(0,15);

  // Si en origen estamos en la hoja que queremos copiar
  if (sheetName == hojaBDOrigen) {
    SpreadsheetApp.getActiveSpreadsheet().toast(‘Copiando datos ‘+hojaBDDestino+’…’, ‘Estado’);
    var sourcesheet = sourceSS.getSheetByName(sheetName);


    // Cada columna a importar
    for (var iCol in arrColsOrigen) {
      // Leemos
      var sourcerange = sourcesheet.getRange(2,arrColsOrigen[iCol],sourcesheet.getLastRow()-1,1);
      var sourcevalues = sourcerange.getValues();
      // Escribimos
      var destrange = destsheet.getRange(iniRow,colPos,sourcevalues.length,1);
  destrange.setValues(sourcevalues);
      colPos++;
    }

….

Este script lo ejecutamos adjunto a una planilla. Lo primero es obtener el enlace completo de la planilla desde la cual vamos a copiar los datos. Luego indicamos las columnas a copiar. Cuando accedemos a la planilla de origen debemos recorrer las hojas e identificar la hoja que es fuente de informacion. Una vez que estamos en la hoja de origen copiar las columnas en el orden que necesitamos a la planilla actual.

Para ver el código completo en Github:

Demo3 – Copiar rangos entre planillas
https://github.com/nrodriguezm/app-scripts-demo/blob/master/demo3-copiar-rangos-planillas/Code.js