Add “last modified” timestamps to Google Sheets

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 to Google Sheets using a very simple custom function.
2. 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.

Welcome to Chartmat! Are you tired of struggling to make sense of your data in Google Sheets? Do you want to turn your sheets into powerful apps and dashboards that can help you make better decisions and drive your business forward? Look no further! With Chartmat, you can easily transform your Google Sheets into beautiful and interactive dashboards and apps. Whether you’re a small business owner, a marketer, or a data analyst, Chartmat has something for everyone. With our easy-to-use platform and extensive range of features, you’ll be able to turn your data into actionable insights in no time. So why wait? Sign up for Chartmat today and start unlocking the full potential of your data! Check out the link given below to know more about charmat!

Chartmat | Build powerful apps on top of Google Sheets. Start for free today.

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 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 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 Timestamps to your Google Sheet, we are going to take a look at how custom functions work. 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 to Google Sheets

Now that we have understood the working of custom functions, it’s time to add timestamps to Google Sheets, using custom functions.

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 custom function 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 the column. You can see this in action in the GIF below:

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 add Timestamps to Google Sheets using the onEdit() function using 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.