Hey there stranger!

Sign up to get access.

Automatically Backup a Spreadsheet

About this Tutorial

Make a copy and rename to BACKUP plus the Date of the backup. Execute this automatically via Triggers in your Apps Script. Two are shown here. to back up a tab or back up a spreadsheet file.

Video Transcript

0:00 Let's say we fill out this kind of sales document every single week and we want to make a copy of this or rather back it up If we want to back up this kind of sales document every week, how do we do that automatically?
0:12 Well, the first thing is that there are sort of two ideas here. Um, we can duplicate this and rename it as the day of the week or like one day or we could do that automatically or we could make a file make a copy of the entire sheet and save that as a backup.
0:31 Um, you- Either way, I'm gonna show you both ways here. So in our Apps Script we go to extensions Apps Script.
0:37 I have presented and created two so far. So one is called create backup tab and one is create backup file.
0:44 Depending on which one you want to use, um, we're going to automate the- is pretty much the same way. So let me walk through the script first and then I'll show you how to automate it.
0:54 First we have create backup tab. We're just duplicating the tab and then we're gonna rename it to this new date which is the date that we're backing it up.
1:04 Uh we can see here that this runs totally fine. Uh we go over here we see backup and the date.
1:12 Now for creating a backup file we're taking the entire spreadsheet making a copy of it and we're gonna call this.
1:20 This is backup of the name of the sheet plus we can also use this same date to have a date for the backup.
1:34 This is just to keep files sort of straight. So let's say that and let's run this one. We're logging the URL of the copy just we have a copy of it so we don't have to search through our uh drive to find it.
1:47 So we'll run it and we'll see in the log here. Once it copies we'll see a URL and we'll go- check it and see if it's correct.
1:54 Here's the URL of the new document and we'll look at it and see yes it's back up and it has the name of the sheet and the date of the backup right the moment that we do it.
2:05 Uh it also still has this backup tab in here. Maybe you want to do one or the other probably not both.
2:11 You probably don't want to create a new tab and create a new spreadsheet file at the same time. So um you're probably picking one or the other.
2:17 So how do we automate this? Meaning how does this happen every single week without us having to come into this?
2:24 Uh sheet and run this file or uh even doing this ourselves file make a copy rename it. How does this work right?
2:33 What we're going to do is create a trigger over on the left side once we have this these scripts create a trigger.
2:40 Click on triggers. On the bottom right we'll click add trigger and whichever one you want to choose to do choose that from the functions to run.
2:52 So we'll choose tab and select events sources is going to be time driven. In this case we want a weekly backup or you may want a daily backup or a monthly backup.
3:02 Either one you'll use time driven. And for a weekly, we use week timer. We're going to do it at the end of the week probably on a Friday night, Friday around Six to seven p.m.
3:13 Give people a little bit chance to stay late a little into their numbers. And sometimes you may want to do this even on a Tuesday or a Wednesday if people are definitely adding their numbers from the last week at the beginning of the week.
3:26 Totally up to you in whatever your business operation. So we're going to say this Friday 6 p.m. And that is it.
3:33 Now we are automatically backing up this spreadsheet every single week. Again we've chose the function backup tab so it'll create a new tab.
3:41 But if we want a new spreadsheet file completely we can go and edit this uh trigger and we can choose create backup file.
3:50 So as long as it runs fine by your testing you'll be able to run this trigger totally fine. It's running as you can also delete the trigger if you click on this three buttons over here just in case you're like hey, we're, we're inundated or it has some errors.
4:08 We're getting an error maybe it's too many tabs or too many specially files. We're doing it every hour or something.
4:14 If you do mess up in that way or you get to some time when you're like hey, I don't need to back this up anymore.
4:20 You can always go over here and delete trigger in this three buttons over to the right to leave that. So that's how you can automatically back up a spreadsheet.
4:29 And I think it's really cool and really useful for those. Those who are trying to keep track of something in a sheet and you know I want to keep back logs of this information and I want to keep it in either a separate file or a separate tab but I want to have one place where I enter the data.
4:48 Each and every time. So that's really cool and I hope this was super fast and easy for you. Again, better sheet members if you're watching this on better sheets down below click the link to the sheet, copy the sheet, get the app script totally free.
5:01 If you're watching this somewhere else, become a better sheet member. Remember today and get every spreadsheet you see in these tutorials, absolutely free once you become a lifetime member.