How to Insert a “Last Modified” Timestamp in Google Sheets: Tips for Adding a Custom Function

When collaborating on a Google Sheets spreadsheet with multiple contributors, it becomes important to know when it was last updated and by whom. In this article, we will present two different ways to add timestamps to Google Sheets whenever a modification is made to the sheet:

Part 1: Using a Simple Custom Function

In this first part, we will show you how to add timestamps in Google Sheets using a very simple custom function.

Part 2: Using the onEdit Function in Google Apps Script

In this second part, we will demonstrate how to insert timestamps in Google Sheets, along with the email address of the user making the changes, using the onEdit function in Google Apps Script.

Now that we know what we will cover in this series, let’s start by learning how to insert timestamps in Google Sheets using custom functions.

What is a Custom Function in Google Sheets?

We all regularly use built-in formulas in Google Sheets like SUM(), COUNTIF(), and many more. But did you know that you can create your own formulas and run them in your Google Sheets spreadsheet? This is possible thanks to Google Sheets’ custom functions. You can define their functionality in Google Apps Script and then use them just like built-in formulas in Google Sheets.

Writing Your First Custom Function in Google Sheets

When you are in the Google Sheets spreadsheet, open the Google Script editor by following these steps:

1. Click on Extensions and open the Script editor.

2. The script editor will then open.

Now we are in the script editor, ready to code.

Before using custom functions to add the timestamp feature to your Google Sheets spreadsheet, let’s understand how custom functions work in Google Sheets. We will write a simple Google Apps Script function that takes a parameter from the Google Sheets spreadsheet (number1) and returns the sum of number1 + 2.

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

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

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

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

How to Insert a Timestamp in Google Sheets Using Custom Functions

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

Example Google Sheets Spreadsheet

The Google Sheets spreadsheet I will be using contains the following details:

1. Package ID
2. Quantity
3. Entry Date

If you prefer, you can work with the Google Sheets spreadsheet by clicking here.

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

Step 1: Write the Google Sheets Timestamp Script that returns the timestamp when data is entered

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

Here, we have created a function (setTimestamp) that accepts a parameter from the Google Sheets spreadsheet. Then we have 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 for that entry will be returned.

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

Step 2: Using 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 whenever the user adds a new entry to the Google Sheets spreadsheet.

Simply pass the cell you want to timestamp as a parameter to the custom function, as shown in the screenshot below.

I have passed the “Quantity” column to the custom function as a parameter, where the code will check if the product quantity is added. As a result, the code will add a timestamp to Google Sheets when the cell is updated or the quantity is added. You can see how the code inserts a timestamp in Google Sheets when the cell changes in the animated screenshot below. As you can see, the code automatically adds the date and time when the cell is modified in Google Sheets.

How to Create a Timestamp in Google Sheets Using Custom Functions: A Summary

We have seen how to create a timestamp in Google Sheets using Google Apps Script and custom functions. In the next blog post, we will learn how to use the onEdit() function of Google Sheets to add timestamps to a spreadsheet with the help of Google Apps Script, followed by adding some details to the sheet.

Feel free to contact us if you have any issues or comments.

Source: Crawlan.com