Leer emails de Gmail y guardarlos en una planilla de Google

Hagamos una planilla que lea Gmail y acumule los correos que nos interesan sin repetirlos.

Uno de los típicos problemas a la hora de automatizar es como obtener los datos desde otro sistema. Para estos casos el email suele ser un aliado ya que la mayoría de los sistemas permiten enviar correos con información. Así podemos usar el email o Gmail en este caso como bandeja de entrada de la planilla de datos.

Haremos que cada correo que llega con determinadas palabras o remitentes sea introducido en la planilla con lo cual podemos iniciar un flujo de trabajo.

Para que nuestra planilla pueda acumular los correos debemos ejecutar la lectura en forma repetitiva, por lo que tenemos que pensar en un loop que se repita y guarde solo correos nuevos.

Buscar emails que contengan una palabra
Recorrer los hilos y cada email dentro del mismo
Guardar datos en planilla

Buscar emails que contengan una palabra
Recorrer los hilos y cada email dentro del mismo
Guardar datos en planilla (solo los nuevos!)
….
Esto en definitiva es un algoritmo.

// Planilla activa actual
var ss = SpreadsheetApp.getActiveSpreadsheet();
  
var hojaDatos = 'Emails';
var sheet = ss.getSheetByName(hojaDatos);


// 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 : "Leer Gmail",
        functionName : "leerGmail"
    });
    menuEntries.push(null);

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


function leerGmail() {

  // Lee de Gmail con un parametro de busqueda
  var emails = leerMails("G Suite");

  // Retorna solo emails nuevos que no estan presentes en la planilla comparando ID
  var emailsNuevos = reducirEmails(emails);

  // Escribe los emails nuevos a la planilla
  escribirEmails(emailsNuevos);

}


function leerMails(searchTerm = 'G Suite') {
  
  var threads = GmailApp.search(searchTerm,0,20);

  var mailsData = [];
    
  // Conversaciones o hilos (threads) que contienen muchos emails
  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++) {

      mailsData.push(messages[j]);      
      
    }
  }
   
  return mailsData;
}


// Los emails ya presentes en la planilla los descartamos
function reducirEmails(messages) {

  var emailsLimpios = [];
  var arrIdsMails = [];
  
  // IDs ya existentes  
  var arrIdsCells = [];
  
  // Columna A donde tenemos los IDs de los emails
  var dataRange = sheet.getRange(2,1,sheet.getMaxRows()-1,sheet.getMaxColumns());
  
  // Obtenemos los valores de la columna A, o sea un arreglo de IDs
  var dataCurrent = dataRange.getValues();
  
  // Guardamos un arreglo son los IDs que usaremos para comparar con IDs de emails nuevos
  for (var i = 0; i < dataCurrent.length; ++i) {
      
    var row = dataCurrent[i];
    
    // celdas actuales
    var valId = row[0];

    // si encontramos
    arrIdsCells.push(valId);

  }

  // Recorremos cada mensaje
  for (var j = 0 ; j < messages.length; j++) {

    var messageId = messages[j].getId();
    var messageDate = messages[j].getDate();
    var messageTitle = messages[j].getSubject();
    var messageBody = messages[j].getBody();       
    
    // Si no es de los ya existentes
    if (arrIdsCells.indexOf(messageId) == -1 && messageDate != undefined && messageDate != "") {
    
      Logger.log("Insertamos email: " + messageId);
      
      // Guardamos datos de cada email nuevo
      emailsLimpios.push([
        messageId,
        messageDate,
        messageTitle,           
      ]);
        
      // Agregarmos 
      arrIdsMails.push(messageId);
      
    } else {
      Logger.log("Ya existe: " + messageId);
    }

  }

  return emailsLimpios;

}


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

  if (numRows > 0) {
    var numCols = mailsData[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(mailsData);
  }

}

  

Apps Script para enviar rangos de celdas por Email como tablas

Dentro de los automatismos de las planillas el lograr que los informes se completen solos es el primer paso, el segundo es lograr que los informes se envían solos. Para esto crearemos un script que envíe rangos de celdas por email a pedido del usuario. 

Los rangos de celdas pueden ser definidos con coordenadas o bien con nombres de forma que si se agregan filas o columnas la referencia al rango sigue funcionando.

var emailRecipient = '[email protected]';
var emailTitle = 'Rango de celdas como tabla';

// Rango de datos que sera copiado como tabla
var hojaInforme = 'Informe';
var rangosDatos = {
  0 : 'RangoNombre1',
  1 : 'RangoNombre2',
};

// 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 ss = SpreadsheetApp.getActiveSpreadsheet();
    var menuEntries = [];
 
    menuEntries.push({
        name : "Enviar Email",
        functionName : "enviarEmail"
    });
    menuEntries.push(null);

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


function enviarEmail() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = SpreadsheetApp.getActive().getSheetByName(hojaInforme);
  
  var name = SpreadsheetApp.getActiveSpreadsheet().getName();
  var subject = emailTitle + ' ';
  subject +=  new Date().toISOString().split('T')[0];
  
  var body = '<html><body><div style="text-align:center;display: inline-block;font-family: arial,sans,sans-serif">'
 
  var iLoop = 1;
  for (var key in rangosDatos) {  // OK in V8
    var value = rangosDatos[key];
    Logger.log("value = %s", value);
    
    body += '<H2>Titulo ' + iLoop + '</H2>';
  
    var rango1 = sheet.getRange(value);
    body += getHtmlTable(rango1);
   
    iLoop++;
  }

  body += '</div></body></html>';

  GmailApp.sendEmail(emailRecipient, subject, "Requires HTML", {htmlBody:body})
}

Para ver el codigo de conversion de rango de celdas a tabla HTML, la funcion getHtmlTable, pueden hacerlo en la siguiente entrada:

Apps Script para crear un menu que ejecute codigo en una hoja de calculo de Google

Veamos un ejemplo de menú para ejecutar apps script desde la planilla. El menú en la planilla nos permite ejecutar el código Apps Script desde la planilla sin tener que acceder al editor, de forma que otros usuarios pueden usar el código directamente desde la hoja de cálculo.

// Planilla activa actual
var 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 : "Notificar",
        functionName : "notificar"
    });
    menuEntries.push(null);

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

function notificar() {

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

Enviar rangos de celdas por email

Para poder enviar por email un rango de una planilla donde se genera un informe, copiando datos desde otra planilla, importando de una API, aplicando filtros, podemos genera un script que copie las celdas y las envie como una tabla.

Planilla de ejemplo:
https://docs.google.com/spreadsheets/d/1Y6GK1Dqm1tyMrVvyhPfAwhGWdIhpLBXezW3dNCJW2w0/edit#gid=0

Lo que necesitamos es crear rangos con nombres en la planilla, los cuales luego obtendremos desde apps script para copiar las celdas:

Hecho esto utilizaremos los nombres de los rangos en el codigo de apps script:



// Rango de datos que sera copiado como tabla
var hojaInforme = 'Informe';
var rangosDatos = {
  0 : 'RangoNombre1',
  1 : 'RangoNombre2',
};

function enviarEmail() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = SpreadsheetApp.getActive().getSheetByName(hojaInforme);
  
  var name = SpreadsheetApp.getActiveSpreadsheet().getName();
  var subject = emailTitle + ' ';
  subject +=  new Date().toISOString().split('T')[0];
  
  var body = '<html><body><div style="text-align:center;display: inline-block;font-family: arial,sans,sans-serif">'
 
  var iLoop = 1;
  for (var key in rangosDatos) {  // OK in V8
    var value = rangosDatos[key];
    Logger.log("value = %s", value);
    
    body += '<H2>Titulo ' + iLoop + '</H2>';
  
    var rango1 = sheet.getRange(value);
    body += getHtmlTable(rango1);
   
    iLoop++;
  }

  body += '</div></body></html>';

  GmailApp.sendEmail(emailRecipient, subject, "Requires HTML", {htmlBody:body})
}

La funcion getHtmlTable() es una funcion auxiliar que copia celdas y las transforma en HTML.

Aunque los rangos con nombre sean modificamos o cambien de ubicacion, el script podra ubicarlos y copiar los contenidos, aun si se agregan celdas dentro del rango!

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




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:

Añadir un indicador de avance de ejecución en App Scripts

Al ejecutar funciones en App Scripts es posible que la ejecución tome cierto tiempo, para lo cual es útil mostrar al usuario algún mensaje de avance para evitar que quiera trabajar sobre datos incompletos.

Para esto podemos usar los toast de app scripts que indican al usuario el estado del script:

// Mensaje de esto para el usuario
SpreadsheetApp.getActiveSpreadsheet().toast(‘Finalizamos!’, ‘Estado’, 2);

Para ver la demo completa ver la siguiente planilla:

Demo1 nrm.uy – Leer API
https://docs.google.com/spreadsheets/d/1ap2IQeQ73049NE6lj_oRDBFQ-Wjz0GDiZoXPkFf-a4I/

Código en Github del proyecto:
https://github.com/nrodriguezm/app-scripts-demo/blob/master/demo1-api-menu-toast/Code.js

Crear un menu personalizado en planilla de Google Drive

Continuando con el ejemplo de como consumir una API externa usando app scripts en una planilla de Google Drive, vamos a agregar un menú personalizado que nos permita ejecutar el script desde la planilla para que el usuario no tenga que acceder al código del proyecto.

Para eso vamos a utilizar la función onOpen de las planillas que se dispara cuando el usuario abre la misma, luego vamos a insertar una entrada de menú:

// 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 ss = SpreadsheetApp.getActiveSpreadsheet();
var menuEntries = [];

menuEntries.push({
name : «Actualizar Datos»,
functionName : «getDatosAPI»
});
menuEntries.push(null);

ss.addMenu(«Actualizar», menuEntries);
}

Con este código logramos un menú como el siguiente:

Cuando el usuario hace clic en el menú personalizado de «Actualizar Datos», se dispara la función de App Scripts «getDatosAPI».

Para ver la demo completa ver la siguiente planilla:

Demo1 nrm.uy – Leer API
https://docs.google.com/spreadsheets/d/1ap2IQeQ73049NE6lj_oRDBFQ-Wjz0GDiZoXPkFf-a4I/

Código en Github del proyecto:
https://github.com/nrodriguezm/app-scripts-demo/blob/master/demo1-api-menu-toast/Code.js

 

Leyendo datos desde una API en planillas de Google

Cuando trabajamos con planillas en la nube tenemos la posibilidad de leer datos desde nuestro sistema de gestión para tener la planilla sincronizada a la realidad de la operación.

Por ejemplo podemos traer la facturación, compras o costos de RRHH de nuestra empresa analizando los números en tiempo real, día a día sin esperar reportes manuales que pueden y suelen tener errores (olvidos, cambios en el formato, etc.).

Para lograr esto podemos utilizar App Scripts en las planillas de Google para leer una API. En el menú superior: Herramientas > Editor de secuencia de comandos

Vamos a definir una función auxiliar para llamadas a APIs externas que responden con JSON:

// Llamada a API externa
function getApiData(url,query) {

try {
var response = UrlFetchApp.fetch(url);
var responseData = response.getContentText();
var json = JSON.parse(responseData);
return json;
}
catch (e) {
Logger.log(e);
return [«Error:», e];
}
}

El código quedará mas o menos así:

Con esto tenemos una función auxiliar para llamadas a APIs externas que responden con formato JSON.

Para ver la demo completa ver la siguiente planilla:

Demo1 nrm.uy – Leer API
https://docs.google.com/spreadsheets/d/1ap2IQeQ73049NE6lj_oRDBFQ-Wjz0GDiZoXPkFf-a4I/

Código en Github del proyecto:
https://github.com/nrodriguezm/app-scripts-demo/blob/master/demo1-api-menu-toast/Code.js