Insert Timestamp in Google Sheets with Custom Functions

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:

Part 1 — This part will show you how to add timestamps to Google Sheets using a very simple custom function.
Part 2 — This part will show you how to add Timestamps to 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:

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

Write Custom Functions to add timestamps in Google Sheets

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

Sample Google Sheet

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

Package ID
Quantity
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! 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. If the quantity is added, the code will add a timestamp to Google Sheets under the relevant column. You can see how the code inserts the date and time when the cell is changed in Google Sheets in the GIF below:

How to write a custom function in Google Sheets to add timestamps: A summary

We saw how you can add timestamps to 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.

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