Enviando emails con Google Apps Script desde hojas de cálculo

A todo G.A.S. con formularios y hojas de cálculo

Los formularios y hojas de cálculo de Google son herramientas extremadamente útiles para aportar cierta organización a esos procesos internos (de un centro educativo o de cualquier organización) que llevamos siglos realizando mediante el simple envío de emails de un lado a otro. Estoy pensando en cosas como peticiones de material, comunicación de tutorías, incidencias de mantenimiento, notificación de conductas contrarias a la convivencia, permisos y ausencias, etc. etc. En fin, que no le voy a descubrir nada a nadie, en este sentido, que ya esté razonablemente familiarizado con el uso de formularios y hojas de cálculo.

Todos estos procesos se caracterizan por que un usuario envía cierta información a algún agente con capacidad para tomar decisiones y realizar acciones en consecuencia. Y esto se resuelve con facilidad con estas herramientas. Pero con frecuencia hay una segunda parte que en la que se requiere que este agente encargado de responder a la petición inicial le devuelva información al solicitante más allá de la confirmación automática que convenientemente envían los formularios, si es que son configurados de ese modo. Y aquí la cosa suele cojear.

Por ejemplo, supongamos que diseñamos un formulario para automatizar las peticiones de espacios para desarrollar determinadas actividades en una organización. Por no salirnos mucho del contexto habitual en este blog, digamos que se trata de actividades formativas. Cuando alguien (por ejemplo, el Jefe de Estudios) recibe una solicitud y determina el espacio libre más idóneo, deberá comunicárselo al solicitante. Enviar un correo electrónico no es una actividad agotadora, concedido, pero ¿no sería preferible solventar esto desde la propia hoja de cálculo?

Este tipo de situaciones se resuelven a menudo incrustando tanto el formulario como la hoja de datos de respuestas en un sitio web interno. El solicitante pide cosas y es responsable de acceder posteriormente a la tabla de respuestas para consultar el estado de su solicitud. No está mal, pero esta solución puede suscitar ciertas dudas relativas a la confidencialidad de la información recogida en la hoja de datos. Quizás no queramos que el solicitante A sepa qué ha pedido B. Simple y rápido, pero tal vez no idóneo.

Si no queremos trepar hasta algún sistema de ticketing tipo Freshdesk, Zoho Desk o Zendesk, lo que además de requerir que nos rasquemos el bolsillo probablemente sería matar moscas a cañonazos, podemos liarnos una vez más con Google Apps Script (GAS, en adelante, para abreviar). Y eso es justo lo que te propongo en este artículo. Adelanto que no vamos a resolver completamente ningún problema, pero sí aprenderemos alguna que otra cosilla y quizás podamos mejorar un poco el estado de cosas por defecto por lo que hace a esta cuestión. No, el código que usaremos no será totalmente óptimo, ni eficiente ni elegante ni glamuroso. Pero espero que sí lo suficentemente comprensible.

Antes de comenzar, te emplazo a que le eches un vistazo a mis artículos anteriores sobre hojas de cálculo y GAS en este espacio, dado que en esta ocasión daremos algunas cosas por sentadas.

El formulario

Nos centramos en el ejemplo que mencionaba unos párrafos más arriba: un formulario que permita solicitar espacios para el desarrollo de actividades formativas.

Dado que no pretendo dar una solución llave en mano sino más bien que cada uno adapte lo que se cuenta aquí a sus propias necesidades (la hoja de cálculo que he preparado tiene, honestamente, poco de solución redonda), no voy a colgar en esta ocasión el formulario ni la hoja de datos a modo de plantillas. Así no tendrás más remedio que preparar los tuyos.

 

Al grano, el formulario puede ser algo como esto (muestro solo un pedacito):

Quizás te extrañe un poco el hecho de que la primera cuestión que se muestra (características del espacio) se haya construido del modo que se ve en lugar de utilizando una sencilla pregunta con varias casillas de verificación. Hay una razón, pero me la guardo para el taller sobre tickets de salida que impartiré en el encuentro Aprendemos en la Nube, organizado por GEG España, el próximo sábado 5 de mayo (teaser al canto).

Y lo habitual... configuramos el formulario para que envíe una copia de las respuestas al solicitante, que este las pueda editar y, por supuesto, que muestre en pantalla una confirmación automática de envío.

Ya solo nos queda crear una hoja de respuestas para recibir las solicitudes...

...y nos trasladamos a la hoja de cálculo.

La hoja de datos

Aquí la tenemos:

No se aprecia gran cosa, ¿verdad? (puedes hacer clic sobre la captura para ampliarla un poco). Bueno, no es importante. Al fin y al cabo tan solo nos encontramos con tantas columnas como preguntas (además del sello de tiempo), que es lo esperable. Lo interesante está a la derecha. Zoom, por favor.

Aquí se han añadido 3 columnas editables que no están conectadas con el formulario (Q, R y S). Se ha recurrido al formato condicional para pintar el color de fondo de las celdas de modo que resulte sencillo reconocer cuál es el estado de cada solicitud de un vistazo. Las columnas R y S se colorean mediante sencillas expresiones del tipo el valor es igual a... Pego únicamente la regla utilizada en la columna R dado que la correspondiente a S es análoga y no tiene mayor misterio:

También hay formato condicional en Q, aunque no se aprecie en la captura. La regla empleada aquí es algo más vistosa. El fondo de la celda será rojo cuando se haya enviado una solicitud (hay marca de tiempo) pero aún no se haya asignado aula (celda en columna Q vacía). Para ello se ha utilizado esta expresión como fórmula personalizada en el panel de reglas de formato condicional:

=Y(NO(ESBLANCO(A2));ESBLANCO(Q2))

Una vez determinada el aula a reservar, la persona encargada de hacerlo enviaría, en circunstancias normales, un correo electrónico a la dirección del solicitante (columna B). Pero lo vamos a resolver de otro modo. Pretendemos que el responsable de atender la solicitud pueda desencadenar de algún modo el envío de una notificación con la información relevante a la persona que la ha enviado. Veamos cómo.

Como se puede apreciar, también se ha empleado validación en estas columnas Q-S para facilitar la introducción de datos. En la columna S, en concreto, se ha definido como único elemento admitido el texto Comunicar.

Vamos a tratar de que cuando se seleccione esta (única) opción a través del desplegable de la celda, nuestra hoja de cálculo le notifique al solicitante por correo electrónico la información que necesita saber, esto es, el aula asignada. Sí, esto es esencialmente un botón, aunque un poco cutre.

Y llegó la hora del código

Nos dirigimos a Herramientas → Editor de secuencias de comandos.

 

Lo primero será definir una función para interceptar el evento onEdit(), que se desencadena cada vez que se modifica una celda:

Sí, empiezo en la línea 28. Mi hoja de datos incluye también código para otras cosas, pero no vienen al caso ahora.

Nuestro código tendrá que disponer de autorización para enviar correos electrónicos por medio del método .sendEmail. Eso implica que nos veremos obligados a definir este disparador (activador) como instalable (más sobre los tipos de disparadores aquí). Vamos a ello en Editar → Activadores del proyecto activo del editor de código:

Y seguimos tecleando...

El código que escribamos dentro de esta función se ejecutará cuando se edite cualquier celda. Tendremos que asegurarnos de que el cambio detectado se haya producido en alguna de las celdas de la columna S, y además que este cambio no sea otro distinto a que su valor haya pasado a ser la cadena de texto Comunicar. Necesitamos un  IF con dos condiciones. Si ambas se cumplen quiere decir que ha pasado lo que estábamos esperando.

Como se aprecia en la cabecera de la función, el disparador recibe como parámetro un objeto (eventInfo) que podemos utilizar para determinar la columna de la celda que se ha modificado (.range.getColumn), así como el propio valor adquirido por la celda afectada (.value). La columna S es la número 19 (se comienza a contar desde 1). Este valor deberá modificarse en el código consecuentemente en función de la posición en la que hayamos colocado nuestro botón cutre en la hoja de datos asociada.

Ahora necesitamos capturar la información correspondiente a la respuesta (solicitud) situada en la misma fila de la celda que ha cambiado:

Obtenemos en ss (línea 32) el objeto que representa a la hoja de datos activa dentro de la hoja de cálculo. A partir de ahí se identifica la fila de la celda que se ha modificado (.range.getRow).

A continuación se define un rango con las celdas de esa fila comprendidas entre la columna A (1) y la R (18) utilizando el método .getRange (línea 36). Este método recibe como parámetros fila inicial, columna inicial, número de filas y número de columnas y devuelve un rango de datos sobre el objeto dataRange.

Finalmente, los valores de las celdas seleccionadas se copian en una matriz (variable data) por medio del método .getValues (línea 37). El índice [0] referencia la única fila presente en el rango definido en  dataRange, con todas sus columnas. Esto simplifica en cierta medida las expresiones que nos permitirán recuperar el texto contenido en cada una de las celdas en lo que viene a continuación, puesto que hemos transformado un rango bidimensional en un vector (1 dimensión) mondo y lirondo.

Ahora solo tenemos que montar el texto del correo electrónico de notificación despacito y con buena letra. Para ello utilizaremos la variable mensaje:

Clic para ampliar.

Algunos comentarios:

• El texto del mensaje se va construyendo por concatenación de cadenas de texto mediante el operador + (mensaje = mensaje + ...). Y sí, ahora me he pasado a las comillas simples (') en lugar de dobles (") para encerrar las expresiones literales de texto. Tanto da.
• Los métodos .toLocaleDateString y .toLocaleTimeString se utilizan para formatear los valores de fecha y hora de acuerdo con la configuración regional y zona horaria. De no recurrir a ellos, se mostrarían en inglés y formato largo.
• Los valores de las celdas que representan las respuestas enviadas por el usuario a cada pregunta del formulario son accesibles indexando en la variable data. A diferencia de lo que ocurría con el método .setRange, y para sembrar un poco de confusión, en este caso el índice comienza en 0. No tenemos más que ir contando y siguiendo el código de las líneas 42 - 51 para entender fácilmente qué está ocurriendo.
• La cadena '\n' se utiliza para introducir un cámbio de párrafo. De no utilizar esta secuencia, todo el mensaje quedaría en la misma línea. Un lío.

Y ya tan solo resta enviar la notificación:

Clic para ampliar.

Antes de ello, no obstante, abriremos un cuadro de diálogo que permita previsualizar el mensaje a enviar y le dé al usuario la oportunidad de echar marcha atrás si algo no es correcto. Para ello primeramente se identifica el objeto Ui, que representa a la interfaz de usuario y da acceso a métodos como .alert, que a su vez lanza una alerta con dos botones (línea 55).

En función de la respuesta (línea 56), se realiza si procede el envío del correo mediante el método .sendEmail(email, asunto, texto) del objeto MailApp y, de propina, se fija el valor de la celda correspondiente situada en la columna R a Comunicado.

Para terminar, no nos olvidemos de cerrar adecuadamente los corchetes abiertos correspondientes a las dos estructuras condicionales IF y a la propia función.

Esto es lo que aparece en pantalla:

Y este es el aspecto de la notificación por correo electrónico que recibe la persona que rellenó el formulario en primera instancia:

Aquí el código empleado en formato texto para copiar & pegar cómodamente:

La cosa puede mejorarse bastante. Podríamos añadir alguna columna más en la parte derecha de la tabla, por ejemplo para dejar constancia de algún comentario por parte de la persona que realiza la asignación, comentarios que quizás deberían ser añadidos a la notificación enviada de vuelta al solicitante. Tampoco hemos introducido controles de tratamiento de errores.

Además, pensándolo bien no me acaba de convencer lo de la columna Tratamiento. Podríamos darle alguna vuelta y repensar el funcionamiento de las columnas R y S para añadir una segunda acción, tal vez requerir, que permitiera enviar un correo electrónico diferenciado para informar de que hay algún problema a resolver con la solicitud. En ese caso probablemente la columna R, en su estado actual nos sobraría y deberíamos transformarla en otra cosa o simplemente prescindir de ella. Algo así:

En fin, deberes para casa para el que quiera jugar un poco con esto. Ahora disponemos de herramientas para adaptar el funcionamiento a distintas necesidades.

Hasta aquí el artículo. Es innegable que lo que hemos montado tiene numerosas carencias y en ningún caso sustituye a un sistema de gestión diseñado específicamente, pero ya es algo más de lo que teníamos.

Para terminar, un comentario que no me gustaría dejar de hacer antes de terminar. Si lo que necesitas es montar un sistema de atención al usuario completo, por ejemplo para resolver incidencias TIC (o de otro tipo), una excelente y totalmente gratuita alternativa es HESK.  Requiere un entorno XAMP / WAMP o ser desplegado en un servidor de hospedaje que soporte PHP, mySQL, etc., pero el resultado es excelente.