Add the Current Date to a Sheet When Data Is Added So That The Date Does Not Change(Static) – Google Sheets

You’ve probably come across the problem where you need to know when a piece of data has been added to your spreadsheet. You probably have been equally frustrated that there is no out-of-the-box function that will do just this.

You’ve tried TODAY() and NOW(), but they change dynamically. What you really need here is something that does not change.

Let’s look at two workarounds that can help you out with this problem.

1. The Simple, But Not So Useful Approach

This approach does not automatically update the sheet with the current date and/or time, but it is a fast solution to adding the current date and time to the sheet automatically.

Date

To add the current date to a cell, simply press CTRL + ;.

Time

To add the current time to a cell, press CTRL + SHIFT + ;  (This is shift and semi-colon is essentially a colon (:).

Like I said, quick but not exactly what you came here for.

2. The Harder, But Exceedingly More Useful Approach

This approach requires the use of Google Apps Script, a built-in scripting program for the Google Suite. You can access it by clicking on the Tools> menu and then  > Script Editor.

How to Get Something from Google Sheets and Display it in the Sidebar in Google Apps Script

Bare Bones Basics for the Non-Coder

Simply delete everything in the Code.gs file that first appears. Then paste in the following.

Then save the document. A popup window will appear asking you to name your project. I usually name it the same as my spreadsheet, but you can call it what you want.  For this tutorial, however, I’ll call the project: DateTimeStamp.

Date Time Stamp - Google Apps Script with Google Sheets

There are only 3 things you need to modify to make this script work how you want and where you want. They can be found at the top of the code and are documented fairly clearly.

COLUMNTOCHECK

This is the column that you will add data in that will cause Google Apps Script to put the date and/or time in another column of your choosing.

Here is an example: I want to create a sheet recording the date I took my blood pressure.

In column A, I want the current date to appear when I add my blood pressure to column B.

My Blood Pressure Sheet

 

In Google Apps Script, each column is represented by a number, starting at 1 (One). In my example, column 2 is the one we will add data to so we will need to change this in the code by changing the value of the COLUMNTOCHECK.

DATETIMELOCATION

The next thing we need to look at is where we want to put the date. In our example, we want to put it in Column 1 when something is added to Column 2. To do this we modify the DATETIMELOCATION variable.  This variable has two sets of numbers. The first is a row number and the second is the column so the variable could look like this:

var DATETIMELOCATION = [row number, column number];

These row and column numbers are OFFSET from the cell that we enter information into. So, for example, if we enter something into the cell of B3 (column = 2, row = 3 in Google Apps Script) and our offset is something like [0,1], then the Date and/or Time will stay on the same row and move to the right 1 column to C3.

Let’s look at our Blood Pressure example. In Column A we want the date, and in column B we enter our blood pressure. If I make my first blood pressure entry in cell B2 (column = 2, row = 2) then I expect the Date to automatically appear in A2.

So we are staying on the same row, so row = 0. But we are going back one column, so column = -1.

Offset - Google Apps Script

So we’ll go ahead and change the DATETIMELOCATION  on line 9 variable to:

var DATETIMELOCATION = [ 0,-1];

SHEETNAME

Finally, we want to tell Google Apps Script what sheet we are working on. Right now in our script the SHEETNAME is equal to “Sheet1”, which is the standard sheet when you open a new spreadsheet. For our example, the name of our sheet is “BP” for Blood Pressure, of course.

Sheet name

 

Go ahead and change SHEETNAME to “BP”:

var SHEETNAME = "BP"

 

Okay, you are all done with Google Apps Script. You can go ahead and save and close the script tab. It will continue to run in the sheet and will run every time you open the sheet.

Check out the example below:

Current Date Time Stamp Google Sheets

 

Modifying the Date Type

You can now change the date information Date, Date and Time or just Time by selecting the column and then going to the <Format> menu and then chose one of the Date options. In my example, I want both the time and date. This is how I changed it:

Changing The Date Type in Google Sheets

Looking to learn more about Google Apps Scripts in a more structured format? Udemy has some great courses that can get your from the basics to a real Google Apps Script pro!

Got a more specific problem you need help with, but don’t have the time to develop the skills? Fiverr’s your best bet to find a skilled Google Apps Script professional to solve your problem quickly and cheaply. *

 

*The above affiliate links have been carefully researched to get you to what you specifically need. If you decide to click on one of these links it will cost you just the same as going to the site. If you decide to sign up, I just get a little pocket money to help pay for the costs of running this website.

 

201 thoughts on “Add the Current Date to a Sheet When Data Is Added So That The Date Does Not Change(Static) – Google Sheets”

  1. Is there a way to adapt this for when a form is entered or information is submitted from a form? I tried it but it doesn’t add the date when information comes from the form into the Form Responses Tab.

    1. Hi Mario,

      Good question. Maybe look at changing the onEdit function to a general function that checks if the reference cell is not empty and the date cell does not already contain a date, then add today’s date. Then apply a time trigger to the function. Alternatively, you could change the above modified function to and onOpen() function that would update when the sheet is opened, should you not access the sheet regularly.
      The form responses should already come with a date-time stamp if you want to use that too. Just modify how you want them displayed by selecting the column and your preferred date type.

      Hope this helps. If you come up with something better. Please share.

        1. Hi Piyush,
          I feel, in this post, that I have fallen into the trap of doing people’s work for them and this is certainly not the purpose of this website.
          If you make and post a solid attempt, I or one of my readers might be able to better help you learn what you need to accomplish your task.

  2. I’m really a script noob here, so… is there a way to make it only add a date if a specific word is in the column? Like it should add a date if there’s a ‘added’ in the column to check, but not if there’s a ‘missing’ in it.

    1. Hi Anne,

      Yes, you can create and extra ‘if’ statement once you get the cell value and if that cell value equals say, ‘added’ then the date will appear.

      /**
      * Creates a Date Stamp if a column is edited.
      */

      //CORE VARIABLES
      // The column you want to check if something is entered.
      var COLUMNTOCHECK = 1;
      // Where you want the date time stamp offset from the input location. [row, column]
      var DATETIMELOCATION = [0,1];
      // Sheet you are working on
      var SHEETNAME = ‘Sheet1’
      // Value condition.
      var VALUETOCHECK = “checked”;

      function onEdit(e) {
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var sheet = ss.getActiveSheet();
      //checks that we’re on the correct sheet.
      if( sheet.getSheetName() == SHEETNAME ) {
      var selectedCell = ss.getActiveCell();
      //checks the column to ensure it is on the one we want to cause the date to appear.
      if( selectedCell.getColumn() == COLUMNTOCHECK) {
      if(selectedCell.getValue() == VALUETOCHECK){
      var dateTimeCell = selectedCell.offset(DATETIMELOCATION[0],DATETIMELOCATION[1]);
      dateTimeCell.setValue(new Date());
      }
      }
      }
      }

  3. Thanks a lot for this extremely useful tool. Is there anyway to run this on all sheets instead of only 1?
    I need to have one sheet for each month and i want the same time stamp for each month.
    Probably i can replicate the script with a different sheetname but maybe there is an easier way.
    Thanks.

    1. Hi Ers,

      Take a look at the code, you can see that one of the if statements checks to see if the sheet name matches. We can remove this if statement and then any sheet with the column selected can be uses.

      /**
      * Creates a Date Stamp if a column is edited.
      */

      //CORE VARIABLES
      // The column you want to check if something is entered.
      var COLUMNTOCHECK = 1;
      // Where you want the date time stamp offset from the input location. [row, column]
      var DATETIMELOCATION = [0,1];

      function onEdit(e) {
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      //checks that we’re on the correct sheet.
      var selectedCell = ss.getActiveCell();
      //checks the column to ensure it is on the one we want to cause the date to appear.
      if( selectedCell.getColumn() == COLUMNTOCHECK) {
      var dateTimeCell = selectedCell.offset(DATETIMELOCATION[0],DATETIMELOCATION[1]);
      dateTimeCell.setValue(new Date());
      };
      };

      1. Hey, thanks a lot for the entire article it was insanely useful and easy to replicate! Can you please, just share how to duplicate the script for more than one sheets, not all of them.

        Should I use

        Sheet you are working on
        var SHEETNAME = ‘Sheet1’ –> add another sheet here or not?

        Thanks again and sorry for bothering you!

        1. Hi Sonya,

          I’m glad you found the tutorial useful. And good job making attempt. I’ll point you in the direction you need to go. There are a number of approaches to putting the sheet on multiple pages.
          Let’s assume the that the column you want to add the date to on each page you want to add the date is the same.
          For one or two extra sheets your could add something like:
          SHEETNAME1 = 'Sheet2';
          SHEETNAME2 = 'Sheet3';

          Then on line 17, add an “OR” comparison operator identified by the two vertical bars: ||.
          if( sheet.getSheetName() == SHEETNAME || sheet.getSheetName() == SHEETNAME1 || sheet.getSheetName() == SHEETNAME2) {

          A better approach for a larger number of sheets would be to put the sheet names in an array( e.g. SHEETNAMES = [“sheet1″,”sheet2″,”sheet3”] and then iterate through them using a “for” loop.

          Give it a try on a practice sheet. If you get stuck, feel free to post your attempt. I or one of our readers might be able to help.

  4. Glad I found this tool, this is so helpful. Just wondering what to do if I want to put the Date stamp and Time stamp on separate columns. Thank you so much.

    1. Hi Riz,

      You could certainly duplicate line 21 and extend the line 9 array to add another offset location.

      Then all you would need to do is to format to date and time in Google Sheets.

      1. Hi Yagi, thanks for the quick reply. Could you please tell me how to do it exactly? Sorry, I just don’t have any idea. Thanks again.

        1. Hi Riz,
          You can add to this Array.
          // Where you want the date time stamp offset from the input location. [row, column]
          var DATETIMELOCATION = [0,1];
          for example :
          var DATETIMELOCATION = [0,1,0,2];

          Then add a line like:
          var dateCell = selectedCell.offset(DATETIMELOCATION[0],DATETIMELOCATION[1]);
          var timeCell = selectedCell.offset(DATETIMELOCATION[2],DATETIMELOCATION[3]);

          Then in your sheet, click in the column header you want the date. Then go to Format>Number>Date and then click on the next column, Format>Number>Time.

          Make an attempt at the code and if you have a problem feel free to post it.

          1. Hi Yagi,

            Should I put in the duplicate in line 22? It doesn’t work. Thanks again.

          2. Hi Riz,

            Yes. you will also need to setValue for the new duplicate variable. Copy and paste the code you are working on in the reply and I can take a look.

            Alternatively, you can simply reference the cell in another cell in Google Sheets. For example, if your date is in cell B2 then say, in cell C2, you could add the formula =B2. Then click and drag the little box on the bottom right of the cell and drag it all the way down. You can then go ahead and click Format>Numbers>Date or Time etc.

          3. Hi Yagi,
            So, I simply referenced the cell into another cell and it perfectly worked! Awesome! Thanks a lot!

  5. Thank you so much Yagi,… but I don’t know for sheet that has some column, and for workbook that has some sheet?
    I’m looking forward your hand…. Thank you…

    1. Sorry… to continue my question…,
      How to make script to set Time such as: dd-MM-yyyy HH:mm:ss,
      Ok, Yagi… I’m waiting your hand….please…

    2. Hi Sugianto,

      You could change the spreadsheet location using something like var targetSheet = SpreadsheetApp.getSheetByName("sheet name"). Have a try and post your attempt and I will take a look.

  6. I tried to make time stamp for several columns is like the below, but it couldn’t run, and also how about making time stamp for several sheet?
    /**
    * Creates a Date Stamp if a column is edited.
    */

    //CORE VARIABLES
    // The column you want to check if something is entered.
    var COLUMNTOCHECK = 4;
    // Where you want the date time stamp offset from the input location. [row, column]
    var DATETIMELOCATION = [0,38];
    // Sheet you are working on
    var SHEETNAME = ‘PR’

    function onEdit(e) {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getActiveSheet();
    //checks that we’re on the correct sheet.
    if( sheet.getSheetName() == SHEETNAME ) {
    var selectedCell = ss.getActiveCell();
    //checks the column to ensure it is on the one we want to cause the date to appear.
    if( selectedCell.getColumn() == COLUMNTOCHECK) {
    var dateTimeCell = selectedCell.offset(DATETIMELOCATION[0],DATETIMELOCATION[1]);
    dateTimeCell.setValue(new Date());
    }
    }
    }
    /**
    * Creates a Date Stamp if a column is edited.
    */

    //CORE VARIABLES
    // The column you want to check if something is entered.
    var COLUMNTOCHECK = 5;
    // Where you want the date time stamp offset from the input location. [row, column]
    var DATETIMELOCATION = [0,38];
    // Sheet you are working on
    var SHEETNAME = ‘PR’

    function onEdit(e) {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getActiveSheet();
    //checks that we’re on the correct sheet.
    if( sheet.getSheetName() == SHEETNAME ) {
    var selectedCell = ss.getActiveCell();
    //checks the column to ensure it is on the one we want to cause the date to appear.
    if( selectedCell.getColumn() == COLUMNTOCHECK) {
    var dateTimeCell = selectedCell.offset(DATETIMELOCATION[0],DATETIMELOCATION[1]);
    dateTimeCell.setValue(new Date());
    }
    }
    }
    /**
    * Creates a Date Stamp if a column is edited.
    */

    //CORE VARIABLES
    // The column you want to check if something is entered.
    var COLUMNTOCHECK = 6;
    // Where you want the date time stamp offset from the input location. [row, column]
    var DATETIMELOCATION = [0,38];
    // Sheet you are working on
    var SHEETNAME = ‘PR’

    function onEdit(e) {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getActiveSheet();
    //checks that we’re on the correct sheet.
    if( sheet.getSheetName() == SHEETNAME ) {
    var selectedCell = ss.getActiveCell();
    //checks the column to ensure it is on the one we want to cause the date to appear.
    if( selectedCell.getColumn() == COLUMNTOCHECK) {
    var dateTimeCell = selectedCell.offset(DATETIMELOCATION[0],DATETIMELOCATION[1]);
    dateTimeCell.setValue(new Date());
    }
    }
    }
    /**
    * Creates a Date Stamp if a column is edited.
    */

    //CORE VARIABLES
    // The column you want to check if something is entered.
    var COLUMNTOCHECK = 7;
    // Where you want the date time stamp offset from the input location. [row, column]
    var DATETIMELOCATION = [0,38];
    // Sheet you are working on
    var SHEETNAME = ‘PR’

    function onEdit(e) {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getActiveSheet();
    //checks that we’re on the correct sheet.
    if( sheet.getSheetName() == SHEETNAME ) {
    var selectedCell = ss.getActiveCell();
    //checks the column to ensure it is on the one we want to cause the date to appear.
    if( selectedCell.getColumn() == COLUMNTOCHECK) {
    var dateTimeCell = selectedCell.offset(DATETIMELOCATION[0],DATETIMELOCATION[1]);
    dateTimeCell.setValue(new Date());
    }
    }
    }

    1. The variables “ss” is used to get the active spreadsheet and “sheet” is used to get the active sheet only need to be called once. These basically state, I want to work on this spreadheet (ss) and this specific sheet(sheet).
      Considering you are changing the COLUMNTOCHECK variable, I think you are trying to do the following:
      – if column 4, 5, 6 or 7 is edited, then add the date in column 38.
      Is this correct?

  7. Hi Yagi
    Q: if column 4, 5, 6 or 7 is edited, then add the date in column 38.
    Is this correct?
    A: Only column 4 and 5 can be edited and can appear time stamp in column38, but column 6 and 7, they cann’t. please your hand… Here the link of my google spreadsheet.

  8. Hi Yagi.. here link the sheet
    “https://docs.google.com/spreadsheets/d/19wfQZd9LLGYjtF5xQ19Whh0jratuiHgj5BVZhyTPvMY/edit?usp=sharing”

    1. Hi Sugianto,
      So it looks like you want to offset each time you add a value in the first half of the month or the second half of the month section.

      Why don’t you just change var DATETIMELOCATION to equal [0,38];
      And then change var COLUMNTOCHECK = 1; to something like var COLUMNSTOCHECK = [4,27];
      Then you can change the “if” statement on line 20 to something like this:
      if( selectedCell.getColumn() >= COLUMNSTOCHECK[0] && selectedCell.getColumn() <= COLUMNSTOCHECK[1]) { Play with this and see if you get the results you want.

      1. Wow..so quickly respon…thank you I Will try… actually I’m not programmer, so I sent you a link of my google sheet so that you can see my false of script… “https://docs.google.com/spreadsheets/d/19wfQZd9LLGYjtF5xQ19Whh0jratuiHgj5BVZhyTPvMY/edit?usp=sharing”

      2. Hello Yagi, I’ve tried to follow your advice, but it cann’t still run to make time stamp; and then I got a problem when the input cell is updated so the time stamp is not updated too… why? if I wanna updated the timestamp… what the script? for clearly you can see my sheet at the link: “https://docs.google.com/spreadsheets/d/19wfQZd9LLGYjtF5xQ19Whh0jratuiHgj5BVZhyTPvMY/edit?usp=sharing”
        ….. please your hand

        /**
        * Creates a Date Stamp if a column is edited.
        */

        //CORE VARIABLES
        // The column you want to check if something is entered.
        var COLUMNTOCHECK = [4,38];
        // Where you want the date time stamp offset from the input location. [row, column]
        var DATETIMELOCATION = [0,38];
        // Sheet you are working on
        var SHEETNAME = ‘PR’

        function onEdit(e) {
        var ss = SpreadsheetApp.getActiveSpreadsheet();
        var sheet = ss.getActiveSheet();
        //checks that we’re on the correct sheet.
        if( sheet.getSheetName() == SHEETNAME ) {
        var selectedCell = ss.getActiveCell();
        //checks the column to ensure it is on the one we want to cause the date to appear.
        if( selectedCell.getColumn() >= COLUMNSTOCHECK[0] && selectedCell.getColumn() < = COLUMNSTOCHECK[1]) {
        var dateTimeCell = selectedCell.offset(DATETIMELOCATION[0],DATETIMELOCATION[1]);
        dateTimeCell.setValue(new Date());
        }
        }
        }

    1. Hi Raul,

      I haven’t really set anything up like this yet, but thank you for your generous thoughts.

  9. Hello Yagi,
    Your Sample is for one column input and one time stamp column and one sheet… How about more than that, for example: more than one column input, more than one column timestamp and more than one sheet…

    1. Sure, you could look at ‘for’ loops, ‘if’ and ‘if else’ statements to achieve this. Give it a try, you’d be surprised what you can come up with.

  10. Hi Yagi

    Like everyone else here I have looked high and wide for how to do this and this is the first place I’ve found an easy tutorial! So thank you!

    I’m just wondering how this can be used twice in the same sheet? I have two columns that I want timestamped. In your example, say you also wanted column D to have a timestamp in column C. I’ve tried fiddling around but can’t make anything work.

  11. Hello Yagi!
    Very impressive, thorough, and articulated article.

    Like David mentioned, what would need to change in the script for the date to be updated when any cell in -let’s say- row 2 is updated? Is there a ‘ROWTOCHECK’?

    1. Hi Jessica,

      You could use a getRange or if you only have a few columns something like COLUMNSTOCHECK = [2,3] and then in your “if” statement you could do:
      if( selectedCell.getColumn() == COLUMNTOCHECK[0] || selectedCell.getColumn() == COLUMNTOCHECK[1] ){...}

      1. Yagi!

        On the first go it didn’t work. Tho truth is, the sheet I’m working with is somewhat complex, I’ll give it a bunch more tries..
        Thank you very much!

  12. Hey Yagi!

    This was amazing, works great for user edits.

    I’m using a program called Zapier to post into a sheet when a certain action is done on another program. My only issue is that Google Sheets doesn’t see this as a ‘User’ post and won’t trigger the above script… Wondering if you have any opinion on how to get it to activate when it isn’t a ‘user’ edit?

    Thanks so much <3

    1. Hi David,

      Unfortunately that is currently one of the pitfalls of the onEdit(e) function. Your next best option would be to create a new function with the same information and set it to a time trigger. This however, is only as accurate up to the minute. You can do this by going into Edit >> Current Project’s Triggers. Time Trigger

      1. Hi Yagi,

        This article is awesome and I wonder if you could explain how to force updating timestamp when value is changed by formula?

        I’ve created new script file, copied/paste whole function set the trigger and it looks like it’s not updating cell with new timestamp when value in formula based cell is changed.

        Could you tell me if I’m wrong at some point? Thanks!

        1. Hi Kambo,

          onEdit() does not consider formulas that result from a change as an event that would trigger it.

          You could:

          1. Reference the actual cells of the values you are editing that the formula draws from.
          2. Create a time trigger to update the column values regularly.
          1. Hey Yagi,

            Edited cells are in different spreadsheet and I’ve tried to do this way error appears and didn’t find an answer how to fix it. Google Script Error: You do not have permission to call openById?

            Maybe there is similar function for google scripts which e.g. count how many times cell was changed even cell to count is formula?

          2. Hey Kambo,

            There are no triggers that recognize changed formula cells.

            Maybe you could use importrange in a hidden sheet in your spreadsheet and reference that. Possibly the changes would be recognised that but I am not in a spot to test it at the moment, sorry.

  13. Hello is it possible to have the same functionality for two column in the same sheet? Im using a checkbox and only want a timestamp if checked not when unchecked, also is it possible to lock the cell from being edited after the timestamp is inserted?
    Thanks!

  14. Hi Yagi,
    Thanks a lot for this extremely useful tool! I’m just wondering if there could be an extra function in this script to make the data to be only generated automatically, meaning not able to be changed manually? Many thanks!

    1. Hi Michael,
      Yes. You could rename your function to something else and set a time trigger to it. This will only update once a minute though.

  15. Very helpful for me, a total newbie to coding.

    Your explanation was very clear. After following the directions for a new column with date of creation, I could figure out how to change the code slightly to add another column which would automatically show the date when a checkbox (in yet another column) was checked. As you might have guessed, this is a to-do list.

    Many thanks!

  16. Hi! This seems close to what I need. I’m making a simple attendance tracker with student names in column one and a range of dates and checkboxes in the rest of the sheet.

    This script works for the first check box, but I need it to work for any box checked in the column and then STOP, because I don’t want the date overwriting a check box. It just needs to appear in the top row of the column on the first checked box.

    Any suggestions?

  17. For someone who has managed to let the computer age slip on by I found this a wonderful tutorial.
    Would it be at all possible to have both the date stamp and a time stamp in the same column?
    I have no clue where to start and I am a little afraid of breaking everything on my Google sheet, I tried adding a date stamp to the script but ended up having to delete everything and starting again. I felt so far out of my depth. This is my first ever sheet and I am learning stuff everyday, but feel maybe this is a step too far.
    I have also tried putting a date stamp at the end of your script but again got so lost I had to redo the original.
    Anyway well done on doing such a great tutorial.

    1. Hi Gary,

      Thanks for the kind words.

      You’ll be pleased to discover that you won’t have to do anything extra to the code (except of course change the column and offset).

      On your Google Sheet, first select he column your date will be added to. Then go to Format>Number and then towards the bottom, you will see the Date Time format. Click that and you’re golden.

      Hope this helps.

      Good luck.

      1. Thank you ever so much Yagi it works better than I could of imagined.

        To total novices like myself your tutorial is awesome. I will be looking at your other tutorials over the coming days and if they are as good as this one I think I will learn a bit.

        Thank you again.

  18. Hi There,, I have applied this script to my workbook and i have 30 sheets for each month,, I am not sure if there is additional script to add to make sure this script work to all the sheets.. also i need a line to add the TimeZone as currentli is doing the stampdate diferent from the system.

    i think all the rest is working very good.

    Thanks Mauricio

    1. Hi Mauricio,

      I think what you might need is a custom solution based on this code that more specifically suits your needs. Perhaps one of the readers might be interested in offering their skills for a fee.

      ~Yagi

  19. Good Morning!
    I am not familiar with coding but I found that your step by step process was very easy to follow. I actually needed the same format as your example. I have looked through all of the previous questions and I do not think you have addressed this yet. I have all the data in script editor and it is working properly but I was wondering if there was a way to prevent the date/time stamp from changing if the data in Column B was changes at a later time. I would like the stamp in Column A to remain the same after initial Column B submission. I hope my question makes sense. I have searched other site and tried to combine various coding but since I’m not familiar with this my attempts did not work. Thanks for your insight!

  20. Great stuff. I’ve been having some issues with having 2 columns running at the same time though. Any ideas? Seems like the second variable cancels out the first one or something. I wanted to have 2 checkboxes. One for when I started a task and one for when I ended it. ( Checkboxes are in A and F Respectively while the dates are in the column next to them ).

  21. Hello,

    Thanks all this was a great help.
    Well i am not a programmer.
    Just copied and pasted your code.

    What i actually want is,

    I have google spread sheet named A, with 2 sheets A1 & A2.

    In A1 i want to fill a column 8, when column 7 is filled with 1.
    In A1 i want to fill a column 10, when column 9 is filled with 1.

    Now, in A2 i want to fill a column 8, when column 7 is filled with 1.
    In A2 i want to fill a column 10, when column 9 is filled with 1.

    Can you please give me a code for the above?

    Regards,
    Bharat

  22. I’m trying to use your timestamp script so that I can see what time a user checks a checkbox. I set the permissions on the column with timestamps so that the user would not be able to edit, but that prevents the timestamp from appearing. Is there a way to get a timestamp without allowing a user to edit it?

  23. Hi Yagi –

    Very helpful. Thanks so much – this works well. I’d like to make a tweak and would love some help.

    First, I’d like a date to be created when the cell contains specific values. I have a picklist and I’d like a date set when the cell value is (example) “Bird” or “Dog” but not “Cat” or “Fish”.

    Second, I’d like the date that is created when the value is selected to be the date the cell value was FIRST selected and not when or it if it is subsequently edited. I’d like a static date stamp when someone first selects the value “Bird” but I don’t want that date to change if someone changes the value to “Dog” or “Cat”.

    Hope that makes sense.

    Thanks!

    1. Great. Love to see how you go with your project. Feel free to share your code when you get stuck. I’m sure one of my readers can help you out.

  24. Hello this is an awesome script, works perfectly for me… I just want to know if there is a way to clear the date if there is no data in the cell being checked. I use this as a “timeclock” and its cleared everyday. the column auto clears every night but the old timestamps stay.

  25. Yagi, Thank you so much. This is an incredibly helpful script. I’ve been trying modify it and I wonder if you (or someone else) could help. What I’d like to do is copy and paste values from some cells in the same row when a cell is changed. (e.g. if I change a cell in column F, I’d like the script to copy and paste values for the cells in columns B:E on the same row. Those cells hold formulas, but I want to make them static once someone changes a particular cell in that row.

    1. I think i figured it out. The only issue is that it doesn’t like having 2 of these in one project (you can see I added a “2” to all the variables. Variable names are little funky – that could be improved easily.

      //CORE VARIABLES
      // The column you want to check if something is entered.
      var COLUMNTOCHECK2 = 2;
      // Where you want the date time stamp offset from the input location. [row, column]
      var DATETIMELOCATION2 = [0,-1];

      // Sheet you are working on
      var SHEETNAME2 = ‘PurchaseOrders’

      function onEdit2(e) {
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var sheet = ss.getActiveSheet();
      //checks that we’re on the correct sheet.
      if( sheet.getSheetName() == SHEETNAME2 ) {
      var selectedCell = ss.getActiveCell();
      //checks the column to ensure it is on the one we want to cause the date to appear.
      if( selectedCell.getColumn() == COLUMNTOCHECK2) {
      var dateTimeCell = selectedCell.offset(DATETIMELOCATION2[0],DATETIMELOCATION2[1]);
      var data = dateTimeCell.getValues()
      dateTimeCell.setValue(data) , {contentsOnly: true};
      }
      }
      }

      1. Hi Roger, you are getting there. onEdit is a built in trigger that will trigger each time the selected range is edited. when you ardd a 2 to the end, “onEdit2” it becomes a function of your own design. Try adding the extra range to the current onEdit. Adding the extra variables you have is fine. My suggestion would be to look at the “if” statements and see if you can add an extra range there by using “or” (||).

  26. Hey Yagi,

    Code works perfect and it’s a great tool! I just need an update to it, so when you delete the cell content, the script will also automatically delete the date time stamp. Appreciate it!

    1. Go for it! The basic Premise is there in the current code. You might look at something like ‘if’ the cell is empty then add the date onEdit, ‘else’ if the date is present and the edited cell is empty remove the date.

      Good luck!

  27. Hey Yagi,

    I MUST say, it’s an excellent tutorial. It helped me a lot.

    I have one problem there are multiple columns and sheets. I tried multiple scripts but it’s not working, I also tried to use array but it’s not working.

    It will hardly take 2 minutes for you to have a look on my sheet. Here it is “docs.google.com/spreadsheets/d/1CPhJnYCMUaQpjPJqy5JQFAxk8h7gPerEhsWlQpzrXs0/edit#gid=0”

    Can you please check and let me know where I am getting wrong?

    1. Hey Yagi,

      Thanks for quick reply, here is a screenshot of my sheet. You will understand what I want to achieve with your super app script.

      https://i.imgur.com/dILA6VH.png

      Here is script I am trying,

      /**
      * Creates a Date Stamp if a column is edited.
      */

      //CORE VARIABLES
      // The column you want to check if something is entered.
      var COLUMNTOCHECK = 1;
      // Where you want the date time stamp offset from the input location. [row, column]
      var DATETIMELOCATION = [0,+1];
      // Sheet you are working on
      var SHEETNAME = ‘Emails’

      function onEdit(e) {
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var sheet = ss.getActiveSheet();
      //checks that we’re on the correct sheet.
      if( sheet.getSheetName() == SHEETNAME ) {
      var selectedCell = ss.getActiveCell();
      //checks the column to ensure it is on the one we want to cause the date to appear.
      if( selectedCell.getColumn() == COLUMNTOCHECK) {
      var dateTimeCell = selectedCell.offset(DATETIMELOCATION[0],DATETIMELOCATION[1]);
      dateTimeCell.setValue(new Date());
      }
      }
      }
      /**
      * Creates a Date Stamp if a column is edited.
      */

      //CORE VARIABLES
      // The column you want to check if something is entered.
      var COLUMNTOCHECK = 3;
      // Where you want the date time stamp offset from the input location. [row, column]
      var DATETIMELOCATION = [0,+1];
      // Sheet you are working on
      var SHEETNAME = ‘Emails’

      function onEdit(e) {
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var sheet = ss.getActiveSheet();
      //checks that we’re on the correct sheet.
      if( sheet.getSheetName() == SHEETNAME ) {
      var selectedCell = ss.getActiveCell();
      //checks the column to ensure it is on the one we want to cause the date to appear.
      if( selectedCell.getColumn() == COLUMNTOCHECK) {
      var dateTimeCell = selectedCell.offset(DATETIMELOCATION[0],DATETIMELOCATION[1]);
      dateTimeCell.setValue(new Date());
      }
      }
      }

      1. Hi Yogesh,

        You might be best served by packing all your sheets and column locations into an object:

        var locs = [
        {
        sheet: "Sheet1",
        cols: [2, 4, 6]
        },
        {
        sheet: "Sheet2",
        cols: [2, 5, 7]
        }
        ];

        This would replace: COLUMNTOCHECK and SHEETNAME

        Put everything in one onEdit() function trigger.
        Use a “for” loop to loop through each “sheet” in your object locs[loc].sheet.

        Then nest another for loop to iterate through your columns locs[loc].cols[col]

        Have a play around and see if you can get it working.

        ~Yagi

        1. Hey Yagi,

          We tried and it worked. The code is different, you can have a look.

          var COLUMNTOCHECK = 1;
          var COLUMNTOCHECK3 = 3;
          var COLUMNTOCHECK5 = 5;
          var COLUMNTOCHECK7 = 7;
          var COLUMNTOCHECK9 = 9;
          var DATETIMELOCATION = [0,1];
          var SHEETNAME = ‘Emails’
          var SHEETNAME2 = ‘Calls’

          function onEdit(e) {
          var ss = SpreadsheetApp.getActiveSpreadsheet();
          var sheet = ss.getActiveSheet();
          if( sheet.getSheetName() == SHEETNAME ) {
          var selectedCell = ss.getActiveCell();
          if( selectedCell.getColumn() == COLUMNTOCHECK) {
          var dateTimeCell = selectedCell.offset(DATETIMELOCATION[0],DATETIMELOCATION[1]);
          dateTimeCell.setValue(new Date());
          }
          }
          if( sheet.getSheetName() == SHEETNAME ) {
          var selectedCell = ss.getActiveCell();
          if( selectedCell.getColumn() == COLUMNTOCHECK3) {
          var dateTimeCell = selectedCell.offset(DATETIMELOCATION[0],DATETIMELOCATION[1]);
          dateTimeCell.setValue(new Date());
          }
          }
          if( sheet.getSheetName() == SHEETNAME ) {
          var selectedCell = ss.getActiveCell();
          if( selectedCell.getColumn() == COLUMNTOCHECK5) {
          var dateTimeCell = selectedCell.offset(DATETIMELOCATION[0],DATETIMELOCATION[1]);
          dateTimeCell.setValue(new Date());
          }
          }
          if( sheet.getSheetName() == SHEETNAME ) {
          var selectedCell = ss.getActiveCell();
          if( selectedCell.getColumn() == COLUMNTOCHECK7) {
          var dateTimeCell = selectedCell.offset(DATETIMELOCATION[0],DATETIMELOCATION[1]);
          dateTimeCell.setValue(new Date());
          }
          }
          if( sheet.getSheetName() == SHEETNAME ) {
          var selectedCell = ss.getActiveCell();
          if( selectedCell.getColumn() == COLUMNTOCHECK9) {
          var dateTimeCell = selectedCell.offset(DATETIMELOCATION[0],DATETIMELOCATION[1]);
          dateTimeCell.setValue(new Date());
          }
          }
          var ss = SpreadsheetApp.getActiveSpreadsheet();
          var sheet = ss.getActiveSheet();
          if( sheet.getSheetName() == SHEETNAME2 ) {
          var selectedCell = ss.getActiveCell();
          if( selectedCell.getColumn() == COLUMNTOCHECK) {
          var dateTimeCell = selectedCell.offset(DATETIMELOCATION[0],DATETIMELOCATION[1]);
          dateTimeCell.setValue(new Date());
          }
          }
          if( sheet.getSheetName() == SHEETNAME2 ) {
          var selectedCell = ss.getActiveCell();
          if( selectedCell.getColumn() == COLUMNTOCHECK3) {
          var dateTimeCell = selectedCell.offset(DATETIMELOCATION[0],DATETIMELOCATION[1]);
          dateTimeCell.setValue(new Date());
          }
          }
          if( sheet.getSheetName() == SHEETNAME2 ) {
          var selectedCell = ss.getActiveCell();
          if( selectedCell.getColumn() == COLUMNTOCHECK5) {
          var dateTimeCell = selectedCell.offset(DATETIMELOCATION[0],DATETIMELOCATION[1]);
          dateTimeCell.setValue(new Date());
          }
          }
          if( sheet.getSheetName() == SHEETNAME2 ) {
          var selectedCell = ss.getActiveCell();
          if( selectedCell.getColumn() == COLUMNTOCHECK7) {
          var dateTimeCell = selectedCell.offset(DATETIMELOCATION[0],DATETIMELOCATION[1]);
          dateTimeCell.setValue(new Date());
          }
          }
          if( sheet.getSheetName() == SHEETNAME2 ) {
          var selectedCell = ss.getActiveCell();
          if( selectedCell.getColumn() == COLUMNTOCHECK9) {
          var dateTimeCell = selectedCell.offset(DATETIMELOCATION[0],DATETIMELOCATION[1]);
          dateTimeCell.setValue(new Date());
          }
          }
          }

          1. Great work Yogesh,

            Yes, that should definitely work. When you get a bit of time, I encourage you to see if you can put your COLUMNTOCHECK and SHEETNAMES into a set of arrays or objects, then iterate through them with two “for” loops. This should cut your code down to about 40 lines of code.

            Nevertheless, this is a fantastic achievement if you haven’t done much coding before. Way to go!

            ~Yagi

        2. Hello Yagi,

          Thank you so much for creating this script, and for all your help answering everybody’s questions!
          I’ve looked through all the comments, and you mention (several times) to use a “for” loop for multiple sheets. I’ve tried to do do this several times with no success. Can you show me what this script would look like?

          Another helpful solution would be to exclude 1 sheet. Maybe you could show the script for that?
          I know that the operator for “not equal to” is , but I haven’t been able to incorporate it successfully.

          Thank you!

          1. Ho Jose,

            Thanks for the kind words.

            I think I mentioned in an earlier reply to a reader that you can change SHEETNAME variable to an array of sheets.
            var SHEETNAME = ["Sheet1","Sheet2",Sheet3","etc"]

            To loop through these sheet names you can use a “for” loop like this:
            for(var i = 0; i < SHEETNAME.lenght; i++){
            SHEETNAME[I]; //This will be each iterated value in your list
            };

            You can then compare each sheet name in the array against the active sheet. The script here under The Code header gives a good example of how to apply a “for” loop.

            For your other alternative of simply permitting the code to work on all pages except 1, see if you can find where we compare the SHEETNAME against the active sheet. Here, you can add the “Not equal to” operator.

            Give it a try and, if you get stuck, feel free to share your code in the comments. Time permitting, I will take a look.

            ~Yagi

          2. Thanks again for your help Yagi!

            I added the for loop, but was unable to make it iterate through my list.
            When I run the following script, it applies the date to all sheets, instead of just the ones in the array.

            /**
            * Creates a Date Stamp if a column is edited.
            */

            //CORE VARIABLES
            // The column you want to check if something is entered.
            var COLUMNTOCHECK = 1;
            // Where you want the date time stamp offset from the input location. [row, column]
            var DATETIMELOCATION = [0,1];
            // Sheet you are working on
            var SHEETNAME = [“Sheet1,Sheet2”]

            function onEdit(e) {
            var ss = SpreadsheetApp.getActiveSpreadsheet();
            var sheet = ss.getActiveSheet();
            //checks that we’re on the correct sheet.
            for(var SHEETNAME = 0; SHEETNAME < SHEETNAME.lenght; SHEETNAME++)
            SHEETNAME[“Sheet1,Sheet2”];{
            var selectedCell = ss.getActiveCell();
            //checks the column to ensure it is on the one we want to cause the date to appear.
            if( selectedCell.getColumn() == COLUMNTOCHECK) {
            var dateTimeCell = selectedCell.offset(DATETIMELOCATION[0],DATETIMELOCATION[1]);
            dateTimeCell.setValue(new Date());
            }
            }
            }

            Alternatively, in my attempt of simply permitting the code to work on all sheets except 1, I added the “<>” (Not equal to) operater where we compare the SHEETNAME against the active sheet, and I get a syntax error. See the following script.

            /**
            * Creates a Date Stamp if a column is edited.
            */

            //CORE VARIABLES
            // The column you want to check if something is entered.
            var COLUMNTOCHECK = 1;
            // Where you want the date time stamp offset from the input location. [row, column]
            var DATETIMELOCATION = [0,1];
            // Sheet you are working on
            var SHEETNAME = ‘Sheet1’

            function onEdit(e) {
            var ss = SpreadsheetApp.getActiveSpreadsheet();
            var sheet = ss.getActiveSheet();
            //checks that we’re on the correct sheet.
            if( sheet.getSheetName() <> SHEETNAME ) {
            var selectedCell = ss.getActiveCell();
            //checks the column to ensure it is on the one we want to cause the date to appear.
            if( selectedCell.getColumn() == COLUMNTOCHECK) {
            var dateTimeCell = selectedCell.offset(DATETIMELOCATION[0],DATETIMELOCATION[1]);
            dateTimeCell.setValue(new Date());
            }
            }
            }

            Some help on this please?

          3. Hi Jose,

            with your ‘for’ loop you want to set a unique variable for example:
            for(var i = 0; i < SHEENAME.length; i++){SHEETNAME[i]...//do something}
            "i" will be your iterator.
            Inside this loop, you can then make your 'if' statement comparting SHEETNAME[i] to sheet
            ...and then continue the rest of your code.

            In your second example, the not equal to characters are actually for Google Sheet not Google Apps Script (Javascript). Try !==

            ~Yagi

          4. I cannot thank you enough Yagi!

            I have given up on getting the “for” loop to work because I am not very good with coding, however, I was successful (and am content) with the alternative script that allows you to…

            “place the date on all sheets except 1 specified sheet” – for the benefit of other readers, see the following script.

            /**
            * Creates a Date Stamp in all sheets, excluding specified sheet, if a column is edited.
            */

            //CORE VARIABLES
            // The column you want to check if something is entered.
            var COLUMNTOCHECK = 1;
            // Where you want the date time stamp offset from the input location. [row, column]
            var DATETIMELOCATION = [0,1];
            // Sheet you are working on
            var SHEETNAME = ‘Sheet 1’

            function onEdit(e) {
            var ss = SpreadsheetApp.getActiveSpreadsheet();
            var sheet = ss.getActiveSheet();
            //checks that we’re on the correct sheet.
            if( sheet.getSheetName() !== SHEETNAME ) {
            var selectedCell = ss.getActiveCell();
            //checks the column to ensure it is on the one we want to cause the date to appear.
            if( selectedCell.getColumn() == COLUMNTOCHECK) {
            var dateTimeCell = selectedCell.offset(DATETIMELOCATION[0],DATETIMELOCATION[1]);
            dateTimeCell.setValue(new Date());
            }
            }
            }

  28. Hey Yagi,

    We have a short script with it has some bugs.

    function onEdit() {
    var Sheet = “SHEET1″;
    var CTC = [1,3,5,7,9];
    var SS = SpreadsheetApp.getActiveSheet();
    var AC = SS.getActiveCell();
    var NC = AC.offset(0, 1);
    if( SS.getName() == Sheet ) {
    if( AC.getColumn() >= 1) {
    if( NC.getValue() === ” && AC !== ”)
    NC.setValue(new Date());
    }
    }
    }

    Problem is – If we delete a cell value it inserts date in adjacent cell.

  29. Amazing!! Thank you very much!! I’ve been looking tfor this type of functions for some time now and found this site. Well done!!

  30. Hi, if i want the format to automatically change without doing the manual format change. is it possible? i want the format to be dd/mm/yyyy hh:mm:ss

  31. First of all thank you very much for your effort and code.
    I just wanted to use it on multiple columns and read all comments but couldn’t make it work for 3 columns (3,6,9)

    How can i do that?

  32. Thank you, Yagi! That’s brilliant.

    I have two questions:

    How can I modify the code to have the same scenario with other pairs of columns on the same sheet? Let’s say I have 5 pairs with value and date.

    I have several sheets with the same culumns and I will be creating new sheets, how do I apply this to the entire spreadsheet?

  33. This was really awesome. I was able to take the core ideas of this, and modify it to meet my needs. Thank you very much.

  34. Hi,

    I spent so long searching around looking for a good code to use and honestly yours was so simple that I didn’t even think it was possible. Thanks so much

  35. Hi,

    This code works really well and has made my life a lot easier so thank you!
    The only problem is i’m trying to use it in conjunction with Zapier
    For example, I’ve set your code to update a row [0, 14] away from where my data changes. It works if i manually change the data in the 0th column but if Zapier updates that row for some reason google sheets doesn’t recognise it as a change?

    Thanks!

    Archie

    1. Hi Archie,

      I’m not too familiar with Zapier, but I think the issue might be due to auto-updating test. You make have to try something like:
      function
      onChange(e){var toEdit = onEdit(e);

      Not 100% this will work, but let me know how you go.

  36. Hi Yagi,

    I’m new to script editor and I’m wondering why I’m not seeing any changes in my end:

    /**
    * Creates a Date Stamp if a column is edited.
    */

    //CORE VARIABLES
    // The column you want to check if something is entered.
    var COLUMNTOCHECK = 11;
    // Where you want the date time stamp offset from the input location. [row, column]
    var DATETIMELOCATION = [0,13];
    // Sheet you are working on
    var SHEETNAME = ‘GRF Feedback’
    // Value condition.
    var VALUETOCHECK = ‘Approved’
    // Value condition.
    var VALUETOCHECK = ‘Not Approved’
    // Value condition.
    var VALUETOCHECK = ‘Invalid’;

    function onEdit(e) {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getActiveSheet();
    //checks that we’re on the correct sheet.
    if( sheet.getSheetName() == SHEETNAME ) {
    var selectedCell = ss.getActiveCell();
    //checks the column to ensure it is on the one we want to cause the date to appear.
    if( selectedCell.getColumn() == COLUMNTOCHECK) {
    var dateTimeCell = selectedCell.offset(DATETIMELOCATION[0],DATETIMELOCATION[1]);
    dateTimeCell.setValue(new Date());
    }
    }
    }

    1. Hi Mark,

      I would first look at the 3 VALUETOCHECK variables. You are changing what is in the variable each time you set the variable. so var VALUETOCHECK = ‘Approved’ will be immediately converted to var VALUETOCHECK = ‘Not Approved’ and then immediately converted to var VALUETOCHECK = ‘Invalid’;
      The VALUETOCHECK variable is also not being used inside the onEdit(e) function.

      Currently, your editing column is column 11 and your date column is column 24 ( COLUMNTOCHECK + DATETIMELOCATION offset of 13).

      If you have a sample sheet you want to share, feel free below.

      ~Yagi.

      1. Hey Yagi,

        Here you go: http s://docs .google.com/spreadsheets /d/1iOU4LpbDgmUGPNlm5gu_ zMqBDh0O1SL_7OHFDEZu2Yk/ edit?usp=sharing

        The focus are the column P and Q.
        Column Q will tell the date and time of when the Status (Column K) was updated to Approved, Not Approved & Invalid
        Column P will tell the date and time of when the row was assigned (Column J)

        Hope this helps.

        1. Hi Mark,

          The file you sent me with id: 1iOU4LpbDgmUGPNlm5gu_zMqBDh0O1SL_7OHFDEZu2Yk does not seem to be shared.

          If Approved, Not Approved & Invalid are the only values in that column, then you can add an “if” statement after:if( selectedCell.getColumn() == COLUMNTOCHECK) {, stating that if the cell is not blank, then create a date 6 columns across (offset).

          See if you can get that running first and then we will move onto column J.

          1. Hi Yagi,

            I didn’t realize the OFFSET! After doing some experimenting, I’m happy to let your know it worked!

            Another question, do you know what script or trigger can I use when a formula changes? Let’s assume:

            B2 = Columntocheck and this is where the formula is
            A2 = Input
            C2 = Datetimelocation

            Whenever a data was input in A2, the B2 changes but there’s no action on C2. I google’d and saw that I should be using onChange() or something. Apologies, I’m really a noob.

          2. Hi Mark,

            Why don’t you make your input column the one to check? COLUMNTOCHECK = 1. Then your DATETIMELOCATION = [0,2];

            ~Yagi

  37. Hi Yagi,

    Great site and hopefully you can help?

    I am new to script editor but have learnt a lot since using your code and trying to use it in my sheet.
    However I am trying to get the date stamp to populate if ‘IQM’ is anywhere in the text in that cell but it only currently works if IQM is the only thing in the cell.

    How do I get the code to search the text for IQM? Do i need to use something else other than getValue()?

    Here is the code I am using..

    /**
    * Creates a Date Stamp if a column is edited.
    */

    //CORE VARIABLES
    // The column you want to check if something is entered.
    var COLUMNTOCHECK = 31;
    // Where you want the date time stamp offset from the input location. [row, column]
    var DATETIMELOCATION = [0,1];
    // Sheet you are working on
    var SHEETNAME = ‘Retailer reports’;
    // Value condition.
    var VALUETOCHECK = “IQM” ;

    function onEdit(e) {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getActiveSheet();
    //checks that we’re on the correct sheet.
    if( sheet.getSheetName() == SHEETNAME ) {
    var selectedCell = ss.getActiveCell();
    //checks the column to ensure it is on the one we want to cause the date to appear.
    if( selectedCell.getColumn() == COLUMNTOCHECK) {
    //checks the correct value
    if(selectedCell.getValue() == VALUETOCHECK ){
    var dateTimeCell = selectedCell.offset(DATETIMELOCATION[0],DATETIMELOCATION[1]);
    dateTimeCell.setValue(new Date());
    }
    }
    }
    }

    1. Hi Martin,
      Thanks for the kind words.

      I would try using indexOf(VALUETOCHECK) I
      in your if statement.
      For example,
      if(selectedCell.getValue().indexOf(VALUETOCHECK)

      ~Yagi

  38. Hello Yagi,

    Thanks a lot for this wonderful tutorial, I’ve been looking for something like for a while before finding it on your website. Keep up the good work !

    I’m a complete newbie to Google App Script so I’m struggling to do something that should be fairly simple : I would like the script to work if a specific cell is populated with data. Hence changing COLUMNTOCHECK with something like CELLTOCHECK but I don’t know the langage well enough it seems… Maybe you can help with this ?

    Thanks a lot anyway !
    Alex

    1. Hi Alex,
      Thanks for the kind words.
      If you would like to share your attempt, I or one of my readers can guide you.

      1. Hello Yagi,

        Thank you for your quick answer. Here’s where I am. This runs an error saying there’s a missing variable name line 7..
        Moreover, I don’t really want to check if the selected cell is the good one, I want a specific cell to be filled with a date stamp when another specific cell is edited (there’s already data by default in the cell).

        I guess I will end-up figuring it out by myself, but I’m afraid it will take ages 🙂

        Thanks to anyone that can help.
        Alex.

        /**
        * Creates a Date Stamp if a cell is edited.
        */

        //CORE VARIABLES
        // The cell you want to check if something is entered.
        var cellToCheck = 1,1;
        // Where you want the date time stamp offset from the input location. [row, column]
        var dateTimeLocation = [0,1];
        // Sheet you are working on
        var sheetName = ‘test’

        function onEdit(e) {
        var ss = SpreadsheetApp.getActiveSpreadsheet();
        var sheet = ss.getActiveSheet();
        //checks that we’re on the correct sheet.
        if( sheet.getSheetName() == sheetName ) {
        var selectedCell = ss.getActiveCell();
        //checks the cell to ensure it is on the one we want to cause the date to appear.
        if( selectedCell == cellToCheck) {
        var dateTimeCell = selectedCell.offset(dateTimeLocation[0],dateTimeLocation[1]);
        dateTimeCell.setValue(new Date());
        }
        }
        }

  39. I actually got a workaround to work by adding a button to my spreadsheet linked to this script which adds a date stamp to a specific cell :

    function dateStamp() {
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];
    var range = sheet.getRange(‘D25’);
    range.activate();
    var selection = sheet.getActiveCell();
    selection.setValue(new (Date));
    }

    It’s ok but having the same action without the click would be even better 🙂

    1. Hi Alex,

      Great work.

      the function, onEdit(e) is a custom trigger. That will run when something is edited on the page according to the parameters you set.

      function onEdit() {
      /* Another alternative to getting the sheet name you might find useful is:
      * getSheetByName(##NAME OF SHEET###)
      */
      var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2");
      var range = sheet.getRange("D25");
      range.activate();
      var selection = sheet.getActiveCell();

      /*offet is tells you where you want to put the new value in relation to
      *the 'selection' value.
      * You could also just simply apply a new range:
      * e.g. var sheet.getRange("E25");
      */
      selection.offset(0,1).setValue(new (Date));
      };

  40. Hi Yagi, this is awesome!!! Really helpful with a simple explanation.

    I am wondering if it works for vlookup or some other formila, that populates the text in a cell? So not a user edit, but the text in the cell is updated via a formula. It doesn’t seem to pick up as an edit.

    Any suggestions? I can see there were some comments that it could work as a trigger for a specific word in a column. For example, in my case, the text will be populated from another sheet as either Live or Not Live.

    1. Hi Kacey,

      Thanks for the kind words.

      You could have a look at the onChange() trigger. I haven’t had a chance to test it, though.

      ~Yagi

  41. Hello. This is very helpful. I’m not a coder but think I might be able to do this. I want to add a date in column 10 whenever anything is changed in columns 1-9. I need to know how to check multiple columns and how to put the date in column 10 as opposed to offset from the input column. Thank you very much.
    Susan

    1. Hi Susan,

      this is probably easier. This code loops through your set numbers in COL_RANGE and sets the value based on any one of those columns.

  42. Hi Yagi,

    It’s really wonderful and easy with your kind explanation!

    I want to make a little difference, so I read all replies but I couldn’t find it.

    I’m trying to get a time stamp on a second cell of the column, by the last edit in that ‘column’.

    For example, if someone edit B3 or B7, timestamp updated in B2.
    If someone edit E18 or E20, timestamp updated in E2!

    And I want to make this timestamp cell to only several columns that I want.

    Could you give me some help?

    Thank you very much, you are my hope!

    Nansol

    1. Hi Nansol,

      Thanks for the kind words. Your best bet is to remove the offset and replace it with the specific cell with getRange(“B2”).setValue(add the date here)

      Give it a solid attempt, and if you have issues, paste you attempt below and I can guide you.

      ~Yagi

      1. Hi Yagi,

        Thanks for fast reply.
        I tried, but I’m not a coder and it’s my first time to make script, I couldn’t catch it up at all.

        var COLUMNTOCHECK = 13;
        var SHEETNAME = ‘Item List’

        function onEdit(e) {
        var ss = SpreadsheetApp.getActiveSpreadsheet();
        var sheet = ss.getActiveSheet();
        if( sheet.getSheetName() == SHEETNAME ) {
        var selectedCell = ss.getActiveCell();
        if( selectedCell.getColumn() == COLUMNTOCHECK) {
        var dateTimeCell = sheet.getrange(“M2”)
        dateTimeCell.setValue(new Date())
        }
        }
        }

        I tried to make it to column M first, but it doesn’t work…

        Can you give me some solution?

        Thanks a lot.

        1. Hi Nansol,

          Awesome job! You almost had it!

          needs to change to:

          This should work now.

          You may also find that the inverted commas (” ” or ‘ ‘) are off is you try and copy and paste the code. So it is probably a good idea to rewrite them.

          ~Yagi

          1. OMG It really works! Am I genius? I thought it would be all wrong!!
            It’s all thanks to you~~
            Have a good day, Yagi 🙂

          2. Hi, Yagi!

            I have been using that script very well, but I have one more question.
            Can I apply it to several columns?

            var COLUMNTOCHECK = 12;
            var SHEETNAME = ‘List’

            function onEdit(e) {
            var ss = SpreadsheetApp.getActiveSpreadsheet();
            var sheet = ss.getActiveSheet();
            //checks that we’re on the correct sheet.
            if( sheet.getSheetName() == SHEETNAME ) {
            var selectedCell = ss.getActiveCell();
            //checks the column to ensure it is on the one we want to cause the date to appear.
            if( selectedCell.getColumn() == COLUMNTOCHECK) {
            var dateTimeCell = sheet.getRange(“L2”)
            dateTimeCell.setValue(new Date())
            }
            }
            }

            var COLUMNTOCHECK = 13;
            var SHEETNAME = ‘List’

            function onEdit(e) {
            var ss = SpreadsheetApp.getActiveSpreadsheet();
            var sheet = ss.getActiveSheet();
            //checks that we’re on the correct sheet.
            if( sheet.getSheetName() == SHEETNAME ) {
            var selectedCell = ss.getActiveCell();
            //checks the column to ensure it is on the one we want to cause the date to appear.
            if( selectedCell.getColumn() == COLUMNTOCHECK) {
            var dateTimeCell = sheet.getRange(“M2”)
            dateTimeCell.setValue(new Date())
            }
            }
            }

            With above script, only the last one works.
            What should I correct?

            Thanks a lot,
            Nansol

          3. Hi Nansol,

            Try to consolidate everything into the one onEdit() function. Either change COLUMNTOCHECK to equal a list like = [12,13] then call COLUMNTOCHECK[0] for 12 or COLUMNTOCHECK[1] for col 13, or you could just have COLUMNTOCHECK1 = 12 and COLUMNTOCHEKC2 = 13.

            In your COLUMNTOCHECK IF statement you will need to add and or “||” operator. So for example:
            if( selectedCell.getColumn() == COLUMNTOCHECK[0] || selectedCell.getColumn() == COLUMNTOCHECK[1] )

            I’ve run through this example a number of times in the comments above. Read through those for guidance and then give it a try.

            ~Yagi

          4. Hi Yagi,
            Thanks again to reply for several times so kindly.
            I looked for the comments what you said and tried to edit a script, but it has one more problem.

            var COLUMNTOCHECK = [10,11,12];
            var SHEETNAME = ‘List’

            function onEdit(e) {
            var ss = SpreadsheetApp.getActiveSpreadsheet();
            var sheet = ss.getActiveSheet();
            if( sheet.getSheetName() == SHEETNAME ) {
            var selectedCell = ss.getActiveCell();
            if( selectedCell.getColumn() == COLUMNTOCHECK[0]
            || selectedCell.getColumn() == COLUMNTOCHECK[1]
            || selectedCell.getColumn() == COLUMNTOCHECK[2] ) {
            var dateTimeCell = sheet.getRange(“L2”)
            dateTimeCell.setValue(new Date())
            }
            }
            }

            What I want to make is each timestamp appears above each column,
            but with above script, 10,11,12 column’s timestamps are all appears on “L2”.
            I guess “||” only applicable within ‘if’ statement, right?
            How can I edit ‘getRange’ to have several cells?

            Thanks a lot,
            have a good day, Yagi!

          5. Hi Nansol,

            Great work getting to where you are now!

            Perhaps a switch statement might be better for you in this case.

            Instead of your if(selectedCell.getColumn() == COLUMNTOCHECK[0] etc, you could try something like:

            <

            pre>
            var dateTimeCell

            switch(selecteCell.getColumn()){
            case COLUMNTOCHECK[0]:
            dateTimeCell = sheet.getRange("J1");
            break;
            case COLUMNTOCHECK[1]:
            DateTimeCell = sheet.getRange("K1");
            break;

            ...etc...
            }

            Give that a try. :)

            ~Yagi

  43. Hello Yagi,

    I am new at using Google Script Editor. The original script in the article work perfectly. Thank you for taking the time to post helpful articles like these. I do need help with something. In my sheet I need a script like the original script to post date, let’s say in column 2 after any value is in column 1. But I also need a date post in column 5 when the word or value “Completed” is in column 4 in the same Google Sheet. I tried combining the original script in the article with the next script in comments that you post for Anne. I’m not sure how to combine the two or if you need a new script all together. I appreciate your help!

    Best,

    John

    1. Hi John,

      Thanks for the kind words.

      Feel free to post your attempt in the comments and we can help guide you.

      ~Yagi

      1. Hi Yagi,

        Thanks for the reply. Here is my attempt below. I combined the two script and it did not the work.

        /**
        * Creates a Date Stamp if a column is edited.
        */

        //CORE VARIABLES
        // The column you want to check if something is entered.
        var COLUMNTOCHECK = 1;
        // Where you want the date time stamp offset from the input location. [row, column]
        var DATETIMELOCATION = [0,6];
        // Sheet you are working on
        var SHEETNAME = ‘Sheet1’

        function onEdit(e) {
        var ss = SpreadsheetApp.getActiveSpreadsheet();
        var sheet = ss.getActiveSheet();
        //checks that we’re on the correct sheet.
        if( sheet.getSheetName() == SHEETNAME ) {
        var selectedCell = ss.getActiveCell();
        //checks the column to ensure it is on the one we want to cause the date to appear.
        if( selectedCell.getColumn() == COLUMNTOCHECK) {
        var dateTimeCell = selectedCell.offset(DATETIMELOCATION[0],DATETIMELOCATION[1]);
        dateTimeCell.setValue(new Date());
        }
        }
        }

        //CORE VARIABLES
        // The column you want to check if something is entered.
        var COLUMNTOCHECK = 4;
        // Where you want the date time stamp offset from the input location. [row, column]
        var DATETIMELOCATION = [0,1];
        // Sheet you are working on
        var SHEETNAME = ‘Sheet 1’
        // Value condition.
        var VALUETOCHECK = “Completed”;

        function onEdit(e) {
        var ss = SpreadsheetApp.getActiveSpreadsheet();
        var sheet = ss.getActiveSheet();
        //checks that we’re on the correct sheet.
        if( sheet.getSheetName() == SHEETNAME ) {
        var selectedCell = ss.getActiveCell();
        //checks the column to ensure it is on the one we want to cause the date to appear.
        if( selectedCell.getColumn() == COLUMNTOCHECK) {
        if(selectedCell.getValue() == VALUETOCHECK){
        var dateTimeCell = selectedCell.offset(DATETIMELOCATION[0],DATETIMELOCATION[1]);
        dateTimeCell.setValue(new Date());
        }
        }
        }
        }

        1. Hi John,

          My apologies for the delay. I’ve been in international transit.

          To start you off, here are a few tips:

        2. Variables and Functions should not be repeated.
        3. Put all your code into one onEdit() function (I can feel the pros cringe, but this best place to start.)
        4. Rename those duplicate global variables (the ones outside the function onEdit()) to something else like COLUMNTOCHECK2, DATETIEMLOCATION2, Then update those variables to the same name inside the onEdit(e) code./li>
        5. You can remove any duplicate Global variables that are the same. For example, ‘SHEET_NAME’ is the same for both.
        6. You might be able to get it running after this. If so, awesome. Otherwise paste your progress and we can go from there.

          ~Yagi

          1. Hi Yagi,

            The error message now is Missing } after function body. (line 48, file “Code”). Line 48 is the last line.

            Here’s what I have.

            /**
            * Creates a Date Stamp if a column is edited.
            */

            //CORE VARIABLES
            // The column you want to check if something is entered.
            var COLUMNTOCHECK = 1;
            // Where you want the date time stamp offset from the input location. [row, column]
            var DATETIMELOCATION = [0,6];
            // Sheet you are working on
            var SHEETNAME = ‘Action Items’

            function onEdit(e) {
            var ss = SpreadsheetApp.getActiveSpreadsheet();
            var sheet = ss.getActiveSheet();
            //checks that we’re on the correct sheet.
            if( sheet.getSheetName() == SHEETNAME ) {
            var selectedCell = ss.getActiveCell();
            //checks the column to ensure it is on the one we want to cause the date to appear.
            if( selectedCell.getColumn() == COLUMNTOCHECK) {
            var dateTimeCell = selectedCell.offset(DATETIMELOCATION[0],DATETIMELOCATION[1]);
            dateTimeCell.setValue(new Date());
            }
            }
            }

            //CORE VARIABLES
            // The column you want to check if something is entered.
            var COLUMNTOCHECK2 = 9;
            // Where you want the date time stamp offset from the input location. [row, column]
            var DATETIMELOCATION2 = [0,1];
            // Value condition.
            var VALUETOCHECK = [Completed];

            function onEdit(e) {
            var ss = SpreadsheetApp.getActiveSpreadsheet();
            var sheet = ss.getActiveSheet();
            //checks that we’re on the correct sheet.
            if( sheet.getSheetName() == SHEETNAME ) {
            var selectedCell = ss.getActiveCell();
            //checks the column to ensure it is on the one we want to cause the date to appear.
            if( selectedCell.getColumn() == COLUMNTOCHECK) {
            if(selectedCell.getValue() == VALUETOCHECK){
            var dateTimeCell = selectedCell.offset(DATETIMELOCATION2[0],DATETIMELOCATION2[1]);
            dateTimeCell.setValue(new Date());
            }
            }
            }

          2. Hi John,
            A good start changing those variables. Next put all those Core Variables in ALL CAPS one under the other at the top of the code.
            You are still running 2 onEdit() functions. Let’s remove the bottom one after copying and pasting it’s data into the top onEdit() function. Don’t forget to remove duplicates.
            Let me know how you go from there.

  44. Hi Yagi,

    Error says missing } in compound statement. In the last line, 36.

    /**
    * Creates a Date Stamp if a column is edited.
    */

    //CORE VARIABLES
    // The column you want to check if something is entered.
    var COLUMNTOCHECK = 1;
    // Where you want the date time stamp offset from the input location. [row, column]
    var DATETIMELOCATION = [0,6];
    // The column you want to check if something is entered.
    var COLUMNTOCHECK2 = 9;
    // Where you want the date time stamp offset from the input location. [row, column]
    var DATETIMELOCATION2 = [0,1];
    // Value condition.
    var VALUETOCHECK = “Completed”;
    // Sheet you are working on
    var SHEETNAME = ‘Action Items’

    function onEdit(e) {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getActiveSheet();
    //checks that we’re on the correct sheet.
    if( sheet.getSheetName() == SHEETNAME ) {
    var selectedCell = ss.getActiveCell();
    //checks the column to ensure it is on the one we want to cause the date to appear.
    if( selectedCell.getColumn() == COLUMNTOCHECK) {
    var dateTimeCell = selectedCell.offset(DATETIMELOCATION[0],DATETIMELOCATION[1]);
    dateTimeCell.setValue(new Date());
    //checks the column to ensure it is on the one we want to cause the date to appear.
    if( selectedCell.getColumn() == COLUMNTOCHECK2) {
    if(selectedCell.getValue() == VALUETOCHECK){
    var dateTimeCell = selectedCell.offset(DATETIMELOCATION2[0],DATETIMELOCATION2[1]);
    dateTimeCell.setValue(new Date());
    }
    }
    }

    1. Hi John,

      Great work! You were very close. In Google Apps Script (like Javascript) functions and “if” statements work inside curly bracket “{}”. All you missed is two over these. One at the end to close the onEdit() function and one to close this “if” statement if( selectedCell.getColumn() == COLUMNTOCHECK) . It helps to indent (tab or 2x or 4x space) each line inside a brace.

      I have updated your coded and added some comments so show you where the curly bracket match one and another.

  45. Hi I’m trying to get a DateTimeStamp when a specific value is entered in the column, not just any value. Here is the the script that doesn’t seem to work. Any ideas how do I add this condition for column to check? I.e I want the date to populate in Column+1 only when data entered in column 6 is RELEASED (there are multiple selections from dropdown menu). Thanks!!

    /**
    * Creates a Date Stamp if a column is edited.
    */

    //CORE VARIABLES
    // The column you want to check if something is entered.
    var COLUMNTOCHECK = 6;
    var VALUETOCHECK = RELEASED;
    // Where you want the date time stamp offset from the input location. [row, column]
    var DATETIMELOCATION = [0,1];
    // Sheet you are working on
    var SHEETNAME = ‘72500-72599’

    function onEdit(e) {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getActiveSheet();
    //checks that we’re on the correct sheet.
    if( sheet.getSheetName() == SHEETNAME ) {
    var selectedCell = ss.getActiveCell();
    //checks the column to ensure it is on the one we want to cause the date to appear.
    if( selectedCell.getColumn() == COLUMNTOCHECK) {
    if(selectedCell.getValue() == VALUETOCHECK){
    var dateTimeCell = selectedCell.offset(DATETIMELOCATION[0],DATETIMELOCATION[1]);
    dateTimeCell.setValue(new Date());
    }
    }
    }

      1. HI Yagi,thank you! Unfortunately, it still populates the date automatically regardless of the selection I made in column 6. Is there anyway to specify that ONLY when the value in column 6 is “RELEASED” should the date be populated?
        Thank you,

        Rena

        1. HI Yagi, I’m struggling to refer to entire workbook in the script. What is below doesn’t seem to work..

          /**
          * Creates a Date Stamp if a column is edited.
          */

          //CORE VARIABLES
          // The column you want to check if something is entered.
          var COLUMNTOCHECK = 6;
          // Where you want the date time stamp offset from the input location. [row, column]
          var DATETIMELOCATION = [0,1];
          // Value condition.
          var VALUETOCHECK = “RELEASED”;
          // Sheet you are working on
          var SHEETNAME = ActiveWorkbook.Sheets

          function onEdit(e) { //Bracket 1
          var ss = SpreadsheetApp.getActiveSpreadsheet();
          var sheet = ActiveWorkbook.Sheets();
          //checks that we’re on the correct sheet.
          if( sheet.getSheetName() == ActiveWorkbook.Sheets ) { //Bracket 2
          var selectedCell = ss.getActiveCell();
          //checks the column to ensure it is on the one we want to cause the date to appear.
          if( selectedCell.getColumn() == COLUMNTOCHECK) { // Bracket 3
          Logger.log(“Column 6”);
          if(selectedCell.getValue() == VALUETOCHECK){ // Bracket 4
          var dateTimeCell = selectedCell.offset(DATETIMELOCATION[0],DATETIMELOCATION[1]);
          dateTimeCell.setValue(new Date());
          }; // Bracket 4
          }; // Bracket 3
          }; // Bracket 2
          }; // Bracket 1

          1. Hi Rena,

            It looks like you are using code that was for Johns purpose. Your original code snippet was fine. It just required quotation marks around the VALUETOCHECK item.


            /**
            * Creates a Date Stamp if a column is edited.
            */

            //CORE VARIABLES
            // The column you want to check if something is entered.
            var COLUMNTOCHECK = 6;
            var VALUETOCHECK = "RELEASED";
            // Where you want the date time stamp offset from the input location. [row, column]
            var DATETIMELOCATION = [0,1];
            // Sheet you are working on
            var SHEETNAME = "72500-72599"

            function onEdit(e) {
            var ss = SpreadsheetApp.getActiveSpreadsheet();
            var sheet = ss.getActiveSheet();
            //checks that we’re on the correct sheet.
            if( sheet.getSheetName() == SHEETNAME ) {
            var selectedCell = ss.getActiveCell();
            //checks the column to ensure it is on the one we want to cause the date to appear.
            if( selectedCell.getColumn() == COLUMNTOCHECK) {
            if(selectedCell.getValue() == VALUETOCHECK){
            var dateTimeCell = selectedCell.offset(DATETIMELOCATION[0],DATETIMELOCATION[1]);
            dateTimeCell.setValue(new Date());
            }
            }
            }
            }

          2. Hi Yagi, yes the original code is fine , but I need to be applied to the entire workbook , not just an individual sheet ..

          3. Hi Rena,

            Have a look at the times the sheet is called, or to see if the selected sheet name matches the current sheet. If you want to use the same date tool across your entire workbook, then you can remove these elements from the code.

            Give it a try, and if you have any issues copy your code and I can guide you from there.

            ~Yagi

  46. Thanks Yagi, removing the spreasheet criteria worked perfectly. You are awesome! Thanks for being patient with the beginner.

  47. Dear Yagi,

    How can I modify the code so it applies to all the tabs I have?

    Additionally, the code works but the time displayed is not my local time (I live in Italy), how can I fix this?

    thanks

    1. Hi Vincent,

      You can make the code available on all tabs by removing the SHEETNAME conditions. Essentially, you would remove lines 11,17 and 24. Go ahead and give it a go.

      The timezone issue should be related to your Google Sheet.
      To change the time zone you into your spreadsheet and go to file > Spreadsheet settings…, then change the time zone.

      If this does not fix the time zone, check your VPN location.

      ~Yagi

  48. Hi Yagi,

    Its awesome! I was able to use it on my own script.
    Can you help me with one issue i have. I was trying to use exactly same approach but instead i want to add a formula that adjusts to each row accordingly. in my google sheet i have contact data that is automatically added through Zapier integration and it creates a new row without and formula should be dragged down, but i want to make it automated. I’ve been trying to use the same way but the code doesn’t work.

    Here’s what i did:

    //CORE VARIABLES
    var COLUMNTOCHECK = 1;
    var FORMULA = [0,10];
    var SHEETNAME = ‘CRM_Updated’

    function onEdit(e) {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getActiveSheet();
    //checks that we’re on the correct sheet.
    if( sheet.getSheetName() == SHEETNAME ) {
    var selectedCell = ss.getActiveCell();
    //checks the column to ensure it is on the one we want to cause the date to appear.
    if( selectedCell.getColumn() == COLUMNTOCHECK) {
    var FORMULA = selectedCell.offset(FORMULA[0],FORMULA[1]);

    }
    }

    Hope you can help me with this.

    1. Hi Nursultan,

      Thanks for the kind words.

      Receiving data programmatically through Google Apps Script or via something like a form is not quite the use-case for onEdit(). I think that maybe the onChange() trigger might be a little better for your purposes for this. Alternatively, you might consider an onOpen or a time-based trigger if onChange is not performing as you hope.

      ~Yagi

  49. Respect Yagi,

    Was wondering if you can help me out. I am trying to Stamp the Date twice(on different columns) on the same Sheet.

    For Example:
    On “Sheet 1” when Column 4 is filled, stamp on Column 5(next one). But also, on the same sheet, when Column 8 shows any value(I have a long IF formula, but by default shows empty), Stamp date on Column 9(next one).

    I only got to make it work for 1 column. The second column doesn’t work.

    Thank you in advance.

    1. Hi Cristian,

      There are a couple of examples of what I believe you are looking for in the comments to this tutorial. See if you can piece together the code from these and if you get stuck you can post your code in the comments. If I get a chance, I can then take a look at it and guide in the right direction.

      ~Yagi.

      1. I tried this. But it didn’t work. It only showed up the date in Column 5 after Column 4 was checked.

        /**
        * Creates a Date Stamp if a column is edited.
        */

        //CORE VARIABLES
        // The column you want to check if something is entered.
        var COLUMNTOCHECK = 4;
        // Where you want the date time stamp offset from the input location. [row, column]
        var DATETIMELOCATION = [0,1];
        // The column you want to check if something is entered.
        var COLUMNTOCHECK2 = 8;
        // Where you want the date time stamp offset from the input location. [row, column]
        var DATETIMELOCATION2 = [0,1];
        // Sheet you are working on
        var SHEETNAME = “Leads”;

        function onEdit(e) { //Bracket 1
        var ss = SpreadsheetApp.getActiveSpreadsheet();
        var sheet = ss.getActiveSheet();
        //checks that we’re on the correct sheet.
        if( sheet.getSheetName() == SHEETNAME ) { //Bracket 2
        Logger.log(“Leads”);
        var selectedCell = ss.getActiveCell();
        //checks the column to ensure it is on the one we want to cause the date to appear.
        if( selectedCell.getColumn() == COLUMNTOCHECK) { // Bracket 3
        Logger.log(“4”);
        var dateTimeCell = selectedCell.offset(DATETIMELOCATION[0],DATETIMELOCATION[1]);
        dateTimeCell.setValue(new Date());
        }; // Bracket 3

        }; // Bracket 2
        }; // Bracket 1

        1. Hi Christian,

          I had a look at what you are doing again. I think you are pretty much on track. In your first message, I missed the part that you wanted the date time stamp when a formula returns a new value. Unfortunately, onEdit() does not recognize formula result changes is if the original formula stays the same.

          For example A1 = 1, A2 = 3, A3 =if(A1<A3),True,False. If you change the values of A1 or A2 this may change the returned result of A3 but not edit the underlying formula, so onEdit won’t work. What you could point your onEdit too, if possible in your case, is the actual cell value that is changing and then adjust the offset accordingly.

          Hope this helps.

          ~Yagi

  50. Is there a version where the cell that is being “edited” is actually on a different tab to where i want the time stamp to show up?

    Sorry if its already been answered, theres a lot of comments

    Thanks in advance!

    1. I don’t think we have discussed that particular version here. However, I think there is enough here in the comments for you to make a good attempt.

      If you get stuck after a solid attempt, post your code below and we will take a look at it if we get a chance.

      ~Yagi

  51. Hello, this has been an absolute life saver. I’m needing to make the date entered 5 days in the future from today. I’ve had a look through the comments and it doesn’t seem to be covered previously, and I’ve tried a couple of ways eg. (new Date()+5) or (new Date())+5 etc. to no avail.
    Would you be able to point me in the right direction to have it be a future date entered?

  52. Hey,

    Is it possible to amend the script to only add the date if the date cell is empty.

    Essentially I don’t want it to edit dates in the past if column 3 is edited.

    1. Hi Ryan,
      You can if you are editing the cell to empty. You would also need to evaluate the value of the cell.
      There are a couple examples in the comments close to what you might need. See if you can piece it together and if you get stuck post a reply with you code.
      ~Yagi

  53. Hello Yagi,
    Following script is running perfectly. However, it is not working in following incidence:
    If User delete cell value
    If user copy paste values in the cells

    /**
    * Creates a Date Stamp if a column is edited.
    */

    //CORE VARIABLES
    // The column you want to check if something is entered.
    var COLUMNTOCHECK = 10;
    // Where you want the date time stamp offset from the input location. [row, column]
    var DATETIMELOCATION = [0,-9];
    // Sheet you are working on
    var SHEETNAME = ‘Sheet1’

    function onEdit(e) {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getActiveSheet();
    //checks that we’re on the correct sheet.
    if( sheet.getSheetName() == SHEETNAME ) {
    var selectedCell = ss.getActiveCell();
    //checks the column to ensure it is on the one we want to cause the date to appear.
    if( selectedCell.getColumn() == COLUMNTOCHECK) {
    var dateTimeCell = selectedCell.offset(DATETIMELOCATION[0],DATETIMELOCATION[1]);
    dateTimeCell.setValue(new Date());
    }
    }
    }

  54. Hi Yagi,
    Yes when user deletes the value in column number 10, Date remains there. It need to be disappear if column 10 is empty

    Many of times user copy earlier values in column 10, Date is not auto updated.

  55. Hi,
    This is how it looks
    1 2 3 4 5 6 7 8 9 10
    Date key1 Sr No Contract No Contract creation date
    Pankaj
    7/23/2019 Pankaj
    7/23/2019 Arpit
    7/23/2019 Ravi
    7/23/2019 Ravi
    7/23/2019 Ravi
    Ravi
    Ravi
    Ravi
    Ravi
    Ravi
    Ravi
    Ravi
    Ravi
    7/24/2019 Pankaj
    7/24/2019

    1. Hi Pankaj,

      The script does update the date if a value has been pasted into the cell or if the cell is deleted. However, only on a cell-by-cell case.

      From what I understand of your issues you might:
      1. Have users delete or copy and paste the whole ranges. For example, the user deleted or pasted data into J3:J10 (col 10) and only J3 Date and time was updated.
      2. You want the date-time-stamp to disappear when the user deletes a cell.

      The tutorial code is not designed for either of these conditions.

      However, I encourage you to investigate the event object parameter ( that’s the ‘e’ in onEdit(e)). The event object has an associated method called range. You could call this range and iterate through the row values applying the date time stamp to their corresponding offset. This would take care of users pasting or deleting in ranges instead of cells.

      To delete the date-time-stamp if there is no data in your cell in COLUMNTOCHECK, you would need to add an extra ‘if’ condition stating that if the active cell is empty return “” (an empty cell).

      There should be enough there for you to experiment with.

      If you get stuck during your process and you have made a solid attempt, feel free post your code and if I have time, I will take a look and guide you to the next step.

      ~Yagi

  56. Hello Yagi. Thanks for this solution. I was looking for this for a long time. Finally I used the correct query (“add date when cell changes google sheets”).

    It happens that I already have another Code.gs script running. When I did File > New Script and pasted your code, it gave me error like myFunction() is missing. Then I inserted your code in the curly braces and it said Edit function missing or something like that.

    What worked for me was, I duplicated the existing Code.gs file, renamed it to DateTimeStamp, cleared the code, and pasted your code, saved it (floppy disk icon) and ran it (play icon). And BOOM it worked.

    So it could be helpful, if you edited your post with instructions on how to use the code if there was already some other script running for the same sheet.

    But thanks once again. Yeah you should definitely get Patreon account or something to receive donations.

    1. Hi Kiran,

      Thanks for the kind words.

      I’m really happy you found a way to incorporate the code into your existing project. It was my intent that these tutorials be a launching point for people to get comfortable enough to play around with Google Apps Script, modifying my tutorials to suit their own projects.

      In my most recent tutorial, I have an example on how to separate your code into it’s own function and link to it: Google Apps Script: Create a Select All Checkbox to Tick Checkboxes in Any Desired Range in Google Sheets

      It’s often challenging to traverse that fine line between building a tutorial that will stimulate the reader to explore more and just free code.

      I really should get a Patreon together. All I have at the moment is XRPtipbot.

      Thanks again for the kind words and more importantly, sharing your discovery. That was the most satisfying part for sure.

      Regards,

      Yagi

  57. Hi Yagi,

    Is this thread still alive?

    What I want is if you change the value or data on columns B:E Timestamp will update on column A only. Is it possible for this code?

    Here’s my current code

    [q]
    /**
    * Creates a Date Stamp if a column is edited.
    */

    //CORE VARIABLES
    // The column you want to check if something is entered.
    var COLUMNTOCHECK = [1,2,3,4];
    // Where you want the date time stamp offset from the input location. [row, column]
    var DATETIMELOCATION = [0,-1];
    // Sheet you are working on
    var SHEETNAME = ‘input’

    function onEdit(e) {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getActiveSheet();
    //checks that we’re on the correct sheet.
    if( sheet.getSheetName() == SHEETNAME ) {
    var selectedCell = ss.getActiveCell();
    //checks the column to ensure it is on the one we want to cause the date to appear.
    if( selectedCell.getColumn() == COLUMNTOCHECK[1] || selectedCell.getColumn() == COLUMNTOCHECK[2] || selectedCell.getColumn() == COLUMNTOCHECK[3]) {
    var dateTimeCell = selectedCell.offset(DATETIMELOCATION[0],DATETIMELOCATION[1]);
    dateTimeCell.setValue(new Date());
    }
    }
    }
    [/q]

    Ex:

    Col A : Last Modified (DATETIMELOCATION)
    Col B: Input1 (COLUMNTOCHECK)
    Col C: Input2 (COLUMNTOCHECK)
    Col D: Input3 (COLUMNTOCHECK)
    Col E: Input4 (COLUMNTOCHECK)

    1. Hi Jade,

      Your COLUMNTOCHECK array starts from zero. So in your COLUMNTOCHECKARRAY = [1,2,3,4] or column “A,B,C,D”, COLUMNTOCHECK[0] would equal column 1.

      In your example, you probably don’t want to use an offset because that will only move the allocated positions horizontally and vertically. You might be better off trying selectedCell.getRow() then getRange() based on column A (or 1) and the selected row and then setValue().

      Give that a try and if you get stuck. Repost your code and I will have a look if I get a chance.

      Happy coding,

      ~Yagi.

  58. Hi Yagi,
    Sorry to be a bother but I have followed your tute and it works fabulously,
    My only question I have is how to make the time stamp that’s generated appear below the cell that the data is being entered/changed in instead of to the right or left.

    Thank you for your great help!,

    Kind regards,
    Jacob

  59. Hi Yagi,
    Sorry to be a bother but I have followed your tute and it works fabulously,
    My only question I have is how to make the time stamp that’s generated appear below the cell that the data is being entered/changed in instead of to the right or left.

    Thank you for your great help!,

    Kind regards,
    Jacob

    1. Hi Jacob,
      The DATETIMELOCATION array has an offset. The first part is vertical and the second is horizontal. So try changing it to [1,0].
      Cheers,
      ~Yagi

  60. Hey Yagi,
    Thank you so so much! Wow! This was by far the clearest, most helpful, and best explanation I have seen after doing days of research. That, plus your attentiveness to comments helped me to troubleshoot 2 big blockers I experienced, similar to many users above. Maybe this will be useful to other users so I will share….

    *Needed multiple different columns to trigger a timestamp
    *Needed timestamp to trigger only when a specific text was chosen
    1. Enter value into Column B = timestamp in column AD
    2. Enter value into Column Y = timestamp in column AE
    3. Specific text “Approved” (chosen from a data validated drop down list of text items “pending,Approved”)

    //CORE VARIABLES
    // The column you want to check if something is entered.
    var COLUMNTOCHECK = [2];
    // Where you want the date time stamp offset from the input location. [row, column]
    var DATETIMELOCATION = [0,28];
    // Sheet you are working on
    var SHEETNAME = ‘Tasks’

    var PHOTODETAILS = 25
    var PHOTODATETIMELOCATION = [0,6]
    //When AM wants to coordinate Photos

    var AGENTSTATUS = 29
    var AGENTSTATUSDATETIMELOCATION = [0,3]
    var VALUETOCHECK = “Approved”;
    //When C&L team selects “approved” transaction from drop down menu

    function onEdit(e) {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getActiveSheet();
    //checks that we’re on the correct sheet.
    if( sheet.getSheetName() == SHEETNAME ) {
    var selectedCell = ss.getActiveCell();
    //checks the column to ensure it is on the one we want to cause the date to appear.
    if( selectedCell.getColumn() == COLUMNTOCHECK) {
    var dateTimeCell = selectedCell.offset(DATETIMELOCATION[0],DATETIMELOCATION[1]);
    dateTimeCell.setValue(new Date())
    }
    }

    if( sheet.getSheetName() == SHEETNAME ) {
    var selectedCell = ss.getActiveCell();
    //checks the column to ensure it is on the one we want to cause the date to appear.
    if( selectedCell.getColumn() == PHOTODETAILS) {
    var dateTimeCell = selectedCell.offset(PHOTODATETIMELOCATION[0],PHOTODATETIMELOCATION[1]);
    dateTimeCell.setValue(new Date())
    }
    }

    if( sheet.getSheetName() == SHEETNAME ) {
    var selectedCell = ss.getActiveCell();
    //checks the column to ensure it is on the one we want to cause the date to appear.
    if( selectedCell.getColumn() == AGENTSTATUS) {
    if(selectedCell.getValue() == VALUETOCHECK) {
    var dateTimeCell = selectedCell.offset(AGENTSTATUSDATETIMELOCATION[0],AGENTSTATUSDATETIMELOCATION[1]);
    dateTimeCell.setValue(new Date())
    }
    }
    }
    }

    If you have any feedback on the above would love to hear it. And thanks again so much!
    Can’t wait to go through some of your other resources and see what I can learn!

    1. Hi Christine,

      Great work figuring this all out and contributing to the thread so proactively.

      First of all, the main thing is that you got it up and running.

      Feedback-wise, we try and remove unnecessary repetition when programming to speed up the code. From your code, I can see three instances of the same ‘if’ statement:
      if( sheet.getSheetName() == SHEETNAME ) {
      var selectedCell = ss.getActiveCell();
      //checks the column to ensure it is on the one we want to cause the date to appear.
      your column check, cell value check, offset and setvalue
      };

      You can add all three column checks inside this ‘if’ statement to save you some extra code.

      Cheers,
      Yagi

  61. Hi Yogi,

    Great Post!!
    I was wondering how to change the format of the date?
    When the script runs, the format is DD/MM/YYYY HH:MM:SS.
    If I change the format manually, it reverts back as soon as the script runs again.

    How do I change the format so it’s just the DD/MM/YY that gets returned?

    I have tried some alternatives but cannot get the script to work.

    Thanks 🙂

    1. Hi Matt,

      Thanks for the kind words.

      There are two options you could use. One, inside the sheet, you could select the column then Format > Number > Then chose one of the date formatting options or create a custom one of your own. This is generally what I use when I run the code.

      Alternatively, in the code, you could format the date by using the getDate() getMonth() getYear() methods of the Date().

      ~Yagi

  62. Many thanks for your time and effect with this script, fairly new at this but I was able to take this and make it work across a number of coloums, again thank and keep up the awesome work

  63. Hi Yagi
    So i tried going through all the replies but still there doesn’t seem to fix one issue I’m facing.

    I used another system to create data and send the data to update on my google sheet. As a result the onEdit function doesn’t work. I tried the “trigger”function in the G-suite but nothing happens, and I’m trying to work around by the logic “when this cell isn’t empty” instead (initially it is empty, then the system adds the data into the sheet). But was trying to find the coding language to make this work.

    I was trying:

    var COLUMNTOCHECK = 1;
    // Where you want the date time stamp offset from the input location. [row, column]
    var DATETIMELOCATION = [0,13];
    // Sheet you are working on
    var SHEETNAME = ‘Sheet1’
    // value to check is empty
    var VALUETOCHECK = “”

    function onEdit(e) {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getActiveSheet();
    //checks that we’re on the correct sheet.
    if( sheet.getSheetName() == SHEETNAME ) {
    var selectedCell = ss.getActiveCell();
    //checks the column to ensure it is on the one we want to cause the date to appear.
    if( selectedCell.getColumn() == COLUMNTOCHECK) {
    if( selectedCell.getValue() !=””){
    var dateTimeCell = selectedCell.offset(DATETIMELOCATION[0],DATETIMELOCATION[1]);
    dateTimeCell.setValue(new Date());
    }
    }
    }
    }

    How can I modify the trigger you provided to make this work? Thanks so much for all the supports!

    1. Hi Maruay,

      Yes, you are correct. onEdit does not with data that has been pushed to the sheet programmatically.

      You have a number of options worth trying:
      1. You could try and change the trigger function onEdit(e) to onOpen(e) and then update the date for anything that has changed since the last time you opened it. This is not ideal. Plus you would have to store the last line in a PropertiesService to update each time. The problem here is that the date won’t be accurate.
      2. You could use a time trigger to run every 5 minutes or so to update and recognise a change then add in the Date-Time-Stamp
      3. If you are running your own code or have access to the codebase for editing before the data reaches the sheet, You could “unshift” the date into the front of each row of your data before setting into the Sheet.

      Cheers,

      Yagi

  64. Hi Yagi,

    I am trying to get the code to work for multiple sheets in the same file. The sheets are identical, just get filled out for different conditions. How should I go about getting the code to work on all the sheets?

    I was able to get it working on only one sheet, (the one I defined in var SHEETNAME = )

    Any help would be appreciated and thank you for getting me this far already!

    Best,
    Lauren

    1. Hi Lauren,
      If all usages cases across all sheets are the same, then you can remove the SHEETNAME variable along with it’s associated if statement, then it is work across all sheets.
      I believe there is an example in the comments.
      Give it an attempt and if you get stuck, I can guide you in the right direction, time permitting.

  65. Hi Yagi,

    Looks like my comment went thru twice. Sorry about that. I tried deleting all variables lines associated with sheet name. It did not do anything. Adding the date is not functional. I have added my code below. Any feedback would be great! Thanks.

    Lauren

    // The column you want to check if something is entered.
    var COLUMNTOCHECK = 1;
    // Where you want the date time stamp offset from the input location. [row, column]
    var DATETIMELOCATION = [0,1];
    // Sheet you are working on

    function onEdit(e) {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getActiveSheet();
    if( selectedCell.getColumn() == COLUMNTOCHECK) {
    var dateTimeCell = selectedCell.offset(DATETIMELOCATION[0],DATETIMELOCATION[1]);
    dateTimeCell.setValue(new Date());
    }
    }

    1. Hi Lauren,

      Almost there!

      Have a look at the variable, selectedCell. This is one that we created (not one of the built-in ones provided by G.A.S.. However, right now selectedCell does not reference anything so we can’t yet use it in our ‘if’ statement. However if we set the variable selectedCell to equal the active sheet and then get the active cell of that sheet, then the if statement should work. Hopefully, there are enough hints in there for you.

      ~Yagi

  66. Hi Yagi, very useful it worked for me the first time I tried it. But I want to add not just the date, but the date + time which is (ctrl+alt+shift+:) if done manually. what do I need to change in the code to do this?

    1. Hi Tony,

      I’m glad you got it up and running.

      It is probably easiest to simply apply the change to the formatting in the column in the sheet.

      In the menu, select, Format > Number > Time

      ~Yagi

    2. Hi Yagi, thanks mate it worked. However I realized I have a new problem… I am using an integration where the Google sheets fields are autopopulated after they signed up from a web form. So the leads are coming in the sheet, but the time stamp script didn’t work, although timestamping really works when I type on the field manually, if its autopopulated, it didn’t, I think the script was never made for something like that? Is it still possible to make it work? appreciate the help Yag!

      1. Hi Tony. Unfortunately the onEdit trigger function doesn’t work for this. 1 solution, if possible, is to add the time stamp in the JS of the form. Otherwise you could look at time triggers, thought this will only be as close as 5 minutes.

  67. I try to do it for 2 columns for the 2nd one overwrite the first code, I even changed the names so it isnt the same but still only 1 out of 2 works. Could someone help me?

      1. Here is my code

        //CORE VARIABLES
        // The column you want to check if something is entered.
        var COLUMNTOCHECK = 2;
        // Where you want the date time stamp offset from the input location. [row, column]
        var DATETIMELOCATION = [0,-1];
        // The column you want to check if something is entered.
        var COLUMNTOCHECK2 = 8;
        // Where you want the date time stamp offset from the input location. [row, column]
        var DATETIMELOCATION2 = [0,1];
        // Value condition.
        var VALUETOCHECK = “Completed”;
        // Sheet you are working on
        var SHEETNAME = “Mythril”;

        function onEdit(e) {
        var ss = SpreadsheetApp.getActiveSpreadsheet();
        var sheet = ss.getActiveSheet();
        if( sheet.getSheetName() == SHEETNAME ) {
        Logger.log(“Sheet Name”);
        var selectedCell = ss.getActiveCell();
        if( selectedCell.getColumn() == COLUMNTOCHECK) {
        Logger.log(“Column 1”);
        var dateTimeCell = selectedCell.offset(DATETIMELOCATION[0],DATETIMELOCATION[1]);
        dateTimeCell.setValue(new Date());
        };

        };
        };

        1. Hi Mat,

          So you are looking for when the user changes the cell to “Completed” in columns 2 (B) and 8 (H). Your offset for your first column is [0,-1], which would display the date in column 1 (A). Your second offset is [0,1], which will display the date in column 9 (I). Just keep in mind that offset[row offset, column offset] and you should be golden :).

          Your code seems to be running well.

          //CORE VARIABLES
          // The column you want to check if something is entered.
          var COLUMNTOCHECK = 2;
          // Where you want the date time stamp offset from the input location. [row, column]
          var DATETIMELOCATION = [0,-1];
          // The column you want to check if something is entered.
          var COLUMNTOCHECK2 = 8;
          // Where you want the date time stamp offset from the input location. [row, column]
          var DATETIMELOCATION2 = [0,1];
          // Value condition.
          var VALUETOCHECK = "Completed";
          // Sheet you are working on
          var SHEETNAME = "Mythril";

          function onEdit(e) {
          var ss = SpreadsheetApp.getActiveSpreadsheet();
          var sheet = ss.getActiveSheet();
          if( sheet.getSheetName() == SHEETNAME ) {
          Logger.log("Sheet Name");
          var selectedCell = ss.getActiveCell();
          if( selectedCell.getColumn() == COLUMNTOCHECK) {
          Logger.log("Column 1");
          var dateTimeCell = selectedCell.offset(DATETIMELOCATION[0],DATETIMELOCATION[1]);
          dateTimeCell.setValue(new Date());
          };

          if( selectedCell.getColumn() == COLUMNTOCHECK2) {
          Logger.log("Column 2");
          if(selectedCell.getValue() == VALUETOCHECK){
          var dateTimeCell = selectedCell.offset(DATETIMELOCATION2[0],DATETIMELOCATION2[1]);
          dateTimeCell.setValue(new Date());
          };
          };


          };
          };

Leave a Reply