Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Creating automatic scheduled backup copies of your Google Sheets using Google Apps Script

How to "Schedule Automatic Backups" of your Google Sheets

This tutorial demonstrates how to use Google Apps Script to:

  • Create copies of the Google Sheet in the desired destination folder automatically at set intervals.

  • Append the time stamp with each backup file's name.

  • Adjust time trigger for backing up every day/hour/minute.

  1. Open your Google Drive and create a new folder right where your Google Sheet is. You may name it anything you like. step 1

  2. Open the folder you just created. You will be able to see its ID in the URL displayed in your browser's address bar. We will need this later. step 2

  3. Open your Google Sheet and select menu item "Tools" --> "Script Editor" step 3

  4. Once the script editor opens, copy and paste the code from the 2-MakeCopy.gs file (see file below) into the "Code.gs" file, replacing the code that's already included. step 4

  5. Copy the ID of the folder you created earlier and paste in place of the xxxxxxxxxxxxxxxxxxxxxxx in the code. step 5

  6. Go to menu "File" --> "Save". step 6

  7. Click on the clocked shaped "Trigger" button. step 7

  8. Click on the blue link to add a trigger. step 8

  9. Select the time interval as per your desire. step 9

  10. Click on "Continue" to authorize running the script you just made. step 10

  11. Click on "Allow" to let the script run on your Google Sheet. step 11

  12. After the set interval(which was every minute in my case), you can see the automatic scheduled backup copies of your Google Sheet saved in the folder you created. step 12

// Abhijeet Chopra
// 26 February 2016
// Google Apps Script to make copies of Google Sheet in specified destination folder
function makeCopy() {
// generates the timestamp and stores in variable formattedDate as year-month-date hour-minute-second
var formattedDate = Utilities.formatDate(new Date(), "GMT", "yyyy-MM-dd' 'HH:mm:ss");
// gets the name of the original file and appends the word "copy" followed by the timestamp stored in formattedDate
var name = SpreadsheetApp.getActiveSpreadsheet().getName() + " Copy " + formattedDate;
// gets the destination folder by their ID. REPLACE xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx with your folder's ID that you can get by opening the folder in Google Drive and checking the URL in the browser's address bar
var destination = DriveApp.getFolderById("xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx");
// gets the current Google Sheet file
var file = DriveApp.getFileById(SpreadsheetApp.getActiveSpreadsheet().getId())
// makes copy of "file" with "name" at the "destination"
file.makeCopy(name, destination);
}
@mudithae

This comment has been minimized.

Copy link

commented Mar 17, 2017

Hi abhijeetchopra,

Thanks for the script. It is really helpful.

However I would like to suggest to use below lines to get the local timezone to your file name.

`var timeZone = Session.getScriptTimeZone();

// generates the timestamp and stores in variable formattedDate as year-month-date hour-minute-second
var formattedDate = Utilities.formatDate(new Date(), timeZone , "yyyy-MM-dd' 'HH:mm:ss");`

Regards,
Muditha

@mghall25

This comment has been minimized.

Copy link

commented May 18, 2017

Thank you for this. It works well. However, in addition to making a copy of the Google sheet, it also makes a copy of the Google Form to which the sheet is connected. How could the copy of the Form be prevented?

Miriam

@lavin80r

This comment has been minimized.

Copy link

commented May 26, 2017

Amazing, thanks!

@aaritdahiya

This comment has been minimized.

Copy link

commented May 31, 2017

Hi Abhijeet

I Want a copy of the Sheet into multiple accounts folder. Will you please share the code for that. If we have a domain environment and want a copy of a single sheet into multiple accounts folder. Please Share.

@AWHF

This comment has been minimized.

Copy link

commented Jul 14, 2017

Hi Abhijeet

I'm having issue to backup my sheet. I'm using IMPORTRANGE and all the backups files are the same even when there are changes on the sheet.

Can you please help to advice or anyone can help?

Thanks~!

@nlhamsley

This comment has been minimized.

Copy link

commented Jul 29, 2017

Hey Abhijeet,
When the script saves a copy of my sheets file it does not copy the information that has recently been added to the sheet. It only copies the original information in the sheet. Is their a way to get it to copy the new information added into the sheet each day? This way is saves a copy of the days new information and i can wipe the old information from the original?
Any help would be great thank you!

@jongmedellin

This comment has been minimized.

Copy link

commented Aug 16, 2017

hi, thanks,. but google need to verified the app, pls help

@mefryar

This comment has been minimized.

Copy link

commented Sep 19, 2017

Thanks for this Abhijeet! I have modified slightly so that the script includes a menu and a function to open the destination folder. https://github.com/mefryar/archive-gsheet

@kodopik

This comment has been minimized.

Copy link

commented Dec 2, 2017

Many thanks, works fine

@mikiec84

This comment has been minimized.

Copy link

commented Dec 4, 2017

Outstanding work man. Very generous of you.

@dekadentno

This comment has been minimized.

Copy link

commented Dec 7, 2017

Thank you for this tutorial! Cheers

@anthonyJRI

This comment has been minimized.

Copy link

commented Jan 12, 2018

Thanks man, very useful

@AlasdairSnow

This comment has been minimized.

Copy link

commented Feb 25, 2018

Awesome! Had a feeling this was possible. Do I need to change anything to get this to work with google docs?
Thanks

@ColinWyper

This comment has been minimized.

Copy link

commented Apr 18, 2018

Hi, anyone have any idea why I keep getting a "server error" when trying to save the trigger?

@mbrewster

This comment has been minimized.

Copy link

commented Apr 21, 2018

@ColinWyper I had to run it once manually first to do the authorization bits... after that it worked fine.

@vvthomasvv

This comment has been minimized.

Copy link

commented Apr 23, 2018

This works for me! But I would like to only have the values displayed instead of the formula. Does anyone know how that would work in this example. Note that I have multiple tabs in the sheet i would like to copy

@jsromeromnz

This comment has been minimized.

Copy link

commented May 16, 2018

Hi, I'm not familiar with programming. Any workaround to make this work on GDocs?

@arjunkarangiya

This comment has been minimized.

Copy link

commented Jun 23, 2018

Hi Abhijeet, I am getting #reference cells while making a backup copies of an automated (Refresh every hour from server input) google spreadsheet.
Can you help me out as in how i can fetch value only??

@cliu2017

This comment has been minimized.

Copy link

commented Jul 5, 2018

Hi Abhijeet,

Thank you for the script. It's working like a charm.
Do you have a code for getting the URL of the backup file and sending it via email to people?

Many thanks!

@sansrid

This comment has been minimized.

Copy link

commented Jul 19, 2018

Thanks for the code Abhijeet! I wanted daily backups. Let me see how it works tomorrow and try to remember n comment here!

@M4i4

This comment has been minimized.

Copy link

commented Jul 30, 2018

Hi, Abhijeet,

Thank you for this script. It's a really useful one. But how to get rid of it?

But here is what happened to me:

  • I have followed your guidelines and established a Backup. Everything went smoothly.

  • But for me it was just a test - to see if it works, if it can be done (I've done it on a test-file.xls).

  • So I've removed the whole thing (test-file.xls and a Backup folder) from Google Sheets.

  • What is going on now is that every 12 hours I get a backup copy of my test file (even though I've removed it from Google Shhets).

  • The test-file.xls and the Backup re still present in Google Drive.

  • I'm trying to open test-file.xls to delete the script that is responsible for the everyday backuping - but I cannot do it. Google Drive says: I cannot convert this file to Google Sheets.

Do you have any idea what could I do to delete everything?

Many thanks in advance,
Maia

@CleaverUK

This comment has been minimized.

Copy link

commented Sep 18, 2018

Amazing, very helpful thankyou!

@jklouse-rei

This comment has been minimized.

Copy link

commented Sep 21, 2018

Hi Abhijeet,

I set this up and the script works if I run it manually -- authorizes and everything, but the scheduled trigger always fails with the error:
typeError: Cannot call method "getName" of null. (line 12, file "sheetBackup")

Line 12 reads:
var name = SpreadsheetApp.getActiveSpreadsheet().getName() + "_Backup_" + formattedDate + " PST";

I think the issue is that the authorization is somehow falling off. In the meantime, I created a button to call the script that I can manually click in the spreadsheet, but I'd like to do nightly backups when no one is using it. When I delete and re-add the trigger, it requests to be authorized again.

Additionally, the script makes a backup of the script itself as attached to the spreadsheet. I assume there's nothing that can be done about that, but I thought I'd call that out. I don't know if there's a way to back up the sheet without backing up its attached scripts too.

Can you (or someone else) help me with the authorization issue, at least?

@KaranPTS

This comment has been minimized.

Copy link

commented Oct 4, 2018

Hi Abhijeet,
I did exactly same as beautifully n clearly shared by you..but unfortunately ..it is not working for me !
I am sharing a screen shot of error that i am getting!
Appreciate your help.
Thanks
error goole script

@AeneasLinden

This comment has been minimized.

Copy link

commented Oct 10, 2018

Hello!

Thank you for a great script!

However, I wonder if it is possible to only save certain tabs in spreadsheets and not the entire document?

Many thanks in advance :)

@yunhoong

This comment has been minimized.

Copy link

commented Dec 20, 2018

hi, thanks,. but google need to verified the app, pls help

hi. i am having the same problem. is yours solved?

script unverified

@zakstern

This comment has been minimized.

Copy link

commented Jan 14, 2019

hi, thanks,. but google need to verified the app, pls help

hi. i am having the same problem. is yours solved?

script unverified

Click "Advanced" at the bottom and then click "continue..." that appears at the bottom.

@brokensandals

This comment has been minimized.

Copy link

commented Jan 23, 2019

Thanks so much for this script & the detailed setup instructions! This helped me build a script for doing automatic backups of all docs/sheets/slides in an account: https://gist.github.com/brokensandals/6b77f73666323d6e4b94ff1df12a532a

@praveenrajaag

This comment has been minimized.

Copy link

commented Apr 18, 2019

Hi Abhijeet,

Thank you for the backup script. Do you have script for deleting the old back after certain time.

Thanks
Praveen

@praveenrajaag

This comment has been minimized.

Copy link

commented May 9, 2019

Hi

Can anyone help me on deleting the old backup after certain time.

Thanks
Praveen

@eytan7raycatch

This comment has been minimized.

Copy link

commented Jul 27, 2019

Hello - I am having trouble with the code, line 20
I won't allow me to use the line I copied from the code and I changed it to saveAs and Folder, but still not.
Does anyone have ideas?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.