Fusión de correo de Gmail para una etiqueta específica con Apps Script

Esquema del Script de fusión de Gmail Mail

• Asegúrate de que todos los correos electrónicos estén etiquetados correctamente en Gmail (puedes utilizar un filtro para hacerlo).
• Luego usa Apps Script para extraer las respuestas de la solución en una hoja con nombres y direcciones de correo electrónico.
• Clasifica cada fila de datos (es decir, cada correo electrónico) en 3 o 4 categorías diferentes, por ejemplo, «Correcto», «Correcto pero…», etc.
• A continuación, cree una plantilla de respuesta para cada una de estas categorías, para agradecer su participación y también para compartir cualquier comentario.
• Luego utilice una simple fórmula de VLOOKUP para agregar una respuesta a cada fila, basada en la categoría.
• A continuación, utiliza Apps Script para crear borradores de mensajes de correo electrónico para todos en la Hoja (la parte de Gmail Mail Merge).
• La última parte es manual: una rápida comprobación del correo electrónico original y la respuesta, añade cualquier personalización y luego pulsa SEND.

Parte 1: Extraer los correos electrónicos de Gmail a Google Sheet con Apps Script

Suponiendo que todos tus correos electrónicos estén etiquetados, de modo que estén todos juntos en una carpeta, puedes usar Apps Script para buscar esta etiqueta y extraer los mensajes en un Google Sheet.

Busca los mensajes bajo esta etiqueta con el método de consulta de búsqueda del servicio GmailApp. Esto devuelve un conjunto de hilos de Gmail que coinciden con esta consulta.

Recupera todos los mensajes con el método getMessagesForThreads().

De este conjunto de mensajes, extrae el campo De y el texto del cuerpo.

El campo «De» toma la forma:

Fullsite <test@example.com>

Analice esto con una función de mapa, que crea un nuevo conjunto a partir del conjunto original donde se ha aplicado una función a cada elemento. En este caso, la función analiza el campo «De» en un nombre y una dirección de correo electrónico utilizando una expresión regular.

Finalmente, esta nueva matriz, que contiene el nombre, la dirección de correo electrónico y el cuerpo del mensaje, se devuelve a cualquier función llamada la función extractEmails().

Aquí está el código:

function extractEmails() {
 
// definir la etiqueta label
var label = 'full-site';
 
// obtener todos los hilos de correo electrónico que coincidan con la etiqueta de Sheet
var threads = GmailApp.search("label:" + label);
 
// obtener todos los mensajes para el lote actual de hilos
var messages = GmailApp.getMessagesForThreads(threads);
 
var emailArray = [];
 
// obtener una serie de direcciones de correo electrónico
messages.forEach(function(message) {
message.forEach(function(d) {
emailArray.push([d.getFrom(),d.getPlainBody()]);
});
});
 
// analizar el campo de From
var parsedEmailArray = emailArray.map(function(el) {
var name = "";
var email = "";
var matches = el[0].match(/\s*"?([^"]*)"?\s+<(.+)>/);
 
if (matches) {
name = matches[1]; 
email = matches[2];
}
else {
name = "N/k";
email = el;
}
 
return [name,email,"'"+el[1]];
});
return parsedEmailArray;
}

Para pegar en la hoja de Google, creé esta función, que en realidad llama a la función extractEmails() en la línea 8 para recuperar los datos del correo electrónico:

function pasteToSheet() {
 // obtener el spreadsheet
   var ss = SpreadsheetApp.getActiveSpreadsheet();
   var sheet = ss.getActiveSheet();  
 // obtener datos de correo electrónico
   var emailArray = extractEmails();
 // borrar cualquier dato antiguo
   sheet.getRange(2,1,sheet.getLastRow(),4).clearContent();
 // pegar nuevos nombres y correos electrónicos y ordenar por dirección de correo electrónico A - Z
   sheet.getRange(2,1,emailArray.length,3).setValues(emailArray);
 }

Al ejecutar esta función pasteToSheet() se crea una hoja de Google con el nombre, la dirección de correo electrónico y el cuerpo del mensaje en las columnas A, B y C:

Ahora revisa cada fila y asigna una categoría. Quiere tener suficientes categorías para captar las principales diferencias en las respuestas, pero no demasiadas para que se convierta en algo manual y tedioso (¡de lo cual estamos tratando de alejarnos!).

Por ejemplo, en este desafío de la fórmula, tenía estas cuatro categorías:

Correcto, Transposición Extra, Otro, N/A

Parte 2: Crear plantillas de respuesta en Google Sheets

En una pestaña diferente (a la que llamé «Plantillas de respuesta»), crea tus plantillas de respuesta. Estas son las respuestas de la plantilla de respuesta para cada categoría genérica.

Now review each row and assign a category. You want to have enough categories to catch the main differences in responses but not too many that it becomes manual and tedious (which we’re trying to get away from!).

For example, in this formula challenge, I had these four categories:

Correct, Extra Transpose, Other, N/a

Part 2: Create Reply Templates In Google Sheets

En una pestaña diferente (a la que llamé «Plantillas de respuesta»), crea tus plantillas de respuesta. Estas son las respuestas de la plantilla de respuesta para cada categoría genérica.

Luego usa un VLOOKUP estándar para agregar una de estas plantillas de respuesta a cada fila, basado en la categoría:

=VLOOKUP(D2,'Reply Templates'!$A$1:$B$6,2,FALSE)

La hoja ahora se ve así (click para ampliar):

Parte 3: Crear respuestas al borrador para Gmail Mail Merge

El paso final es crear borradores de respuestas de Gmail para cada correo electrónico de tu Hoja, y luego enviarlos después de una rápida revisión.

Esta función recupera los datos de correo electrónico extraídos de la Hoja, y luego los busca en la carpeta de etiquetas. Crea un borrador de respuesta para cada correo electrónico con la plantilla de respuesta de los datos de la Hoja.

function createDraftReplies() {
 
// tomar las direcciones de correo electrónico de Google Sheet
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var data = sheet.getRange(2,1,sheet.getLastRow(),7).getValues();
 
// pasar por encima de ellos, encontrar mnost correo electrónico reciente bajo esa etiqueta para esa dirección de correo electrónico
data.forEach(function(arr) {
 
if (arr[6] === "") {
var emailAddress = arr[1];
var reply = arr[5];
var threads = GmailApp.search('label:marketing-formula-challenge-#3 from:' + emailAddress)[0];
var message = threads.getMessages()[0];
message.createDraftReply(reply);
}
 
});
}

Cuando el guión termine de ejecutarse, todos los correos electrónicos en esta carpeta de etiquetas tendrán un borrador de respuesta.

Revísalos, personalízalos si es necesario y pulsa Enviar! 📤

Notas sobre la combinación de correspondencia de Gmail

1) Podría haber usado el método de respuesta del servicio GmailApp para enviar respuestas automáticamente y omitir el proceso de revisión del borrador. Esto sería útil si la revisión de cada borrador consumiera demasiado tiempo a escala.

2) No he incluido ningún tratamiento de errores en este script.

Esto fue deliberado porque estaba creando una solución de un solo uso, por lo que quería avanzar lo más rápido posible. Este es uno de los puntos fuertes de Apps Script. Puedes usarlo para crear soluciones rápidas y sucias para llenar pequeños huecos en tu flujo de trabajo. Si el problema es lo suficientemente específico y no está pensado para ser usado en otro lugar, no tienes que preocuparte demasiado por el manejo de errores y los casos límite.

3) Por último, tenga en cuenta las cuotas de Apps Script cuando envíe correos electrónicos automáticamente con Apps Script. Son 100 para los planes de consumo y 1.500 para la Suite G (Negocios y Educación).