Tip: andere talen zijn Google-Vertaald. Je kunt het English versie van deze link.
Log in
x
or
x
x
Registreren
x

or

Tijdstempel automatisch invoegen wanneer gegevens in een andere kolom in het Google-blad worden bijgewerkt?

Als u een celbereik hebt en een tijdstempel automatisch in de aangrenzende cel wilt invoegen wanneer de gegevens in een andere kolom worden gewijzigd of bijgewerkt. Hoe kun je deze taak oplossen in het Google-spreadsheet?

Tijdstempel automatisch invoegen wanneer gegevens worden bijgewerkt in een andere kolom met scriptcode


Tijdstempel automatisch invoegen wanneer gegevens worden bijgewerkt in een andere kolom met scriptcode


De volgende scriptcode kan u helpen om deze taak snel en gemakkelijk te voltooien. Dit doet u als volgt:

1. Klikken Gereedschap > Script-editorzie screenshot:

2. Kopieer en plak de onderstaande scriptcode in het geopende projectvenster om de originele code te vervangen, zie screenshot:

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")); 
    }
  }
}

Notes: In de bovenstaande code, bestelgegevens is de bladnaam die u wilt gebruiken, Datum is de kolomkop waarvan u het tijdstempel wilt invoegen, en bestelling is de kolomkop welke celwaarden u wilt bijwerken. Verander ze alstublieft naar uw behoefte.

3. Sla vervolgens het projectvenster op en voer een naam in voor dit nieuwe project, zie screenshot:

4. En ga dan terug naar het blad, nu, wanneer de gegevens in de kolom Volgorde is gewijzigd, wordt de huidige tijdstempel automatisch ingevoegd in de cel van de kolom Datum die grenst aan de gewijzigde cel, zie screenshot:


Kutools voor Excel - De beste Office-productiviteitstool Verhoog uw productiviteit met 80%

  • Super Formula Bar (bewerk eenvoudig meerdere regels tekst en formule); Lay-out lezen (gemakkelijk grote aantallen cellen lezen en bewerken); Plakken op gefilterd bereik...
  • Cellen / rijen / kolommen samenvoegen en gegevens bewaren; Inhoud gesplitste cellen; Combineer dubbele rijen en som / gemiddelde... voorkomen dubbele cellen; Ranges vergelijken...
  • Selecteer Dupliceren of Uniek rijen; Selecteer Lege rijen (alle cellen zijn leeg); Super Find en Fuzzy Find in veel werkboeken; Willekeurig selecteren ...
  • Exacte kopie Meerdere cellen zonder formule-referentie te wijzigen; Automatisch referenties maken naar meerdere vellen; Voeg kogels toe, Selectievakjes en meer ...
  • Favoriete en snel formules invoegen, Bereiken, grafieken en afbeeldingen; Coderen van cellen met wachtwoord; Maak een mailinglijst en stuur e-mails ...
  • extract Text, Tekst toevoegen, verwijderen op positie, Verwijder de spatie; Subtotalen voor paging maken en afdrukken; Converteren tussen cellen Inhoud en opmerkingen...
  • Super filter (bewaar en pas filterschema's toe op andere bladen); Geavanceerde sortering per maand / week / dag, frequentie en meer; Speciaal filter door vet, cursief ...
  • Combineer werkmappen en werkbladen; Tabellen samenvoegen op basis van sleutelkolommen; Gegevens splitsen in meerdere bladen; Batch Converteer xls, xlsx en PDF...
  • Werkt met Office 2007-2019 en 365 en ondersteunt alle talen. Het is eenvoudig te implementeren in uw bedrijf. Volledige functionaliteit 60-daagse gratis proefversie.
kte-tab 201905

Tabblad Office Brengt interface met tabbladen naar Office en maakt uw werk veel eenvoudiger

  • Bewerken en lezen met tabbladen inschakelen in Word, Excel, PowerPoint, Publisher, Access, Visio en Project.
  • Open en maak meerdere documenten in nieuwe tabbladen van hetzelfde venster, in plaats van in nieuwe vensters.
  • Verhoogt uw productiviteit met 50% en verlaagt dagelijks honderden muisklikken voor u!
Officetab onderaan
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 · 20 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 · 1 months 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?