Automatic Weekly Backup of Google Sheets
How can we create backups of Google sheets every week or every day or every month?
This tutorial’s going to 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, 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.
The question is, “How can we create backups of Google sheets every week or every day or every month?”
Don't worry, I've written the code for you and I will show you how to edit it for yourself.
We have this Google Sheet I just created – brand new sheet – but yours might have lots of edits, version history, and other things going on. You want to just make a copy of it.
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, and then we're going to set the script that I wrote already. We're going to set a trigger for it to happen every single week.
Let’s get started!
What you will need to have is two things:
- A sheet
- A Google Drive folder
In the sheet, you’re going to have to access the code. Go to “Extensions” and then “Apps Script.” It'll open code. You won't have this code. You'll have to either copy and paste it from the sheet if you have access to the sheet, or follow along this tutorial and you can type it out yourself.
You will also need a Google Drive folder. 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. The name of the folder does not matter. You probably could keep backups all in one folder.
I'm going to show you a way in the script how I get around having the same name all the time. We're going to 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.
Let's go walk through the function first and see where we need to get this ID. This is going to be a folder ID.
But I want to walk through this script first.
We get the sheet variable first, then we need a destination folder. That’s DriveApp. This is different than the spreadsheet app. It's actually accessing your Google Drive. We’re going to get the folder by ID. What does that mean, ID?
We have links to our Google Drive folder. Just right click on the “Backup” name and go to “Get Link” in your Google Drive folder.
Copy the link. Then open a new tab and paste the link you just copied. In this whole URL, all we need is from after folders.
There's a slash and then some characters. Until there is a question mark, this is the ID of the drive folder. That's all we need.
If you are inside of the folder, this is the same ID (highlighted in blue in the screenshot below):
We're going to put that right here, next to getFolderById. That's probably the hardest part about this, honestly. The next few things are just going to be a few clicks and we'll get this done.
Next is “var SheetName” and this is just literally sheet.getName.
Something backupNew() {
var sheet = SpreadsheetApp.getActiveSpreadsheet();
var DestFolder =
var SheetName = sheet.getName();
DriveApp.getFileById(sheet.getId()).makeCopy(“Backup ” + sheetName + “ ” + new Date(), destFolder);
}Let’s do this line of code: Drive
We're using the Google Drive app. We are working inside of a spreadsheet, but we're accessing Google Drive from within this sheet's code. It’s pretty cool.
We have sheet. This file is the sheet that we're in, so we do sheet.getId then do makeCopy.
This is a little convoluted, but I'll walk through this.
The first part of this is the name, and then there'll be a comma destination folder. That destFolder is already here. We've already written that.
Let's walk through this name. I'm calling this backup with a colon. Then I put a space. I use the plus sign to concatenate inside of Google Script. Next, I'm going to grab the sheet name. It’s this whole thing:
That is what we named it. If we ever change the name here and start, and it keeps creating backups, whatever name you changed it to, we'll start creating the backups with that name.
This new date is a timestamp. You can format this. I just didn't do it in this code. I just wrote new date, and you'll see what happens.
Now hit Command S. If you run it for the first time, it'll ask you to review permissions. This will only ask you the very first time you do it. That's the only time you’re going to have to authorize this.
It looks like it completed. 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: “ and then over on the very end, we see the timestamp.
Cool! This is exactly the sheet we have. We even have the history of the sheet at that time.
Now that's not all, we're not done here. What's next is going to be auto magic testing.
We want a weekly backup. We don't want to have to go in to Apps Script and click run. We're going to create a trigger.
Go to the clock icon over on the left side in Apps Script, click on Triggers, go to the bottom right corner, and click Add Trigger.
In my sheet right now, I only have one script. If you have multiple functions or multiple scripts, you are going to have to choose from this dropdown menu which one to use.
I'm using “backupNow.” That's what I called it.
We're going to choose which deployment. It's always going to be Head. There's no other option.
In event source, choose “Time-driven.” we don't want the spreadsheet to be the trigger of this event. We want time to be the trigger of event, like a cron job.
Now we select the type of time and we're going to do “Week timer.” Once we do that, we have the options of which day we'll probably do it, like on a Friday night. Your time of backup will be completely dependent on what you need to do.
You get to select a time of day, an hour within the day. You cannot do it at exactly 5:00 PM every day. So yes, it is like cron jobs where you have this timer that's always running. It'll always happen on your selected day, but you have to pick a particular hour in which it will run.
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.
We have this trigger now and now every week, we're going to get this backup. We're going to have a new sheet created in our backup folder.
Two things to think about:
You're going to have 52 of these a year if you do it every week. You're going to have 365 if you do it every day. You can set up daily backups on weekdays, but you'll have to set up one for each weekday. So that’s every Monday, every Tuesday, every Thursday each day of the week. So you'll set up five triggers to create the weekday triggers, unless you want to do it every single day.
If you do it every weekday, it'll be around 250. Files are going to be a little hard to manage. I would recommend something around weekly, because we already have version history here. So daily is a little overkill.
But to each business is their own. You can do whatever you want as long as it solves your business need.
Another thing to think about is if you have 52 or 365 of these files in a backup folder, perhaps naming this backup 2022 or 2023 is a better idea.
What I would do is I would keep the same folder as the backup, so I don't have to go to my script and edit the Id. If I create a new folder for 2023, I have to go and edit this on the day before these backups backs up.
So what I would do is perhaps in 2023, I would copy this folder and then delete everything within the folder. Then this (new 2023) folder remains the folder everything goes into, and then you create a backup of the folder of backups. That might get a little confusing. Hopefully, this tutorial was helpful to those who are trying to create a weekly backup.
You might not have to copy the entire sheet as a backup. If you just want to save the data, at the end of the URL, you type in this code:
“export?format=csv”Now, this URL won’t open the sheet every single time you click it. It will actually copy to a CSV file. That's all it's gonna do: Create a CSV file. It doesn't go to the sheet itself. It doesn't open it. It just saves it as a CSV file.
That might be all you need. An email trigger, a calendar reminder, “Hey, download this as a CSV.” There are ways to do it programmatically, but I'm not going to go through it in this particular tutorial.
This is the tutorial to back up and create a copy of the Google Sheet in a Google Drive. 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.
Don’t make any sheets. Make Better Sheets.
Watch the video for this tutorial:
Watch these tutorials to learn more about automation on Google Sheets
Get more Google Sheets Tutorials at BetterSheets.co
Join other members! For $19/month you can access over 200 Better Sheets tutorials. Learn Apps Script in under 40 minutes. Design better dashboards. Make your sheets faster and yourself more confident in sheets.
