Existen funciones de las hojas de cálculo de Google que nos permiten automatizar fácilmente muchos procesos. Una muy útil es la habilidad de detectar cuando se edita la hoja de cálculo y realizar acciones a partir de esa edición. Estamos hablando de la función onEdit, que nos permite detonar cualquier acción que le indiquemos en el momento en que la hoja sea editada, solamente usando un poquito de código de Apps Script. Hoy aprenderemos a usar onEdit.

Preparando nuestros datos

Lo primero como siempre es preparar nuestra hoja de cálculo para trabajar en ella. Empezaremos con dos columnas sencillas: Fecha de registro y Nombre. Aquí la idea es poder llevar un simple registro de nombres, y hacer que la fecha de registro se llene sola automáticamente al escribir un nombre nuevo.

¿Cómo funciona onEdit?

Ahora tenemos que aprender una función nueva: onEdit. onEdit es una función especial que se activa automáticamente al detectar un cambio en la hoja de cálculo. Tal vez recordarás que antes hemos usado funciones especiales parecidas, como por ejemplo onOpen, que se activa justo después de abrir una hoja de cálculo y ejecuta todo lo que se le indique dentro. Estas funciones especiales pertenecen a una familia llamada Simple Triggers (o activadores simples, en español), y lo que nos sirve saber de estas funciones es que son automáticas dependiendo de ciertos eventos.

A la función onEdit se le pasa un parámetro e, que contiene información del cambio que se hizo. Por ejemplo, en ese objeto viene contenida la fila y columna donde sucedió la edición. Eso nos ayudará a controlar lo que queremos que suceda después.

En la documentación de Google podemos encontrar más información útil sobre onEdit y los demás simple triggers.

Usando onEdit

OK, ahora sí, usemos onEdit para algo. Primero, vamos al menú Extensiones – Apps Script para poder editar el código de nuestra hoja. Borramos el código que viene por default y escribimos este:

function onEdit(e) {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var nombre_hoja_editada = spreadsheet.getActiveSheet().getName();
  var rango_editado = e.range;
  var fila_editada = rango_editado.getRow();
  var columna_editada = rango_editado.getColumn();

  spreadsheet.getActiveSheet().getRange(fila_editada,1).setValue(new Date());
}
Lenguaje del código: JavaScript (javascript)

En este código podemos ver que primero asignamos algunas variables que nos ayudarán, como la hoja actual, el nombre de la hoja actual, el rango que se editó, y de ese rango podemos deducir la fila y la columna que se editaron. Fíjate cómo ese rango editado viene en el objeto e que hablamos antes.

Finalmente, usamos toda esta información para escribir la fecha de hoy en la columna de Fecha de registro, es decir, la columna 1.

Si guardamos nuestro código, regresamos a nuestra hoja y agregamos un nombre nuevo a la lista, veremos que hemos tenido éxito y al agregar nombres se escriben las fechas automáticamente.

Controlando a onEdit

Ahora, hay que tener algunas consideraciones al usar onEdit, porque como es una automatización general, si no pensamos en todos los casos en los que se puede activar nuestro evento o trigger, se nos puede salir de la manos fácilmente y hará cosas que no queremos.

Por ejemplo, si editamos el nombre de la columna para que diga Nombres en vez de Nombre, se activará onEdit y cambiará el nombre de la columna de Fecha de registro a la fecha de hoy. Eso no está bien.

Y además, si editamos algo en otra columna que no sea la de nombre o en otra hoja del mismo documento en cualquier celda, también escribirá la fecha en la primera columna. Y eso tampoco está bien.

Entonces, hay que agregar algunos controles al código para asegurarnos de que solo escribirá esa fecha bajo las condiciones que nosotros queramos.

function onEdit(e) {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var nombre_hoja_editada = spreadsheet.getActiveSheet().getName();
  var rango_editado = e.range;
  var fila_editada = rango_editado.getRow();
  var columna_editada = rango_editado.getColumn();

  if(nombre_hoja_editada == "Registros" && fila_editada > 1 && columna_editada == 2) {
    spreadsheet.getActiveSheet().getRange(fila_editada,1).setValue(new Date());
  }
}
Lenguaje del código: JavaScript (javascript)

Si nos fijamos bien, ahora estamos acotando esa acción de escribir en la hoja al cumplimiento de las siguientes condiciones: que el nombre de la hoja que se editó sea Registros, que la fila que fue editada sea mayor a 1, es decir, que no se editen los encabezados, y que la columna editada sea la número 2, es decir, solamente cuando se agregue un nombre nuevo. Si guardamos el código y regresamos a nuestra hoja, ya debe estar todo bajo control.

Este es un buen momento para recordar que debemos probar todo antes de implementar, esto es especialmente relevante para triggers como onEdit.

💡 NOTA: Recuerda renombrar tu hoja a Registros para que siga funcionando todo.

Creando un contador sencillo con onEdit

Bueno, ¿que más podemos hacer con este conocimiento? ¡Cientos de cosas interesantes! Vamos a demostrar un par que me parecieron divertidas.

Aquí tenemos una hoja nueva llamada Contador, y en ella tenemos una casilla de verificación. Este tipo de celda solo puede tener dos valores: verdadero o falso. Abajo tenemos simplemente una celda con un 0, que hará las veces de nuestro contador.

Con un poco de código podemos hacer que al momento de hacer click en la casilla de verificación, se sume 1 al contador:

function onEdit(e) {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var nombre_hoja_editada = spreadsheet.getActiveSheet().getName();
  var rango_editado = e.range;
  var fila_editada = rango_editado.getRow();
  var columna_editada = rango_editado.getColumn();

  if(nombre_hoja_editada == "Contador" && fila_editada == 1 && columna_editada == 2) {
    // Obtenemos el valor actual del contador
    var contador = spreadsheet.getActiveSheet().getRange(2,2).getValue();

    // Sumamos 1 al contador y lo escribimos en la hoja
    spreadsheet.getActiveSheet().getRange(2,2).setValue(contador + 1);

    // Regresamos la casilla a su estado original
    spreadsheet.getActiveSheet().getRange(1,2).setValue(false);
  }
}
Lenguaje del código: JavaScript (javascript)

Primero estamos revisando que la hoja sea la correcta, es decir Contador, después que la fila y la columna sean exactamente B1, es decir la fila 1 y la columna 2, porque ahí tenemos nuestra casilla de verificación. Una vez cumplidas esas condiciones, obtenemos el número actual del contador en una variable. Después escribimos en la columna 2, fila 2 (osea B2) el valor del contador más uno. Y finalmente, regresamos el valor de la casilla a su estado original, osea falso, para simular que es un botón.

Si guardamos y probamos el código, ya tenemos un bonito contador interactivo.

Inventario interactivo con onEdit

Por último, ¿qué pasaría si combinamos el último ejemplo con todo lo que hemos aprendido hasta ahora? Podemos aplicarlo a un sencillo sistema de inventario interactivo, para facilitar agregar 1 a la cantidad de cierto producto que tengamos listado en una hoja llamada Inventario:

El código de este pequeño sistema sigue siendo muy sencillo, sobre todo después de lo que ya aprendimos:

function onEdit(e) {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var nombre_hoja_editada = spreadsheet.getActiveSheet().getName();
  var rango_editado = e.range;
  var fila_editada = rango_editado.getRow();
  var columna_editada = rango_editado.getColumn();

  if(nombre_hoja_editada == "Inventario" &&
                        fila_editada > 1 && 
                        columna_editada == 3 && 
                        spreadsheet.getActiveSheet().getRange(fila_editada,1).getValue() != "") {
    // Obtenemos la cantidad del producto en la fila donde se dio click a la casilla
    var cantidad = spreadsheet.getActiveSheet().getRange(fila_editada,2).getValue();

    // Escribimos la cantidad + 1 en la fila correspondiente y en la 2da columna
    spreadsheet.getActiveSheet().getRange(fila_editada,2).setValue(cantidad + 1);

    // Regresamos la casilla a su estado original en la fila correspondiente
    spreadsheet.getActiveSheet().getRange(fila_editada,3).setValue(false);
  }
}
Lenguaje del código: JavaScript (javascript)

Las condiciones son similares: el número de la fila editada debe ser mayor a 1, indicando cualquier fila después de la primera, donde están los encabezados; y la columna editada, debe ser exactamente la tercera, porque ahí siempre estará nuestra casilla de verificación. El cambio más grande aquí es que estamos revisando que la celda correspondiente a la fila que se editó, tenga algún valor diferente de vacío en la primera columna, para que no agregue el contador si no hay artículos listados.

Además, estamos usando el valor de fila_editada en getRange para asegurarnos de que se sumará 1 en la fila correcta.

Si pruebas dando click en las casillas de verificación de cada artículo, verás que sumará 1 correctamente al inventario. ¿Está genial no? 😉

¡Y listo! Ahora ya sabes cómo utilizar onEdit correctamente para automatizar un sinfín de acciones en tu hoja de cálculo.

Guía en video

Si tienes dudas puedes ver esta misma guía explicada en video:

Plantilla terminada para descargar

Espero que te haya sido útil esta guía! No olvides seguirnos en YouTube para más tips, trucos y guías de productividad.

Alekz

Alekz es un apasionado de la tecnología y los videojuegos. Además de ser locutor comercial, disfruta de la expresión artística más sastisfactoria y técnica que existe: programar (y pintar algo de pixel art). Fundó Tesel para ayudar a empoderar a las empresas a desarrollar su máximo potencial mediante la tecnología. Es un absoluto nerd de Star Trek.

Alekz

Alekz es un apasionado de la tecnología y los videojuegos. Además de ser locutor comercial, disfruta de la expresión artística más sastisfactoria y técnica que existe: programar (y pintar algo de pixel art). Fundó Tesel para ayudar a empoderar a las empresas a desarrollar su máximo potencial mediante la tecnología. Es un absoluto nerd de Star Trek.