Propiedades para guardar datos en apps script

Hasta el momento hemos ejecutado scripts en la nube en forma aislada, cada script corre una vez pero no tiene forma de dejar un registro o leer registros de la ejecución anterior más allá de usar una hoja de la planilla adjunta. 

Supongamos usando el ejemplo anterior de envío de informe con pdf adjunto, que queremos evitar enviarlo múltiples veces en el mismo día. Si nuestro programador de tareas corre cada algunas horas, tenemos que poder detectar que el envío ya se hizo para el día de hoy.

Una solución elegante a este problema es utilizar el servicio de propiedades, PropertiesService, el cual nos permite guardar valores que pueden ser recuperados por una siguiente ejecución del script.

Para acceder al servicio de propiedades tenemos tres opciones o niveles:

  1. Script Properties: Propiedades a nivel de script: disponible para todo el script y todo usuario que ejecute el script.
  2. User Properties: Propiedades a nivel de usuario: disponible solo para el usuario que guarda los datos.
  3. Document Properties: Propiedades a nivel de documento: disponible solo para el documento asociado al script (por ejemplo la planilla).

Nota: Es importante tener en cuenta que este servicio guarda los valores como clave => valor de texto (string), por lo que al guardar un número por ejemplo, deberemos convertirlo nuevamente a tipo número cuando lo consultemos de las propiedades.

En nuestro caso nos interesan los dos primeros modos, las propiedades a nivel de script que serán visibles por todo usuario que utilice el script y las de nivel de usuario para que cada usuario pueda guardar datos propios. 

Utilicemos las propiedades a nivel de script (Script Properties). Volviendo a nuestro ejemplo de enviar el informe diario con un PDF adjunto. Queremos que el activador corra cada hora pero solo envie si han pasado cierta cantidad de horas desde el ultimo envío.

Para esto agregamos una función verificarEnviado en la cual guardaremos una marca de tiempo en milisegundos con el último envío de email y en cada ejecución siguiente compararemos esa marca de tiempo para saber si debemos volver a enviar o esperar.

Con un objeto Date, obtenemos los milisegundos actuales con getTime. Usaremos esta marca hoy_milis para comparar con la marca de la ejecución anterior. 

En la primera ejecución, no tenemos una marca anterior guardada por lo que debemos verificar si ultimoEnvio obtenido de las propiedades del script es nulo. Si es asi procedemos a hacer el envío y guardamos la marca de tiempo por primera vez.

En las siguientes ejecuciones obtendremos la marca de tiempo anterior y procedemos a comparar los milisegundos, en caso de que la última marca más las horas de espera sea mayor a la actual, esperamos y no haremos el envío de email.

function verificarEnviado() {

  // Cuantas horas queremos esperar entre envios? 
  // Las marcas trabajan en milisegundos
  // Modifcar el 1 para cambiar la cantidad de horas
  const horas_espera = 1 * 60 * 60 * 1000;

  const hoy = new Date();
  const hoy_milis = hoy.getTime();
  Logger.log('hoy_milis: ' + hoy_milis);

  // Buscamos la marca en milisegundos del ultimo envio
  const scriptProperties = PropertiesService.getScriptProperties();
  // Para obtener un numero (int) nuevamente convertimos de string a int
  const ultimoEnvio = scriptProperties.getProperty('MAIL_SENT_ON') * 1;
  Logger.log('MAIL_SENT_ON: ' + ultimoEnvio);


  // Si no hay marca de envio o si la marca es menor a la marca actual en cantidad: horas_espera
  if (ultimoEnvio === null || (ultimoEnvio + horas_espera) < hoy_milis ) {

    // Seteamos nueva marca en propiedades del script para consultar en proxima ejecucion
    scriptProperties.setProperty('MAIL_SENT_ON', hoy_milis);
    Logger.log('seteamos nuevo MAIL_SENT_ON: ' + hoy_milis);

    return true;

  } else {

    if (ultimoEnvio !== null) {

        const suma = ultimoEnvio + horas_espera;
        Logger.log('suma: ' + suma + ' > ' + hoy_milis);

    }

    return false;
  }

}

Funciones con nombre en Hojas de Calculo de Google

A medida que realizamos fórmulas más complejas y queremos reutilizar las mismas, nos encontramos con la dificultad de escribir correctamente la fórmula cada vez. Además debemos repetir en cada planilla y celda la misma fórmula, que en caso de necesitar algún ajuste debe ser actualizada en todos los lugares donde fue usada. 

Esto hace difícil mantener las planillas, compartir fórmulas y aprovechar el análisis de datos en lugar de estar revisando fórmulas en forma continua.

Para estos casos es que tenemos la posibilidad de utilizar las funciones con nombre, lo cual nos permite definir una función y reutilizarla cuantas veces precisemos. Así escribimos una única vez la fórmula y luego la invocamos con los parámetros.

Por ejemplo, si queremos reutilizar una fórmula sencilla como la siguiente:

=SPARKLINE(B3:O3,{«charttype»,»bar»;»max»,20})

Esta sencilla fórmula de gráfico de una celda recibe un rango de datos como parámetro. Para crear nuestra primera fórmula con nombre vamos al menú superior, Datos > Funciones con nombre. Veremos la barra lateral para definición de fórmulas y hacemos clic en “Agregar nueva función”.

El primer campo a completar será el nombre de la función, ingresamos un nombre personalizado por ejemplo BARRITA.

El segundo paso nos permite ingresar una descripción, esto es importante ya que a medida que acumulamos funciones podemos olvidar la razón por la cual creamos esta función con nombre en particular. Podemos ingresar algo sencillo pero que ilustra, por ejemplo: “Recibe un rango de celdas y grafica una barra con máximo de valor 20”.

El tercer paso es definir los parámetros, es decir el o los valores que vamos a pasar a esta función. Escribiremos el nombre de parámetro y presionamos enter. Noten que solo definimos el nombre del parámetro y no de qué tipo es o qué valores puede tomar. Solo es un parámetro con nombre. Pueden ver que podemos agregar cuantos parámetros sean necesarios, cada uno con su nombre.

rango

El cuarto y último paso de la definición es hacer uso de los parámetros en la definición de la fórmula en sí. Para esto copiamos la fórmula original que estamos reemplazando y debemos sustituir los valores por nuestros parámetros con nombre.

=SPARKLINE(B2:O2,{«charttype»,»bar»;»max»,20})

=SPARKLINE(rango,{«charttype»,»bar»;»max»,20})

Hacemos clic en Siguiente para ver el paso de revisión y vista previa de la fórmula, donde podemos completar la ayuda al usuario de esta fórmula. Es la ayuda que vemos al desplegar más detalles de cualquier forma y nos ilustra con descripciones de argumento y ejemplo de fórmula. Al momento de crear una fórmula es buena idea poner todo el detalle posible ya que es posible que utilicemos esta fórmula por mucho tiempo y que no recordemos los detalles que nos llevaron a crearla.

Rango de celdas a graficar.

B2:O2

Al hacer clic en Crear función la misma pasará a estar disponible en la planilla. Podemos escribir la función BARRITA en lugar de la función anterior y veremos el mismo resultado.

Reutilizar desde otras planillas   

Para utilizar fórmulas con nombres creadas en otra planilla, simplemente vamos al menú superior Datos > Funciones con nombre, al abrirse la barra lateral haremos clic en “Importar función”, lo que nos permite seleccionar la planilla en la cual definimos la fórmula y así utilizarla. De esa forma evitamos volver a definir las fórmulas en cada planilla y realmente logramos colaborar y reutilizar.

Al seleccionar la hoja de cálculo fuente, podemos indicar que funciones queremos importar de la misma.

Al hacer esta selección veremos las funciones con nombre disponibles en nuestra nueva hoja de cálculo sin necesidad de volver a definirlas.

Datos combinados de varias fuentes de datos en Data Studio

Por lo general utilizamos una fuente de datos conectada a los gráficos de un informe. Sin embargo en algunas ocasiones podemos precisar graficar datos de dos planillas distintas pero que están unidas por alguna columna en común.

Repasemos los datos de la planilla de casos por semana de cada país. Como podríamos incorporar datos relevantes al informe, como puede ser el promedio de casos de todos los países para cada semana.

En estos casos lo más probable es contar con otra hoja o planilla de datos donde tendremos los promedios semanales. La columna “semana” sería la columna en común entre las dos fuentes de datos. Podemos hacer un cálculo rápido con un listado de las semanas del periodo y luego aplicando un promedio al Filtro de datos de la semana.

Agregamos la fuente de datos de promedios globales por semana.
Recurso > Gestionar las fuentes de datos añadidas

Buscamos la planilla y seleccionamos la hoja de promedios.

Luego debemos unir las dos fuentes de datos por su columna en común, en este caso la columna de semana. Para esto accedemos a datos combinados en:

Recurso > Gestionar datos combinados.

Para crear la combinación de datos agregamos la nueva fuente y seleccionamos la semana como Tecla de unión o Key, esta es la columna que tienen en común los dos informes. Luego en Dimensiones agregamos las columnas de datos relevantes. En este caso sería Promedio.