How to Insert "last modified" Timestamp in Google Sheets with Custom Functions

When there are multiple collaborators for a Google Sheets, it becomes important to track when it is updated and by whom. From this perspective, knowing how to insert a timestamp in Google Sheets can be a great asset for managers and team leaders.

In this series, we will be covering two different ways of adding timestamps to Google Sheets, as soon as a change is made in the Google Sheet:

1. Part 1 — This part will show you how to add timestamps in Google Sheets using a very simple custom function.
2. Part 2 — This part will show you how to insert a Timestamps in Google Sheets and the email address of the user making the changes using the onEdit function in Google Apps Script.

So let’s get started on how to insert timestamps in Google Sheets with the help of custom functions.

.

What are Custom Functions in Google Sheets?

We all regularly use the in-built formulas in Google Sheets, like the SUM(), COUNTIF() and many more. What if I told you, you can create your own formulas and run them in your Google Sheet. Yes, that is possible using Google Sheets custom functions. You can define how it works in Google Apps Script, and then use it the same way you use in-built formulas in Google Sheets.

Writing your first Google Sheets Custom Function

While you are in the Google Sheet, let’s open up the Script Editor to write some Google Apps Script. To open the script editor follow these steps:

1. Click on Extensions and open the Script Editor.

2. This brings up the Script Editor as shown below.

We have reached the Script Editor, let’s code.

Before we use custom functions to add the Google Sheet timestamp formula to your Spreadsheet, we are going to take a look at how a custom functions in Google Sheets works. We are going to write a simple Google Apps Script function that takes one parameter from the Google Sheet (number1) and returns the sum of the number1 + 2.

function addTwo(number1){    
    const sum = number1 + 2;
    return sum;
}

You can use this custom function in Google Sheets just like you use any other function in Google Sheets, by calling its name and using cells as parameters.

Here I have passed cell A1 as a parameter in the custom function.

As expected, the addTwo custom function returns 12. As you can see, custom functions work just like normal formulas, and they can be very handy.

How to timestamp on Google Sheets by writing custom functions

Now that we have understood the working of custom functions in Google Sheets, it’s time to see how those functions can be used insert a timestamp in Google Sheets  (automatic dates and times).

Sample Google Sheet

The Google Sheet that I will be using contains the following details:

1. Package ID
2. Quantity
3. Date Entered

If you prefer working with the Google Sheet click here.

Using custom formulas and Google Sheets, our plan is to add a timestamp as soon as the cells in the Quantity column are filled.

Step 1: Write the Google Sheets timestamp script that will return the timestamp on data entry

function setTimestamp(x) {
    if(x != ""){
        return  new Date();
    }
}

Here we have created a function (setTimestamp), that accepts one parameter from the Google Sheet. Next, we opened an if condition that checks if the passed parameter is not empty.

If the condition is met, it will return a new timestamp using the new Date() function. This means that as soon as someone enters data in the cell passed as a parameter, the timestamp of that data entry will be returned.

We are done with our code to add timestamps on Google Sheets! Let’s check the output.

Step 2: Use this new custom function

Now that the custom function is ready, it's time to use it. We want to add a timestamp to the sheet when the user adds a new entry in the Google Sheet.

Simply pass the cell you want to be timestamped as a parameter to the custom function, as per the screenshot below.

Here I have passed the quantity column in the custom function as a parameter, where the code will check whether the product quantity is added. As a result, the code will add a Google Sheets timestamp when cell is updated or the quantity is added. You can see how the code inserts a Google Sheets timestamp when the cell changes in the GIF below. As is visible, the code automatically adds the date and time when the cell is changed in Google Sheets:

How to create a timestamp in Google Sheets using Custom Functions: A summary

We saw how to create a timestamp in Google Sheets using Google Apps Script and custom functions. In the next blog, we are going to learn how to use the Google Sheets onEdit() function to add Timestamps to a  Spreadsheet with the help of  Google Apps Script followed by adding some details to the sheet.

Feel free to reach out if you have any issues/feedback at aryanirani123@gmail.com.