Crear un sistema de recordatorios automáticos con triggers en Google Sheets

por | Mar 7, 2024 | Blog, Google Spreadsheets

Sistema de recordatorios con Google Sheets

Usando solo hojas de cálculo podemos crear nuestro propio sistema automático de recordatorios y avisos para hacerle saber a otros sobre cualquier tema futuro. Puede ser un aviso de pago de una renovación futura o un amigable recordatorio de asistir a la junta de padres de familia de la escuela. Hoy, usaremos Google Sheets para crear este útil sistema que una vez terminado, como por arte de magia enviará correos automáticamente en una fecha determinada usando triggers o activadores.

Preparando el documento con nuestros datos

Supongamos que queremos necesitamos avisar por correo con anticipación sobre algún tema a algunas personas. La idea es que nosotros solo debemos ajustar la fecha en que se enviará el recordatorio futuro, escribir los mensajes correspondientes y la hoja de cálculo hará el resto.

Para lograrlo, vamos a preparar un documento con una hoja llamada Recordatorios con las siguientes columnas: Nombre, Correo, Fecha del recordatorio, Asunto y Mensaje. El nombre de la hoja y estas 5 columnas deben estar así y en este orden para que el código que usaremos después funcione. A la derecha podemos agregar más columnas con datos adicionales que pueden servir para incluir información en el mensaje. En mi ejemplo se puede ver una columna con una fecha de renovación y otro con datos sobre una ubicación para una junta de padres de familia, pero en realidad puede ser lo que tú quieras.

Columnas necesarias para sistema de recordatorios en Sheets

Triggers (activadores) y envío de correos

Para crear este proyecto en Google Sheets, necesitamos usar dos funcionalidades que ya hemos visto antes: triggers (activadores en español) y envío de correos usando código de Apps Script.

Recordemos que un trigger es una funcionalidad de Apps Script que permite ejecutar una función automáticamente dado un evento específico. En nuestro caso el trigger deberá ejecutarse automáticamente una vez todos los días, y cuando coincida el día actual con el día del recordatorio, deberá enviar el correo.

Código para automatizar envío de recordatorios y avisos por correo

Para lograr nuestro cometido, vamos a utilizar código en la plataforma de Apps Script, que es la forma en la que aumentamos las capacidades de nuestra hoja de cálculo mediante código.

Entonces vamos al menú Extensiones – Apps Script, borramos el código inicial que vemos, y lo reemplazamos por este. No te preocupes, no es mucho y lo explicaremos paso a paso 😉.

function onOpen() {  
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Recordatorios')
      .addItem('Activar recordatorios', 'activar_trigger_recordatorios')
      .addItem('Desactivar recordatorios', 'desactivar_trigger_recordatorios')
      .addToUi();
}

function activar_trigger_recordatorios() {
  if(checar_trigger("enviar_recordatorios")) {
    SpreadsheetApp.getUi().alert("Los recordatorios ya estaban activados.");
  } else {
    crear_trigger("enviar_recordatorios");
    SpreadsheetApp.getUi().alert("Recordatorios activados");
  }
}

function desactivar_trigger_recordatorios() {
  if(checar_trigger("enviar_recordatorios")) {
    borrar_trigger("enviar_recordatorios");
    SpreadsheetApp.getUi().alert("Recordatorios desactivados");
  } else {
    SpreadsheetApp.getUi().alert("No hay recordatorios activados.");
  }
}

function checar_trigger(nombre_trigger) {
  var triggers = ScriptApp.getProjectTriggers();
  var trigger_existe = false;
  triggers.forEach((trigger) => {
    if(trigger.getHandlerFunction() === nombre_trigger) {
      trigger_existe = true;
    }
  });
  return trigger_existe;
}

function crear_trigger(nombre_trigger) {
  ScriptApp.newTrigger(nombre_trigger)
    .timeBased()
    .atHour(0) // a las 00:00 horas aproximadamente
    .everyDays(1) // diario
    .create();
}

function borrar_trigger(nombre_trigger) {
  var trigger = ScriptApp.getProjectTriggers().filter(t => (t.getHandlerFunction() === nombre_trigger));
  ScriptApp.deleteTrigger(trigger[0]);
}

function enviar_recordatorios() {
    var sheet_recordatorios = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Recordatorios");
    var range_recordatorios = sheet_recordatorios.getRange("A2:E");

    var datos_recordatorios = range_recordatorios.getValues().map(
      ([nombre, correo, fecha_recordatorio, asunto, mensaje]) => 
      ({nombre, correo, fecha_recordatorio, asunto, mensaje})
    ).filter(d => (d.nombre == "" ? false : true));

  datos_recordatorios.forEach((dato) => {
    var fecha = new Date(); // fecha actual al momento de ejecutar
    var fecha_aviso = dato.fecha_recordatorio;

    // Si coincide la fecha actual con la fecha en que debemos avisar, avisamos
    if( fecha.getDate()     === fecha_aviso.getDate() &&
        fecha.getMonth      === fecha_aviso.getMonth() &&
        fecha.getFullYear() === fecha_aviso.getFullYear()) {
          MailApp.sendEmail(
                      dato.correo, 
                      dato.asunto,
                      dato.mensaje,
                      );
    }
  });
}
Lenguaje del código: JavaScript (javascript)

Lo primero que estamos haciendo es establecer una lógica para activar o desactivar los recordatorios. Usamos la función onOpen para agregar un menú que llamará a los métodos que harán justo eso dependiendo de nuestra necesidad. Esos dos métodos a su vez, revisan si ya existe el trigger de nombre enviar_recordatorios con el método checar_trigger y llaman a la función correspondiente, ya sea para para crearlo o borrarlo. De esta forma podemos crear y borrar nuestro trigger programáticamente sin necesidad de usar la interfaz gráfica.

Menú activar o desactivar recordatorios
function onOpen() {  
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Recordatorios')
      .addItem('Activar recordatorios', 'activar_trigger_recordatorios')
      .addItem('Desactivar recordatorios', 'desactivar_trigger_recordatorios')
      .addToUi();
}

function activar_trigger_recordatorios() {
  if(checar_trigger("enviar_recordatorios")) {
    SpreadsheetApp.getUi().alert("Los recordatorios ya estaban activados.");
  } else {
    crear_trigger("enviar_recordatorios");
    SpreadsheetApp.getUi().alert("Recordatorios activados");
  }
}

function desactivar_trigger_recordatorios() {
  if(checar_trigger("enviar_recordatorios")) {
    borrar_trigger("enviar_recordatorios");
    SpreadsheetApp.getUi().alert("Recordatorios desactivados");
  } else {
    SpreadsheetApp.getUi().alert("No hay recordatorios activados.");
  }
}

function checar_trigger(nombre_trigger) {
  var triggers = ScriptApp.getProjectTriggers();
  var trigger_existe = false;
  triggers.forEach((trigger) => {
    if(trigger.getHandlerFunction() === nombre_trigger) {
      trigger_existe = true;
    }
  });
  return trigger_existe;
}
Lenguaje del código: JavaScript (javascript)

Fíjate bien en la función crear_trigger, pues ahí es donde está la configuración del activador. Estamos diciéndole que será basado en tiempo (timeBased), y que se debe ejecutar a las 00:00 horas cada 1 días. Como nota aquí, Google decidirá el minuto exacto en el que se ejecutará nuestro activador, es decir, cuando le decimos 00:00 horas, en realidad le estamos indicando “en algún momento entre las 12:00 AM y la 1:00 AM”.

function crear_trigger(nombre_trigger) {
  ScriptApp.newTrigger(nombre_trigger)
    .timeBased()
    .atHour(0) // a las 00:00 horas aproximadamente
    .everyDays(1) // diario
    .create();
}

function borrar_trigger(nombre_trigger) {
  var trigger = ScriptApp.getProjectTriggers().filter(t => (t.getHandlerFunction() === nombre_trigger));
  ScriptApp.deleteTrigger(trigger[0]);
}
Lenguaje del código: JavaScript (javascript)

Código para el envío de correos

Ahora vamos a revisar la parte del código que envía el mensaje. Esto lo recordarás del tutorial para enviar correos con Google Sheets con archivos adjuntos, pues es muy similar.

En la función enviar_recordatorios(), primero estamos obteniendo la información de la hoja Recordatorios y el rango de A2:E, que es donde está la información que nos interesa. Luego, en el objeto datos_recordatorios recopilamos toda esa información y mapeamos las propiedades que necesitaremos después, como nombre, correo, fecha_recordatorio, asunto y mensaje. Filtramos también para no leer filas con datos incompletos, como el nombre.

Después, recorremos este objeto para procesar cada registro como un aviso de recordatorio. Creamos una variable con la fecha actual, y otra con la fecha indicada en el recordatorio correspondiente. Con un condicional comparamos el día, mes y año de ambas fechas, y si coincide usamos la clase MailApp para enviar el correo.

function enviar_recordatorios() {
    var sheet_recordatorios = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Recordatorios");
    var range_recordatorios = sheet_recordatorios.getRange("A2:E");

    var datos_recordatorios = range_recordatorios.getValues().map(
      ([nombre, correo, fecha_recordatorio, asunto, mensaje]) => 
      ({nombre, correo, fecha_recordatorio, asunto, mensaje})
    ).filter(d => (d.nombre == "" ? false : true));

  datos_recordatorios.forEach((dato) => {
    var fecha = new Date(); // fecha actual al momento de ejecutar
    var fecha_aviso = dato.fecha_recordatorio;

    // Si coincide la fecha actual con la fecha en que debemos avisar, avisamos
    if( fecha.getDate()     === fecha_aviso.getDate() &&
        fecha.getMonth      === fecha_aviso.getMonth() &&
        fecha.getFullYear() === fecha_aviso.getFullYear()) {
          MailApp.sendEmail(
                      dato.correo, 
                      dato.asunto,
                      dato.mensaje,
                      );
    }
  });
}
Lenguaje del código: JavaScript (javascript)

Si guardamos nuestro código y lo ejecutamos una vez, nos pedirá aceptar los permisos de siempre para asegurar que el código que usamos pueda usar los datos de nuestra hoja de cálculo. Una vez hecho eso, vamos al menú que hicimos de Recordatorios – Activar recordatorios. Debe mostrarnos el mensaje que habíamos visto hace un momento en el código confirmando que se activó el trigger correctamente.

Si probamos la otra opción, deberá desactivarlos.

Por ahora los activamos de nuevo.

Ahora solamente resta esperar a alguna de las fechas de recordatorio para que se envíen los avisos correspondientes automágicamente. ¡Genial!, ¿no? 😉

Revisar si se enviaron los recordatorios

Ahora, para saber si se están enviando nuestros avisos por correo será muy útil que vayamos de nuevo a Extensiones – Apps Script, y del lado izquierdo en la sección de Activadores, primero revisamos si efectivamente está instalado nuestro trigger y si marca un porcentaje mayor a 0% de errores al ejecutarse.

De nuevo en el menú del lado izquierdo, ahora vamos a la sección de Ejecuciones. Ahí podemos ver todas las ejecuciones que se han hecho de nuestro código, con información sobre si se completaron o no. En la columna de Tipo vemos que cuando un trigger de tiempo invoca la ejecución, se marca como “A partir del tiempo”.

Documentación y limitaciones

Recuerda revisar la documentación correspondiente a los triggers si es que quieres hacer cambios al código:

También será útil que revises la referencia de los límites y cuotas que aplican a los triggers y al número de correos que se pueden enviar por día.

¡Y listo! Ahora ya sabes cómo crear un sistema de recordatorios o avisos futuros por correo para cualquier necesidad que tengas. Lo mejor es que como usamos hojas de cálculo de Google Sheets, podrás ahorrarte mucho tiempo de redactar y programar mensajes muy parecidos, pues ya no tendrás que hacerlos uno por uno.

Plantilla terminada para descargar

Plantilla para Crear un sistema de recordatorios automáticos con triggers en Google Sheets

¡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

Deja un comentario