Enviar correos con archivos adjuntos desde Google Sheets

por | Jun 13, 2023 | Blog, Google Spreadsheets

Enviar correos con adjuntos en sheets

Casi diario tenemos que enviar correos, y muchas veces tenemos que preparar prácticamente el mismo correo dirigido a varias direcciones pero cambiando solamente algunos datos como el nombre, el asunto o algo en el mensaje. Copiar y pegar texto no es tanto problema, pero adjuntar los archivos correspondientes podría ser un dolor de cabeza. Hacerlo manualmente no es muy práctico, pero si nos ayudamos de Google Sheets podemos automatizar esta tarea fácilmente, y eso es lo que haremos hoy.

Enviar correos con Google Sheets

Como recordarás, este tema ya lo hemos tratado en una guía anterior, en la que aprendimos a enviar uno o más correos usando Google Sheets y Apps Script. En los comentarios hubo muchas personas que preguntaban sobre cómo realizar este mismo proceso pero tomando en cuenta archivos adjuntos. No es algo difícil de hacer, pero vale la pena volver al tema para modernizar un poco el código que teníamos y agregarle la funcionalidad que necesitamos ahora.

Preparando nuestros datos

Entonces, empezamos con un documento en el que tendremos como encabezados varios datos importantes por fila que conformarán cada correo: el nombre, el correo, el asunto, la dirección o URL de Google Drive del archivo adjunto que queremos enviar, el mensaje y finalmente una columna donde monitoreamos el status de cada envío.

Datos necesarios para enviar cada correo

Como puedes ver, el Asunto lo estamos armando directamente en la celda correspondiente usando el dato del nombre y el operador de & para concatenar. Lo mismo pasa con la columna de Mensaje.

Código para enviar correos con archivos adjuntos desde Google Sheets

OK, ya tenemos lista la información que enviaremos en cada correo, entonces lo que sigue es aprovechar las capacidades de Apps Script para enviar esos correos. Apps Script es la plataforma que nos permite usar código de JavaScript para manipular los datos de Google Sheets.

Entonces vamos al menú Extensiones – Apps Script:

Menú: Extensiones - Apps Script
Menú: Extensiones – Apps Script

Una vez que se abrió el editor de código, pegamos este código, que obviamente también vamos a explicar:

function onOpen() {  
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Correos')
      .addItem('Enviar correos', 'enviarCorreos')
      .addToUi();
}

function enviarCorreos() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var fila_inicial = 2;
  var columna_inicial = 1;
  var columna_status = 6;
  var ultima_fila = sheet.getLastRow() - 1;
  var remitente = "facturacion@ejemplo.com";
  var nombre_remitente = "Facturación Arrakis";

  // El rango con la información necesaria: 
  // desde A2 (columna Nombre) hasta la última fila con datos,
  // abarcando 6 columnas
  var range = sheet.getRange(fila_inicial, columna_inicial, ultima_fila, columna_status);

  // Mapeamos los valores a un arreglo de objetos con sus propiedades correspondientes
  var datos = range.getValues().map(
    ([nombre, correo, asunto, archivo, mensaje, status]) => 
    ({nombre, correo, asunto, archivo, mensaje, status})
  );

  // Para cada elemento de la lista de datos enviamos un correo
  datos.forEach((dato,index) => {
    // Solamente procesamos los correos que no estén vacíos y no se hayan "Enviado"
    if(dato.status != "Enviado" && dato.correo != "") {
      // Preparamos el archivo adjunto
      var id_archivo = getFileIdFromUrl(dato.archivo);
      var adjunto = DriveApp.getFileById(id_archivo).getBlob();
      // Enviamos el correo sendEmail(to, subject, body, options)
      MailApp.sendEmail(
                      dato.correo, 
                      dato.asunto, 
                      dato.mensaje, 
                      {
                       attachments: [adjunto],
                       name: nombre_remitente,
                       replyTo: remitente 
                      });
      // Escribimos en la hoja la confirmación del envío
      // en la columna de Status
      sheet.getRange(fila_inicial + index, columna_status).setValue("Enviado");
      // Recomendado por Google para obligar a la hoja a actualizarse
      SpreadsheetApp.flush();
    }
  })
}

function getFileIdFromUrl(url) {
  let file_id = "";

  // Variantes de la URL
  const rx1 = /https:\/\/drive\.google\.com\/open\?id=([\w\-_]*)\&?.*/;
  const rx2 = /https:\/\/drive\.google\.com\/file\/d\/([\w\-_]*)\/.*/;

  if (url.match(rx1)) {
    file_id = url.replace(rx1, "$1");
  } else if (url.match(rx2)) {
    file_id = url.replace(rx2, "$1");
  }

  return file_id;
}
Lenguaje del código: JavaScript (javascript)

La primera función de onOpen simplemente nos crea un menú sencillo para que podamos accionar el código. Fíjate cómo hace referencia a la función enviarCorreos

function onOpen() {  
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Correos')
      .addItem('Enviar correos', 'enviarCorreos')
      .addToUi();
}
Lenguaje del código: JavaScript (javascript)

En la función de enviarCorreos primero creamos el objeto sheet que contendrá toda la información de la hoja actual. También creamos algunas variables que nos serán útiles después para recorrer correctamente nuestra matriz de datos, como la fila inicial, que es la fila a partir de la cual consideramos que empiezan los datos. En nuestro caso empiezan en la fila 2. Lo mismo con columna inicial, la columna de status y la última fila con datos, a la que restamos uno porque comenzamos en la fila 2. Por último, especificamos un nombre y dirección de remitente, es decir, de dónde queremos que se muestre que se envía el correo para que también ahí recibamos las respuestas.

function enviarCorreos() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var fila_inicial = 2;
  var columna_inicial = 1;
  var columna_status = 6;
  var ultima_fila = sheet.getLastRow() - 1;
  var remitente = "facturacion@ejemplo.com";
  var nombre_remitente = "Facturación Arrakis";
...
Lenguaje del código: JavaScript (javascript)

Con todo esto ya preparado, estamos listos para obtener el rango preciso desde el cual trabajaremos. Empezamos en A2, terminamos en la última fila y considerando que la columna Status es la última, en este caso serán 6. Si agregas más datos antes de Status, asegúrate de calcular bien a qué número de columna corresponde cada cosa.

var range = sheet.getRange(fila_inicial, columna_inicial, ultima_fila, columna_status);
Lenguaje del código: JavaScript (javascript)

Ahora mapeamos estos valores uno a uno a un arreglo de objetos con sus propiedades correspondientes llamado datos, usando el método map de JavaScript.

...
var datos = range.getValues().map(
    ([nombre, correo, asunto, archivo, mensaje, status]) => 
    ({nombre, correo, asunto, archivo, mensaje, status})
  );
...
Lenguaje del código: JavaScript (javascript)

Lo único que nos resta ahora es ir uno por uno de estos datos y aplicar la lógica que ya conocemos para enviar o no los correos. Solo haremos algo si la columna de Status para una determinada fila no dice “Enviado” y si la dirección de Correo no está vacía.

Para poder adjuntar un archivo con este método, primero debe estar ya en nuestro Google Drive y debemos tener permisos para leerlo. Cada archivo de Drive tiene asociada una URL, que es la que ya tenemos en nuestra hoja, y cada URL contiene un identificador único de archivo, que tendremos que deducir. Para esto usaremos una función adicional llamada getFileIdFrom Url, cuya definición podemos ver un poco más abajo. Esta función simplemente toma una URL de Google Drive y con expresiones regulares, nos regresa el identificador que necesitamos.

En la variable adjunto guardamos los datos del archivo adjunto, usando la clase DriveApp, que controla lo relacionado con Google Drive, y su método getFileById, al que le damos el identificador de archivo que acabamos de obtener.

datos.forEach((dato,index) => {
    if(dato.status != "Enviado" && dato.correo != "") {
      var id_archivo = getFileIdFromUrl(dato.archivo);
      var adjunto = DriveApp.getFileById(id_archivo).getBlob();
...
Lenguaje del código: JavaScript (javascript)

Ahora sí, usamos la clase MailApp, que controla lo relacionado al envío de correos. Con el método sendEmail, especificamos la dirección a la cual queremos enviar el correo, el asunto, el mensaje y un objeto que incluirá opciones como todos los archivos adjuntos y la dirección y nombre del remitente. En nuestro caso solamente es uno, pero si quieres agregar más, por ejemplo si tuvieras una columna adicional con otro archivo de Drive, aquí es donde tendrías que modificar el código para incluirlo. También podrías modificar el mensaje para usar HTML si quieres darle más formato, pues al final del día el cuerpo del mensaje es algo muy parecido a una página web sencilla.

...
      MailApp.sendEmail(
                      dato.correo, 
                      dato.asunto, 
                      dato.mensaje, 
                      {
                       attachments: [adjunto],
                       name: nombre_remitente,
                       replyTo: remitente 
                      });
...
Lenguaje del código: CSS (css)

Finalmente, escribimos la palabra “Enviado” en la fila correspondiente y en la columna de status.

...
sheet.getRange(fila_inicial + index, columna_status).setValue("Enviado");
...
Lenguaje del código: CSS (css)

Ejecutar el código de envío de correos con archivos adjuntos

Bueno, es la hora de la verdad, vamos a ver si nuestro código funciona 🤔.

Guardamos nuestro código, refrescamos nuestra hoja para que aparezca nuestro menú, lo usamos una vez y le damos los permisos necesarios para acceder a nuestro Drive. Lo volvemos a ejecutar, y ahora sí veremos cómo al enviar cada correo se escribe la confirmación de “Enviado” en cada fila.

Si en las direcciones teníamos algún correo de prueba y lo revisamos, podremos ver que el envío fue correcto y que se incluyó el archivo adjunto con éxito. Incluso podemos ver que la dirección del remitente se respetó. ¿Genial, no crees? 😃

Limitaciones y consideraciones de envío de correos por Google Sheets

Como en las guías anteriores que usamos funciones internas de Google como Gmail o Google Drive, debemos tomar en cuenta las limitaciones que existen al respecto de estos servicios. Por ejemplo, Gmail permite solamente enviar hasta 2,000 correos por día desde una sola cuenta, y cada correo puede tener hasta 25 MB en total de todos los archivos que adjuntes. Conviene que revises esta información en los siguientes links, ya que si tu operación es demasiado grande para que la soporte este método, deberías considerar algo más robusto, como SendGrid o MailChimp.

¡Y listo! Ahora ya sabes cómo hacer uso de las funciones avanzadas de Google Sheets y Apps Script para automatizar el envío de correos con archivos adjuntos.

Plantilla terminada para descargar

Plantilla para Enviar correos con archivos adjuntos desde Google Sheets

Guía en video

Si tienes dudas puedes ver esta misma guía explicada en video

¡Espero que te haya sido útil esta guía! No olvides seguirnos en YouTube para más tips, trucos y guías de productividad.

Aprende Sheets: Curso práctico de Hojas de cálculo de Google
Aprende Sheets: Curso práctico de Hojas de cálculo de Google

1 Comentario

  1. Antonio
    Antonio el marzo 15, 2024 a las 5:14 am

    hola buen dia Alekz, consulto tengo 700 certificados para enviar, tengo que pegar el enlace de a uno? habría una forma mas rapida de pegar todos los enlaces de una sola vez? gracias

    Responder

Deja un comentario