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

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

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

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

1. The Simple, But Not So Useful Approach

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


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


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

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

2. The Harder, But Exceedingly More Useful Approach

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

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

Bare Bones Basics for the Non-Coder

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

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

Date Time Stamp - Google Apps Script with Google Sheets

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


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

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

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

My Blood Pressure Sheet


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


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

var DATETIMELOCATION = [row number, column number];

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

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

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

Offset - Google Apps Script

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



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

Sheet name


Go ahead and change SHEETNAME to “BP”:



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

Check out the example below:

Current Date Time Stamp Google Sheets


Modifying the Date Type

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

Changing The Date Type in Google Sheets



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

Leave a Reply

Your email address will not be published. Required fields are marked *