Cómo automatizar el envío de correos desde Google Spreadsheets

A veces tenemos tareas que son un poco tediosas, o que tenemos que hacer cada cierto tiempo con bastante regularidad. Mecánicas, repetitivas, aburridas… lo cual nos hace más propensos a cometer errores. Me he encontrado en muchas ocasiones con un excel inmenso, lleno de gente a la que mandar una misma información con ligeras variaciones. Monto un correo, lo envío, marco en el excel que lo he enviado; siguiente fila: copio el correo anterior, cambio destinatarios, modifico un par de cosas, lo envío, marco como enviado en el excel; siguiente fila… (30 correos más tarde) Si te ha pasado como a mí, seguro que te viste pensando “¿no puedo hacer algo más valioso con mi tiempo?”. ¡Claro que sí! ¡Automatiza!

Tabla de contenidos

1. Tu hoja de cálculo
2. La primera prueba
3. Metiendo un poco de lógica
4. Poniéndolo bonito con HTML y templates
5. Tuneando un poco más
6. Un vistazo completo al código
7. Resumen y conclusiones

Lo dicho, hoy te voy a contar cómo automatizar el envío de correos desde Google Spreadsheets, la aplicación de hojas de cálculo gratuita de Google. Para eso tendrás que saber un poquito hojas de cálculo, HTML y Javascript, y aprender un par de cosas de Google Apps Script. Nada del otro mundo, ya verás. ¡Vamos allá!

1 Tu hoja de cálculo

Esta es la parte más fácil. Simplemente necesitas una hoja de cálculo en la que tengas el listado de destinatarios a los que quieras enviar los correos. Voy a enseñarte un ejemplo real para el cual lo he utilizado recientemente. El 25 de octubre estuve en la 4ª edición de la Tarugoconf con el equipo de Autentia. Montamos varios concursos a través de Kahoots, que tenían como premio para los ganadores un taller de Efectividad Personal. 

El lunes siguiente, me vi en la oficina con un largo listado de gente a la que contactar (los ganadores y los que se quedaron en lista de espera). Montada mi hoja de cálculo, quedó algo así:

Ya ves, una fila por participante, en la que tenía el nick que habían usado en el concurso, nombre apellido, el identificador del kahoot en el que participaron y su puesto. Lo simplifico mostrando sólo 3 kahoots y 5 posiciones.

2  La primera prueba

Anteriormente ya había automatizado correos desde Excel y Outlook, pero no desde la suite de Google así que antes de nada hice una pequeña prueba de concepto. El objetivo era enviar un correo con texto plano a partir de los contenidos de la hoja de cálculo.

¿Por qué no la repetimos? Venga, créate una nueva hoja de cálculo de Google y empezamos a cacharrear… Una vez que la tengas, en el menú de herramientas lanzamos el “Editor de secuencias de comandos”.

En ese punto se nos abre un nuevo proyecto, al que le cambiaremos el título, con un archivo “Código.gs” que tiene el esqueleto de una función.

Google Apps Script tiene un modelo de objetos y clases mediante el cual representa aplicaciones, como Google Spreadsheets o GMail. Cada aplicación, a su vez tiene una serie de métodos para acceder a otros objetos que representan sus componentes.

Vamos con el código de la primera prueba que así quedará más claro. Comenzamos creando la función enviarCorreos(), que va a ser la que orqueste toda la lógica del script.

/** @OnlyCurrentDoc */
function enviarCorreos() {
 const libro = SpreadsheetApp.getActiveSpreadsheet();
 libro.setActiveSheet(libro.getSheetByName("Candidatos"));
 const hoja = SpreadsheetApp.getActiveSheet();
 const filas = hoja.getRange("A2:E3").getValues();
  
 for (indiceFila in filas) {
   var candidato = crearCandidato(filas[indiceFila]);
   enviarCorreo(candidato);   
 }
}

En ella, obtenemos el “libro” de Google Spreadsheets activo (línea 3) y nos situamos en la “hoja Candidatos” (línea 4). Para acceder y manejar sus datos, utilizamos un rango (método getRange() de la hoja) y con getValues() tendremos una matriz (array bidimensional) con los datos (línea 6). A través de un bucle for, recorreremos cada una de las filas y enviaremos un correo (líneas 9-12). Para  hacerlo bonito, encapsulamos en un objeto “candidato” la información que tenemos en cada fila.

function crearCandidato(datosFila) {
  const candidato = {
    nick: datosFila[0],
    nombre: datosFila[1],
    email: datosFila[2],
    kahoot: datosFila[3],
    puesto: datosFila[4]
  };
  return candidato;
}

Finalmente, mandaremos el correo en la función enviarCorreo(), a la que le pasamos como parámetro el candidato.

function enviarCorreo(candidato) {   
  MailApp.sendEmail(candidato.email, "Taller de Efectividad Personal", "Eres un candidato. Responde si estás interesado.");
}

El correo se envía con el método sendMail() de la clase MailApp. Super fácil; tiene tres parámetros: email del destinatario, asunto y cuerpo (en texto plano).

Para lanzar el script, lo hacemos desde el menú de Ejecutar, invocando la función enviarCorreos.

Tras lo cual…

¡Prueba de concepto superada! Enviamos un correo desde la hoja de cálculo. Vamos con la siguiente iteración…

3 Metiendo un poco de lógica

Como prueba, lo que hemos hecho nos vale, pero poco más. Ni está bonito, ni tiene en cuenta las distintas casuísticas. Por ejemplo, ¿y si nos falta la dirección de correo de alguien en la hoja de cálculo? Además, en nuestro concurso las plazas las ganaban los 3 primeros, quedando el resto en lista de espera. Con un par de if en el método enviarCorreo() lo arreglamos…

function enviarCorreo(candidato) { 
  if (candidato.email == "") return;
  var mensaje;
  if (candidato.puesto <= 3) {
    mensaje = "Tienes una plaza para el taller. Responde a este correo para confirmar tu asistencia.";
  } else {
    mensaje = "Estás en lista de espera para el taller. Responde a este correo si estás interesado en asistir.";
  }
  
  MailApp.sendEmail(candidato.email, "Taller de Efectividad Personal", mensaje);
}

Con el primero (línea 2), si está vacío el email del candidato salimos de la función sin llegar a enviar el correo. Con el segundo (líneas 4-8), variamos el mensaje a enviado según el puesto del candidato.

Podríamos afinar más, cosas como indicar el puesto en el que quedaron o el kahoot en el que participaron. Vamos con ello, pero al mismo tiempo que dejamos el correo más presentable.

4 Poniéndolo bonito con HTML y templates

Para formatear mejor el correo, tendremos que utilizar el método sendMails de otra manera, que nos permite pasarle el cuerpo del mensaje en HTML, añadir adjuntos, CC, CCO, etc. En vez de tres parámetros, recibe solamente uno del tipo message (echa un vistazo a la documentación de MailApp si quieres entrar en detalles). Con un poco de HTML y la nueva llamada, el correo resultante ya empieza a quedar mejor…

function enviarCorreo(candidato) { 
  if (candidato.email == "") return;
  var mensaje = "<p>Hola " + candidato.nombre + ",</p>";
  if (candidato.puesto <= 3) {
    mensaje += "<p><strong>Tienes una plaza para el taller</strong>. Responde a este correo para confirmar tu asistencia.</p>";
  } else {
    mensaje += "Estás en <strong>lista de espera</strong> para el taller. Responde a este correo si estás interesado en asistir.";
  }
  mensaje += "<p>Un saludo,<br/>Javier González</p>";
  
  MailApp.sendEmail({
    to: candidato.email,
    subject: "Taller de Efectividad personal con GTD y Personal Kanban",
    htmlBody: mensaje 
  });
}

Pero picar HTML directamente en nuestras variables no es lo más cómodo ni lo más recomendable. ¡Aquí entran en juego las plantillas! Primero creamos un nuevo archivo HTML y le ponemos un nombre que nos guste.

La estructura de este archivo será la normal y corriente de un HTML de los de toda la vida. 

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
	<p>...te cuento de todo y lo dejo <strong>bonito</strong></p>
  </body>
</html>

No entraré en detalle, pero es importante que en el head tengamos el tag <base target="_top">.

¿Y el contenido con sus variaciones? Antes vamos a ver cómo utilizar la plantilla dentro del código Javascript. De nuevo, solo hay que tocar el método enviarCorreo().

function enviarCorreo(candidato) { 
  if (candidato.email == "") return;
  const plantilla = HtmlService.createTemplateFromFile('plantillaTallerGTDPK');
  plantilla.candidato = candidato;
  const mensaje = plantilla.evaluate().getContent();
  
  MailApp.sendEmail({
    to: candidato.email,
    subject: "Taller de Efectividad personal con GTD y Personal Kanban",
    htmlBody: mensaje 
  });
}

Creamos primero un objeto plantilla (línea 2), indicando el nombre del archivo HTML que creamos anteriormente (si en la extensión .html y estando en la misma ubicación que el archivo .gs). Después “le enchufamos” el objeto candidato para poder acceder a sus atributos desde la plantilla, en la línea 3. En tercer lugar, generamos el contenido HTML tras evaluar el objeto plantilla (línea 4).

Ahora completaremos los contenidos e incluiremos cierta lógica para tener un correo personalizado y bien formateado. Y como te puedes imaginar, en vez de picar el código HTML dentro de las variables de Javascript, usaremos nuestra plantilla HTML para generar el cuerpo del mensaje, que será mucho más cómodo. Dentro de ella tendremos pequeños scriptlets, que irán entre <? y ?> para sentencias y entre <?= y ?> para expresiones. Con ellos, podremos personalizar el contenido en base a los datos de nuestra hoja de cálculo, los cuales están disponibles con el DTO “candidato” que te mencioné antes. Veamos el código…

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
    <h2>¡Prepárete para disparar tu Efectividad Personal!</h2>
    <p>
    Hola <?= candidato.nombre ?> (<?= candidato.nick ?>),
    </p>
    <p>Gracias por haberte pasado por el stand de <strong>Autentia</strong> en la <strong>Tarugoconf2019</strong>.
    ¡No sólo eso!, participaste en uno de nuestros kahoots y no has quedado nada mal ;-)</p>
    <p>En tu caso en el puesto <?= candidato.puesto ?> del Kahoot#<?= candidato.kahoot ?>!!!</p>
    <? if (candidato.puesto <= 3) { ?>
      <p><strong>Has ganado una de las plazas</strong> disponibles 
    <? } else { ?>
      <p>Estás en <strong>lista de espera</strong> 
    <? } ?>
    para el taller de <strong>Efectividad personal con GTD y Personal Kanban</strong>. 
    En el taller veremos la importancia de gestionar adecuadamente nuestros recursos, crítica para los trabajadores del conocimiento. 
    No necesitamos  más trabajo duro, sino trabajo inteligente. Con <i>los 5 pasos de GTD</i> (capturar, procesar, organizar, revisar 
    y hacer) aprenderemos a ser más <u>efectivos</u> (hacer lo adecuado), <u>eficaces</u> (hacerlo correctamente) y <u>eficientes</u> 
    (minimizando nuestro esfuerzo). Suena bien, ¿no? Pues complementaremos el sistema con <i>Personal Kanban</i>, que nos ayudará a 
    ver todo más claro y a mantenernos enfocados.</p>
    <p>Será el próximo día <strong>14/11/2019 de 18:00 a 20:00</strong> en las instalaciones de <i>Autentia</i> 
    (<a href="https://goo.gl/maps/1aMhb3QLUVaNkCvY8">Edificio BestPoint, Avenida de Castilla, 1, Planta 2, Local 21B, 28830 
    San Fernando de Henares, Madrid</a>). 
    <? if (candidato.puesto <= 3) { ?>
      Pero antes necesito tu <strong>confirmación de asistencia</strong> así que...
    <? } else { ?>
      ¿Quieres que te <strong>avise</strong> si se libera alguna plaza? 
    <? } ?>
    </p>
    <p>Si estás interesado <strong>responde a este correo</strong> y te mantengo al tanto de todo.</p>
    
    <p>Un saludo,</p>
    <p>Javier González.</p>
  </body>
</html>

No es tan complejo, ¿verdad? Un poco de formateo con tags de HTML (h2, p, strong, i, u, …) y unos pocos scriptlets:

• Para personalizaciones, como incluir el nombre (<?= candidato.nombre ?>)
• Para meter lógica condicional, diferenciando los ganadores de plaza de los de la lista de espera (<? if (candidato.puesto <= 3) { ?>).

5 Tuneando un poco más

¿Quieres meter una firma chula en tu correo? Abre un correo tuyo desde GMail, selecciona tu firma, inspecciona y cópiate el HTML de la misma. Bastará con lo incluyas al final del body (en vez de <p>Javier González.</p> en la línea 37 del ejemplo).

¿Y meter un foto con el mapita de donde haremos el taller? Una línea de código para generarla dinámicamente y otra para adjuntarla al correo.

const mapa = Maps.newStaticMap().addMarker("Autentia");
   
MailApp.sendEmail({
  to: candidato.email,
  subject: "Taller de Efectividad personal con GTD y Personal Kanban",
  htmlBody: mensaje, 
  attachments:[mapa]    
});

¿Lanzar el script desde el menú? Claro, así lo podrán utilizar personas que les «dé cosa» eso de tocar código.

function onOpen() {
  const spreadsheet = SpreadsheetApp.getActive();
  const menuItems = [{name: 'Enviar', functionName: 'enviarCorreos'}];
  spreadsheet.addMenu('Enviar Correos', menuItems);
}

Sobreescribimos la función onOpen(), añadiendo la lógica para agregar un nuevo menú en el que asociamos la entrada «Enviar» con nuestro método enviarCorreos(). La primera vez que intentemos lanzar el script vía menú, Google nos pedirá permisos para poder ejecutar el código.

Con todo estoy el aspecto final de los correos queda así…

Y podríamos hacer muchas cosas más. Las posibilidades que tenemos son tan grandes como tu imaginación.

6 Un vistazo completo al código

Te dejo ahora el código final completo, por si lo quieres reutilizar o leer del tirón.

Primero el Javascript…

/** @OnlyCurrentDoc */
function enviarCorreos() {
  const libro = SpreadsheetApp.getActiveSpreadsheet();
  libro.setActiveSheet(libro.getSheetByName("Candidatos"));
  const hoja = SpreadsheetApp.getActiveSheet();
  const filas = hoja.getRange("A2:E16").getValues();
  const mapa = Maps.newStaticMap().addMarker("Autentia");
  
  for (indiceFila in filas) {
    var candidato = crearCandidato(filas[indiceFila]);
    enviarCorreo(candidato, mapa);
 }
}

function crearCandidato(datosFila) {
  const candidato = {
    nick: datosFila[0],
    nombre: datosFila[1],
    email: datosFila[2],
    kahoot: datosFila[3],
    puesto: datosFila[4]
  };
  return candidato;
}

function enviarCorreo(candidato, mapa) {
  if (candidato.email == "") return;
  const plantilla = HtmlService.createTemplateFromFile('plantillaTallerGTDPK');
  plantilla.candidato = candidato;
  const mensaje = plantilla.evaluate().getContent();
  
  MailApp.sendEmail({
    to: candidato.email,
    subject: "Taller de Efectividad personal con GTD y Personal Kanban",
    htmlBody: mensaje,
    attachments:[mapa]
  });
}

function onOpen() {
  const spreadsheet = SpreadsheetApp.getActive();
  const menuItems = [{name: 'Enviar', functionName: 'enviarCorreos'}];
  spreadsheet.addMenu('Enviar Correos', menuItems);
}

Y después la plantilla HTML…

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
    <h2>¡Prepárete para disparar tu Efectividad Personal!</h2>
    <p>
    Hola <?= candidato.nombre ?> (<?= candidato.nick ?>),
    </p>
    <p>Gracias por haberte pasado por el stand de <strong>Autentia</strong> en la <strong>Tarugoconf2019</strong>.
    ¡No sólo eso!, participaste en uno de nuestros kahoots y no has quedado nada mal ;-)</p>
    <p>En tu caso en el puesto <?= candidato.puesto ?> del Kahoot#<?= candidato.kahoot ?>!!!</p>
    <? if (candidato.puesto <= 3) { ?>
      <p><strong>Has ganado una de las plazas</strong> disponibles 
    <? } else { ?>
      <p>Estás en <strong>lista de espera</strong> 
    <? } ?>
    para el taller de <strong>Efectividad personal con GTD y Personal Kanban</strong>. 
    En el taller veremos la importancia de gestionar adecuadamente nuestros recursos, crítica para los trabajadores del conocimiento. 
    No necesitamos  más trabajo duro, sino trabajo inteligente. Con <i>los 5 pasos de GTD</i> (capturar, procesar, organizar, revisar 
    y hacer) aprenderemos a ser más <u>efectivos</u> (hacer lo adecuado), <u>eficaces</u> (hacerlo correctamente) y <u>eficientes</u> 
    (minimizando nuestro esfuerzo). Suena bien, ¿no? Pues complementaremos el sistema con <i>Personal Kanban</i>, que nos ayudará a 
    ver todo más claro y a mantenernos enfocados.</p>
    <p>Será el próximo día <strong>14/11/2019 de 18:00 a 20:00</strong> en las instalaciones de <i>Autentia</i> 
    (<a href="https://goo.gl/maps/1aMhb3QLUVaNkCvY8">Edificio BestPoint, Avenida de Castilla, 1, Planta 2, Local 21B, 28830 
    San Fernando de Henares, Madrid</a>). 
    <? if (candidato.puesto <= 3) { ?>
      Pero antes necesito tu <strong>confirmación de asistencia</strong> así que...
    <? } else { ?>
      ¿Quieres que te <strong>avise</strong> si se libera alguna plaza? 
    <? } ?>
    </p>
    <p>Si estás interesado <strong>responde a este correo</strong> y te mantengo al tanto de todo.</p>
    
    <p>Un saludo,</p>
    <p>Javier González</p>
</body>
</html>

Con unas 80 líneas de código en total hacemos un montón cosas. ¡No está mal!

7 Resumen y conclusiones

Hay veces que nos toca hacer tareas repetitivas y poco creativas. En vez de actuar como autómatas, una mejor alternativa bajo mi criterio es preguntarnos: “¿lo puedo automatizar?, ¿vale la pena es esfuerzo?”. En caso afirmativo, si utilizas la suite de aplicaciones de Google, Google Apps Script será tu mayor aliado. No hace falta ser un developer experto, con un poco de interés y ganas de aprender te bastará para muchos escenarios.

En el artículo de hoy te conté cómo automatizar el envío de correos desde Google Spreadsheets, mediante el uso de pequeños fragmentos de código de Google Apps Script. El modelo de objetos para acceder a los datos de una hoja de cálculo es bastante intuitivo. Si te manejas habitualmente con ellas y conoces conceptos como los rangos, no te costará nada pillarlo. Por otra parte, la integración básica con GMail es todavía más fácil. Incluso para crear correos a partir de plantillas HTML la cosa no se complica demasiado. Eso sí, Google nos limita el envío de correos automatizados a 100/día (GMail personal y G Suite free edition) ó 1.500/día (G Suite basic o superior).

Las herramientas están ahí para utilizarlas y hacer nuestro día a día más fácil, para apalancarnos en ellas. Creo que para nuestro trabajo, a día de hoy y todavía más en el futuro, hay dos palabras clave: sistematizar y automatizar. Y tú, ¿cómo lo ves?

Si te ha gustado el artículo no olvides compartirlo por redes sociales y/o dejar tus comentarios.

Be agile, my friend!