Copy Date to Next Cell Automatically

0:00 Hey, let's copy a date to the next cell automatically. Now, this use case is very interesting. We got a question here on YouTube asking, "Hey, we have a next visit to a doctor in a column, and to the left of that column, we have the last visit." So we have last visit and next visit. We already highlight everything that's today. We highlight also everything that's tomorrow, so we really can keep track of what's going on today, what's going on tomorrow. However, there's a little optimization that could potentially be here is we are very much on top of this. We do go to these appointments, and we want to copy the date from whatever is the next visit, like today, May seventh, we wanna copy that to last visit without having to copy, paste, delete, save a few clicks here and there, or save a lot of clicks because maybe there's a lot of appointments or a lot of things happening on this particular day and we know we are taking care of them. We just want them swapped over. So how do we do this automatically? Let's go up and write a little bit of script, and then trigger that script every day to look at this exact situation, find the situation, execute it, and just do it for us. All right. Let's go to Extensions, Apps Script, and start writing our code. We're just gonna name this Project Visit Auto Move. You can name it anything you want. Same with this function name. Function my function we can change to Daily Visit. But again, that's up to you what you wanna name it. Doesn't matter. What matters is the code that goes in here. How to Trigger Macros Daily Google Sheets Interface Changes Copy Date to Next Cell Automatically

0:51 lot of clicks because maybe there's a lot of appointments or a lot of things happening on this particular day and we know we are taking care of them. We just want them swapped over. So how do we do this automatically? Let's go up and write a little bit of script, and then trigger that script every day to look at this exact situation, find the situation, execute it, and just do it for us. All right. Let's go to Extensions, Apps Script , and start writing our code. We're just gonna name this Project Visit Auto Move. You can name it anything you want. Same with this function name. Function my function we can change to Daily Visit. But again, that's up to you what you wanna name it. Doesn't matter. What matters is the code that goes in here. Let's go variable sheet equals spreadsheetApp .getActiveSpreadsheet, Automatic Screenshots in Apps Script Getting Started Coding in Apps Script Automated Project Management in Google Sheets Automated Daily Reminders

1:39 getSheetByName, and whatever the name is of the sheet you're on. So maybe you have t- tons of tabs here. This is called maybe Visits. And maybe there's some other information here, but we have Visits. We need to name this Visits. Our date values or our data is gonna be in sheet. getRange E3 colon E. This could be anything for you. We're gonna get all the values there, 'cause that's all we care about. We're gonna go to this column, look for the date, so let's go get the column. There we go. And we also need today. So this is gonna be new Date, a capital D with parentheses. One extra thing we're gonna do here when dealing with dates- And timestamps is we want the date, not the time. So we're gonna s- basically strip away all of the time elements, and Can I Automatically Rename a Sheet based on a Date? Google Sheets Interface Changes Get new Date

2:27 we're gonna do setHours, and all four of these are gonna be zeros. So now we have the data we're looking at, the date of today, what we're looking at. We need to go data .forEach. We're gonna create a little function here in parentheses row, index . And then we're gonna use this, what's sort of called a pipe function . We're gonna create parentheses-- Sorry, not parentheses, curly brackets. And inside, we're gonna have a const cellDate is equal to row and square bracket zero. This says whatever row we're on, get that information. And if cellDate.setHours… Remember we stripped away the date information. We wanna do that here as well. Is equal to today. What are we gonna do? How To Change Date Format Automatically

3:12 Well, now we're gonna do something. Basically, we're saying, "Hey, look at the row data zero," because we're in the E column here. And if these two things are the same, today and that cell date, let's do something. Which we need to know the row number , and that's going to be index plus three only because we're in-- starting our range in the third row. If it was E one or E, this would be plus one. Plus three, three here, just so you know, just in case you have different headers , different starting point. Let's copy to column D. Again, just double-checking that this case and what you may have to change later is we're looking at the E column, but we're gonna copy to the D column, Basics - Structure of a Sheet: Index() Row() and Column() Beginners Guide to Using Index, Row, and Column | Simply useful Google Sheets Formulas

3:58 and then we're gonna clear the E column. So let's do that. Sheet. getRange rowNumber, four. And then outside the parentheses, set value is the date. Then sheet. getRange rowNum, five . clearContent. clearContent means we are just deleting the text, not any formatting , nothing else other than just delete the text. Okay, let's save this, or click up here and save project to drive. And I wanna run this now. I have an example. I have today's next… Today has two next visits. What should happen is that these dates copy over to the last date, and then there's nothing left in the E column. So let's try it. The first time we run this, click Run, it's gonna ask us to authorize. Automatically Clear Content | Refresh Reuse Recycle Templates Email Yourself a Cell from a Google Sheet, Every Day Copy Date to Next Cell Automatically

4:43 We're gonna have to review permissions. And it'll say execution started, and let's see if it worked. Yes, it worked. There's a clear date, there's a clear date, and the last visits are there. So how do we set this up to be automatic? I'm gonna show you right here. It's just a few clicks. We have our function completely working. It has no errors. We like it. It's gonna run once a day. Go over to the left side, and there's a few things. There's Overview, Editor , Project History, Triggers. That's the one we want. There are sort of two clocks here. One of them is a stopwatch, one of them is a ti- like sort of a, sort of a arrow pointing to the left, past history. We want Triggers. So click on Triggers, and on the bottom right, click the blue button, Add Trigger . Choose which function to run. If you have other functions , this will be a dropdown menu Create an Automated Task List Learn to Code in Google Sheets, For Programmers | For Advanced Google Sheet Users

5:28 showing you other functions . If this is the only thing you have in your sheet, in, in the Apps Script , just… You don't need to select it. It's already selected for you. Our event source is gonna change to time driven, and we want a day timer. We wanna run this probably around the time of every visit or before you come in to set the next visit, something like that, maybe around 11 to noon. Or if, if you're like, "Hey, every night at 6 PM, I come down and I sit down and I say, 'What's up? What, what do I have to calculate and put in and document?'" Put that time, whatever the hour before that is, so like 4 to 5 PM if you want. I'm gonna put 11 AM to 12 PM. What this means is that the trigger will run the very first time at some random point between 11 AM and 12 PM, and then every day Now Do It Every Damn Day - Learn to Code in Google Sheets Part 5 What Can You Automate in Google Sheets? Every single trigger available to Google Sheet users

6:17 after, it'll be 24 hours every… The next 24 hours, the next 24 hours, the next 24 hours after that. So Google doesn't let you have a very specific time, but does s- let you select one of 24 hours, and then it'll run within that. So let's s- save that. We may have to authorize again But once it's set up, you'll see this line here. It'll say who it's owned by, the event, error rate if there's any errors. There'll be a pencil icon to edit and three dots if you wanna delete this. If you're like, "Hey, I actually don't need this automation anymore," then click Delete Trigger , delete forever, and it won't happen again. But there's your script to move from column E to column D. Depending on which columns you have, change this get range of getting the values . Also change this number here of where you're setting the values and where you're clearing the values . So this is gonna be a number . Four is D, five is E, and so on and so forth. Enjoy. 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. Better Sheets Videos Checklist Absolute Basics of Google Sheets