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);

}

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

Leer datos de Firestore a una planilla de Google

Al ser Firestore una base de datos no relacional puede ser un poco engorroso navegar los datos dentro de la consola de Firebase.

Una buena forma de visualizar, filtrar y validar los datos que ingresan a Firestore es con una simple planilla donde podemos aplicar fórmulas en un formato conocido.

Para lograr esto en una planilla de calculo de Google, vamos a utilizar la siguiente libreria: FirestoreGoogleAppsScript
https://github.com/grahamearley/FirestoreGoogleAppsScript

Codigo de referencia del video, verificar que contamos con una hoja llamada «Registros» donde se escribiran los datos:


const hojaRegistros = 'Registros';

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 Datos",
        functionName : "update"
    });
  
    menuEntries.push(null);
  
    ss.addMenu("Actualizar Datos", menuEntries);
}

function update() {  
  getFireStore();

}

function writeInSpreadSheet(data, current_sheet) {
  var numRows = data.length;
  if (numRows > 0) {
    var numCols = data[0].length;
	
	  var Avals = current_sheet.getRange("B1:B").getValues();
	  var last_row = Avals.filter(String).length;
	  last_row++;
    current_sheet.getRange(last_row, 1, numRows, numCols).setValues(data);
  }
}


function getFireStore() {

 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheetByName(hojaRegistros);
 const allDocuments = firestore.getDocuments("registros");

 var data = [];

 // for each column and row in the document selected
 for(var i = 0; i < allDocuments.length; i++){

  var document_key = allDocuments[i].name.split("/").pop();
  var nombre = allDocuments[i].fields["nombre"].stringValue;
  //var agregado = new Date(allDocuments[i].fields["agregado"].timestampValue).toISOString();

  data.push([
    document_key,
    nombre,
    //agregado,
  ]);

 }

 if (data.length > 0) {  
  // write to ss    
  writeInSpreadSheet(data, sheet);
 }

}

Por ultimo debemos agregar un archivo adicional con el contenido de la cuenta de servicio, para luego inicializar la libreria en una variable firebase:

var config = {

  "project_id": "ejemplo-escribir-leer-drive",
  "private_key_id": "0451f9......891",
  "private_key": "-----BEGIN PRIVATE KEY-----\n....",
  "client_email": "flujos-de-da......ount.com",
  "client_id": "102555....",

 };

 var firestore = FirestoreApp.getFirestore(config.client_email, config.private_key, config.project_id);

Formulas configurables en hojas de calculo Drive usando MATCH

Al momento de preparar informes suele suceder qur vamos cambiando algun criterio por el cual queremos acumular datos, sumar, agrupar.

Una formula que permite hacer sencillo de mantener y actualizar los criterios utilizados es la formula MATCH, que nos permite definir una condicion como si fuera un IF pero para una serie de datos y definiendo los valores en un rango fuera de la formula.

Si cada vez que cambia un criterio es necesario actualizar formulas podemos tener errores de formulas que no se actualizan o quedan inconsistentes.

=SUM(FILTER(BD!G:G,MATCH(BD!C:C,Datos!A2:A5,0)))

Este formula suma todos los elementos que cumplan la condicion que la columna C tiene un valor de los presentados en Datos A2:A5

De esta forma podemos configurar nuestra formula cambiando los datos en la hoja Datos, si agregamos o quitamos valores del rango A2:A5 cambiara el resultado de la formula.

Supongamos que tenemos una lista de propiedades para las cuales queremos sumar el precio:

Queremos sumar la columna G de las propiedaes, pero solo las de determinados tipos definidos en la columna C.

En una hoja auxiliar definidos los tipos que queremos sumar:

Es recomendable usar una hoja auxiliar para mantener el orden claro.

Por ultimo realizamos la formula utilizando MATCH y haciendo referencia al rango A2:A5 para comparar con la columna C de Tipo:

La formula SUM, SUMA el resultado de FILTER. FILTER trae la columna G cuando la columna C tome uno de los valores definidos en Datos!A2:A5

Un detalle importante al utilizar la formula MATCH, es que sin el tercer parametro, 0 en la captura de ejemplo, nos traera resultados que no sean identicos. Es importante siempre incluir este tercer parametro para obtener coincidencias exactas.

Pueden ver una planilla de ejemplo en el siguiente link:

https://docs.google.com/spreadsheets/d/1j5qVHDJvsuceGRczu90CXfU34pXcE8dk-cueKfx4ES4/edit?usp=sharing

Restringir ejecución de apps script por usuario o dominio

A medida que se desarrollan herramientas y automatizaciones en la organización es posible que se maneje información sensible que solo deba ser ejecutado por ciertos usuarios o areas de la empresa.

En estos casos podemos limitar la ejecución de apps script mediante el archivo manifest.json definiendo la configuración y acceso del script.

Según la documentación del manifest debemos agregar en el parametro webapp la propiedad access restringiendo por ejemplo al dominio, asi si corremos los scripts con un usuario @empresa podemos evitar que usuarios externos a la organización utilicen los mismos.

En el editor de script.google.com, abrimos el menu Ver para mostrar el archivo de manifiesto:

Abrimos el archivo appscript.json y debemos agregar dentro de webapp lo siguiente:

«webapp»: {
«access»: «DOMAIN»,
«executeAs»: «USER_DEPLOYING»
},

Quedando de la siguiente forma:

El parametro executeAs nos permite definir a nombre de que usuario se ejecuta el script, puede ser a nombre del desarrollador que hace el deploy o bien a nombre del usuario que pide la ejecución.

Leer mails de Gmail e insertar en una planilla de Drive

El correo suele ser la forma típica de comunicación para eventos de facturación, impuestos, relaciones oficiales y otras que suelen perderse en bandejas de entrada son cientos de correos sin leer.

Una forma práctica de procesar cadenas de mail que no se deben perder es procesar todos los mensajes de una casilla y generar una planilla donde dar seguimiento a todos los correos sin excepción.

Vamos a utilizar la API de Gmail para leer los mensajes en App Scripts via GmailApp:
https://developers.google.com/apps-script/reference/gmail/gmail-message

El primer paso es realizar una busqueda para obtener las conversaciones pertinentes, luego recorrer los hilos y dentro de los hilos los mensajes:

var searchTerm = «Google Cloud»;
var threads = GmailApp.search(searchTerm,0,20);
//Logger.log(«Search term: «+searchTerm);

var mailsData = [];
var arrIdsMails = [];

// Conversaciones
for (var i = 0; i < threads.length; i++) {
Logger.log(«Each Thread: «+threads[i].getFirstMessageSubject());

var thread = threads[i];
var messages = GmailApp.getMessagesForThread(thread);
// Mensajes dentro de conversaciones
for (var j = 0 ; j < messages.length; j++) {
//Logger.log(«Each Message: «+threads[i].getFirstMessageSubject());

var messageId = messages[j].getId();
var messageDate = messages[j].getDate();
var messageTitle = messages[j].getSubject();

// Se puede obtener el mensaje completo
var messageBody = messages[j].getBody();
….

Es importante guardar el ID de cada mensaje para luego al escribir las celdas comparar si el ID ya está insertado. Si no controlamos esto vamos a volver a insertar los mismos mensajes cada vez.
Para procesar el cuerpo del mensaje podemos buscar textos o bien expresiones regulares. El cuerpo del mensaje puede ser excesivamente grande especialmente si se trata de un HTML con estilos.
El código completo se puede ver en el repositorio en Github: