Una pregunta que se hacen frecuentemente los usuarios de Sheets es como registrar en una celda la fecha en la que se modifica o se crea una nueva fila.
Esto se conoce en ingles como timestamp, y yo lo bautizaré «registro de fecha automática».
En este artículo vemos dos métodos para insertar una fecha automática en Google Sheets.
Que es un timestamp en Google Sheets
Un timestamp es básicamente una fecha que ponemos para registrar un ingreso de un documento o un dato.
Lo importante de esta fecha es que quede fija y que no sea fácil cambiarla.
Esto lo podríamos hacer de varias maneras: De manera manual, con una macro, o con una fórmula con referencias circulares.
Método 1: Manual
Si bien el título de este artículo es registro automático de fechas, no sobra saber un par de métodos para hacerlo manualmente antes de involucrarnos con macros o fórmulas complejas
Hay dos formas manuales de hacerlo
Función HOY() en Google Sheets
La primera es con la función HOY().
Esta función, precisamente nos trae la fecha y hora de hoy. Pero como no queremos que se modifique, y la función HOY() nos da un valor hoy y otro diferente mañana, entonces deberíamos después de aplicarla pegarla como valores para que quede fija
De nuevo, no es la mejor forma ni la más automática, pero es bueno saber que se puede hacer así.
Atajo de teclado: Ctrl + ´
La segunda es con un atajo de teclado: Ctrl + ´
Esta es una manera muy sencilla de poner la fecha de hoy sin que quede con una función como en el anterior método
Método 2: Macro
La segunda forma es a través de una sencilla macro.
Simplemente va a evaluar cuando un usuario ponga un valor en una celda específica, y una vez se haga, traerá la fecha y hora del día de hoy.
Ventaja principal
Siempre va a ejecutarse y no depende de accionar manual del usuario
Desventajas principales
- Por más de que la fecha no va a cambiar por si sola, podríamos borrar o modificar por error la formula, al menos que la protejamos.
- Como la macro edita nuestra hoja, obligatoriamente debemos dar permiso de edición a todos los colaboradores que participen, aumentando el riesgo de que cambien la celda.
Armando la base
Primero estructuremos nuestra base de ejemplo de la siguiente manera:
Vamos a crear una base de control de documentos. En esta, necesitaremos los siguientes registros
- Una columna para escribir el nombre del documento
- Una columna para seleccionar un supervisor o responsable. Un desplegable sencillo.
- Una columna de fecha. Aquí es donde nuestro código imprimirá la fecha actual
Construyendo el código
Vamos a nuestro código.
Para acceder al mismo vamos a «Herramientas» > «Editor de Secuencia de Comandos».
Vamos a borrar lo que encontramos aquí y escribir una nueva función que se llame timestamp.
function timestamp() {
//Aqui va el código
}
Vamos a conectarnos con nuestro archivo y nuestra pestaña usando los métodos .getActiveSpreadsheet() y .getSheetByName(), así:
//"Conectarse" con el archivo
var archivo =SpreadsheetApp.getActiveSpreadsheet();
//"Conectarse" con la pestaña donde se va a realizar el timestamp
var ss = archivo.getSheetByName("Datos");Luego, buscamos la celda en la que el usuario está situado, su valor, su fila y su columna.
Para esto usamos .getActiveCell, getRow() y .getColumn() respectivamente.
//Traer la celda activa
var activa = ss.getActiveCell();
//Traer el valor de la celda activa
var valor = activa.getValue();
//Traer la fila de la celda activa
var filaActiva = activa.getRow();
//Traer la columna de la celda activa
var colActiva =activa.getColumn();Construimos un condicional para asegurarnos que el código solo se ejecute cuando la fila sea superior al encabezado, este en la columna adecuada (la del responsable, en nuestro caso la 2) y que solo se ejecute en la pestaña de datos.
/*Asegurarnos que solo se ejecute despues del encabezado, en la colummna especificada y en la hoja especificada */
if(filaActiva>=2 && colActiva==2 && archivo.getActiveSheet().getName()=="Datos"){
}Por último, si se cumple la condición, vamos a imprimir la fecha en la celda que especifiquemos. En nuestro ejemplo será la celda a la derecha de la columna del responsable.
Para esto vamos a usar el método offset que nos «mueve» nuestra celda las filas o columnas que le digamos.
En nuestro ejemplo .offset(0,1) le dice a Sheets: «mueva la celda una a la derecha y cero abajo.
//Pondremos la fecha una celda a la derecha de la celda modificada
activa.offset(0,1).setValue(new Date());Una alternativa a esto, cuando no queremos que la columna del registro de fecha sea relativa a alguna otra podemos reemplazar la línea anterior por esta
ss.getRange(filaActiva,3).setValue(new Date());
Donde el número 3 lo van a cambiar por la columna donde quieran imprimir el timestamp.
Una última cosa que podremos hacer es solo imprimir la fecha si no hay valor anterior. Es decir impedir que al modificar el responsable cambie la fecha original.
Esto lo hacemos con otro condicional
if(activa.offset(0,1).getValue()){
//No hacer nada
}else{
//Poner la fecha
}De nuevo, si no quieren que la columna sea relativa, podemos cambiar el .offset por .getRange() como en el ejemplo anterior
Si tuviéramos más aprobaciones en distintas columnas, como en este ejemplo:
Solo tendríamos que añadir en la condición del código la nueva columna, así
if(filaActiva>=2 && (colActiva==2 || colActiva==4) && archivo.getActiveSheet().getName()=="Datos"){
//Ejecutar
}Donde 2 y 4 serian las columnas de los responsables, y a la derecha de cada uno estaría la fecha.
Así mismo podrían poner todas las condiciones que quieran con el mismo operador ||.
Ejecutar el código
Nos falta una última parte y es como hacer para que el código se ejecute de manera automática cuando alguien escriba algo en la base
Hay varias maneras de ejecutar el código para que de verdad sea automático
Lo último que nos falta es crear una función onEdit() y dentro de esta correr nuestra función:
function onEdit(){
timestamp();
}onEdit es un ejemplo de lo que se conoce como activadores. Un activador es una función que se ejecuta al momento que pasa algo.
En el caso de onEdit, se ejecuta cada vez que alguien edita una celda, es decir cada vez que alguien cambia el valor de una celda.
Código
Aquí te dejo el código completo.
Ten en cuenta que puedes personalizarla con algunas de las variaciones que mencioné en el artículo
function timestamp() {
var archivo =SpreadsheetApp.getActiveSpreadsheet();
var ss = archivo.getSheetByName("Datos");
var activa = ss.getActiveCell();
var valor =activa.getValue();
var filaActiva = activa.getRow();
var colActiva =activa.getColumn();
if(filaActiva>=2 && (colActiva==2 || colActiva==4) && archivo.getActiveSheet().getName()=="Datos"){
if(activa.offset(0,1).getValue()){
}else{
activa.offset(0,1).setValue(new Date());
}
}
}
function onEdit(){
timestamp()
}Método 2: Formula con referencia circular
Nota: Este método es muy interesante, pero es altamente inestable. Como experimento y para conocer los límites de Sheets es recomendable que lo prueben, pero para sus bases de «producción» y de uso diario, les recomiendo altamente que usen el método anterior.
Esta va a ser una fórmula extraña si no están acostumbrados a trabajar con iteraciones (Como el 99% de usuarios. Yo la descubrí cuando investigaba este tema, pero no tenía ni idea que existía).
Ventaja Principal
Al ser una fórmula, podemos proteger la hoja, y no va a tener problema para los colaboradores, y no van a poder cambiar o borrar las fechas
Desventajas
- Al ser una fórmula, si llegamos a eliminar el dato del responsable, la fórmula se va a resetear y quedar en blanco
- Desafortunadamente esta fórmula no funciona bien con ArrayFormula, entonces nos va a tocar arrastrarla hasta nuestra última fila, y cada vez que agreguemos nuevas filas, asegurarnos que esté con fórmula.
- Es inestable. A veces, por alguna actualización de Sheets, se puede «dañar», y todas las fórmulas pueden quedar en 0.
Construyendo la función
Básicamente vamos a usar la fórmula HOY(), pero solo la vamos a correr la primera vez que se modifique el responsable, es decir cuando la celda donde está la fecha esté vacía.
La fórmula evaluaría las siguientes 3 condiciones
- Si no hay un responsable, pues no hay fecha
- Si ya hay una fecha, mantengamos esa fecha
- Si no hay fecha, pongamos la fecha de hoy con la función HOY()
Esta es nuestra base de ejemplo
Teniendo en cuenta que la fecha iría en la C, la fórmula sería la siguiente:
=IFS(O(A2="";B2="");"";C2<>"";C2;VERDADERO;AHORA())Pero si pones esa fórmula así como está. te va a dar un error.
Nos falta un paso.
Como estamos hablando de referencias circulares (Estamos invocando a la celda C2 desde la misma celda C2), entonces debemos manejar las iteraciones para referencias circulares.
Para esto vamos a «Archivo» > «Configuración de la hoja de cálculo»
Vamos a la pestaña de «Cálculo»
Vamos a la sección «Calculo iterativo»
Escogemos la opción «Activar»
En número máximo de iteraciones ingresamos 1
Ya con esto, como por arte de magia nuestra fórmula va a funcionar.
Pero ojo, de nuevo hago la aclaración que esta fórmula es inestable y a veces se «resetea» por razones que no tengo 100% claras. Por eso les recomiendo usar el método anterior para que no tengan problemas.
Conclusion
El registro de fechas automático en Google Sheets o timestamp es una herramienta muy útil para poder llevar registros de control de documentación o de entrada y salida, y también puede ser muy útil en muchos otros casos, de gestión de tareas y demás.
Vimos dos métodos para hacer el timestamp, cada uno con sus ventajas y desventajas y que pueden llegar a ser utiles en distintos casos.
Cuéntame si te es útil y que nos quedó faltando.
Suscríbete para acceder al curso «Descubriendo el poder de Google Sheets» y empezar tu camino a la maestría de Sheets, Forms y Apps Script
Deja una respuesta