Note: The other languages of the website are Google-translated. Back to English
Se connecter  \/ 
x
or
x
S'inscrire  \/ 
x

or

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

Si vous disposez d'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, veuillez copier et coller le code de script ci-dessous pour remplacer le code d'origine, 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")); 
    }
  }
}

Notes: Dans le code ci-dessus, données de commande est le nom de la feuille que vous souhaitez utiliser, Date est l'en-tête de colonne dans lequel vous souhaitez insérer l'horodatage, et Placez une commande est l'en-tête de colonne des valeurs de cellule que vous souhaitez mettre à jour. Veuillez les modifier selon vos besoins.

3. Ensuite, 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 les données de la colonne Order sont modifiées, l'horodatage actuel est inséré automatiquement dans la cellule de la colonne Date qui est adjacente à la cellule modifiée, voir capture d'écran:


Les meilleurs outils de productivité de bureau

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 e-mails ...
  • Barre Super Formula (modifiez facilement plusieurs lignes de texte et de formule); Disposition de lecture (lire et modifier facilement un grand nombre de cellules); Coller dans la plage filtrée...
  • Fusionner les cellules / lignes / colonnes sans perdre de données; Contenu des cellules divisées; Combiner des lignes / colonnes en double... Empêcher les cellules en double; Comparer les gammes...
  • Sélectionnez Dupliquer ou Unique Lignes; Sélectionnez les lignes vides (toutes les cellules sont vides); Super Find et Fuzzy Find dans de nombreux classeurs; Sélection aléatoire ...
  • Copie exacte Plusieurs cellules sans changer la référence de formule; Créer automatiquement des références à plusieurs feuilles; Insérer des puces, Cases à cocher et plus encore ...
  • Extrait du texte, Ajouter du texte, Supprimer par position, Supprimer l'espace; Créer et imprimer des sous-totaux de pagination; Conversion entre le contenu et les commentaires des cellules...
  • 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 par gras, italique ...
  • Combiner des classeurs et des feuilles de travail; Fusionner les tableaux en fonction des colonnes clés; Diviser les données en plusieurs feuilles; Conversion par lots xls, xlsx et PDF...
  • Plus de 300 fonctionnalités puissantes. Prend en charge Office / Excel 2007-2019 et 365. Prend en charge toutes les langues. Déploiement facile dans votre entreprise ou organisation. Essai gratuit de 30 jours. Garantie de remboursement de 60 jours.
onglet kte 201905

Office Tab apporte une interface à onglets à Office et simplifie considérablement votre travail

  • Activer l'édition et la lecture par onglets 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 des centaines de clics de souris chaque jour!
bas de cabine
Say something here...
symbols left.
You are guest
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.
    betha · 4 months ago
    Hi.. Thank you  very much,

    I was struggling to get the below excel macro script into google sheet script. Can you please help me out. 

    "Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Target.Column = 10 Then
    Application.EnableEvents = False
    If Target.Value <> """" Then
    Target.Offset(, 6).Value = Environ(""username"")
    Else
    Target.Offset(, 6).ClearContents
    End If
    Application.EnableEvents = True
    End If

    If Target.Column = 11 And Target.Column Mod 1 = 0 And Target.Row >= -8 Then
    For Each c In Target
    If c.Value = c.Offset(0, -4).Value Then
    c.Offset(0, -8).Value = Format(Date, ""DD/MMM/YYYY"")
    Else
    c.Offset(0, -8).Value = """"

    End If
    Next c

    End If


    If Target.Column = 10 And Target.Column Mod 3 = 1 And Target.Row >= 6 Then
    For Each c In Target
    If c.Value = """" Then
    c.Offset(0, 7).Value = """"
    Else
    c.Offset(0, 7).Value = Format(Time, ""h:mm AM/PM"")
    End If
    Next c
    End If
    End Sub



    "

  • To post as a guest, your comment is unpublished.
    Geneva · 4 months ago
    This worked for me perfectly. I had three separate columns that I had to update with static dates. Just made three diff arguments for each column and it worked. Thank you!
    • To post as a guest, your comment is unpublished.
      Michelle · 2 months ago
      Can u show how do you do that?
  • To post as a guest, your comment is unpublished.
    MissBFierce · 5 months ago
    How do i apply this to work in multiple tabs in google sheet?
  • To post as a guest, your comment is unpublished.
    Belinda · 5 months ago
    I need this to apply in multiple sheets. How do I do that
  • To post as a guest, your comment is unpublished.
    SKD · 8 months ago
    Replace e.source by SpreadsheetApp.getActiveSpreadsheet()
  • To post as a guest, your comment is unpublished.
    manish · 10 months ago
    TypeError: Cannot read property 'source' of undefined
    onEdit @ Code.gs:3
    pl. give solution ,,, on google sheet
  • To post as a guest, your comment is unpublished.
    MANISH · 10 months ago
    TypeError: Cannot read property 'source' of undefined
    onEdit @ Code.gs:3

  • To post as a guest, your comment is unpublished.
    Vinay Pal · 1 years ago
    THANK YOU IT WORKED

  • To post as a guest, your comment is unpublished.
    uma · 1 years ago
    Hi all, will this script work for the column populated by a formula or auto populated. depending on a auto populated column how can i generate the the updated date.
  • To post as a guest, your comment is unpublished.
    Allyson · 1 years ago
    Hello I found this very helpful yet have a additional step in my spreadsheet. My Column A data remains untouched which is what I want. I need Column B when updated by staff date&time stamp Column C. I have this so far BUT I also need Column D when updated to date&time stamp Column E. So B&C interact and D&E interact. Advice?
  • To post as a guest, your comment is unpublished.
    Michael · 1 years ago
    How do i set the script so that id E11 is changed then the time stamp auto populates in E10 ?
  • To post as a guest, your comment is unpublished.
    Vince M · 1 years ago
    Thanks, so much! The was exactly what I needed. My use case: Collecting devices from students. I'll be scanning the barcode from each device, and want to note when I receive the device. I adjusted code, obviously, to reference my column names, and I added the time of day to the date format so that I can be more precise.

    Vince M
    IT Admin
    PreK-8 School
    • To post as a guest, your comment is unpublished.
      jag · 18 days ago
      Hi Vince. Your case sounds very much alike what I'm about to do at this moment. It is possible to see your code for this case? Thank you very much in advance.
    • To post as a guest, your comment is unpublished.
      skyyang · 1 years ago
      Hello, Vince M,
      Thank you for your comment, do you solve your problem?
  • To post as a guest, your comment is unpublished.
    Mas JO · 1 years ago
    Well its works
  • To post as a guest, your comment is unpublished.
    eddie · 2 years 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 years ago
      Same problem here.
    • To post as a guest, your comment is unpublished.
      skyyang · 2 years 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 years 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 years 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 years ago
            It didn't work for me
          • To post as a guest, your comment is unpublished.
            skyyang · 2 years 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 · 2 years 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 · 2 years 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 · 2 years 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 · 2 years ago
    Genial, excelente, es lo que estaba buscando, muchas gracias, saludos
  • To post as a guest, your comment is unpublished.
    Juan Hernnandez · 2 years ago
    Awesome! Thanks
  • To post as a guest, your comment is unpublished.
    Daniel Méndez · 2 years 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 · 2 years 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 · 2 years 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.
          fabriciogan2016@gmail.com · 2 years 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 · 2 years 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 · 2 years 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 · 3 years 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 · 3 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 · 3 years ago
      I am trying to do the same, any luck figuring this out?
  • To post as a guest, your comment is unpublished.
    Nicky · 3 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.
      Anton · 3 years ago
      Same here. Please help.
      • To post as a guest, your comment is unpublished.
        Dhaval · 2 years ago
        same issue here
  • To post as a guest, your comment is unpublished.
    Willy · 3 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.
      federica@crowdmobile.com · 3 years ago
      hello, I have the same problem, did you solve it?
      • To post as a guest, your comment is unpublished.
        Nikita · 2 years ago
        hello I have a similar problem, did you solve it?
  • To post as a guest, your comment is unpublished.
    Ryan · 3 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.
      Superbyte · 1 years ago
      Use function
      var sheet = SpreadsheetApp.getActiveSheet();

      instead var sheet = e.source.getActiveSheet(); in Line 3.

      It solved my problem.

      I am using it like this

      f
      { var sheet = SpreadsheetApp.getActiveSheet(); if (sheet.getName() == "Sheet1") //"Sheet1" is the name of the sheet not File name { 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 Stamp") + 1; var orderCol = headers[0].indexOf("Date Entry") + 1; if (dateCol > 0 && rowIndex > 1 && editColumn == orderCol) { sheet.getRange(rowIndex, dateCol).setValue(Utilities.formatDate(new Date(), "UTC+5.30", "MM-dd-yyyy HH:MM:SS")); //I am using it in India GMT 5.30+ with Time also } } }

    • To post as a guest, your comment is unpublished.
      Shivam · 3 years ago
      Yup, same. Admin, pliss to help.
  • To post as a guest, your comment is unpublished.
    KLip123 · 3 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.
    Mirzaei · 3 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 · 3 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.
        sratner@gmail.com · 3 years ago
        How do I make it have both Time and Date?


        Thanks.


        Scott
        • To post as a guest, your comment is unpublished.
          Guy · 3 years 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.
            Marco · 2 years ago
            Hi

            Can you help me please? I need to display the Central America time, I have changed the time zone to GTM-6 but it does not work, it still shows me Pacific time, (6 hours apart)

            Regards
          • To post as a guest, your comment is unpublished.
            iqbal · 2 years ago
            Thank you, it's works
        • To post as a guest, your comment is unpublished.
          skyyang · 3 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 · 3 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 · 3 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.
    88cesar88@gmail.com · 3 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.
      jv · 2 years ago
      i have the same question
  • To post as a guest, your comment is unpublished.
    88cesar88@gmail.com · 3 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 · 3 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 · 3 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 · 3 years ago
    can this be modified to apply to any sheet?