Astuce: Les autres langues sont Google-Traduction. Vous pouvez visiter le English version de ce lien.
Se connecter
x
or
x
x
S'enregistrer
x

or

Comment insérer l'horodatage automatiquement lorsque les données sont mises à jour dans une autre colonne de la feuille Google?

Si vous avez une plage de cellules et que vous souhaitez insérer automatiquement un horodatage dans la cellule adjacente lorsque les données sont modifiées ou mises à jour dans une autre colonne. Comment pourriez-vous résoudre cette tâche dans la feuille Google?

Insérer l'horodatage automatiquement lorsque les données sont mises à jour dans une autre colonne avec le code de script


Insérer l'horodatage automatiquement lorsque les données sont mises à jour dans une autre colonne avec le code de script


Le code de script suivant peut vous aider à terminer ce travail rapidement et facilement, procédez comme suit:

1. Cliquez Outils > Éditeur de script, voir capture d'écran:

2. Dans la fenêtre de projet ouverte, copiez et collez le code de script ci-dessous pour remplacer le code original, voir capture d'écran:

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

Note: Dans le code ci-dessus, données de commande est le nom de la feuille que vous voulez utiliser, Date est l'en-tête de colonne que vous voulez insérer l'horodatage, et Commande est l'en-tête de la colonne quelles valeurs de cellule vous voulez mettre à jour. S'il vous plaît, changez-les selon vos besoins.

3. Puis enregistrez la fenêtre du projet et entrez un nom pour ce nouveau projet, voir capture d'écran:

4. Et puis revenez à la feuille, maintenant, lorsque la colonne de données dans la commande est modifiée, l'horodatage actuel est automatiquement inséré dans la cellule de la colonne Date qui est adjacente à la cellule modifiée, voir capture d'écran:


Kutools for Excel résout la plupart de vos problèmes et augmente votre productivité de 80%

  • Réutilisation: Insérer rapidement formules complexes, graphiques et tout ce que vous avez utilisé auparavant; Crypter les cellules avec mot de passe Créer une liste de diffusion et envoyer des emails ...
  • Super Formula Bar (éditez facilement plusieurs lignes de texte et de formule); Disposition de lecture (facilement lire et éditer un grand nombre de cellules); Coller à la gamme filtrée...
  • Fusionner les cellules / rangées / colonnes sans perdre de données; Contenu des cellules divisées; Combiner les lignes / colonnes en double... Prévenir les cellules en double; Comparer les plages...
  • Sélectionnez Dupliquer ou Unique Des rangées; Sélectionnez les lignes vierges (toutes les cellules sont vides); Super Find et Fuzzy Find dans de nombreux cahiers d'exercices; Sélection aléatoire ...
  • Copie exacte Plusieurs cellules sans changer la référence de la formule; Créer automatiquement des références à plusieurs feuilles; Insérer des balles, Cases à cocher et plus ...
  • Extrait du texte, Ajouter du texte, Supprimer par position, Supprimer l'espace; Créer et imprimer des sous-totaux de pagination; Conversion entre contenu de cellules et commentaires...
  • Super filtre (enregistrer et appliquer des schémas de filtrage à d'autres feuilles); Tri avancé par mois / semaine / jour, fréquence et plus; Filtre spécial en gras, en italique ...
  • Combinaison de classeurs et de feuilles de calcul; Fusionner les tables en fonction des colonnes clés; Fractionner les données en plusieurs feuilles; Conversion par lots xls, xlsx et PDF...
  • Plus que de puissantes fonctionnalités 300. Prend en charge Office / Excel 2007-2019 et 365. Prend en charge toutes les langues. Déploiement facile dans votre entreprise ou organisation. Fonctionnalités complètes Essai gratuit du jour 30.
kte tab 201905

Office Tab apporte une interface à onglets à Office et simplifie grandement votre travail

  • Activer l'édition par onglets et la lecture dans Word, Excel, PowerPoint, Publisher, Access, Visio et Project.
  • Ouvrez et créez plusieurs documents dans de nouveaux onglets de la même fenêtre, plutôt que dans de nouvelles fenêtres.
  • Augmente votre productivité de 50% et réduit le nombre de clics de souris pour vous chaque jour!
fond 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.
    eddie · 2 months ago
    hi when i run this script it give me a error on line 3 ? can someone help me get this fix please
    • To post as a guest, your comment is unpublished.
      Carlotta · 2 months ago
      Same problem here.
    • To post as a guest, your comment is unpublished.
      skyyang · 2 months ago
      Hello, eddie,
      Could you change the sheet name in the above code to your own sheet?
      Please check it, thank you!
      • To post as a guest, your comment is unpublished.
        eddie · 2 months ago
        function onEdit()
        {
        var sheet = SpreadsheetApp.getActiveSheet();
        var capture = sheet.getActiveCell();
        if (sheet.getName() == "Jewel Inbound trailer") //"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("Time") + 1;
        var orderCol = headers[0].indexOf("Tractor") + 1;
        if (dateCol > 0 && rowIndex > 1 && editColumn == orderCol)
        {
        sheet.getRange(rowIndex, dateCol).setValue(Utilities.formatDate(new Date(), "UTC-6", "MM-dd-yyyy"));
        }
        }
        }
        • To post as a guest, your comment is unpublished.
          eddie · 2 months ago
          can someone help me to make this work correctly on google sheets


          thank you
          • To post as a guest, your comment is unpublished.
            Javier · 2 months ago
            It didn't work for me
          • To post as a guest, your comment is unpublished.
            skyyang · 2 months ago
            Hello, eddie,
            I have tried your code, it works well in my Google sheet, please try it again! Thank you!
  • To post as a guest, your comment is unpublished.
    isrami · 3 months 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 · 3 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 · 4 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 · 4 months ago
    Genial, excelente, es lo que estaba buscando, muchas gracias, saludos
  • To post as a guest, your comment is unpublished.
    Juan Hernnandez · 5 months ago
    Awesome! Thanks
  • To post as a guest, your comment is unpublished.
    Daniel Méndez · 7 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 · 6 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 · 6 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 · 6 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 · 7 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 · 8 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 · 10 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 · 1 years 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 · 10 months ago
      I am trying to do the same, any luck figuring this out?
  • To post as a guest, your comment is unpublished.
    Nicky · 1 years 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 · 1 years 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 · 1 years 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 · 1 years 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 · 1 years ago
        How do I make it have both Time and Date?


        Thanks.


        Scott
        • To post as a guest, your comment is unpublished.
          Some · 11 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 · 1 years 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?