Autofill Date on Edit in Multiple Columns
In this video, learn how to automatically fill dates in multiple columns using Google Sheets' Apps Script on edit events. Discover how to set up your script to update dates based on changes in task status efficiently.
So I had a question here about auto-filling dates on edit when we have multiple columns. Let's do that in this video. In the last video, we add a task here, task here, and then we change the status to, let's say, pending and the date is going to be automatically changed here. Now this could be updated, meaning we go in progress or we are like, "Hey, this is done." And you see this date in the C column is being updated automatically. I'm not doing this. No edits, no weird edits here. Uh, here's the Apps Script Looks like this. It's just function onEdit event based on the row and column, and we say a little if here. We say if the row is greater than one, meaning it's not the header row , and the column is the two column, the second column.
So not B, we're not using ABC, we're using the numbers one, two, three. So if we edit the second column, then do this. Well, we could take this little if. We're gonna use the exact same onEdit function . We're not going to add more onEdit functions . In fact, we can't, uh, the sheet and the Apps Script can't have multiple onEdit functions . If you do it that way, just combine them in this way, so as I'm gonna show you. I'm gonna have this if. I'm gonna hit Enter. I'm gonna just copy and paste it down. So it's the exact same code Now I'm gonna change the column. So let's say we have another status. We have, like, a status in the D column, and we want… Actually, we want a little bit of space here. We don't want it just right next to each other.
So in this particular case, the next status column, maybe this is a different kind of status or a part of a project Copy paste those dropdowns all the way. So in the E column, I want the F column to update based on the E column changing. Well, that's going to be column five, right? One, two, three, four, five. And I need to change one more thing, which is the v- date is here, and the action we're taking is right here, SpreadsheetApp . activeSpreadsheet. getSheetByName, getRange . We're on the same row as the edit, but we need to change this three to a six. It's wherever we want this edit to be. Now, this edit … Or sorry, this last updated date could be way over here, over on the Z column.
It doesn't matter. Um, as long … I mean, I think it matters just as a data nerd here, but, uh, for your purposes, I, I've actually done this a lot of times where we have just an updated column at the end and some status column here. For a variety of reasons, you might need that. Okay, so let's change this to pending. , Let's save our code. Sorry, we, we have not saved our code. We have the little orange button there. So yeah, it didn't work. But yeah, let's go in progress. And there's the date. We say, "Oh, this, we haven't started this, we haven't started this, we haven't started this on the if," hey, there you go. So that's how to do multiple row- uh, multiple columns, but autofill those dates. Hopefully you enjoyed that. You are watching better sheets here on YouTube.
Make sure you check out this video or this video and subscribe right now to get more tips, tricks, how tos, get more out of your Google sheets than you ever have before. I'm excited to be making a ton more videos here. Ask me questions down in the comments and I will answer them in future videos. But for right now, right here, one of these videos is gonna be your next Google sheet.