00:01 Hello, this video's gonna be really fun because it's a question that I've gotten asked a couple times recently, these things happen where I get a couple questions of the same.
00:10 It's the same question a couple times. And then I get really excited when I get to write a little bit of code and it works almost the first time.
00:18 So what the question is is how can we create backups of Google sheets every week or every day or every month?
00:25 And it's gonna take a little bit of code, but don't worry. I've written the code for you. And I will show you how to edit it for yourself.
00:33 So we have this Google sheet, I just created brand new sheet, but yours might have lots of edits, lots of version history, lots of things going on, and you want to just make a copy of it.
00:41 So a backup of your sheet is not really like an actual backup. What we're doing is we are creating a copy of your sheet as it is now saving it to a Google drive folder.
00:53 And then we're going to set that the script that I wrote already, we're gonna set a trigger for it to happen every single week.
01:01 So let's get started. What you will need to have is two things. One a sheet you're gonna have to AC access the, uh, code.
01:11 So go up to extensions app script, and it'll open code. You won't have this code. You'll have to either copy and paste it from this sheet.
01:18 If you have access to the sheet or follow along this video and you can type it out yourself, um, you will also need a Google drive folder.
01:28 I just called this one backup. If you are backing up a specific sheet, I would probably call it backup with the name of the sheet in this name of the folder.
01:38 The name of the folder does not matter. Um, you probably could keep back ups all in one folder. Maybe I'm going to show you a way in the script, how I get around having the same name all the time.
01:54 We're gonna put in the name, the timestamp of when it is created so that you do have the same name, but it has this timestamp to know that it's different.
02:03 Okay? So let's go walk through the function first and see where we need to get this ID. This is gonna be a folder ID.
02:12 We're gonna get this, but I wanna walk through this script first. So we get the sheet variable sheet equals spreadsheet, app dot, get active spreadsheet.
02:21 Then we need a destination folder. That's drive app. So this is different than the spreadsheet app. It's actually accessing your Google drive.
02:28 You're gonna go to get folder by ID. Now, what does that mean? ID? We have links to our Google drive folder.
02:37 So I just go to get link. Um, you can also see it up here, but I copy the link and let's go to a new, uh, tab.
02:46 Now this whole URL, all we need is from after folders. There's a slash then there's some characters until there is a question, mark.
02:59 This is the ID of the drive folder. So that's all we need. Um, you can also sort of see it.
03:07 If you are inside of the folder, this is the same ID up here. So if you're in your drive and looking at the folder, you can right click on that folder and do get link.
03:19 If you go into the folder, it's empty obviously for this one, cause I just created it. You can also get this ID up here.
03:26 We need all the entire character, all of the characters here from the slash until the end. Okay, we're gonna put that right here.
03:37 Next in get folder by ID. That's probably the hardest part about this. Honestly, the next few things are just gonna be a, a few clicks and we'll get this done variable sheet name.
03:50 This is just literally sheet dot getName. Okay. So now we have these three variables. We have the sheet, we know it's name and we know where it's gonna go.
04:00 So what we do is we do this one line of code and it's drive app again, we're using the Google drive app, not the, we are working inside of a spreadsheet, but we're accessing Google drive from within this sheets code.
04:11 It's pretty cool. Drive app dot get file by ID. So we already have that. We have sheet. This file is the sheet that we're in.
04:19 Okay? So we do sheet dot, get ID, and then we do dot make a copy. Now this is a little convoluted, but I'll walk through this.
04:30 This is the name. The first part of this is the name. And then there'll be a comma destination folder. So that desk folder is here.
04:37 We've already written that we have that ID, but let's walk through this name. I'm calling this backup with a colon.
04:44 Then I put a space. I use the plus sign to concatenate inside of a Google script here. I'm gonna grab the sheet name.
04:52 Okay? So that it still is this whole thing. This is, this is what we named it. If we ever changed the name here and start, and it keeps creating backups, whatever name you changed it to will start creating the backups with that name.
05:05 So old backups, old copies will have the old name and new copies will have the new name. Just a little note there.
05:14 I added a space and then I put dot, uh, plus it's not dot, sorry. Plus new date. Now this new, um, space date is a timestamp.
05:24 You can format this. I just didn't do it in this code. I just wrote new date and you'll see what happens.
05:32 So hit a command. S if you run it for the first time, it'll ask you to review permission. This will only ask you the very first time you do it.
05:43 Once you review permissions, I'm gonna use my own Google, uh, account. I'm gonna allow it. I'm only editing, doing a couple things there.
05:52 Didn't really walk through those. That's the only time I'm gonna have to authorize this. Okay? So it looks like it, it, it completed.
06:01 If we go to our drive, there it is. Now we have a new sheet called backup, weekly backup. If we open it up, we will see that it does say backup, colon, uh, space.
06:14 And then over on the very end we see Wednesday, September, and I'm in central Indonesia time. This is my plus zero eight GMT.
06:23 This is my time zone. So whatever your time zone is, uh, it will create a time stamp. Cool. So we have a copy of the sheet.
06:31 This is exactly the sheet we have. We have SU the history of the sheet at that time. Now that's not all we're not done here.
06:43 What's next is gonna be auto magic. Um, we want this backup to happen every single week, right? We want a weekly backup.
06:55 We want every single week. Oh, we don't wanna have to go in to app script and click run. That's what we would have to do.
07:04 If we didn't do what we're about to do. All right, we're gonna create a trigger. The answer is we're gonna create a trigger, go to the, uh, clock over on the left side in app script, click on triggers, go to the bottom, right corner, click, add trigger.
07:20 Now in my sheet right now, I only have one script. So it says it only shows up here. If you have multiple functions or multiple scripts, you are gonna have to choose from this dropdown menu, which one to use, and I'm using backup.
07:35 Now, that's what I called it. We're gonna choose which deployment it's always gonna be head. There's no other option from event source of time driven.
07:45 So we don't want the spreadsheet to be the trigger of this event. We want time to be the trigger of event, like Aron job.
07:51 If you're into coding, now we select the type of time and we're gonna do week timer. Once we do that, we have the options of which day we'll probably do it like on a Friday night.
08:03 Maybe if you have sales teams that may work overnight, uh, or towards the end of the day, or if you have like a dashboard that you're keeping track of things, and people are really bad at like updating them until maybe Saturday or like Friday, midnight or Friday night, then you're gonna probably wanna do a backup on like Sunday morning or Sunday night, or even possibly Monday morning in like the 4:00 AM or something.
08:32 Your time of backup will be completely dependent on what you need to do. You could also, honestly, if you find that like, oh, you need to do it in the middle of the week.
08:40 Right? Um, you can, so we're gonna choose a Friday. We're gonna choose 5:00 PM and you get to select a time of day an hour within the day.
08:51 You cannot do it at exactly 5:00 PM every day. So yes, It is like crime jobs where you have this timer, that's always running and it'll always happen on this day, but you have to pick a particular hour in which it will run.
09:05 So that is the only weird thing I think about this trigger. It doesn't happen at a specific time, but it does happen within this hour.
09:13 So maybe we wanna do it like after, after work hours. So maybe eight to 9:00 PM and click save, oh, there is one reason you might want <laugh>.
09:22 There is one reason you might want to do this. Like more like four or 5:00 PM is if you need to do it during work hours, this trigger needs to happen during work hours.
09:32 But at the end of the day, you might wanna do that. Um, so we have this trigger now, and now every week we're gonna get this backup.
09:41 We're gonna have a new sheet created in our backup folder. Uh, two things to think about here is that one you're gonna have 52 of these a year, right?
09:52 If you do it every week, you're gonna have 365. If you do it every day, you can set up daily backups on weekday, but you'll have to set up one for each weekday.
10:04 So every Monday, every Tuesday, every Thursday, each day of the week. So you'll set up five triggers to create a, to create the weekday triggers.
10:12 If you, unless you wanna do it every single day, um, if you do it every weekday, what it'll be around 250 are going, files are gonna be in this.
10:20 It'll be a little hard to manage. So I would recommend something around weekly because we already have version history here.
10:28 If you click on the, the, the, uh, last edit, you will have a list of your edits here. So daily is a little overkill daily backups, but to each business is their own.
10:41 You can do whatever you want, as long as it solves your business need. Right? Another thing to think about is if you have 52 or 365 of these files in a backup folder, perhaps naming this back up 2022 or 2023 is a better idea.
11:00 And then what I would do is I would keep the same folder as the backup. So I don't have to go to my, um, go to my script and edit this ID.
11:11 Cuz if I create a new folder for 2023, I have to go and edit this on the day, the day before this backups backs up.
11:20 So what I would do is perhaps in 2023, go through these, uh, copy this folder and then delete everything within the folder so that this folder remains the folder everything goes into, and then you create a backup of the folder of backups that might get a little confusing.
11:38 Sorry. Um, but hopefully this video is helpful to those who are trying to create a weekly backup. If you're trying to save your sheets, um, you can also a little bonus here.
11:50 Idea is you might not have to Co copy the entire sheet as a backup. If you are looking for, let's say you want to just save the data.
12:02 If you have an entire data sheet and you're like, I just wanna save the data. You could also do this.
12:07 Where at the end of the URL, you write export question, mark, um, format equals CSV. Now this URL, every single time you click it, if we just click, just go to the URL, it won't open the sheet.
12:23 It will actually, um, copy to a CSV file. That's all it's gonna do is create a CSV file. There you go.
12:33 Create a CSV file right away. Doesn't go to the sheet itself. It doesn't open it. It just saves it as C as a CSV file.
12:41 That might be all you need is an, an email trigger, a calendar reminder, Hey, download this as a CSV. There are ways to do it programmatically, but I'm not gonna go through it in this particular video.
12:53 Um, this is the video to back up and create a copy, just a copy of the Google sheet in a Google drive.
13:01 So we accomplished that, but I hope that that last little thing gave you a little bit of hope that maybe you don't necessarily need to have copies of the sheet.
13:08 You might need to have a formatted, uh, download of the sheet if that's what you need. Um, thanks so much for watching.
13:15 Keep watching videos, keep learning, learn more.