ENVÍO DE FORMULARIOS SIN REPETICIONES

El objetivo es el de simplificar y automatizar la tarea de enviar convocatorias (ahora que hacemos tantas para webinars, videoreuniones...) y que, en muchas ocasiones, ocurre que nos llega la convocatoria al evento y, para hacer la inscripción nos ponen un formulario. En el momento de recibirlo lo rellenamos y enviamos, o no. A los pocos días nos vuelve a llegar la invitación y dudamos de si lo enviamos o estábamos ocupados con otras cosas y no lo hicimos. Quien envía esta invitación de nuevo se disculpa con un párrafo en el que dice: "si usted ya hizo la inscripción no haga caso a este mensaje", con lo que finalmente seguimos dudando de si lo enviamos o no, quien convoca el evento sigue haciendo envíos con la invitación llegando a ser pesado recibir muchas invitaciones que nos llevar a dudar de si hicimos esa inscripción y de si estaremos aceptados a ese evento.

Bien, para evitar esto tan común, proponemos, utilizando el entorno de Google Workspace (Gmail, Google Forms, Google Sheets y un poco de programación con Google Apps Script) un procedimiento que simplifique el tráfico de correos para este tipo de convocatorias y que de una forma automatizada para quien organiza el evento le simplifique las inscripciones y, para los asistentes resulte sencillo, claro y no inunde la bandeja de entrada del correo electrónico.

En el gráfico siguiente ilustramos el procedimiento:

Aunque pueda parecer complejo, el procedimiento es rápido y prácticamente automático. Lo vamos describiendo sobre este ejemplo en el que organizamos un webinar sobre introducción al Big Data (podría ser cualquier otro tipo de evento en el que aunque sea gratuito queremos registar y controlar la asistencia o el aforo).

En primer lugar debemos contar con una hoja de cálculo (Google Sheets) en la que tenemos los datos de los posibles interesados en participar (nuestra base de datos en la que deberemos tener una columna al menos, la más importante, en la que recopilamos los correos electrónicos de los contactos a los que vamos a invitar.

ENLACE para descargar la hoja de ejemplo.

Desde esta hoja, crearemos el formulario (Google Forms) que vamos a preparar para enviarlo como invitación y que sirva para recoger los datos de los/as interesados/as.

ENLACE para descargar el formulario de ejemplo.

En el formulario ponemos los campos necesarios para recoger la informacion mínima necesaria para poder organizar el evento.

Ahora, el documento de Google Sheets, tendrá dos hojas. Una la de nuestra base de datos: "Datos de los contactos" y otra, la que alojará los datos que vayan llegando por el formulario: "Respuestas de formulario 1".

El siguiente paso será incluir el código que gestionará los envíos y las confirmaciones.

Desde el "Editor de secuencias de comandos" añadiremos en Google Apps Script el código tal y como lo hemos preparado y lo podemos ver. Por un lado, una primera rutina "onOpen" que servirá para añadir en la hoja de cálculo el menú "Enviar invitación email > Enviar", aunque luego veremos que también le podemos añadir un botón para facilitar estos envíos.

Esta función es la siguiente:

function onOpen() {

const ui = SpreadsheetApp.getUi();

ui.createMenu('Enviar invitación email').addItem('Enviar', 'enviar_email').addToUi()

}

La siguiente función "enviar_email" se encarga del envío a todas las direcciones de correo contenidas en la columna "D" de la hoja de datos.

Además de enviar los correos con la invitación, también se encarga de enviar el formulario a todos los contactos y de no enviarlo a los que ya hubieran respondido al formulario.

function enviar_email() { //Esta función se ejecutará desde el botón de la hoja de datos

const ui = SpreadsheetApp.getUi();

var libro = SpreadsheetApp.openById('1_t8r1YSty_AUYlTmkoUUn3v-zUhAGNfzN7dG3uwcWVQ'); //Cambiar esta ID por la propia (esta es la del ejemplo)

var hoja = libro.getSheetByName('Datos de los contactos');

var ultimaFila = hoja.getLastRow();

Logger.log(ultimaFila);

var emails = hoja.getRange(2, 4, ultimaFila).getValues();

Logger.log(emails);

var okeis = hoja.getRange(2, 7, ultimaFila).getValues();

Logger.log(okeis);

var mensaje="Invitación a webinar sobre Big Data"

for (var i = 0; i < ultimaFila-1; i++)

{

//Detectar si ya está marcado con OK para no volver a enviar

if (okeis[i] != "OK")

{ usuario = emails[i],

Logger.log(usuario);

var textoHtml=HtmlService.createHtmlOutputFromFile("emails_masivo").getContent();

GmailApp.sendEmail(usuario,"Invitación a participar en webinar BIG Data", mensaje,{htmlBody:textoHtml});

}

La anterior función llama al fichero "emails_masivo.html" que prepara el diseño del correo que se enviará a todos los contactos incluyendo el enlace al formulario, la firma en la parte inferior con logotipos y otros añadidos.

Este fichero puede tener un contenido como el siguiente, aunque este HTML se puede personalizar:

<!DOCTYPE html>

<html>

<head>

</head>

<body>

Hola,

<div>Mediante este correo queremos invitarte a participar en el webinar sobtre introducción al Big Data que que hemos organizado el día XX del mes XX de 2021.</div>

Desde el <a href="https://docs.google.com/forms/d/e/1FAIpQLSdhcSnuGtpW97DumZmO9lWB31WVxC6S0np-bGDGDTvX2AQPpA/viewform">ENLACE</a> puedes enviar la solicitud.

Un saludo

---- Zuzendaria - Director CIFP BIDASOA LHII - IRUN 943 666 010 - XXX XXX XXX <a href="mailto:aarranz@fpbidasoa.eus">aarranz@fpbidasoa.eus</a> - <a href="http://www.fpbidasoa.eus/">www.fpbidasoa.eus</a>

INGURUMEN-MEZUA: garapen iraunkorraren alde, e-mail hau inprimatu aurretik baloratu beharrezkoa den. Inprimatuz gero, ez ahaztu birziklatzea; izan ere, papera euskarri naturala, berriztagarria eta birziklagarria da.

MENSAJE MEDIOAMBIENTAL: Por un desarrollo sostenible, antes de imprimir este e-mail valore si es necesario hacerlo. En caso de imprimirlo, no olvide reciclarlo; porque el papel es un soporte natural, renovable y reciclable.

</body>

</html>

Con esto habremos conseguido que a todas las cuentas de correo les haya llegado la invitación a rellenar el formulario para inscribirse en el evento que organicemos.

A medida que nuestros invitados vayan rellenando el formulario y haciendo su envío los datos irán llegando a la hoja "Respuestas de formulario 1".

Ahora, la función "confirmaciones" se encarga de enviar un mensaje de confirmación a cada uno de los que han respondido al formulario. y también de marcar con un "OK" con fondo verde en la columna "Confirmado" de nuestra base de datos quiénes de nuestros invitados son los que responden. Esta función tiene el siguiente aspecto:

function confirmaciones() { //Esta función se ejecutará de forma automática cada vez que alguien envíe datos por el formulario

//Detectar en la hoja de los datos que recoge el formulario cada email que va llegando

var ss = SpreadsheetApp.openById('1_t8r1YSty_AUYlTmkoUUn3v-zUhAGNfzN7dG3uwcWVQ'); //Cambiar esta ID por la propia (esta es la del ejemplo)

var hoja1 = ss.getSheetByName('Respuestas de formulario 1');

var fila = hoja1.getLastRow();

//En el caso de que lleguen emails escritos en mayúsculas se transforman a letra minúscula

var emailConfirmado = hoja1.getRange(fila,2).getValue()

var celdaemail = hoja1.getRange('B'+fila);

var emailConfirmado = emailConfirmado.toLowerCase(); //toLowerCase para transformar a minúsculas

celdaemail.setValue(emailConfirmado);

//Buscar el email recibido en el formulario con los de la base de datos y ver en qué fila está para poder marcarlo como "confirmado"

var hoja = ss.getSheetByName('Datos de los contactos');

var ultimaFila = hoja.getLastRow();

var emails = hoja.getRange(2, 4, ultimaFila).getValues();

for (var j = 0; j < ultimaFila; j++) {

if (emailConfirmado == emails[j]) {

var row = j+2;

break;

}

//Marcar en la hoja de la base de datos el confirmado que acaba de llegar

var ss = SpreadsheetApp.openById('1_t8r1YSty_AUYlTmkoUUn3v-zUhAGNfzN7dG3uwcWVQ');

var hoja = ss.getSheets()[1];

var cell = hoja.getRange('G'+row);

Logger.log(cell)

cell.setValue('OK').setBackgroundRGB(087,166,057);

//Para el envío de las confirmaciones al email del remitente

Logger.log('Enviar a: ' + emailConfirmado)

var textoHtml=HtmlService.createHtmlOutputFromFile("emails_confirmacion_html").getContent();

textoHtml=textoHtml.replace("{{emailConfirmado}}",emailConfirmado)

var mensaje1="Confirmación solicitud webinar sobre Big Data"

GmailApp.sendEmail(emailConfirmado,"Confirmación de la solicitud para participar en webinar BIG Data", mensaje1,{htmlBody:textoHtml});

}

El mensaje de confirmación a cada uno de los usuarios que responden al formulario les llega gracias al fichero "emails_confirmacion_html". Se puede personalizar el contenido de este documento e incluso hacer que aparezcan algunos campos personalizados con la información concreta de cada uno de los inscritos.

<!DOCTYPE html>

<html>

<head>

</head>

<body>

Hola,

<div>Hemos recibido correctamente tu solicitud para participar en el webinar que hemos organizado sobre Big Data. Unas horas antes del webinar recibirás el enlace para conectarte a la videoconferencia.</div>

<div>Se te enviará la información a esta cuenta de correo: {{emailConfirmado}} </div>

Un saludo

---- Zuzendaria - Director CIFP BIDASOA LHII - IRUN 943 666 010 - XXX XXX XXX <a href="mailto:aarranz@fpbidasoa.eus">aarranz@fpbidasoa.eus</a> - <a href="http://www.fpbidasoa.eus/">www.fpbidasoa.eus</a>

INGURUMEN-MEZUA: garapen iraunkorraren alde, e-mail hau inprimatu aurretik baloratu beharrezkoa den. Inprimatuz gero, ez ahaztu birziklatzea; izan ere, papera euskarri naturala, berriztagarria eta birziklagarria da.

MENSAJE MEDIOAMBIENTAL: Por un desarrollo sostenible, antes de imprimir este e-mail valore si es necesario hacerlo. En caso de imprimirlo, no olvide reciclarlo; porque el papel es un soporte natural, renovable y reciclable.

</body>

</html>

Si nos parece que han respondido todavía pocos de nuestros invitados podemos volver a hacer un envío masivo pero nuestras rutinas se encargan de que sólo se haga el envío a aquellos que no han respondido todavía al formulario.

Para facilitarlo en la hoja de datos: "Datos de los contactos" podemos añadir un botón como el que se muestra a la derecha al que asignamos la función "enviar_email".

Se ha tenido en cuenta que algunos de los inscritos pueden escribir su email en mayúsculas o en minúsculas (o una mezcla de ambas) para lo que se ha tenido la precaución de pasar todos los caracteres a minúsculas en evitación de errores al comparar los datos del formulario con los de la base de datos previa.

En una versión posterior de este trabajo se podría complementar con algunas otras funciones que envíen de forma automática un diploma de asistencia personalizado a cada uno de los asistentes, que envíen también de forma automática una encuesta de satisfacción y muestren los datos y estadísticas o que incluso utiicen otras aplicaciones, como Google Data Studio para mostrar un dashboard con la información relacionada con la participación y satisfacción.

Seguro que esto es muy mejorable pero sí que puede servir para animar a construir aplicaciones que faciliten y automaticen muchas de nuestras tareas habituales.

Alberto Arranz