Leer API desde planilla y relacionar registros con formulas

Veamos un ejemplo donde buscamos obtener dos listados de registros en nuestra planilla leyendo una API externa. Por ejemplo un listado de Entradas (posts) y sus Comentarios (comments):

Como podemos ver en la captura, los comentarios refieren a las entradas a las que pertenecen.

Retomando el post de obtener datos de una api externa: https://nrm.uy/2021/11/leer-una-api-externa-con-apps-script-en-una-planilla/

Podemos agregar una funcion para leer la API demo de jsonplaceholder en su endpoint comments: https://jsonplaceholder.typicode.com/comments

function comentarios() { // Obtenemos datos de la API datos = apiGetBasic('comments'); // Procesamos los datos obtenidos datos = procesarComentarios(datos); // Escribimos en la hoja destino escribirRango(datos, hojaComentarios); }

Al obtener los comentarios, lo que haremos referenciar la hoja donde ya tenemos las entradas cargadas a las cuales hacen referencia los comentarios:

// Recibimos datos de API y seleccionamos los que nos interesan
function procesarComentarios(datos) {

  console.log('procesarComentarios');
  var comentariosProcesados = [];

  let iRow = 2;
  for (var i in datos) {

    console.log(datos[i]);

    comentariosProcesados.push([
      datos[i]["id"],   
      datos[i]["name"],       
      datos[i]["email"],       
      datos[i]["body"],     
      '=VLOOKUP(' + datos[i]["postId"] + ',' + hojaDatos + '!A2:B' + ', 2, 0)',       
    ]);

    iRow++;

  }

  return comentariosProcesados;

}

En esta funcion procesarComentarios, agregamos los datos de cada comentario y ademas agregamos una formula al final para buscar el titulo de la entrada a la cual pertenece el comentario, esto lo hacemos insertando un VLOOKUP indicando el postId de referencia, en la columna A de la hoja de Entradas, para traer el titulo en la columna B:

Al ejecutar el script veremos en la hoja Comentarios que se cargan los mismos y que en la columna E tenemos la funcion referida:

De esta forma podemos generar datos con referencias por mas que la API de la que disponemos no tenga en un solo endpoint todos los datos que buscamos en un solo resultado.

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.

Leer una API externa con Apps Script en una planilla

Una de las tareas más importantes, repetitivas y que ante un error pueden tirar abajo todo un proceso es la carga de datos en la planilla. Por lo general debemos exportar un informe, copiar y pegar en la planilla para iniciar el proceso.

La alternativa es usar una API (Application Programming Interface) que permite a los sistemas hablar en directo entre ellos, por lo general en texto plano con el formato JSON como estándar.

Apps Script nos permite leer una API para obtener los datos directamente desde el software que tiene la base de datos evitando el trabajo manual de copiar y pegar desde planillas, verificar formatos de columnas, puntos y comas… entre otros.

Los tres pasos básicos son:

  1. Leer datos desde la API.
  2. Procesar los datos.
  3. Escribir los datos.

function actualizar() {
  
  // Obtenemos datos de la API
  datos = apiGetBasic('posts');

  // Procesamos los datos obtenidos
  datos = procesarDatos(datos);
  
  // Escribimos en la hoja destino
  escribirRango(datos, hojaDatos);  

}

function apiGetBasic(query) {
  var apiURL = 'https://jsonplaceholder.typicode.com/';
  var USERNAME = '';
  var PASSWORD = '';  
  
  // Si la API precisa autenticacion, podemos usar usuario y clave
  var headers = {
    "Authorization" : "Basic " + Utilities.base64Encode(USERNAME + ':' + PASSWORD)
  };    
  var params = {
    "method":"GET",
    "headers":headers
  };
  
  try {
    var response = UrlFetchApp.fetch(apiURL+query, params); 
    var responseData = response.getContentText();
    var json = JSON.parse(responseData);
    return json;
  }
  catch (e) {
    Logger.log(e);
    return ["Error:", e];
  }
}

Auditoría de permisos de archivos en Google Drive

Con cada vez más archivos compartidos en la nube suele haber accesos demasiado permisivos o directamente fallas de seguridad.

Al compartir documentos en Google Drive podemos cometer el error de exponer información sensible a cualquiera que tenga el link o bien a usuarios que ya no deberian tener mas acceso.

Una vez que se acumulan los archivos puede ser imposible revisar uno a uno los archivos, para eso veamos un script de Apps Script para listar los archivos desde un directorio y todos sus subdirectorios para detectar estos casos:

https://github.com/nrodriguezm/nrm-apps-script-demos/blob/permisos-de-archivos-drive/Code.js

// Planilla activa actual
var timezone = Session.getScriptTimeZone();
var ss = SpreadsheetApp.getActiveSpreadsheet();
  
var hojaDatos = 'Archivos';
var sheet = ss.getSheetByName(hojaDatos);

var rootID = "1AwDzuyFf7gfSJWn1EaNM__FgESj9-Bla";

function onOpen() {

    // La función onOpen se ejecuta automáticamente cada vez que se carga un Libro de cálculo
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var menuEntries = [];
 
    menuEntries.push({
        name : "Leer Archivos Drive",
        functionName : "actualizar"
    });
    menuEntries.push(null);

    ss.addMenu("Actualizar", menuEntries);

}


function actualizar() {

  // Lee una carpeta Drive 
  var archivos = listarArchivos( DriveApp.getFolderById(rootID) );

  console.log(archivos);

  // Escribe los archivos leidos en Drive en la planilla
  escribirArchivos(archivos);

}

function listarArchivos(folder) { // Modified

  var filesData = [];

  var folderName = folder.getName();
  var files = folder.getFiles();

  while (files.hasNext()) {
    //var fileName = files.next().getName();
    Logger.log(folderName);

    filesData.push( leerArchivo(files.next()) );
  }

  var subfolders = folder.getFolders();
  while (subfolders.hasNext()) {
    listarArchivos(subfolders.next()); // Modified
  }

  return filesData;
}


function leerArchivo(file) {

  console.log( 'leerArchivo: ' + file.getName() );

  try {

    access     = file.getSharingAccess();
    permission = file.getSharingPermission();
    editors    = file.getEditors();
    viewers    = file.getViewers();
    
    edit = [];
    view = [];
    

    date =  Utilities.formatDate(file.getDateCreated(), timezone, "yyyy-MM-dd HH:mm")

    for (var v=0; v<viewers.length; v++) {
      view.push(viewers[v].getName() + " " + viewers[v].getEmail());
    }

    for (var ed=0; ed<editors.length; ed++) {
      edit.push(editors[ed].getName() + " " + editors[ed].getEmail());
    }

    switch(access) {
      case DriveApp.Access.PRIVATE:
        privacy = "Private";
        break;
      case DriveApp.Access.ANYONE:
        privacy = "Anyone";
        break;
      case DriveApp.Access.ANYONE_WITH_LINK:
        privacy = "Anyone with a link";
        break;
      case DriveApp.Access.DOMAIN:
        privacy = "Anyone inside domain";
        break;
      case DriveApp.Access.DOMAIN_WITH_LINK:
        privacy = "Anyone inside domain who has the link";
        break;
      default:
        privacy = "Unknown";
    }
    
    switch(permission) {
      case DriveApp.Permission.COMMENT:
        permission = "can comment";
        break;
      case DriveApp.Permission.VIEW:
        permission = "can view";
        break;
      case DriveApp.Permission.EDIT:
        permission = "can edit";
        break;
      default:
        permission = "";
    }
  
    edit = edit.join(", ");
    view = view.join(", ");

    user = file.getOwner().getName();
    users_editors = (edit === "" ? "" : edit);
    users_viewers = (view === "" ? "" : view);

  } catch (e) { Logger.log(e.toString()); Logger.log(file.getName()); };

  return [
    file.getId(),
    file.getName(),
    file.getUrl(),
    privacy, 
    user,
    users_editors, 
    users_viewers, 
    date,
    file.getSize(),
    file.getDescription(),
    file.getMimeType(),
  ];
  
}


// Recorrer e insertar en planilla
function escribirArchivos(data) {
    
  // calculate the number of rows and columns needed
  var numRows = data.length;

  if (numRows > 0) {
    var numCols = data[0].length;
                      
    // Escribir en filas nuevas antes de la fila 2 (para mantener formato)
    sheet.insertRowsBefore(2, numRows);
    sheet.getRange(2, 1, numRows, numCols).setValues(data);
  }

}

Ingreso de datos por parte del usuario en AppsScript

Al momento de ejecutar un script en nuestra planilla, suele ser útil contar con datos ingresados por el usuario los cuales modifican el comportamiento de nuestro script. Para esto podemos mostrar un cuadro de diálogo que permita al usuario ingresar datos.

Utilizando la clase Ui de la planilla mostraremos un cuadro de diálogo donde el usuario podrá ingresar datos y luego la ejecución de nuestro script cambiará de acuerdo a lo ingresado.

// Planilla activa actual
const ss = SpreadsheetApp.getActiveSpreadsheet();

// Preparamos menu para que el usuario pueda actualizar desde la planilla sin entrar al editor de codigo
function onOpen() {

    // La función onOpen se ejecuta automáticamente cada vez que se carga un Libro de cálculo
    var menuEntries = [];
 
    menuEntries.push({
        name : "Enviar mensaje",
        functionName : "enviar"
    });
    menuEntries.push(null);

    ss.addMenu("Actualizar", menuEntries);
}

function enviar() {
  var ui = SpreadsheetApp.getUi(); 

  var result = ui.prompt(
     'Cual es tu nombre?',
     'Ingresa los datos:',
      ui.ButtonSet.OK_CANCEL);

  // Procesar la respuesta del usuario
  var button = result.getSelectedButton();
  var nombre = result.getResponseText();

  if (button == ui.Button.OK) {

    console.log('OK');
    notificar(nombre);

  } else if (button == ui.Button.CANCEL) {

    console.log('CANCEL');

  } else if (button == ui.Button.CLOSE) {
    
    
    console.log('CLOSE');

  }
  
}

function notificar(nombre = '') {

  console.log('Estamos en notificar, console log: ' + nombre);

  if (nombre.length > 0)
    ss.toast('Estamos en notificar', 'Tu nombre es: ' + nombre, 3);
  else
    ss.toast('Estamos en notificar', 'Otro dia nos conoceremos :) ', 3);

}

Notificaciones al usuario con cuadros de diálogo

Antes de ejecutar código mediante Apps Script suele ser una buena idea obtener la confirmación por parte del usuario de que quiere realizar la acción. Por ejemplo si vamos a enviar un email con un informe podemos mostrar los destinatarios del correo antes de realizar el envío.

Para esto podemos utilizar cuadros de diálogo con botones de confirmación o cancelación. Para esto utilizaremos la clase Ui de la planilla lo cual nos da acceso a una serie de diálogos estándar. Por ejemplo el diálogo “Alert” nos permite mostrar un mensaje que pide confirmación o cancelación al usuario y obtendremos la respuesta en el código.

function enviar() {
  var ui = SpreadsheetApp.getUi(); 

  var result = ui.alert(
     'Confirmar',
     'Esta seguro de continuar?',
      ui.ButtonSet.YES_NO);

  // Process the user's response.
  if (result == ui.Button.YES) {

    // User clicked "Yes".
    ui.alert('El usuario acepto.');

    notificar();    

  } else {
    // User clicked "No" or X in the title bar.
    ui.alert('El usuario cancelo.');
  }
}

function notificar() {

  console.log('Estamos en notificar, console log.');
  ss.toast('Estamos en notificar', 'Apps Script', 3);

}

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.

Adjuntar planilla de Google como Excel XLSX en un correo con Apps Script

En la medida que generamos informes automatizados puede ser buena idea contar con copias diarias de los mismos que lleguen a las partes interesadas. Por ejemplo podemos enviar correos con copias adjuntas de planillas en la nube de forma de contar con una copia o foto del informe en una fecha. También puede ser útil para compartir la información de la planilla pero sin necesidad de otorgar acceso a la misma a terceros.

Utilizaremos la función de exportar el documento a Excel XLSX desde Apps Script, lo cual descarga la planilla como un archivo excel que adjuntamos en un correo electrónico.

Para lograr esto desde Apps Script utilizaremos la funcion UtlfetchApp.

function enviarCorreo() {

    var token = ScriptApp.getOAuthToken(); 
    var nombrexlsx = "Informe adjunto.xlsx";

    var AdjuntoExcel = UrlFetchApp.fetch(
        'https://spreadsheets.google.com/feeds/download/spreadsheets/Export?key=' + ssID + '&exportFormat=xlsx',
        { 
          headers : {Authorization : 'Bearer '+ token}
        }
      )
      .getBlob()
      .setName(nombrexlsx);
                                        
    var OpcionesAvanzadas = { name: "Informe Adjunto", htmlBody: emailText , attachments: [AdjuntoExcel]};
    
    // Confirmamos envio con usuario
    var ui = SpreadsheetApp.getUi(); 

    var result = ui.alert(
      'Confirme envio de reporte',
      'Destinatarios: ' + emailTo,
      ui.ButtonSet.YES_NO
    );

    if (result == ui.Button.YES) {
    
      GmailApp.sendEmail(emailTo, emailSubject, emailSubject, OpcionesAvanzadas);      
      SpreadsheetApp.getActiveSpreadsheet().toast("Se envio el correo",'', 10);
    
    }

}

Escribir datos en Firestore mediante functions y requests externos

En este video analizaremos la generación de datos y su almacenamiento en Firestore, mediante la utilización de un proyecto Firebase y Cloud Functions. Firestore es la base de datos no relacional de Firebase, Cloud Functions es un servicio de funciones a demanda serverless, es decir que no requiere un alojamiento dedicado.

El primer paso sera crear un proyecto en Firebase. Hecho esto debemos activar Firestore. Luego al habilitar cloud functions se nos pedirá una cuenta de facturación para utilizar cloud functions. Es posible usar en forma limitada las funciones sin cuenta de facturación pero no podremos llamar APIs externos desde cloud functions si no lo hacemos. 

Luego de pasar el proyecto Firebase del plan Spark básico a un plan de pago por uso, deberemos instalar las librerías locales de node para desarrollar las funciones en nuestra pc local.

Utilizaremos los pasos de la guía básica de referencia de cloud functions disponible en la ayuda de google y añadiremos algunos pasos adicionales para guardar datos en Firestore.

https://firebase.google.com/docs/functions/get-started
https://nodejs.org/

npm install -g firebase-tools

Debemos iniciar sesión en la línea de comandos usando las herramientas de firebase tools recién instaladas. Para hacerlo nos dirigimos a la línea de comandos y ejecutamos el comando firebase login.

Es recomendable iniciar el proyecto de cloud functions en un directorio de código y a su vez en un directorio separado por proyecto. También inicializar un repositorio git para dar seguimiento a los cambios del código.

Podemos copiar el código de ejemplo del link al blog en los comentarios o en el repositorio de referencia:

https://github.com/nrodriguezm/flujo-de-datos-firestore-apps-script-data-studio/commits/master