Cómo detectar cambios en un spreadhseet con Google Apps Script


Artículo referente a como usar Google Apps Script en spreadsheets

Un lector del blog me ha pedido de que manera puede detectar cambios en una hoja de cálculo con google apps script y apuntar la fecha de dichos cambios.

Esto es una pregunta muy recurrente cuando se trata de ficheros compartidos con muchas personas en las que todas pueden editar.

Os dejaré un ejemplo de como hacerlo, evidentemente, no tiene por que ser exactamente como vosotros lo necesitéis, si tenéis dudas de como implementarlo en vuestro caso no vaciléis en preguntar en los comentarios.

Aquí tenéis el código:

function onEdit(e)
{  
     //Queremos que solo funcione en la hoja 1, si lo quereis en todas, quitad este if
     if(SpreadsheetApp.getActiveSpreadsheet().getSheetName() == "Hoja 1")
     {
         //Obtenemos donde  se ha realizado el cambio
        var range = e.range;

        //Apuntamos en la primera columna de la fila donde se ha hecho el cambio la fecha de modificación
        SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Hoja 1").getRange(range.getRow() , 1).setValue(new Date());
     }
}

Para introducirlo en vuestro spreadsheet, herramientas > Editor de secuencias de comandos y copiáis el código. Guardad y ya está.





Para ver como funciona solo tenéis que modificar el contenido de una celda cualquiera y veréis como al cabo de un segundo se pondrá la fecha actual en la primera columna de esa fila.


Espero que os haya servido

Nos vemos










94 comentarios:

  1. HOla
    Este ejemplo me ha sido muy util porque nos estamos volviendo locos en como hacerlo.

    Pero necesitaríamos hacerlo diferente.
    Aparte de poner el cambio en otra hoja (que esto lo solucione cambiando el segundo Hoja 1) que copie la linea en la siguiente disponible aparte de añadir la
    fecha
    Es como un registro de cambios

    Se te ocurre como podría ser?

    Gracias

    ResponderEliminar
    Respuestas
    1. HOLA PRUEBA AGREGANDO ESTO.
      function onEdit(e) {
      var range = e.range;
      range.setNote('Ultima modificación: '+ new Date());
      };

      Eliminar
    2. Mis disculpas por no ver la pregunta original.

      Intento responder todas las preguntas pero a veces se me cuela alguna.

      Si, SACM tiene razón con el código que ha publicado.

      Nos vemos

      Eliminar
    3. Hola. Yo también necesito algo similar. Que al detectar cambios me ejecute una función cualquiera que sea. Pero que ésta se ejecute automáticamente sin necesidad darle la orden. Gracias por la ayuda y tus aportes

      Eliminar
    4. Hola Alexander.

      En el onEdit deberias poder ejecutar funciones. Ahora bien, no todas.

      El onEdit se ejecuta sin permisos, así que no puedes enviar correos, acceder a otros ficheros y cosas similares.

      Basicamente desde el onEdit puedes modificar el spreadsheet actual y poco más.

      Dime que función quieres ejecutar y te diré como puedes abordar el problema

      Nos vemos

      Eliminar
    5. Hola buenas tardes, yo necesito enviar correo, y colocar información en archivos externos, ya he utilizado la función de envío y he usado funciones con SpreadsheetApp.openById(""); pero necesito ejecutarlo desde el drive con android y aquí no funcionan los script asignados a imagenes... por eso necesito el onedit, pero veo de acuerdo a tus comentarios que está limitado a ciertas acciones... que me recomiendas hacer para solucionar mi problema? de antemano muchas gracias!!

      Eliminar
    6. Buenas,
      Ponerlo en el menu superior?

      SpreadsheetApp.getUi().createMenu....

      Mirate esto, yo creo que te puede ir bien. Aqui tienes un ejemplo: https://googleappscriptsweb.blogspot.com/2017/08/como-hacer-una-factura-sencilla-con.html

      Eliminar
  2. Este comentario ha sido eliminado por el autor.

    ResponderEliminar
  3. Buenos dias

    Me sale este error :

    TypeError: Cannot read property "range" from undefined.

    en la linea

    var range = e.range;

    Como se resuelve?

    ResponderEliminar
    Respuestas
    1. Hola Cesar,

      Me jugaria algo que estas ejecutando la funcion onEdit desde el editor de código... cierto?

      La funcion onEdit se ejecuta cuando editas algo en el spreadsheet. ejecutarlo de otra manera te dara ese error.

      Eso es debido a que el objeto e (lo que vendria a ser la info de la edicion) solo la tendras si se produce una edicion.

      Avisame si no fuera el caso.

      Nos vemos

      Eliminar
  4. Hola Pako LordPakus gracias por el ejemplo me sirvió de 10. lo que si me podes ayudar a que tome los cambios de una columna especifica, ejem. columna 2.
    desde ya mil gracias.-

    ResponderEliminar
    Respuestas
    1. Hola,

      Es fácil lo que pides.

      Justo despues de obtener el rango: var range = e.range;

      Puedes mirar si ese cambio aplica a una columna especifica.

      if(range.getColumn() == 2)
      {
      //balbalalblab lo que necesites hacer
      }

      Cualquier cosa me lo dices

      Nos vemos

      Eliminar
  5. Sos un capooo. gracias, funciono perfecto.-

    ResponderEliminar
  6. LordPakus disculpa las molestias pero habría una posibilidad de que una ves que se ejecute la condición fije el valor de tiempo, independientemente de los cambios que se realicen en la columna 2.
    desde ya mil gracias.

    ResponderEliminar
    Respuestas
    1. Si te he entendido bien lo que quieres es que el timestamp solo te lo haga la primera vez no?

      Lo único que te faltaria hacer seria mirar la celda donde vas a poner el timestamp si está vacia o no. Si está vacia seteas el valor, si tiene valor no lo sobreescribes.

      Dime si te entendido bien

      Nos vemos

      Eliminar
  7. Hola LordPakus disculpa la demora en responder, entendiste bien, es exactamente lo que necesito hacer, pero mi conocimiento es escaso en programación y no se setear la celda.
    desde ya muchísimas gracias

    ResponderEliminar
    Respuestas
    1. A ver si con esto lo ves más claro:
      sheet.getRange(x,y).setValue(valor)

      Ya me contaras

      Nos vemos

      Eliminar
    2. Esto me pasaste el día 4 y lo tengo que ejecutar con el otro código.
      El timestamp funciona perfecto pero no logro que funcione una sola ves.

      Eliminar
  8. Hola LordPakus no puedo hacer funcionar, el código que estoy usando es este y funciona perfecto, pero no logro fijar el resultado.-


    function onEdit(e)
    {
    //Queremos que solo funcione en la hoja 1, si lo quereis en todas, quitad este if
    if(SpreadsheetApp.getActiveSpreadsheet().getSheetName() == "Hoja 1")
    {
    //Obtenemos donde se ha realizado el cambio
    var range = e.range;
    if(range.getColumn() == 2)
    //Apuntamos en la primera columna de la fila donde se ha hecho el cambio la fecha de modificación
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Hoja 1").getRange(range.getRow() , 1).setValue(new Date());
    }
    }

    ResponderEliminar
    Respuestas
    1. "funciona perfecto, pero no logro fijar el resultado"
      Funciona o no funciona? :)

      Que quieres decir con "fijar el resultado"?

      Nos vemos

      Eliminar
    2. Prueba con esto:

      function onEdit(e)
      {
      //Queremos que solo funcione en la hoja 1, si lo quereis en todas, quitad este if
      if(SpreadsheetApp.getActiveSpreadsheet().getSheetName() == "Hoja 1")
      {
      //Obtenemos donde se ha realizado el cambio
      var range = e.range;
      if(range.getColumn() == 2)
      {
      //Apuntamos en la primera columna de la fila donde se ha hecho el cambio la fecha de modificación
      var range_to_write = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Hoja 1").getRange(range.getRow() , 1);

      if( range_to_write.getValue() == "" )
      range_to_write.setValue(new Date());
      }
      }
      }

      Eliminar
  9. Mil graciaassssss. sos un genio funciona perfecto.
    Gracias.

    ResponderEliminar
  10. Hola LordPakus de nuevo yo Martin, quisiera hacerte una consulta mas pero esta ves quisiera que me cobres, pues es muchísimo le que te estoy pidiendo.
    te paso a consultar.
    el timestamp funciona de 10, es mas logre ejecutarlo en varias columnas de una sola hoja y poder cruzar el tiempo de arribo y el de respuesta, pero mi hoja de trabajo esta compartida con varios usuarios, y quise restringir el acceso de esos usuarios para que no puedan borrar las fechas que se generan en forma automática y el timestamp no se ejecuta si ese usuario tiene restringida el acceso a sobrescribir la fecha osea no puede escribir en este rango Pedidos!A:A,
    la pregunta seria como el usuario que tiene acceso restringido puede ejecutar el timestamp y a la misma ves no pueda modificar las fechas automaticas?
    desde ya mil gracias

    ResponderEliminar
    Respuestas
    1. Tengo malas noticias, lo que pides no se puede hacer.

      El evento de onEdit se ejecuta con los permisos del usuario que hace la edición, es decir, es lo mismo que si ese usuario ejecutara directamente esa función.

      Si ese usuario no tiene acceso a esa celda no hay manera de hacerlo.

      La unica opción para hacer algo parecido seria montar una web y que los usuarios usaran esa web para atacar al spreadsheet.

      El único problema de esto es que se te convertiría en un proyecto de docenas de horas de trabajo que no te compensa si solo quieres el timestamp.

      Lo siento por no poder darte mejores noticias.

      Nos vemos

      Eliminar
  11. Entiendo, no importa mil gracias igual.
    te quería consultar si se puede hacer y como una consulta web sencilla, que el cliente consulte un código de producto y le devuelva como resultado 4 o 5 datos, Precio, Disponibilidad, Reemplazo, Descripción, y que la base de dato sea una hoja de calculos de google. y que no sea lenta.

    ResponderEliminar
    Respuestas
    1. Eso si se puede hacer, pero deberiamos hablar de algun precio, es un proyecto a medida.

      Enviame un mensaje a Fiverr y lo miramos (Tienes el link en la columna de la derecha)

      Gracias por todo!

      Eliminar
  12. Hola LordPakus... el codigo me funciona cuando edito manualmente una celda...ahora estoy generando desde un formulario nuevos registros, pero ahi no se ejecuta onedit... espero me puedas ayudar...muchas gracias

    ResponderEliminar
    Respuestas
    1. Desde un formulario va diferente.

      En vez de llamarle onEdit(e) llamala onFormSubmit(e) entra en el editor de triggers y pidele que ejecute esa función cuando alguien haga un submit de un formulario.

      Es bastante más facil de lo que parece, si tienes problemas, dimelo

      Eliminar
    2. graciassssssssssssssssssssssssss

      Eliminar
  13. Que tal mi lord,

    Quiero realizar algo parecido a la solución que propusiste pero diferente. Quiero concentrarme solo en una columna de datos y quiero detectar la actividad al momento de poner valores específicos en las celdas. Al obtener el valor que yo busco quiero mandar un correo correspondiente a la celda donde se realizó el cambio. ¿Cómo puedo solucionar esto?

    Muchas gracias!

    ResponderEliminar
    Respuestas
    1. Hola,

      Lo que pides es "imposible", al menos directamente :)

      Te explico. Google es (logicamente) muy restrictivo con los permisos. Cuando tu editas un spreadsheet lo que ejecutes lo hará sin permisos ni usuario. Esto quiere decir que solo podrás modificar el mismo libro donde estes (que es donde tienes permisos dado que has editado).

      La función para enviar un correo necesita de un usuario con permisos (dado que alguien tiene que ser quien envie el correo).

      Por lo tanto, no puedes enviar un correo desde el onEdit.

      Ahora bien, el truco está en que puedes llenar una hoja oculta desde el onEdit y tener un chronotask que se ejecute cada X minutos (con tu usuario) que revise esa hoja y envie los correos como necesites.

      Esto hará que haya un cierto delay en el envio del mensaje pero te permitiria hacer lo que quieres.

      Intentalo, si tienes algún problema puedo intentar ayudarte.

      Si quieres que te lo haga yo directamente deberiamos mirar de buscar una opción profesional. No deberia ser un proyecto demasiado caro. Puedes contactarme en Fiverr si quieres que me lo mire yo como proyecto. Aquí tienes el enlace a Fiverr. http://www.fiverr.com/s2/bbb67c5713

      Eliminar
    2. Oh vaya, creí que lo que necesitaba no era tan complicado. Traté de hacer el envío de correo pero creo que no será del todo necesario. Muchas gracias por tu apoyo.

      Eliminar
    3. Para eso estamos.Cualquier cosa ya lo sabes

      Nos vemos

      Eliminar
  14. Que tal LordPakus, tengo una duda,

    me gustaria poder obtener la notacion de una celda modificada pero no la que yo edito, es decir, al yo modificar una celda en una hoja por ejemplo A1 y que esta automaticamente me modifique b1 de otra hoja me gustaria que devolviera el b1 y no a1 por ser la que yo modifiqué.

    espero que puedas ayudarme.
    Saludos.

    ResponderEliminar
    Respuestas
    1. Lo mejor que puedes hacer es pillar directamente el valor de la celda que te interese.

      var value = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("tuhoja").getRange(2,1).getValue();

      Espero que te sirva

      Eliminar
    2. gracias por tu respuesta,
      pero por ejemplo que no supiera en que rango esta el valor si no que tuviera que buscar que buscar el rango como el ultimo que cambio de valor.?

      Eliminar
    3. Eso que pides es bastante más complicado.

      Deberias tener en el onEdit un control de en que sheet se está aplicando y si el cambio se aplica en la hoja que quieres tener controlada guardar ese valor cambiado en algún otra celda.

      De esta manera podrias acceder al ultimo valor editado.

      Espero haberme explicado bien

      Nos vemos

      Eliminar
  15. Estaba pensando hacerlo de esta manera
    function onEdit(change){

    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("pais");
    var rango = change.range.getA1Notation();
    Logger.log(rango);


    }

    pero solo me devuelve la notacion de la hoja activa, es decir la que yo editoy no la de la hoja que le digo que busque.

    en caso de que el valor que busco no sea un OnEdit sino un OnChange que el activador se dispare con un cambio no una edicion no devuelve ningun valor.

    ResponderEliminar
    Respuestas
    1. Hasta donde yo se onChange no existe: https://developers.google.com/apps-script/guides/triggers

      Creo que hace años había algo parecido pero juraría que se eliminó en alguno de los cambios de seguridad que hizo Google.

      Hasta donde se, todos los cambios que se hagan en la hoja manualmente ( o a través de script) se gestionan con el onEdit.

      Los cambios que vengan a través de formulario se gestionan a través del onFormSubmit (ejecutando dicha función desde la sección de triggers de GAS)

      Fuera de esto, creo que lo que quieres, no es factible.

      Suerte

      Eliminar
  16. con OnChange me refiero a una funcion de nombre cualquiera pero editada para que se dispare con cualquier cambio.

    ResponderEliminar
  17. Hola, necesito que se ejecute este filtrado al editar solo la celda B2 de la hoja "CONSULTA". ¿Como quedaría la sintaxis de onEdit? ¡Gracias!

    var spreadsheet = SpreadsheetApp.getActive();
    spreadsheet.getRange('B2').activate();
    var criteria = SpreadsheetApp.newFilterCriteria()
    .setHiddenValues(['', 'Nº CORTE'])
    .build();
    spreadsheet.getActiveSheet().getFilter().setColumnFilterCriteria(2, criteria);

    ResponderEliminar
    Respuestas
    1. Si quieres que el onEdit funcione solo con una casilla en concreto deberias tener en cuenta la condición que tu quieras al inicio del onEdit(e)
      var range = e.range;
      var sheet = range.getSheet();
      var name = sheet.getName();
      var row = range.getRow();
      var col = range.getColumn();

      if( (name != "CONSULTA") || (row != 2) || (col != 2))
      return;

      //Aqui el codigo que tu necesites

      No lo he probado, pero debería funcionarte

      Espero que te sirva

      Nos vemos

      Francisco

      Eliminar
  18. Esto me funciono, la dejo por si a alguien le sirve de ejemplo. Gracias

    function onEdit(e){

    if(e.source.getSheetName() === 'CONSULTA' && e.range.rowStart === 2 && e.range.columnStart === 2 && e.range.rowEnd === 2 && e.range.columnEnd === 2){
    var spreadsheet = SpreadsheetApp.getActive();
    spreadsheet.getRange('B2').activate();
    var criteria = SpreadsheetApp.newFilterCriteria()
    .setHiddenValues(['', 'Nº CORTE'])
    .build();
    spreadsheet.getActiveSheet().getFilter().setColumnFilterCriteria(2, criteria);
    } else {
    return;
    }
    }

    ResponderEliminar
  19. Hola Buenas tardes
    Necesito que todo lo que se modifique de la celda A me ponga la fecha de modificación en la celda B y capture el usuario que realizo el cambio, Me puedes ayudar?

    ResponderEliminar
  20. Buenas,

    Lo has intentado? Si tu fin es el aprendizaje intentalo e intentaré resolver tus dudas.

    Si tu fin es tener un producto para una empresa tal vez podamos apalabrar una pequeña cantidad a fin de hacerte el proyecto (http://www.fiverr.com/s2/bbb67c5713)

    Las pistas que puedo darte son:

    1. Metodo onEdit(e) recibe cuando se hace una edicion en el fichero
    2. El parametro e hace referencia a que es lo que se ha tocado
    3. Sabiendo la celda que se ha tocado puedes saber en que fila se ha hecho la edicion y escribir la celda que te interesa.

    En cuanto al usuario , primero intenta el resto y luego hablamos de esto.

    Suerte!

    ResponderEliminar
    Respuestas
    1. Hola Lord,
      esto es lo que necesito, intento obtener el usuario pero no me deja, vi que abajo comentas que es dificil mostrar en una celda quien lo modificó.
      ¿ que solución podemos encontrar? porque imagino que es una practica comun en un trabajo colaborativo saber quien modificó, cuando y qué celda (o que fila al menos)

      este es el codigo que estoy corriendo, no tengo conocimientos de programacion pero queria agregar en la columna "B" quien hizo la modificación, y luego con una function sendEmail o algo de eso avisar a los usuarios que alguien modificó la planilla.
      ¿puedes ayudarme? muchas gracias

      function onEdit(e)
      {
      //Queremos que solo funcione en la hoja 1, si lo quereis en todas, quitad este if
      if(SpreadsheetApp.getActiveSpreadsheet().getSheetName() == "Hoja 1")
      {
      //Obtenemos donde se ha realizado el cambio
      var range = e.range;

      //Apuntamos en la primera columna de la fila donde se ha hecho el cambio la fecha de modificación
      SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Hoja 1").getRange(range.getRow() , 1).setValue(new Date());
      SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Hoja 1").getRange(range.getRow() , 2).setValue('aca nombre de quien modifico la fila');

      }
      }

      Eliminar
    2. lo logre, adentro de setValue(Session.getEffectiveUser().getEmail())

      lo que necesito ahora es enviar un mail notificando a los usuarios con un registro de los cambios hechos en la planilla, no se si se podrá.

      saludos

      Eliminar
    3. El onEdit no permite realizar envios de mails. Mas que nada por que en el onEdit no das permisos para nada por lo que el onEdit no tiene los permisos de usuario para enviar correos en su nombre.

      Solución: En el onEdit vas apuntando en otra hoja que es lo que quieres enviar y a quien se lo quieres enviar y montas una función aparte que cada X minutos chequee esa hoja. Si detecta información, envia un correo a donde le digas y borra la información. Algo así como un buffer de información.

      Espero que te sirva!

      Nos vemos

      Eliminar
  21. Hola, me funciona genial, solo quisiera saber como puedo llevarlo a hora, minuto y segundos, muchas gracias

    ResponderEliminar
    Respuestas
    1. Te dire que lo tienes complicado :)

      Google Apps Script es un lenguaje de servidor, no está enfocado a tener precisión de segundos.

      Ahora bien, tienes un truco que puedes hacer. Podrías servir una web desde un spreadsheet y desde el javascript de la web medir el tiempo y ejecutar una función de Google Apps Script.

      No es trivial.

      Ya me explicaras como va

      Nos vemos

      Eliminar
  22. LordPakus, necesito tu ayuda, te comento como es:
    tengo un documento de google (hoja de calculo) con 4 pestañas, la primera pestaña es la de inicio donde hay un logo, luego en las siguientes 3, usuario A, B y C. este documento lo comparto con 3 usuarios para que llenen información, mi problema es que los usuarios pueden ver la información entre todos, pero no pueden editar porque restringi los permisos. lo que quiero es que tampoco puedan ver las demas hojas a excepcion de la pestaña de inicio, se me ocurre la idea de primero obtener la cuenta google activa de cada usuario, luego que el usuario A,B o C quiera acceder a otra hoja que no le pertenece, al hacer click en otra hoja se dispare un evento click antes de, para que no le permita cambiar de hoja sino quedarse en su hoja que le pertenece.

    ResponderEliminar
    Respuestas
    1. Hola,

      No vas a poder.

      Siempre que me piden cosas de este estilo acabamos montando una web con permisos en que cada usuario tiene acceso solo a la zona restringida que le pertenece: https://googleappscriptsweb.blogspot.com/2015/03/como-crear-una-web-dinamica-con-google.html

      Si te da mucho problema y necesitas ayuda más profesional tal vez podamos montarlo como proyecto: http://www.fiverr.com/s2/bbb67c5713

      Suerte , que no es un proyecto facil

      Eliminar
    2. ok Gracias por tu apoyo LordPakus.
      Saludos.

      Eliminar
  23. Hola, alguna manera de dejar evidencia en una celda, del usuario que modificó? Muchas gracias

    ResponderEliminar
    Respuestas
    1. Hasta donde yo se, no podrás hacerlo.

      Para saber que usuario ha ejecutado el script hacen falta unos permisos que el onEdit no puede pedir.

      La solución que a veces he hecho para estos casos es generar una web con los datos del spreadsheet de forma que los usuarios solo trabajen a través de la web y en este caso si que se puede registrar lo que se quiera.

      No es una solución fácil. Hazme llegar si te puedo ayudar en algo

      Nos vemos

      Eliminar
  24. Hola LordPakus me parece genial tu explicación y formulas, yo tengo un pequeño conocimiento de programación pero no logro hacer lo que quiero, si puedes ayudarme te agradeceria muchisimo.

    Mi caso es que tengo una hoja compartida con muchas personas a la vez y necesito ver las primeras 3 modificaciones que se hagan a una celda y necesito ver el timestamp de esas modificaciones y tengo una columna donde escogen el nombre de quien lo hizo, hasta ahorita con los códigos que presentas se va modificando cada vez que se le hace una actualización, pero necesito que se ponga en tres columnas diferentes para que quede un registro de las primeras tres modificaciones.

    Muchas gracias por tu ayuda

    ResponderEliminar
    Respuestas
    1. Buenas, lo que deberias hacer es que el onEdit buscara la siguiente celda libre donde apuntar el timestamp en la misma columna.

      Si no tiene celdas libres que no apunte nada.

      Pruebalo, si te da problemas dimelo

      Nos vemos

      Eliminar
  25. Muchas gracias por compartir tu conocimiento, por ahora tengo la siguiente novedad:

    El script funciona perfecto si realizo al modificacion de la celda de forma manual por asi decirlo, pero en mi caso los datos vienen de otra hoja si son pegados mediante otro script, en este caso no la columna en donde quiero montar la informacion del timestamp no funciona. Como podria lograrlo?

    Muchas gracias

    ResponderEliminar
    Respuestas
    1. Deberas usar el evento onChange en vez del onEdit. El onEdit funciona con cambios manuales, el onChange cuando se producen cambios de terceros.

      Cuidado por que el onChange es un evento que se ha de instalar a diferencia del onEdit.

      Si tienes cualquier duda no vaciles en hacermela llegar, a veces puede ser un tema un poco lioso

      Eliminar
  26. Hola amigo no tengo ni la menor idea de programación, sin embargo se entrar en el editor de macros de google spreadsheets, lo he intentado muchas veces sin logro alguno porque no se si lo copio y pego mal o no lo estoy haciendo donde es..

    1. Necesito un código que al realizar cambios o modificaciones en la fila de celdas desde la 12 hacia la 1 se genere la acción de eliminar el contenido de la celda 13, 14, 15, 16 y que eso pase solo en esa fila basado en la modificación de ese contenido desde otra hoja.

    si me puedes ayudar te lo agradezco ya no se cuantos foros ver y cuantos vídeos al respecto.

    ResponderEliminar
    Respuestas
    1. Hola,

      Como siempre digo si tenéis dudas de código os ayudo en lo que sea, pero si queréis que os haga un proyecto tendremos que hablar de algún tipo de retribución: https://googleappscriptsweb.blogspot.com/p/como-contratar-mis-servicios-de.html

      Nos vemos!

      Eliminar
  27. Hola, gracias por todo lo compartido!
    Logré hacer que al modificar algún valor en una planilla me escriba fecha y hora de modificacion en la misma fila en la columna que designo para ello.

    El problema que se me presentó es que también me afecta a la primer fila que es donde están los títulos de la planilla.

    Quisiera exceptuar la primer fila de dicha macro. ¿cómo puedo hacerlo?

    Aquí la macro:

    function onEdit(e)
    {
    if(SpreadsheetApp.getActiveSpreadsheet().getSheetName() == "Hoja1")
    {
    var range = e.range;

    SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Hoja1").getRange(range.getRow() , 4).setValue(new Date());
    }
    }

    ResponderEliminar
  28. Hola,

    No lo he probado pero deberia funcionarte con esto.

    if ( range.getColumn() != 1 )
    {
    // tu codigo
    }

    Ya me diras como te ha ido

    Nos vemos

    ResponderEliminar
  29. Hola que tal,

    Disculpa quisiera usar onEdit para enviar un correo tengo este escrip pero en vez de que ponga el fecha de modificación quiero que mande correo si se edita la columna 2:
    function onEdit(e)
    {
    //Queremos que solo funcione en la hoja 1, si lo quereis en todas, quitad este if
    if(SpreadsheetApp.getActiveSpreadsheet().getSheetName() == "x")
    {
    //Obtenemos donde se ha realizado el cambio
    var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('x');
    var range = e.range;
    var aa = ss.getActiveCell();


    if (aa.getColumn() != 1);

    //Apuntamos en la primera columna de la fila donde se ha hecho el cambio la fecha de modificación
    ss.getRange(range.getRow() ,2 ).setValue(new Date());


    }
    }

    ResponderEliminar
  30. Buenas tardes Lord

    He intentado usar tu sistema para un juego con mis alumnos, de tal forma que van acumulado puntos con sus trabajos y cuando alcanzan determinados puntos van subiendo de nivel

    La cosa es que quiero que quede constancia de la fecha en que lo consiguen (las notas de los exámenes se incrementan ligeramente cuando van subiendo de nivel) y necesito que quede constancia de la fecha en que han cambiado de nivel para que la hoja de cálculo realice automáticamente la corrección de nota.

    El problema es que el onEdit solamente funciona si el cambio de hace manualmente, pero necesitaría que lo hiciese de forma automática, ya que los cambios se realizan mediante formulas (sumando los puntos de poder conseguidos)

    ¿Hay alguna forma de que el onEdit u otro similar se active cuando el cambio de la celda se realiza de forma automática?

    Muchas gracias y perdona la parrafada.

    ResponderEliminar
    Respuestas
    1. Hola David,

      Me encanta cuando los profesores usan Google Apps Script para mejorar la experiencia educativa, os tengo en un pedestal, va en serio.

      En cuanto a tu pregunta yo le veo dos posibles soluciones:
      1. Usar onChange en vez de onEdit. Por norma general onChange deberia funcionar donde onEdit no llega, ahora bien, no tengo claro que funcione con el resultado de funciones. Es posible que no, y yo ahora mismo voy muy liado para probarlo. Puedes mirartelo a ver si te funciona por este camino.
      2. Sino te funciona el onChange otra manera seria mirarlo a través de una formula personalizada. Es decir, que tu uses una formula pero que realmente lo que estes haciendo sea ejecutar un función de código de Google Apps Script. En esa función podrias hacer lo que quisieras. Tiene limitaciones a nivel de permisos, pero para lo que quieres debería funcionarte. Si quieres puedes mirarte este link para saber más como va: https://googleappscriptsweb.blogspot.com/2015/03/como-hacer-una-funcion-personalizada.html

      Espero que te haya servido de ayuda, sino, dímelo y buscaremos otra solución.

      Nos vemos!

      Eliminar
    2. Hola.

      Gracias por tu ayuda, pero no he logrado que funcione.

      He cambiado el código por onChange y queda así:

      function onChange(e) {

      addTimestamp(e);

      }


      function addTimestamp(e){
      //variables
      var startRow = 2;
      var targetColumn = 2;
      var hoja = "Fechas";

      //get modified row and column
      var row = e.range.getRow();
      var col = e.range.getColumn();

      if(col === targetColumn && row >= startRow && e.source.getActiveSheet().getName() === hoja){

      var currentTime = new Date();

      e.source.getActiveSheet().getRange(row,3).setValue(currentTime);

      } //END IF check date exists
      } // End IF check column, row, worksheet
      } // END function addTimestamp


      Como ves al introducir un dato o cambiar lo que esta en las celdas de la columna determinada aparece la fecha en la celda correcta, pero al poner como contenido de las celdas operaciones dependientes de otras celdas y cambiar el valor de estas no se actualiza.

      He intentado crear el código para usarlo como función directa (la segunda solución) pero no he sido capaz de hacer algo que funcionase.

      Eliminar
    3. Ponte en contacto conmigo a través de este formulario: https://googleappscriptsweb.blogspot.com/p/como-contratar-mis-servicios-de.html y lo hablamos con más calma.

      Normalmente para profesores el proyecto lo hago gratis o cobro algo simbólico ( como mucho ), depende del tiempo que lleve.

      Espero tu mensaje

      Eliminar
  31. Hola,
    ¿Me podrias decir donde esta mi falla?
    Mira que tengo un botón que corre una macros y también hice un onedit para poderlo correr en android. Pero cuando uso el onedit, me corre 2 veces la macro. Y si me podrías decir como llamo una segunda función con onedit

    function prueba1() {
    var spreadsheet = SpreadsheetApp.getActive();
    spreadsheet.setActiveSheet(spreadsheet.getSheetByName('REGISTRO POR DIA'), true);
    spreadsheet.getRange('A7').activate();
    spreadsheet.getRange('\'FORMATO DE INSPECTORES\'!A35:CX35').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
    spreadsheet.setActiveSheet(spreadsheet.getSheetByName('REGISTRO POR DIA'), true);
    spreadsheet.getRange('7:7').activate();
    spreadsheet.getActiveSheet().insertRowsBefore(spreadsheet.getActiveRange().getRow(), 1);
    spreadsheet.getActiveRange().offset(0, 0, 1, spreadsheet.getActiveRange().getNumColumns()).activate();
    spreadsheet.setActiveSheet(spreadsheet.getSheetByName('BITACORA'), true);
    };
    function PRUEBABORRAR() {
    var spreadsheet = SpreadsheetApp.getActive();
    spreadsheet.getRangeList(['B2', 'C1', 'D2', 'F2', 'H2', 'A5', 'A8:I9', 'K8:W9', 'A13:H14','B19:B20', 'K13:L16', 'A25:H25', 'L23:N25']).activate()
    .clear({contentsOnly: true, skipFilteredRows: true});
    };
    function onEdit(e){
    var NombreHoja = e.source.getActiveSheet().getName();
    var range = e.range;
    if(NombreHoja == "FORMATO DE INSPECTORES" & range.getA1Notation()=="E27" & !e.range.isBlank())
    {
    prueba1();
    }
    };


    ResponderEliminar
    Respuestas
    1. Hola,

      Me he montado un spreadsheet con las hojas necesarias para probar tu código y no soy consciente del problema que decias. Podria ser que hayas instalado algún trigger manualmente?

      Aparte de eso, tengo que decirte que el código es bastante ineficiente. Te va a dar problemas toda la vida, tanto a nivel de rendimiento como , sobretodo, de mantenibilidad.

      Aunque lo tengas funcionando ahora, es un proyecto muerto, no vas a poder mantenerlo , mejorarlo o ampliarlo en el futuro, dado que la cálidad del código es bajita.

      Si quieres, ponte en contacto conmigo: https://googleappscriptsweb.blogspot.com/p/como-contratar-mis-servicios-de.html y te intento hacer un presupuesto ajustado para dejarte el código pequeño, limpio, eficiente y mantenible.

      Nos vemos!

      Eliminar
    2. Muchas gracias por tu rápida respuesta, solo soy un pasante😅

      Eliminar
    3. Para eso estamos!

      Cualquier duda no vaciles en hacérmela llegar

      Nos vemos

      Eliminar
  32. Hola! Tengo este código:
    lo que me hace es copiar la celda activa en la misma hoja, pero cuando quiero copiarla en otro libro distinto ya no funciona. Si la funcion onEdit la cambio de nombre y lo ejecuto manualmente si funciona.
    function onEdit(){
    var archivo = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    var nombreHoja = archivo.getName();
    var activa = archivo.getActiveCell();
    var filActiva = activa.getRow();
    var valor = activa.getValue();
    var colActiva = activa.getColumn();
    var fondo = activa.getBackground();

    activa.offset(0,1).setValue(valor).setBackground(fondo);

    cambioDatoOrigen(nombreHoja,filActiva,colActiva,valor,fondo);
    }

    function cambioDatoOrigen(nombreHoja,filActiva, colActiva,valor,fondo){

    var ss = SpreadsheetApp.openById("XXX");
    var sheet = ss.getSheetByName(nombreHoja);
    var range = sheet.getRange(filActiva, colActiva);

    range.setValue(valor).setBackground(fondo);


    }

    ResponderEliminar
    Respuestas
    1. No te va a funcionar nunca. OnEdit se ejecuta sin permisos y no se instala, así que no puede "tocar" nada que no sea en el mismo spreadsheet. Abrir otros spreadsheet esta totalmente prohibido desde el onEdit.

      En estos casos la única opción que te veo es que te marques en un hoja lo que quieres hacer y luego un trigger se encargue de realizar la tarea.

      Tal vez te link te pueda ser de utilidad: https://www.tutorialesgoogleappscripts.com/2020/09/como-crear-triggers-con-google-apps-script.html

      Suerte! Nos vemos!

      Eliminar
  33. cómo puedo ejecutar una función (resta de dos columnas) cuando los valores de un campo presenta cambios. Por favor si alguien me puede ayudar éste es mi correo fpaez7@gmail.com

    ResponderEliminar
    Respuestas
    1. Disculpa por responder con tantos dias de retraso. Lo más fácil para restar dos columnas tal y como comentas seria realizando una formula sencilla del tipo =A1 - B1 y arrastrar la formula hasta donde lo necesites. Te serviria?

      Eliminar
  34. Hola! Soy nuevo en Google App Script. Tengo una celda que cambia de valor según una fórmula, como puedo hacer para que cuando el valor de la celda cambie desde la fórmula, se lance el evento onEdit? De antemano, muchas gracias!

    ResponderEliminar
    Respuestas
    1. Pues te diré que depende. En principio te tengo que decir que no es tan facil como parece. Comparteme el documento donde quieres hacerlo y puedo probar a revisarlo cuando tenga un momento.

      Eliminar
  35. Hola LordPakus, gracias por tan pronta respuesta. Ese caso lo resolví por UI.
    El problema que tengo con el código, es que hago un filtro que me devuelve unos registros, pero cuando obtengo el Range de ese filtro, lo que me muestra es todos los registros desde el inicio. Recorrerlo se hace complicado cada vez se aumentan los registros. Hay manera de obtener en el rango solo los resultados del filtro? Te agradezco mucho!

    ResponderEliminar
  36. Hola amigo muy buenas tardes, de antemano muchas gracias por toda la ayuda que me puedas proporcionar. Existe una manera con el código que nos diste poder copiar toda la fila de la celda en donde se está realizando el cambio para trasladarlo a la hoja 2 del sheets? te paso el código que tengo que actualmente no me está realizando la función, lo que en resumidas cuentas quiero es que identifique la fila de la celda en la columna (D) que se está modificando, y copie y pegue la información de toda la fila (A), (B) y (C)

    function onEdit(e)
    {
    if(SpreadsheetApp.getActiveSpreadsheet().getSheetName() == "1")
    {
    var range = e.range
    var datos = range.getRow().getValues();

    SpreadsheetApp.getActiveSpreadsheet().getSheetByName("2").getRange(range.getRow() , 1).setValues(datos);
    }
    }

    ResponderEliminar
  37. Hola, es posible que al correr un script no tenga en cuenta las filas que están ocultas? mi problema es que tengo creado esto para que me oculte filas de manera automática cuando se cumplen ciertas condiciones pero como tengo muchas filas el tiempo máximo de ejecución se alcanza antes de finalizar todas las filas. Como lo tengo automatizado mediante un activador temporal, me gustaría que cuando se corre por segunda vez y sucesivas no tenga en cuenta las filas ya ocultas y así será mas fácil que pueda llegar al final de las filas.....espero haberme explicado.

    function ocultarFilasPAMIRO() {
    var ss = SpreadsheetApp.getActive().getSheetByName("VENTAS");
    var uf = ss.getLastRow();
    var datos = ss.getRange(3,2,uf,25).getDisplayValues();

    for(var i=0;i<datos.length;i++){
    if(datos[i][19] == " SI" && datos[i][20] == " SI"|| datos[i][19] == " SI" && datos[i][20] == "*" && datos[i][14] == "SI"){
    ss.hideRows(i+3);
    }
    SpreadsheetApp.flush();
    }
    }

    ResponderEliminar
    Respuestas
    1. Si el displayValues no hace lo que quieres ( y por lo que dices , parece que no) te diré que solo le vería dos opcions:
      1. Intentar agrupar el hideRows. Si pudieras detectar filas consecutivas q se han de ocultar y entonces ocultarlas todas de una sola vez, ganarias bastante tiempo. Ejemplo: ss.hideRows(i+3,5); .
      2. Realmente te hace falta tener el flush a cada fila? Ya te lo digo, no. Como mucho cada vez hicieras un hideRows, pero ni así. Yo intentaria hacer el flush después del bucle, no a cada fila. Esto tiene pinta que te está chupando mucho tiempo.

      Inténtalo hacer tu, si no lo ves claro como implementarlo avisame e intento montarte algo cuando tenga un momento.

      Nos vemos

      Eliminar
  38. Hola... quiero que en lugar de poner la hora en la primera columna no ponga la fecha si no la siguiente formula:

    =IF(B4="";;(CONCATENATE(F4&D4&if(G4="";if(H4="";I4;H4);G4)))&E4)

    logré el concatenar así: .setFormula("=concatenate(R[0]C[5]&R[0]C[3]")

    pero al momento de intentar la validación con la formula de IF no he podido lograrlo... me ayudas?...

    Gracias

    ResponderEliminar
    Respuestas
    1. Hola Victor. Seguro q tienes problemas con los separdores ". Los tienes que convertir a \" para que no te fastidien el string. Yo lo he probado con estoy me ha funcionado: setFormula("=IF(B4=\"\";;(CONCATENATE(F4&D4&if(G4=\"\";if(H4=\"\";I4;H4);G4)))&E4)"); Ya me dirás como te ha ido

      Nos vemos!

      Eliminar
  39. hola amigo buen día.
    espero me puedas ayudar. tengo libro de excel con 3 hojas:
    hoja 1 = formulario
    hoja 2 = datos
    hoja 3 historial

    básicamente en la "hoja 1 = formulario" son macros que guardan registros" en la "hoja "= datos".

    esa "hoja 2 = datos" tiene distintos encabezados, pero me interesaría generar un histórico de movimientos.

    ejemplo que cuando algún campo de la columna "Y" de la "hoja 2 = datos" sea modificado, se genere un historial con la columna ("a"-"c"-"x"-"y") y campos adicionales como fecha y hora en que se modifico el dato, usuario que lo modifico (si es posible si no solo con la fecha y hora) esto para poder ver cual era el dato original y compararlo con el nuevo, pero que no afecte la primera vez que se llena con el formulario si no apartir de que se modifica o el dato es diferente al ingresado la primera vez.

    no se mucho de programación, están siendo mis primeros proyectos en esta plataforma y aun tengo dudas.

    ResponderEliminar
    Respuestas
    1. Hola Alberto,
      Perdoname la pregunta, pero, cual es tu duda? Que te haga yo el proyecto? :D
      Dime que cosa concreta es lo que te falla y te intentaré ayudar
      Nos vemos

      Eliminar
    2. mira tengo este script que ya me arroja la fecha y hora cuando modifican la columna "5" que es donde quiero darle seguimiento.
      pero solo me arroja eso, quisiera saber como podría hacer que me arroje algunos campos de la hoja 1 a la hoja 2 donde quiero generar un histórico de movimientos.
      por ejemplo, columnas 1,2,5,7 mas el usuario que la modifico) no se si se pueda.

      function onEdit(e){

      {

      if(SpreadsheetApp.getActiveSpreadsheet().getSheetName() == "Hoja 1")

      {
      var range = e.range;
      if(range.getColumn() == 5)

      SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Hoja 2").getRange(range.getRow() , 6).setValue(new Date());
      }
      }

      }

      apenas son mis primeros pasos, perdona mi incoherencia.

      Eliminar
    3. A priori, dentro del onEdit, si son del mismo spreadsheet no deberia haber problema en que obtuvieras info. De otro libro creo que no te funcionaria.
      Lo mejor que puedes hacer es debugar. Creo que con breakpoints deberías de poder ver el problema.
      Los principios son complicados, paciencia :D

      Eliminar
  40. Tambien he intentado con este pero no corre y no se cual es el error.

    function historico(e) {

    var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Historico");
    var rango = e.range;
    var fil = rango.getRow();
    var hoja = rango.getSheet();
    var datos = e.source.getRange("A"+fil+":J"+fil) .getDisplayValues();Logger.log(datos);


    if (hoja.getName() == "Datos" && rango.getColum() == 5) {
    if (e.value !== "") {
    ss.appendRow(datos)[0]);
    }

    }

    ResponderEliminar
    Respuestas
    1. Has instalado la función historico ? https://www.tutorialesgoogleappscripts.com/2020/09/como-crear-triggers-con-google-apps-script.html

      Eliminar

Tal vez te interese