Add timestamps to track data input in your Google Sheets with Google Apps Script

Welcome to the second part of the series. In the previous blog, we learned how to add timestamps to Google Sheet using custom functions. In continuation, we will now use the onEdit() function in Google Apps Script. This will allow us to add the user’s email address making the changes to the Google Sheet in addition to the timestamp.

So let’s get started.

As a reminder, this is our use case: you have your inventory data stored in your Google Sheet. Every time new stock arrives, you add new lines. Until now, you manually added the time at which the new supply was added and the name or email address of the person who added it. Using Google Apps Script, you can automate this process.

Sample Google Sheet

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

1. Package ID
2. Quantity
3. Date Added [The original date/time of entering the product
4. Date Modified [ Latest date/time for adding quantity]
5. Email Address of the user

The Google Sheet template is available here.

Step1: Write the Automation Script

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.

function onEdit(e){
const row = e.range.getRow();
const col = e.range.getColumn();
const SHEET_NAME= “Sheet2”;

Here I have created a onEdit function. The onEdit function runs as soon as a change is made in the Google Sheet.

In the onEdit function, we have passed e, known as the event object.

Inside the onEdit function, we have started by declaring the row and the column using the getRow and getColumn commands. After declaring the variables, we have stored the sheet’s name in a variable.

const QUANTITY_COLUMN= 2;
const FIXEDROW= 1;
const DATE_MODIFIED_COLUMN= 4;
const USER_EMAIL_COLUMN= 5;

Here I have declared four variables that we will be using in the for loop. The first variable is the quantity column, where the user will be adding the quantity of the product followed by the product ID. Next, I have declared the FIXEDROWcolumn and assigned 1 to it. We will use this variable to make sure that the change is not made in the 1st row.

Here I have declared four variables that we will be using in the for loop. The first variable is the QUANTITY_COLUMN, where the user will be adding the quantity of the product. We will use this variable to check whether a change made by the user is in the quantity column.

Next, I have declared the FIXEDROW variable and assigned 1 to it. We will use this variable to make sure that if any change I made to the first row of the Google Sheet, we don’t want to add a timestamp.

Next, I have declared the DATE_MODIFIED_COLUMN and USER_EMAIL_COLUMN, respectively. The DATE_MODIFIED_COLUMN is the column where the timestamp at which the product is logged will be added. The USER_EMAIL_COLUMN will be used to store the email address of the user adding the inventory to the Google Sheet.

if (e.source.getActiveSheet()
.getName() === SHEET_NAME && col === QUANTITY_COLUMN&& row > FIXEDROW){

As we discussed previously, we want to add timestamps to our Google Sheet when the QUANTITY_COLUMN is changed. To do that we use an if condition where we will be passing all the conditions that we need. The timestamps will only be added, when all the conditions are met.

I have passed three conditions in my if loop

(1) The first condition checks whether a change is made in the correct sheet. To do that, we use the getActiveSheet() function that gets the sheet in which the user is currently working. After getting the sheet, we use the getName() function to get the name of the sheet and then check if it matches the name of the sheet that we previously declared.

(2) Now that we know the inventory is added to the correct sheet, we are going to check if the changes are made in the correct columns. The second condition checks if the column in which the change is made matches the QUANTITY_COLUMN.

(3) Now that we know the sheet is correct and the column in which the change is made is correct, we are going to check the row in which the change is made is greater than the CHANGED_ROW, so that we don’t change the headers of the Google Sheet.

If all these conditions are met, it will add the timestamps to the sheet using the following code.

const CURRENT_DATE= new Date();

Here we have created a new variable (CURRENT_DATE), where we have created a new timestamp using the new Date() function.

e.source.getActiveSheet().getRange
(row,DATE_MODIFIED_COLUMN ).setValue(CURRENT_DATE);

Now that we have created the timestamp, it’s time to set the value in the Google Sheet. Using the setValue function we are going to set the timestamp in the 4th column (Date Modified) using the getRange function. (In the getRange function we have passed the row where the change has been made followed by the 4th column where we want to set the values.)

const email= Session.getActiveUser().getEmail();
e.source.getActiveSheet().getRange
(row,USER_EMAIL_COLUMN ).setValue(email)

After setting the timestamp, we also want to paste the email address of the person making the change in the Google Sheet. We are going to do that using the Sessions.getActiveUser() function, followed by getting the email address of the user using the getEmail() function.

After getting the email address of the user, we are going to be setting the value in the same row where the new entry has been added. To do that we are going to use the getRange function, where we pass the row and column and get the exact location where the email address has to be set. After getting the location, we use the setValue function to paste the user email address in the cell.

Now if we go back to our sheet and make a change you can see that the email address of the user followed by the timestamp at which the change has been made has come in successfully.

If you go ahead and make a change in the data entry, you will see that the time will change again.

As we discussed previously, when a user adds a new package we want both the columns (Date Entered and Date Modified) to change. On the other hand, when a user makes a change in an already existing entry we only want to change the date modified.

const DATE_ENTERED_COLUMN= 3
if(e.source.getActiveSheet()
.getRange(row,DATE_ENTERED_COLUMN ).getValue() == “”){
e.source.getActiveSheet().getRange(row,DATE_ENTERED_COLUMN).setValue(CURRENT_DATE);
}

To do that we are going to open an if loop and check if the Date Entered row is empty, if it is empty it will paste the timestamp using the setValue function. If a timestamp is already added, it won’t change the original timestamp.

We are done with our code !! Let’s check the Output.

Step3: Check the Output

Our code is complete and good to go. Since this is a onEdit function we don’t need to click on any function and run it. All you have to do is make a change and the script will do all the work for you.

On successful execution, the script added the Date Entered and the Date Modified, followed by the user email address.

Now let’s go ahead and make a change to the existing package entry.

Here you can see I made a change to the first package entry and the Date Entered has stayed constant, but the Date Modified has changed. This means our second if loop has worked successfully.

Summary

We saw how we can add timestamps to Google Sheets using Google Apps Script. I hope you have understood how to add timestamps to Google Sheet using Google Apps Script. Feel free to reach out if you have any issues/feedback at aryanirani123@gmail.com.