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

add date and time to a google sheets adjacent cell on edit apps script

Google Sheets, Google Apps Script: onEdit

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" + ";".

The “+” just means that you add the following button.

Time

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

Date and Time

Finally CTRL + SHIFT + ALT + ; will add both a date and time (Commonly known as a timestamp or shortened to DTS).

For the Mac users out there simply replace ‘CTRL’ with your command key (⌘).

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 Extensions> menu and then  > Apps Script.
(Note Extensions previously called Add-ons)

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

Hire me for our next Google Workspace project.

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.

Update project name Google Apps Script

 

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 under the ‘GLOBALS’ section and are documented fairly clearly.

SHEET_TAB_NAME

We want to tell Google Apps Script what sheet tab we are working on. Right now in our script, the SHEET_TAB_NAME 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.

?You can support me for free by using this Amazon affiliate link in your next tech purchase :Computers & Stuff! ?

Sheet name

 

Go ahead and change SHEETNAME to “BP”:

const SHEETNAME = "BP"

COLUMN_TO_CHECK

This is the column where you will add data into a cell 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 into so we will need to change this in the code by changing the value of the COLUMN_TO_CHECK.

DATE_TIME_COL_OFFSET

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 DATE_TIME_COL_OFFSET variable.

const DATE_TIME_COL_OFFSET = -1;

This is the number of columns to the right or left of the COLUMN_TO_CHECK column. For columns to the right use positive integers and for columns to the left, use negative integers.

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.

This means that our offset is one position to the right. So our DATE_TIME_COL_OFFSET value will be -1.

Offset - Google Apps Script

 

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

More example on use onEdit(e)

If you have found the tutorial helpful, why not shout me a coffee ☕? I'd really appreciate it.

The Video

Alternate Modifications to your Google Sheets Script

In this next section, we’ll cover some alternate modifications to the date time picker to give you an idea of how to make minor modifications to the code for your own projects.

Don’t Add the Date and Time when Certain Values are Added to the Target Cell

Sometimes there might be conditions where you only need to update the date-time stamp in your adjacent cell when the target cell doesn’t meet a certain value parameter.

Here are three examples:

  1. Add the date when the user checks a check box. Here we don’t want to add the date-time stamp when the user unchecks the box. This means any time the value false appear the script should do nothing.
    This is useful when recording task completion.
  2. Ignore the date time cell when the target cell value is deleted. Here whenever the script sees "" no date should be added.
    This is handy to indicate whenever a new value is added but we don’t want a record of when that value is deleted, giving us a false indication of what is going on. It is also handy when you only want to add the date the very first time the cell was updated and no consecutive times.
  3. Ignore certain values. Here we have a list or array of values to ignore and we want to add our date and time stamp when those values have not been inputted.

To update the script we will need to add a new global variable IGNORE_VALS. So our 3 examples above might look like this:

  1. const IGNORE_VALS = [false];
  2. const IGNORE_VALS = [""];
  3. const IGNORE_VALS = ["blue", "red", "pink"];

This time around we will need to also grab the current date time range, then update that range with the new values if it meets the criteria.

Only add a date-time stamp to Google Sheets for selected values

Conversely, you may wish to only add a date and time stamp when certain parameters are met.

To do this change the const IGNORE_VALS variable in the Globals section to const ONLY_VALS. Add in the values.

Then update the map function (Line 34) down the bottom in dateVals to the following:

.map((val, idx) => (ONLY_VALS.includes(columnVals[idx][0])? [dateTimeStamp]:val :))

Clear date-time stamp when the ignore value is met

In this tweak of the scenario if the INGORE_VALUE has been added, then instead of adding a date-time stamp, we deleted the existing date-time stamp from the cell if there is one.

This could come in handy if you are constantly updating and clearing out target cells. You probably don’t want a date time stamp on an empty cell so it would be handy to remove it.

To do this, it is a quick modification of the map function for the dateVals variable again. You can use the same global variable setup as before. However, we will provide the option to clear out the cell with the CLEAR_ON_IGNORE_VALS global variable. Setting this value to true will clear the cell if the value is to be ignored.

Here, any time an IGNORE_VALS is added to the target cell the date time cell value is deleted.

Inserting the automatic date-time stamp updater into multiple sheet tabs.

In this scenario, we want to add our date-time stamp auto-updater to multiple Google Sheets tabs based on multiple conditions.

We can use an array of objects to set up all of our conditions and then use a JavaScript forEach() loop to iterate through each condition.

The Global array is going to look a little like this:

Let’s add the script below. I’ve also added an example locations variable set to the globals so you can better see how it works.

 

Can you do the same for multiple Google Sheets? We’ll not really. You will need to add copy and paste the code to another Google Sheet or create a template Google Sheet that you can use.

Add a Date Time Stamp to a Record Cell when a Cell in a Range is Edited in Google Sheets

In this common scenario, you might just want to provide a date-time stamp indicator in one single cell to indicate that someone has recently made an edit to the range.

You can always check this out by right-clicking the cell and selecting ‘Show edit history’, hovering over the ‘Edit history’ clock icon in the top right of the Google Sheet or get granular by clicking on the ‘Edit history’ icon. But sometimes it’s just best to be explicit.

Let’s roll back to our original code and make some modifications.

We’ll probably put our last updated cell at the top of the same sheet tab, but we could put it on a summary Google Sheets tab or anywhere else.

Have a look at the example:

We’ve added two new variables to the Globals here and  removed DATE_TIME_COL_OFFSET:

  1. STARTING_ROW – Number. The starting row where we want to record the changes. This prevents us from updating the ‘Last Edited’ cell if we made any changes above this row.
  2. DATE_TIME_STAMP_CELL – String. This contains the Sheet tab name inside single quotation marks followed by an Exclamation mark and then the cell in A1 notation. For example, 'Summary'!A2. This can be any other sheet tab.

Let’s take a look at the script:

Log Date Time, Editor and Cell

We could go a little crazy here and quickly include the editor’s email and the range that they edited.

Let’s swap out DATE_TIME_STAMP_CELL and replace it with SUMMARY_RANGE in our global variables. Here we will include the full range of the three cells that we want to include.

Take a look at the example results:

Add date email an cell reference to Google Sheets Cell on change of range Apps Script

Here is the script:

Lines 32-36: Note the editValues array that we added. We now put our three data points in a 2d array (That’s 2 square brackets). The date is added as usual, then we add the user’s email that we can extract from e.user, and get the range in A1 notation from the range.

Lines 39-41: Finally, we update the SUMMARY_RANGE ensuring that we change getValue to getValues (Note the extra ‘s’).

Create an Edit Log in Google Sheets

You just might want to do things your own way and not worry about the Google Sheets Edit history tools. That’s all Good.

We can create an edit log for the example above by using the appendRow() method on a separate Google Sheet Tab.

Check out our example now:

Add date email an cell reference to Google Sheets Log Sheet on change of range Apps Script

If another user makes a change then their email would be added to the log.

Change out the SUMMARY_ROW to SUMMARY_SHEET_TAB_NAME in the global variable section. This will contain the summary sheet name, in our case, ‘Summary’.

Lines 32-36: Here, we have removed the outer array. We only need a single array to append a row.

Lines 39-41: Next, we update the sheet name to our new variable and then apply the append row that takes the editValues array.

Tidying Up Your Google Apps Script Code for Larger Projects

On larger projects, you might want to use the Google Apps Script onEdit(e) trigger to carry out other tasks. In this case, it might make things easier to manage if you abstract out the date-time stamp updater into a separate function and then call this function from onEdit.

See how this looks in the script below:

Troubleshooting

Error: Cannot read properties of undefined (reading ‘range’)

This likely occurs if you are trying to run the script from the IDE (Script Editor) rather than on the Google Sheet as you edit it.

To check to see if the code is working, you can:

a. Make an edit in your target range.

Create and Publish Google Workspace Add-ons with Apps Script Course 300px

b. User a test script for example:

 

Conclusion

That should be enough examples for you to modify the code for your own project.

I always love to hear how you apply these tutorials to your own projects, so feel free to share what you did below. It just might provide inspiration for someone else.

Create and Publish a Google Workspace Add-on with Apps Script Course

Need help with Google Workspace development?

My team of experts can help you with all of your needs, from custom app development to integrations and security. We have a proven track record of success in helping businesses of all sizes get the most out of Google Workspace.

Schedule a free consultation today to discuss your needs and get started or learn more about our services here.


If you are looking for more onEdit() simple trigger tutorials check these out:

Changelog

  • 2024-01-05
    • Added a Troubleshooting Chapter for common user issues.
  • 2023-07-08
    • Major update to the original tutorial.
    • Inclusion of a large number of variations that have come up in the comments section over the years.
    • Basic date time stamp inserter video added.

 

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

  1. You are amazing, the only place I found it explained so clearly, THANKS HEAPS

    1. Hi , i am totally new to this but tried copy paste as u said , however it didnot work , instead it said that after row 24 code missing !
      will appreciate any guidance
      thanks
      cc

      1. Hi CC,

        It’s difficult to see where your issue is without looking at your code. Was it a direct copy and paste? I take it you changed the global variables at the top.

        Cheers,

        Yagi

  2. 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 there! Can you please share a code specifically for this.

        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.

  3. 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());
      }
      }
      }
      }

      1. Thanks a lot for the quick reply! You’re a life saver.

      2. Will this work if the cell you’re checking the condition of has a drop-down box and you only want it to post a timestamp for 1 of 3 choices?

        1. Hi LeAnna,

          Yes a data validation drop-down menu change is an onEdit() event. You will need to include another if statement that checks that the dropdown cell contains the value you want. I’ve provided some examples in the comments above for reference.

          ~Yagi

  4. 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.

      2. How can you get the same time stamp to dissapear if a box is unticked as ‘false’ ?

      3. Thank you very much , I was also having same question and solved my issue

  5. 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!

  6. Hi All, what if I want the script to check the entire row ? Or multiple cells in the same row?

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

  8. 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?

  9. 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.

  10. 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());
        }
        }
        }

  11. Thank you very much for this!!!
    How can I buy you a cup of coffee?

    1. Hi Raul,

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

  12. 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.

  13. 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.

  14. 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!

  15. 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.

      2. Hi Yagi,

        Awesome Tutorial.

        I have tried this, but cant seem to get it to work.

        Any advice is much appreciated.

        Thank you.

        function addTimeStamp(e) {
        const sh = e.range.getSheet();
        if(e.range.getSheet() == ‘Form Responses 1’) {//this will differentiate between different form submissions to the same spreadsheet
        const dt = new Date();
        sh.getRange(e.range.rowStart, 7).setValue(dt);
        if (sh().getRange(e.range.rowStart, 6).getValue() == “”) {
        sh().getRange(e.range.rowStart, 6).setValue(dt);
        }
        }
        }

        1. Hi MK,

          How are you planning on triggering this function? On a form submit?

          ~Yagi

  16. Awesome !! Super clear and detailed, thanks for the work dude

    1. No problem, I’m glad you found it useful.

  17. 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!

  18. 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.

    2. Did you ever figure out how to accomplish this? I’m looking for the exact same solution. Thanks!

  19. 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!

    1. Sandy, how did you get this to work on a second column?

  20. Great! I was wondering ¿is there a template that automatically uses this function?

    1. Hi Carlos,

      Unfortunately google apps script does not let you enable this as a ‘formula.

  21. 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?

  22. 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.

  23. 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

  24. 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!

    1. Perhaps adding an “if” statement based on the condition that the offset cell is not empty.

  25. 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 ).

  26. 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

  27. 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?

  28. 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.

  29. 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.

  30. 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” (||).

  31. 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!

  32. 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());
            }
            }
            }

  33. 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.

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

  35. 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

  36. 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?

  37. 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?

  38. 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.

    1. Hi Cameron,

      Thank you for the kind words. It’s always nice to get feedback like this.

      ~Yagi

  39. 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

    1. That’s really kind of you. Thanks, Nazza.

  40. 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.

  41. 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

  42. 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

      1. Yagi

        This works perfectly!

        Thank you.

  43. 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());
        }
        }
        }

  44. 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));
      };

  45. Great, thanks a lot Yagi !
    Cheers !
    Alex.

  46. 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

  47. 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.

  48. 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. You certainly are! Great work, mate!

            Happy coding.

            ~Yagi

          3. 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

          4. 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

          5. 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!

          6. 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

  49. 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.

  50. 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.

  51. 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 Rena,

      Try changing your VALUETOCHECK to ="RELEASED". Note the quotation marks.

      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

  52. Hi Yagi,

    Thank you very much for all your help. Yagi = Goat

    Best,

    John

  53. Hello Yagi
    how do i change date from AD to helical?

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

  55. 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

  56. 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

  57. 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

  58. 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

  59. 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?

  60. 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

  61. 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());
    }
    }
    }

    1. Hi Pankaj,
      Do you mean when the user deletes or pastes in a range not just a cell?

  62. 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.

  63. 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

      1. Pankaj, did you have an attempt at the solution proposed by Yagi? If yes, please let me know.

      2. Hi Yagi,

        How about to delete the date if there is no data in your cell and not equal to your VALUETOCHECK? This is my first time to use script editor. Apologies on this.

        Thank you.

      3. Hi Yagi,

        How to return “” if there is no data in your cell in COLUMNTOCHECK and not equal to VALUETOCHECK?

        *I’m not sure why my replies are not showing up here

        Thank you.

  64. 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

  65. 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.

  66. 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

  67. 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

  68. 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

  69. 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

  70. Thank you so much, you have just helped one more non-coder solved her problem ^.^

    1. Thank you. That’s very kind of you to say.

  71. 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

  72. 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

  73. 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.

  74. 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

  75. 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.

  76. 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. Hi Mat,

      You’ll need to look at the offset to correct this.

      ~Yagi.

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


          };
          };

  77. Hi Yagi! Thanks for the helpful tutorial, it was fantastic!

    I was able to combine two of the scripts you mentioned in the comments to add timestamps as follows:

    if data in a range changed (so a last modified timestamp)
    if a cell was marked as completed (so a date completed timestamp)
    if data was entered into a cell (so a date created timestamp)

    The last thing I’m hoping you can help with is to figure out how to make timestamps #2 and #3 static. Any help is appreciated!

    Thank you!!
    Jenn

    /**
    * 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,11];
    // The column you want to check if something is entered.
    var COLUMNTOCHECK2 = 6;
    // Where you want the date time stamp offset from the input location. [row, column]
    var DATETIMELOCATION2 = [0,8];
    // Value condition.
    var VALUETOCHECK = “Completed”;
    // Sheet you are working on
    var SHEETNAME = “Summary”;

    var COL_RANGE = [1,13]
    var DATE_COL = 16;
    // Sheet you are working on
    var SHEETNAME = ‘Summary’;

     
    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(“Sheet Name”);
        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 1”);
          var dateTimeCell = selectedCell.offset(DATETIMELOCATION[0],DATETIMELOCATION[1]);
          dateTimeCell.setValue(new Date());
        }; // Bracket 3
       
        //checks the column to ensure it is on the one we want to cause the date to appear.
        if( selectedCell.getColumn() == COLUMNTOCHECK2) { // Bracket 4
          Logger.log(“Column 2”);
          if(selectedCell.getValue() == VALUETOCHECK){ // Bracket 5
            var dateTimeCell = selectedCell.offset(DATETIMELOCATION2[0],DATETIMELOCATION2[1]);
              dateTimeCell.setValue(new Date());
          };  // Bracket 5
        };  // Bracket 4
       
        var selectedCell = ss.getActiveCell();
        //checks the column to ensure it is on the one we want to cause the date to appear.
        for(i = COL_RANGE[0]; i < COL_RANGE[1]; i++){
     
          if( selectedCell.getColumn() == i) {
     
            sheet.getRange(selectedCell.getRow(),DATE_COL,1)
                                       .setValue(new Date());
          break;
        };  // Bracket 2
    };  // Bracket 1
    };
    }; 

  78. So, if I understand correctly (and in my experience so far), it’s no problem to just do the quick keyboard shortcut when the cell is empty, but if I want to add today’s static date in the middle of some text that’s already there, I need to run a script? In Excel, the keyboard shortcut worked in both cases and was extremely handy.

    1. Hi Glen,

      Yes, the keyboard shortcut should work in the same manner in Excel as Google Sheets.

      The script is used when someone wants to automatically add a static date when another action occurs. For example, if I add new data to a cell in column B, then we could use the code to automatically update the corresponding cell in, say, column A. Alternatively, you could change the value of a cell and then the corresponding date-time-stamp cell would automatically update.

      Hopefully, this clears things up for you.

  79. Thanks for the clarification. The problem I’m experiencing is that when my cursor is in text that’s already in the cell, typing the keyboard shortcut does nothing at all, today’s date does not get inserted as it would in Excel. That is what I am trying to fix.

    For example, I check on proofs to non-responsive clients and record the dates I do that in one cell. In Excel I could go into that cell, put the cursor after the last date in the series, type a comma and then the keyboard shortcut to add today’s date. So it looks like this: 1/2/20, 1/13/20, 1/23/20 checked on Proof1

    This doesn’t work in Sheets except for the very first date when the cell is empty (1/2/20 in the above example), at which point I can hit return and keep typing after the date has been inserted. Am I missing something basic here?
    Thanks very much for your help.

    1. Hi Glen,

      You’re right, that won’t work in Google Sheets. Google Sheets seems to discourage the use of multiple data points in a single cell, like comma-separated-values, as it is often not considered best practice.

      You could, however, emulate this by modifying the Google Apps Script code in this post (That might be an interesting tutorial down the track for me to tackle).

      Alternatively, you could check the history of the cell and its previous dates by right-clicking the cell and selecting Show edit history.

      I hope this helps.

  80. Thanks again- That’s too bad they don’t offer the same simple functionality, it’s quite handy.

  81. Thank you. I used this in checking multiple columns and applying the date to 3 columns over from the column checked. Worked perfectly.

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

    //CORE VARIABLES
    // The column you want to check if something is entered.
    var COLUMNTOCHECK = 1;
    var COLUMNTOCHECK1 = 6;
    var COLUMNTOCHECK2 = 11;
    var COLUMNTOCHECK3 = 16;
    var COLUMNTOCHECK4 = 21;
    // Where you want the date time stamp offset from the input location. [row, column]
    var DATETIMELOCATION = [0,3];
    // Sheet you are working on
    var SHEETNAME = ‘Featured Programs’

    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 || selectedCell.getColumn() == COLUMNTOCHECK1 || selectedCell.getColumn() == COLUMNTOCHECK2 | selectedCell.getColumn() == COLUMNTOCHECK3 || selectedCell.getColumn() == COLUMNTOCHECK4) {
    var dateTimeCell = selectedCell.offset(DATETIMELOCATION[0],DATETIMELOCATION[1]);
    dateTimeCell.setValue(new Date());
    }
    }
    }

  82. First off I want to thank you so much for this post, I applied this script to one of my documents and it has worked flawlessly for well over a year. Today, I was made aware that the autodate is not working for 2 of my experienced users and I am struggling with identifying the cause. I verified that they are utilizing chrome to access the link and I have double checked and even redid the script with no luck. I did notice a message at the top of the screen when I opened the script: “This project is running on our new Apps Script runtime powered by Chrome V8.More infoDismiss” and thought that may be effecting the script somehow…?? Any advice you may have on how to troubleshoot and resolve this issue would be greatly appreciated. Note: I am not sure if this detail is needed but all users access the document through a shared link and do not have log-ins.
    /**
    * 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,7];
    // Sheet you are working on
    var SHEETNAME = ‘FORM’

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

  83. var SHEETNAME = ‘INVENTORY’
    var SHEETNAME = ‘JOURNAL’

    after adding two sheet names here I changed on line 17 as well. but its not working for me on both sheet, it work on journal only.
    //checks that we’re on the correct sheet.
    if( sheet.getSheetName() == SHEETNAME || sheet.getSheetName() == INVENTORY || sheet.getSheetName() == JOURNAL) {
    var selectedCell = ss.getActiveCell();

  84. Hi YAGI,

    Thank you for this, very helpful. Is it possible to get the date yesterday rather than the current date?

    Thanks in advance.

      1. Thank you for the quick revert Yagi. But to set the expectation, this is my first time to use script editor. And I’m having a hard time incorporating the link you have provided to your code. Apologies for this.

        Thank you

      2. Hi Yagi,

        Just to update. I was able to incorporate this in your code. Now, I was trying to return to “”(empty cell) if there is no data in my cell in COLUMNTOCHECK and not equal to VALUETOCHECK by adding the script below but it’s not working. Hope you can help me again. Thank you.

        } else {
        selectedCell.setvalue(“”)
        }

        1. Hi Chris,

          You are close. What you are currently trying to do is set the value on the cell that is being edited. I think you want to edit the offset ( The cell next to the one you are checking). After your else statement, try something like this:
          }else{
          var dateTimeCell = selectedCell.offset(DATETIMELOCATION[0],DATETIMELOCATION[1]);
          dateTimeCell.setValue("");
          }

          1. Hi Yagi,

            Thank you so much, it worked! Very much appreciated. Please continue helping other. 🙂

          2. Great to hear, Chris.

            Happy coding.

  85. Hi Yagi,

    I just discovered your tutorial, and after toying around with it, managed to produce the results I wanted. Thank you!

    I do have a question: Is there a way to get the script to record just the date when the referenced cell was modified/updated. While the Number Format option is a great way to show only the Date data, it doesn’t lump every row that was edited on the same date together as due to the hidden time.

    For reference, this is the modified version I used that did not get me my desired results:
    /**
    * Creates a Date Stamp if a column is edited.
    */

    //CORE VARIABLES
    // The column you want to check if something is entered.
    var COLUMNTOCHECK = 26;
    // Where you want the date time stamp offset from the input location. [row, column]
    var DATE = [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 dateCell = selectedCell.offset(DATE[0],DATE[1]);
    dateCell.setValue(new Date());
    }
    }
    }

    I’ve been forced to learn scripts and coding because of the current global health crisis, so you may need to really break it down for me.

    Appreciate any help I can get from you!

  86. How do I keep the date to show the date that the data was originally entered vs updating every time the data in edited?

  87. Hi Yagi, What a brilliant piece of script. It worked perfectly. Thank you!! Loved the solutions & discussions in comments.

    Here are some variables that I added to the original – user email and user time zone.

    As a newbie, pretty excited that timezone script works. Yay!

    But how to make the user email script to work. Would appreciate some guidance. Here is my script:

    //*/

    //CORE VARIABLES
    // The column you want to check if something is entered.
    var COLUMNTOCHECK = 13;
    // Where you want the date time stamp offset from the input location. [row, column]
    var DATETIMELOCATION = [0,+2];
    // Where you want the user email
    var EMAILLOCATION = [0,+4];
    // Where you want the time zone of editor
    var TIMEZONELOC = [0,+3];
    // Sheet you are working on
    var SHEETNAME = ‘R PR’

    function onEdit(e) {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getActiveSheet();
    var email = Session.getActiveUser().getEmail();
    var timeZone = Session.getScriptTimeZone();
    //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());
    // Log the email address of the person running the script.
    if( selectedCell.getColumn() == COLUMNTOCHECK) {
    var emailCell = selectedCell.offset(EMAILLOCATION[0],EMAILLOCATION[1]);
    emailCell.setValue(email);
    // Log the time zone of the script.
    if( selectedCell.getColumn() == COLUMNTOCHECK) {
    var TimeZoneCell = selectedCell.offset(TIMEZONELOC[0],TIMEZONELOC[1]);
    TimeZoneCell.setValue(timeZone);
    }
    }
    }
    }
    }

    Thanks in advance. And yes, please set up something so that we can buy you coffee. ?

  88. Ive tried this and set the calue to check to be “true” as I want it to stamp when a checkbox is ticked. but it is giving me a syntax error

  89. Thanks so much for such an easy explanation! Is there a way to use this for multiple columns in the same sheet? E.G. I’d like a date automatically added to column 2 when column 1 is edited, and to column 4 when column 3 is edited etc?

    1. Hi sirie,

      I’m glad you found the tutorial easy to understand. Yes, you can, we’ve discussed it a few times in the comments already. Have a read through and see if you can get it to work if you have any issues, feel free to paste your code in the comments and I can guide you in the right direction, time permitting.

      Cheers,

      Yagi

  90. I tried the first option but where I press CTRL Plus and it justs asks me if I want to add a new row or column

    1. Hi Dubai Jiggy,

      Try holding down the ctrl button and then pressing the ; key. The ‘+’ symbol just indicates ‘and’. I’ll update the post to make it clearer.

      Cheers,

      Scott

  91. I just want to say THANK YOU! After reading your article and perusing the comments and replies I was able to successfully get this to work (for MULTIPLE sheets, eeek)! I hope to get better with understanding code but until then, you have been a lifesaver. Thanks again…

    1. You are very welcome. Best of luck on your journey!

      Yagi!

  92. Hey Yagi,
    I was wondering if you could help me.
    I took your code and removed the “if sheet” function to allow for the dates to be used for every sheet in the entire spreadsheet. I also wanted multiple dates (one for sent and one for received).
    Here is the code and nothing is working. I’m not sure if it’s the code or the program. The original code doesn’t work either anymore. Any advice would be really helpful. Thank you!

    /**
    * 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,3];

    function onEdit(e) {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getActiveSheet();
    //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,4];
    // Value condition.
    var VALUETOCHECK = “Received”;

    function onEdit(e) {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getActiveSheet();
    //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 Kaitlyn,

      Good first crack.

      With scripting languages you can’t repeat the same function or variable without it changing. For example, if you decair onEdit(e) to do one thing, and then create another function with the same, onEdit(e) name, it will only follow the last function instructions.

      The onEdit(e) funciton is a Google Apps Script custom function. So what you can do is create two new functions inside it and direct them to the two functions you have for example:

      Give that an update first and see if you can get it running. If you still have issues, feel free to post your updated code in the comments.

      ~Yagi

  93. Hello, My previous comment got deleted. I have been trying to automatically insert the date when and something is input (first section of code) and then another date when something is “received” (second part of code). The first part of the code was working until I added the second part of the code? Is it because the DateTimeLocation is overriding the first one? Thank you in advance for your help and here is my code:

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

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

    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() == COLUMNTOCHECK0) {
    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 COLUMNTOCHECK1 = 5;
    // Where you want the date time stamp offset from the input location. [row, column]
    var DATETIMELOCATION = [0,4];
    // Value condition.
    var VALUETOCHECK = “Received”;

    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() == COLUMNTOCHECK1) {
    if(selectedCell.getValue() == VALUETOCHECK){
    var dateTimeCell = selectedCell.offset(DATETIMELOCATION[0],DATETIMELOCATION[1]);
    dateTimeCell.setValue(new Date());
    };
    };
    };

  94. Hey, this tutorial is awesome and very helpful. I am using this as an indicator when a note was last added and I have notic if there is an error made or a not put on to an incorrect line there is no way re undo or override the date once a comment is made . I wonder is there any way to undo or override the date stamp cell?

    1. Hi Annette,

      Yes, you could set something up like that. Google Apps Script stores cell history in the Event Object that comes with simple triggers like onEdit(e) << notice the “e”. This can be called to find the old value with something like, e.oldValue() . You could set a custom menu item that could revert the value to the old date if you found an error.

      ~Yagi

  95. You may have answered this, but if we want this to apply to multiple tabs (I have tabs broken down by specific weeks), do we need to run a new code for each tab, or can this be entered into the initial script?

    1. Hi Jacob,

      You might find these two answers useful:

      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

      And…

      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

      Give it a try based on these examples and feel free to post your code if you get stuck and I can point you in the right direction.

      ~Yagi

  96. Hi,
    Thanks so much for the detailed explanation! This is extremely useful.
    I am very new to GAS and just wondering how I can edit this to work even when information is copy pasted in COLUMNTOCHECK, and not just manually inputted line by line. Any info would be very appreciated!

    1. Hi Gab,

      You are very welcome.

      If you are pasting data into a single cell, then onEdit() will activate and the date will be displayed in the adjacent cell.

      If you are looking to update each line that a range is pasted then you would need to do a rewrite for this code. Essentially the task would be to:
      1. Notice an edit event has occurred.
      2. Find the range that was copied. Using the event (e) in onEdit() will get this (e.range).
      3. Create an array of dates equal to the height of the pasted data.
      4. Set those dates into your date range.

      Now if you are doing a lot of pasting in quick succession or it is a busy spreadsheet with a lot of collaborators working at the same time, then you may have to lock the sheet with LockService to ensure no overlaps.

      I might do a separate post on this in the near future. I’m getting quite a backlog ?.

      If you think you are up for the challenge, give it a go based on the hints above and if you get stuck, feel free to reply to this comment and I or one of the readers can guide you in the right direction.

      ~Yagi

  97. Hi Yagi.
    A great script that solves most of my problems.
    But, is there any way to make the script take the Date/Time from the device the sheet are used on?
    If I lost the internet connection now the only thing I get is #REFERENCE.

    1. Hi Marcus,
      This may work, but I haven’t tested it and am not sure if it will get the Google Apps Script TZ offset if it is recognising the serverside as the origin.
      const timezoneOffset = new Date().getTimezoneOffset()

      or…

      Within Apps Script the best you could do is to get the timezone of the user based on their primary calendar timezone.
      function getUserTimeZone() {
      var userTimeZone = CalendarApp
      .getDefaultCalendar()
      .getTimeZone();

      console.log(userTimeZone)

      return getUserTimeZone()
      }

      Finally you could create a small Webapp to open and run in the background grabbing the users TZ offset.

      ~Yagi

  98. Hi! It don’t seems to auto-update the timestamp when someone else is editing my google sheet. Is there a workaround to fix this? Thank you

  99. This formula is working well to update the timestamp for a checked box, but at first it was only applying the date when i UNchecked the box. I added in additional lines to check for the Value (in column E) as True, but something about my syntax must be off because now it isn’t working at all.

    //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,1];
    // Sheet you are working on
    var SHEETNAME = ‘Questions/thoughts’
    // Value condition.
    var VALUETOCHECK = ‘True’;

    function onEdit(e) {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getActiveSheet();
    //checks that we’re on the correct sheet.
    if( sheet.getSheetName() == ‘Questions/thoughts’ ) {
    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() == 5) {
    if(selectedCell.getValue() == True){
    var dateTimeCell = selectedCell.offset(DATETIMELOCATION[0],DATETIMELOCATION[1]);
    dateTimeCell.setValue(new Date());
    }
    }
    }
    }

    I had to remove the * lines because the script editor kept giving me errors. Not sure why this isn’t working.

  100. Would there be a variant on the IF statement if I want to (not) display a date if the cell is empty? Otherwise, the script is working wonderfully! Anytime my team adds any value in a cell, it’s displaying a date. Just need it to not display if the cell is empty. Seems like it’s also registering deleting content as an edit.

  101. Hey there, I am actually trying to do this. Would you mind if you share the code with me?

    1. Hi Shin (and Andrea, sorry I missed your post),

      After you selected the column on line 20, you can use another conditional if statement that checked if the selected cell value (selectedCell.getValue()) is not empty (!=) or a string containing no values. If this is the case, then add the date otherwise don’t.

      Give it a try and if you get stuck you can always share your attempt. If I have time, I will try and take a look.

      ~Yagi

  102. Hi, thank you for the tutorial! I’m confused on one point: how do I get it to run on my sheet? I followed your instructions exactly (I think), then went to the sheet I named in the code, entered data in the column I changed the COLUMNTOCHECK to and nothing happened. I tried clicking Run then Deploy but nothing seems to work. It’s not giving my any errors, just doesn’t seem to be linked to the sheet.

    1. Hi,

      I’m not sure what is causing this for you. If you would like to share a sample sheet or paste the code in the comments, I or one of the readers might be able to help out.

      ~ Yagi

  103. Cheers Yagi! Mahoosively helpful.

    I’ve read the above comments on checking more than one column but can’t seem to get it to work. When you have a moment, can you help me to add a date when any number columns are updated? Thanks.

    1. Hi Jay,

      I think this tutorial is due a good update and possibly an accompanying video.

      In the mean time, feel free to share your code in the comments. This will help me and the other readers to better understand where they can guide you.

      cheers,

      Yagi

    2. Updated. You should now find an answer to your question in the tutorial.

  104. Just curious, but do you know of a way to get the “last updated time,” for a Sheet when the data has not been physically typed into the sheet, but rather, has been added via an integration with, say, Zapier, Make et al?

    In my experience the onedit facility doesn’t work when adding a row of data to a sheet via an integration platform. So as I say, interested to know if you are aware of a way of circumventing this issue with onedit?

    Cheers.

    1. Hi Idris,

      The onEdit() simple trigger does not work with scripted inputs from say Apps Script or 3rd party tools like Zapier unfortunately. Sometimes these tools have the option to add a date-time-stamp that might solve your problem.

      Alternatively you could create our own automation with Apps Script doing the same job as Zapier or Make if possible and then include a new Date() field into your input row.

      ~ Yagi

  105. Hello,

    I got this error. I tried to search for a solution but it ends up not adding the date.

    Error
    TypeError: Cannot read properties of undefined (reading ‘range’)
    addDateWhenCellsInColTwoORThreeChanged @ Code.gs:7

    1. Hi Youssef.
      What’s happening on line 7? It seems that the range you are providing might not be right.

      ~Yagi

      1. Hi Yagi,

        I’m having a similar problem. I copy/pasted your script and edited the sheet tab name, column to check and date/time column offset as recommended and removed the notes from the code and it keeps giving me ;

        TypeError: Cannot read properties of undefined (reading ‘range’)
        onEdit @ Code.gs:7

        I’m not an experienced code, so I have no idea what’s going on. Help?

        1. What’s on line 7? Are you trying to run the code from the spreadsheet or the script?

  106. Hi, When I tried to test run this, it said that e was undefined. What should be getting passed in? It seems like it’s not accepting an argument.

    1. Hi MMM,

      Try to run the script by editing the selected cell. The ‘e’ or event parameter is generated by Google when the onEdit() simple trigger even is triggered from a physical edit of the cell. You could use console.log(e) to view this and copy the subsequent object to then run testing from a test function at a later date.

      ~Yagi

  107. This Works when it is Edited but I have previous old data which was already present. Then how to get edit time stamp of the cell.

    1. Unfortunately, the API does not provide the ability to retrieve edit history at this time.

  108. Hello, I copied and pasted your code directly and after updating the sheet tab name, column to check and column offset for my variables, it still didn’t work. I also received an error saying: TypeError: Cannot read properties of undefined (reading ‘range’). It referred to the line of code:

    const range = e.range;

    Do you know why this error occurred when I didn’t make any changes besides the three const values at the top?

    1. Hi Gretchen, it look like you might be trying to run the code from the App Script IDE rather than the Sheet. The e (event) parameter is only generated when it is triggered in the Sheet.

  109. I am receiving the following error:
    TypeError: Cannot read properties of undefined (reading ‘range’)
    onEdit @ Code.gs:8

    When I edit in the spreadsheet at column 3 (Column to check) nothing happens

    1. Hi Devin,

      This is commonly a result of the function being run from the IDE rather than the Sheet. The event (e) parameter cannot be read.

      I have added a Troubleshooting chapter to help out with any issues that come up with peeps in the future.

      ~ Yagi

  110. Hi Yagi, I’m triggering onedit from two separate columns to do two separate things, which is working.
    Now in my second myfunction I need it to only trigger if the COLUMN2_TO_CHECK has a specific value (VALUECHECK = “Total”). When I add an if statement to myfunction2 to accomplish that, it stops working. Can you tell what I’m doing wrong?

    const SHEET_TAB_NAME = “Expenses”;
    const COLUMN_TO_CHECK = 2;
    const DATE_TIME_COL_OFFSET = -1;
    const COLUMN2_TO_CHECK = 6;
    const VALUETOCHECK = “Total”
    const TOTAL_COL_OFFSET = 3;
    const TOTAL2_COL_OFFSET = 4;

    function onEdit(e) {
    myFunction1(e);
    myFunction2(e);
    }

    function myFunction1(e) {
    var range = e.range;
    var currentSheet = range.getSheet();
    var dateTimeStamp = new Date();
    var column = range.getColumn();

    range
    .offset(0, DATE_TIME_COL_OFFSET)
    .setValue(dateTimeStamp)
    };

    function myFunction2(e) {
    var range = e.range;
    var currentSheet = range.getSheet();
    var column = range.getColumn();

    if(currentSheet.getName() !== SHEET_TAB_NAME) return;
    if( selectedCell.getColumn() == COLUMN2_TO_CHECK) {
    if(selectedCell.getValue() == VALUETOCHECK){

    range
    .offset(0, TOTAL_COL_OFFSET)
    .setFormulaR1C1(“=SUM(R[-2]C[0]:R[-1]C[0])”);
    range
    .offset(0, TOTAL2_COL_OFFSET)
    .setFormulaR1C1(“=SUM(R[-2]C[0]:R[-1]C[0])”);
    }}
    };

  111. I would like to Add or update a date stamp when any change is made in an entire row. If you have already answered this question, please point me to the coding.

Leave a Reply