Google Apps Script: aumenta las funciones de un sheet y personaliza un sistema de alertas

En este blog, ya os hemos ofrecido un post de introducción sobre el uso de la herramienta Google Apps Script y las valiosas contribuciones que nos puede brindar. El propósito de esta publicación es ejemplificar con un caso de uso concreto, en el que podamos ver la simplicidad y agilidad que esta herramienta puede proporcionarnos.

En referencia a la pregunta que nos ofrecía el autor del blog referenciado anteriormente, «¿A mí para qué me sirve Google Apps Script?« Vamos a exponer un caso de uso real (y simple) que nos de solución a una posible necesidad y en el que aumentaremos la funcionalidad de un sheet.

Vamos a ponernos en contexto…

La necesidad surge al buscar un seguimiento efectivo en la verificación de procesos automatizados, los cuales en ocasiones (debido a la falta de control en su desarrollo o visibilidad en su ejecución), no podemos justificar su correcto comportamiento.

Supongamos que inicialmente partimos de que la justificación de ese seguimiento, lo estamos haciendo a partir de un sheet de google, en la que diariamente, ponemos en cada celda un Ok o Error en la celda correspondiente.

En el caso del ejemplo la celda en verde nos indica que estamos en ese día y que todavía no se ha marcado el check.

Con el uso y el caso que se muestra en la imagen anterior, la persona responsable de revisar los procesos de la columna «Proceso Ejemplo» puede deja pasar los días sin verificar los procesos por despiste (no por que sea tedioso realizar este proceso ya que conlleva 10seg).

Por tanto, vamos a suponer que:

1. Estos procesos se ejecutan diariamente.
2. Por tanto, debemos confirmar a diario, que ese proceso se ha revisado (independientemente de si han fallado o no).
3. Por la naturaleza y el origen de los mismos, los sistemas de confirmación de la ejecución de estos procesos, no son suficientes (Ejemplo: Se envía un email el cual lo tenemos filtrado en nuestra bandeja o cuando lo vemos (si lo vemos), ya es tarde).
4. Necesitamos un sistema de confirmación y aviso adicional que nos aporte la seguridad de que los procesos se han verificado.

¡Comenzamos!

Lo primero que haremos será añadir un complemento o script sobre la que podamos gestionar un aviso.

Para ello, nos vamos a Herramientas/Apps Script:

Se nos abrirá una hoja de edición de código basado en lenguaje Javascript para que empecemos a darle forma a nuestro sistema de avisos:

Comienza lo difícil (?)

Como podemos ver, directamente se crea la cabecera de una función sobre la cual ya podemos empezar a «picar» código, tanto, como funcionalidades adicionales que queramos aplicarle al sheet.

En este caso, solamente necesitamos un aviso informativo en algún medio que si que veamos normalmente, por lo que podemos empezar por gestionar un aviso que nos llegue por ejemplo a un chat de Google.

Comenzamos por cambiarle el nombre al método en el que recogeremos los datos necesarios y a definir otro método para gestionar el envío:

/* Aquí definiremos las variables necesarias para procesar el envio*/
function seguimientoProcesosLogs() {
  this.envioXChat(X, Y);
}

/* Aquí procesaremos el envío*/
function envioXChat(X,Y){
  /*...*/
}

Lo siguiente que haremos será definir las variables del proceso y para ello, necesitamos obtener todo lo necesario para el planteamiento escogido, que en este caso, lo gestionaremos usando los siguientes métodos propios de GAS:

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Blog");
  var date = Utilities.formatDate(new Date(), "GMT+1", "dd/MM/yyyy");
  var searchValue = date;

  /* Obtenemos los títulos de las columnas para mostrarlas en caso de error */
  var rango = sheet.getRange("A1:B300");
  
  /* Devuelve los valores seleccionados */
  var rows = rango.getValues();
  var totalColumna = rango.getNumColumns();
    
  /* Definimos la cabecera del mensaje */
  var recuentoPendientes = "TOTAL DE PROCESOS DIARIOS PENDIENTES DE REVISAR!: ";

  /* En este espacio, vamos a recorrer las distintas celdas del sheet*/
  for { ... }

Vamos por partes…

Partimos de la clase y métodos que nos proporciona SpreadsheetApp desde la que (y cito textualmente a la documentación que nos ofrece Google) podremos «acceder a archivos de Hojas de cálculo de Google y modificarlos Las operaciones comunes son agregar hojas nuevas y colaboradores.»

Intuitivamente, vamos «rascando» métodos como sheet.getRange("A1:B300") y  rango.getValues() para obtener los datos que nos son necesarios para la obtención de la Sheet y las hojas sobre las que nos interesan ejecutar el proceso.

Sin hacer mención a algunas variables que se utilizan en el proceso (ya que se indican por comentarios), solamente, nos quedaría recorrer los datos con un bucle for y ayudarnos de los conocimientos básicos de programación, para terminar de dar forma a la obtención de datos a enviar:

    /* Recorremos las filas seleccionadas */
    for(var i = 0 ;i < rows.length; ++i)
    {
      /*Obtenemos la fecha y la formateamos utilizando la clase Utilities*/
      var rowDate = new Date(rows[i][0]);
      var rowDatefmt = Utilities.formatDate(rowDate, "GMT+1", "dd/MM/yyyy");
      
       /*Cuando coincida la fecha del día de ejecución con la fecha del sheet*/
      if(rowDatefmt == searchValue){
        //Logger.log("La fecha coincide: " + rowDatefmt)
        for(var column = 1 ;column < totalColumna; ++column){ 
          //Logger.log(rows[i][column]) 
          if(rows[i][column] != "Ok" && rows[i][column] != "Error" && rows[i][column] != "ErrorResuelto"){ 
             if(column>0){
                 totalRecuentoPendientes++;
              }
          }
        break
        }
      }
    }

    if(totalRecuentoPendientes>0){
      recuentoPendientes +="["+totalRecuentoPendientes+"]:\n";
      if(totalRecuentoPendientes>0){
        recuentoPendientes +="- Proceso de ejemplo (x"+totalRecuentoPendientes+")\n";
      }
    }
    
    this.envioXChat(recuentoPendientes, totalRecuentoPendientes)

}

Resumiendo el proceso anterior, lo que hace es lo siguiente:

1. El primer bucle recorre las filas del rango seleccionado.
2. Obtiene y formatea la fecha de la columna 0 de cada fila (es A=0).
3. Compara la fecha formateada con la fecha actual y cuando coincide…
4. Recorre las columnas a partir de la columna 1 (en este caso hay una, pero podría haber tantas por cada proceso a revisar).
5. A partir de ahí, Incrementa totalRecuentoPendientes si no ha informado con los valores indicados en las celdas.
6. Al salir de los bucles, solamente llama al método que gestionará el envío de la forma que nos interese (Google chat)

Creación del webhook:

Antes de nada, tenemos que indicarle a nuestro proceso, donde queremos enviar la información, que como hemos comentado anteriormente, la queremos notificar en un grupo de chat sobre el que tenemos visibilidad varias personas diariamente.

Para ello nos vamos al grupo sobre el que queramos enviar la información y dentro de las configuraciones del mismo, nos vamos a implementar un webhooks:

Le damos un nombre y asignamos un avatar si es necesario:

Y nos copiamos la url que nos genera para utilizarla posteriormente:

Desenlace del proceso, los avisos:

function envioXChat(recuentoPendientes, totalRecuentoPendientes){
  
  if(totalRecuentoPendientes!=0){
    
    var msgHtml = recuentoPendientes;

    var message = {
      "cards": [{
        "sections": [{
          "widgets": [{
            "textParagraph": {
              "text": msgHtml
            }
          }]
        }]
      }]
    };

    const url = "https://chat.googleapis.com/v1/spaces/...";
    const options = {
      "method": "post",
      "headers": {
        "Content-Type": "application/json; charset=UTF-8"
      },
      "payload": JSON.stringify(
        message
        )
    };
    
    const response = UrlFetchApp.fetch(url, options);
    //Logger.log(response);
    }
  
}

De la misma forma que hemos resumido el proceso obtención de datos, en este punto se realiza lo siguiente:

1. Creación del mensaje HTML y estructura del mensaje para Google Chat, donde:
   • messagees un objeto que sigue la estructura requerida para enviar mensajes a Google Chat. En este caso, se está utilizando una tarjeta (cards) con secciones (sections) y widgets (widgets) que contienen un párrafo de texto (textParagraph).
2. Definición de la URL asociada al chat y opciones para realizar una solicitud HTTP a la misma donde:
   • url  es la URL de la API de Google Chat a la que se enviará el mensaje (veremos de donde la obtenemos).
   • options  Contiene las opciones para la solicitud HTTP, incluyendo el método (post), los encabezados (Content-Type), y el cuerpo de la solicitud en formato JSON.
3. Envío del mensaje a Google Chat:
   • Se utiliza UrlFetchApp.fetch(url, options) para realizar la solicitud HTTP y enviar el mensaje a Google Chat.
     El objeto response contiene la respuesta de la solicitud (puede ser útil para depuración, pero en este script no la utilizamos).

Lanzamiento manual:

Ahora es momento de realizar el lanzamiento del aviso manual, y para ello debemos irnos a nuestro script y darle a ejecutar:

Nos vamos a nuestro grupo de chat y…

Confirmado, Ya tenemos nuestro mensaje funcionando, pero claro, lo que nos interesa es automatizarlo para que se envíe solo.

Para ello, solo tenemos que realizar algunos ajustes dentro del editor de código en Activadores y realizar la configuración que mejor nos venga:

¡Listo! Ahora sí, ya tenemos listo nuestro sistema de avisos automatizados para revisar procesos diariamente.

Resumiendo

Hemos automatizado la ejecución de un proceso sobre un sheet de Google gestionando un sistema de avisos comunicado con un chat del mismo entorno, aumentándole la funcionalidad con conocimientos básicos de la programación.

¿Alguien recoge el testigo?

Para quien tenga más inquietudes sobre su uso o quiera ampliar las funcionalidades y conocimientos de su uso, sobre el caso del ejemplo os propongo las siguientes tareas:

1. ¿Qué cambios aplicaríais para añadir 10 procesos e individualiza las revisiones entre los 3 miembros de un equipo?
2. ¿Cómo le añadiríais estilos al mensaje recibido?
3. ¿Podrías realizar una comunicación por email en vez de a un chat?

Referencias:

• https://developers.google.com/apps-script/
• https://youtu.be/aPJ-2U45BpA?feature=shared