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 he 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

 

 

77 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

  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

Leave a Reply