Consejo: Otros idiomas son traducidos por Google. Puedes visitar el English versión de este enlace.
Iniciar sesión
x
or
x
x
Suscríbete
x

or

¿Cómo insertar la marca de tiempo automáticamente cuando los datos se actualizan en otra columna en la hoja de Google?

Si tiene un rango de celdas y desea insertar una marca de tiempo automáticamente en la celda adyacente cuando los datos se modifican o actualizan en otra columna. ¿Cómo podrías resolver esta tarea en la hoja de Google?

Insertar marca de tiempo automáticamente cuando los datos se actualizan en otra columna con código de secuencia de comandos


Insertar marca de tiempo automáticamente cuando los datos se actualizan en otra columna con código de secuencia de comandos


El siguiente código de script puede ayudarlo a terminar este trabajo de forma rápida y sencilla, haga lo siguiente:

1. Hacer clic Herramientas > Editor de scripts, mira la captura de pantalla:

2. En la ventana del proyecto abierto, copie y pegue el siguiente código de script para reemplazar el código original, vea la captura de pantalla:

function onEdit(e)
{ 
  var sheet = e.source.getActiveSheet();
  if (sheet.getName() == "order data") //"order data" is the name of the sheet where you want to run this script.
  {
    var actRng = sheet.getActiveRange();
    var editColumn = actRng.getColumn();
    var rowIndex = actRng.getRowIndex();
    var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues();
    var dateCol = headers[0].indexOf("Date") + 1;
    var orderCol = headers[0].indexOf("Order") + 1;
    if (dateCol > 0 && rowIndex > 1 && editColumn == orderCol) 
    { 
      sheet.getRange(rowIndex, dateCol).setValue(Utilities.formatDate(new Date(), "UTC+8", "MM-dd-yyyy")); 
    }
  }
}

Nota: En el código anterior, datos de los pedidos es el nombre de la hoja que quieres usar, Fecha es el encabezado de columna en el que desea insertar la marca de tiempo, y Pedido es el encabezado de columna que valores de celda desea actualizar. Por favor, cámbielos a su necesidad.

3. Luego guarde la ventana del proyecto e ingrese un nombre para este nuevo proyecto, vea la captura de pantalla:

4. Y luego regrese a la hoja, ahora, cuando la columna de datos en la Orden se modifique, la marca de tiempo actual se inserta automáticamente en la celda de la columna Fecha que se encuentra junto a la celda modificada, vea la captura de pantalla:


Kutools for Excel: la mejor herramienta de productividad de Office aumenta su productividad en un 80%

  • Super Formula Bar (edite fácilmente varias líneas de texto y fórmula); Diseño de lectura (lee y edita fácilmente un gran número de celdas); Pegar en rango filtrado...
  • Combinar celdas / filas / columnas y mantener datos; Contenido de celdas divididas; Combinar filas duplicadas y suma / promedio... Prevenir células duplicadas; Comparar rangos...
  • Seleccione Duplicado o Único Filas; Seleccionar filas en blanco (todas las celdas están vacías); Super Find y Fuzzy Find en muchos libros de trabajo; Selección aleatoria ...
  • Copia exacta Celdas múltiples sin cambiar la referencia de fórmula; Crear referencias automáticamente a múltiples hojas; Insertar viñetas, Casillas de verificación y más ...
  • Fórmulas favoritas e insertadas rápidamente, Gamas, cuadros y cuadros; Cifrar celdas con contraseña Crear una lista de correo y enviar correos electrónicos ...
  • Extracto del texto, Agregar texto, Eliminar por posición, Eliminar espacio; Crear e imprimir subtotales de paginación; Convertir entre contenido de celdas y comentarios...
  • Súper filtro (guardar y aplicar esquemas de filtro a otras hojas); Clasificación avanzada por mes / semana / día, frecuencia y más; Filtro especial por negrita, cursiva ...
  • Combinar libros de trabajo y hojas de trabajo; Combinar tablas basadas en columnas clave; Dividir datos en varias hojas; Conversión por lotes xls, xlsx y PDF...
  • Funciona con Office 2007-2019 y 365, y es compatible con todos los idiomas. Es fácil de implementar en su empresa. Funciones completas de prueba gratuita de 60-day.
pestaña kte 201905

Office Tab lleva la interfaz con pestañas a Office y hace que su trabajo sea mucho más fácil

  • Habilitar la edición y lectura con pestañas en Word, Excel, PowerPoint, Editor, Acceso, Visio y Proyecto.
  • Abra y cree varios documentos en nuevas pestañas de la misma ventana, en lugar de en nuevas ventanas.
  • ¡Aumenta tu productividad en un 50% y reduce cientos de clics de ratón por ti todos los días!
fondo officetab
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    isrami · 19 days ago
    can we change this to track changes on certain range of column instead of column? assuming that our column to be tracked is at the middle of our sheet?
  • To post as a guest, your comment is unpublished.
    Sarah · 29 days ago
    How do you track changes on more than one column though? Using your example, how do you edit the script to track changes in both "product" and "order" columns?
  • To post as a guest, your comment is unpublished.
    Simón Blanco · 1 months ago
    Existe una manera de hacer esto pero que la fecha se introduzca sólo si se escribe una palabra específica?
  • To post as a guest, your comment is unpublished.
    Ricardo · 1 months ago
    Genial, excelente, es lo que estaba buscando, muchas gracias, saludos
  • To post as a guest, your comment is unpublished.
    Juan Hernnandez · 2 months ago
    Awesome! Thanks
  • To post as a guest, your comment is unpublished.
    Daniel Méndez · 4 months ago
    Hola, hice los pasos que mencionas pero me aparece un error: TypeError: No se puede leer la propiedad "source" de undefined. (línea 3, archivo "Código")
    • To post as a guest, your comment is unpublished.
      Fabricio Rodrigues · 3 months ago
      I fix it whit this code.


      function onEdit() {
      var sheet = SpreadsheetApp.getActiveSheet();
      var capture = sheet.getActiveCell();
      if (sheet.getName() == "Updates") //"Updates" is the sheet name.
      if(capture.getColumn() == 1 ) {
      var add = capture.offset(0, 1); //"0" is the line in reference the cell updated, ''0'' same line, "1" reference at column "1" is 1 column to the right.
      var data = new Date();
      data = Utilities.formatDate(data, "GMT-03:00","dd/MM' 'HH:mm' '");
      add.setValue(data);
      }

      }
      • To post as a guest, your comment is unpublished.
        Jorge · 3 months ago
        Hi Fabricio!

        On the 1 I have to write the Date column (where I want to get the date) and on 0 column where I write text?
        Do I need "" or similar?

        Thanks!
        • To post as a guest, your comment is unpublished.
          Fabricio Rodrigues · 3 months ago
          Hello Jorge, no, you just need to write the number referente to column, like A = 1 , B = 2 .....
  • To post as a guest, your comment is unpublished.
    Rej · 4 months ago
    Good day! I'm just wondering if it's possible to add a code for the timestamp to automatically disappear once the main cell has been cleared. Thank!
  • To post as a guest, your comment is unpublished.
    ScottC · 5 months ago
    How should the script be modified to look for changes in a contiguous range of columns rather than a single column? e.g. trigger the script if there are changes in columns labeled, "Amount", "Category" and "Type" rather than the single column labeled "Order" in the example script.
  • To post as a guest, your comment is unpublished.
    Annette · 7 months ago
    Hey! I got this code "Missing } after function body. (line 18, file "Code")" How do I fix this issue? Thank you so much! This is amazing!
  • To post as a guest, your comment is unpublished.
    James · 9 months ago
    Hi
    I got the code working, thanks!
    If I would like to include mutiple columns, how would I alter the code?
    • To post as a guest, your comment is unpublished.
      Blaze · 7 months ago
      I am trying to do the same, any luck figuring this out?
  • To post as a guest, your comment is unpublished.
    Nicky · 10 months ago
    Hi I face an error TypeError: Cannot read property "source" from undefined. (line 3, file "Code")
    Able to help on this
  • To post as a guest, your comment is unpublished.
    Willy · 10 months ago
    Thanks for this code, it's exactly what I need. The only problem is I am running a script that sends some data to google sheet, but the time stamp doesn't trigger for this data, only when I edit the cell manually. Any advice?
  • To post as a guest, your comment is unpublished.
    Ryan · 11 months ago
    I am getting an error "TypeError: Cannot read property "source" from undefined. (line 3, file "Code"). Do I have to provide the link of the sheet in this line?


    thanks,


    Ryan
  • To post as a guest, your comment is unpublished.
    Kurtis Lipman · 11 months ago
    HI there,


    I'm looking to do the equivalent get a timestamp in the "date" column whenever the "Order" is updated, but also whenever the "Delivery Status" or "Payment Status" is updated as well (making up column heading but I hope you get my drift).

    Is this possible?


    Thanks
  • To post as a guest, your comment is unpublished.
    Mahdi · 1 years ago
    I love this script. How do I only get this to Print Time instead of DATE? That is what I need
    • To post as a guest, your comment is unpublished.
      skyyang · 1 years ago
      Hello,

      you also can apply the following code, but, you should change the time zone to your own. Please try it.

      function onEdit(e)
      {
      var sheet = e.source.getActiveSheet();
      if (sheet.getName() == "order data") //"order data" is the name of the sheet where you want to run this script.
      {
      var actRng = sheet.getActiveRange();
      var editColumn = actRng.getColumn();
      var rowIndex = actRng.getRowIndex();
      var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues();
      var dateCol = headers[0].indexOf("Date") + 1;
      var orderCol = headers[0].indexOf("Order") + 1;
      if (dateCol > 0 && rowIndex > 1 && editColumn == orderCol)
      {
      sheet.getRange(rowIndex, dateCol).setValue(Utilities.formatDate(new Date(), "GMT+8:00", "HH:mm:ss"));
      }
      }
      }
      • To post as a guest, your comment is unpublished.
        Scott Ratner · 10 months ago
        How do I make it have both Time and Date?


        Thanks.


        Scott
        • To post as a guest, your comment is unpublished.
          Some · 8 months ago
          You can simply add hh:mm:ss after the date in line 14 of the code (copied below). Note: I had to change the UTC+8 to GMT-5 to get it to stamp the correct time for US Eastern.

          sheet.getRange(rowIndex, dateCol).setValue(Utilities.formatDate(new Date(), "GMT-5", "MM-dd-yyyy hh:mm:ss"));
        • To post as a guest, your comment is unpublished.
          skyyang · 10 months ago
          Hi, Scott,

          To make the column have both date and time, you should apply the following script code. After inserting the code, and then select the column that you want to insert the date and time, then click Format > Number > Date time to format the cells as date time formatting.

          function onEdit(e)
          {
          var sheet = e.source.getActiveSheet();
          if (sheet.getName() == "order data") //"order data" is the name of the sheet where you want to run this script.
          {
          var actRng = sheet.getActiveRange();
          var editColumn = actRng.getColumn();
          var rowIndex = actRng.getRowIndex();
          var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues();
          var dateCol = headers[0].indexOf("Date") + 1;
          var orderCol = headers[0].indexOf("Order") + 1;
          if (dateCol > 0 && rowIndex > 1 && editColumn == orderCol)
          {
          sheet.getRange(rowIndex, dateCol).setValue(new Date());
          }
          }
          }

          Please try it, hope it can help you!
    • To post as a guest, your comment is unpublished.
      Basir · 1 years ago
      Change the last line to sheet.getRange(rowIndex, dateCol).setValue(new Date());
      This will return a date time, but you can show only the time if you want from Format -> Number -> Time
  • To post as a guest, your comment is unpublished.
    PatKat · 1 years ago
    Hi. Thanks for the solution. I have a shared file and I would like the time to be reflected when anyone edits the sheet. Currently, this works only when I edit the sheet. How do I do that? Thanks in advance :)
  • To post as a guest, your comment is unpublished.
    césar pereira · 1 years ago
    I also would like to know how to lock that cell after the information is inserted in the previous cell.
  • To post as a guest, your comment is unpublished.
    césar pereira · 1 years ago
    Hi there, thanks for the code it worked perfectly for what i needed. However I would need your help to know how to add a condition for this date to appear.
    In fact, I would like to have this date only when numbers are inserted and nothing else.
    Do you know what I should add to the code for that?
    I am not a coder at all, only a copy paster, this is why I really need help and can't figure it out by myself.
    thanks a lot already for your help

    cesar
  • To post as a guest, your comment is unpublished.
    Jeff Oxford · 1 years ago
    Do I need to run the function in the script editor for this to work? I keep getting this error when I try it: TypeError: Cannot read property "source" from undefined. (line 3, file "Code")
    • To post as a guest, your comment is unpublished.
      Nathan · 1 years ago
      Hi there!
      I had this issue too. It ended up being that I renamed my file to "order data", but my sheet name was still "Sheet1" once I renamed the sheet and not the workbook to "order data" everything worked.
  • To post as a guest, your comment is unpublished.
    David · 1 years ago
    can this be modified to apply to any sheet?