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
Copy link

mudithae 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

Loading

@mghall25
Copy link

mghall25 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

Loading

@lavin80r
Copy link

lavin80r commented May 26, 2017

Amazing, thanks!

Loading

@aaritdahiya
Copy link

aaritdahiya 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.

Loading

@AWHF
Copy link

AWHF 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~!

Loading

@nlhamsley
Copy link

nlhamsley 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!

Loading

@jongmedellin
Copy link

jongmedellin commented Aug 16, 2017

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

Loading

@mefryar
Copy link

mefryar 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

Loading

@kodopik
Copy link

kodopik commented Dec 2, 2017

Many thanks, works fine

Loading

@gaybro8777
Copy link

gaybro8777 commented Dec 4, 2017

Outstanding work man. Very generous of you.

Loading

@dekadentno
Copy link

dekadentno commented Dec 7, 2017

Thank you for this tutorial! Cheers

Loading

@anthonyJRI
Copy link

anthonyJRI commented Jan 12, 2018

Thanks man, very useful

Loading

@AlasdairSnow
Copy link

AlasdairSnow 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

Loading

@ColinWyper
Copy link

ColinWyper commented Apr 18, 2018

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

Loading

@brewsteropsdev
Copy link

brewsteropsdev commented Apr 21, 2018

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

Loading

@vvthomasvv
Copy link

vvthomasvv 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

Loading

@jsromeromnz
Copy link

jsromeromnz commented May 16, 2018

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

Loading

@arjunkarangiya
Copy link

arjunkarangiya 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??

Loading

@cliu2017
Copy link

cliu2017 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!

Loading

@sansrid
Copy link

sansrid 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!

Loading

@M4i4
Copy link

M4i4 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

Loading

@CleaverUK
Copy link

CleaverUK commented Sep 18, 2018

Amazing, very helpful thankyou!

Loading

@jklouse-rei
Copy link

jklouse-rei 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?

Loading

@KaranPTS
Copy link

KaranPTS 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

Loading

@AeneasLinden
Copy link

AeneasLinden 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 :)

Loading

@yunhoong
Copy link

yunhoong 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

Loading

@zakstern
Copy link

zakstern 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.

Loading

@brokensandals
Copy link

brokensandals 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

Loading

@praveenrajaag
Copy link

praveenrajaag 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

Loading

@praveenrajaag
Copy link

praveenrajaag commented May 9, 2019

Hi

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

Thanks
Praveen

Loading

@eytan7raycatch
Copy link

eytan7raycatch 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?

Loading

@MauriceSnell
Copy link

MauriceSnell commented Mar 27, 2020

I am having the same authentication issue above, and google seems to say that these services that require authentication can't be used from a time trigger. Have they changed the security and stopped this working? Are other people still getting this to work now?

Loading

@sudevschiz
Copy link

sudevschiz commented Apr 17, 2020

Is there a way to overwrite the backup copy instead of creating a new file?

My usecase is to provide a sort of "Mirror" of the original sheet to several users. So, the "backup" in this case should reflect in the same file. Any ideas?

Loading

@LudekCizinsky
Copy link

LudekCizinsky commented May 4, 2020

Thanks for sharing this. Great work!

Loading

@niddhogg
Copy link

niddhogg commented May 19, 2020

For those who have FORMS within the spreadsheet, you will have issues with forms copied again and again to the folder of the spreadsheet. So I have updated the script of the author.

What the script does is taking all "Copy of X" files and moving them to separate folder "Forms Backup" with a new folder specifically for backed up spreadsheet.

Make sure you don't run that in Root folder as it will move all "Copy of ..." files into backup folder. The best use is to have the main spreadsheet in separate folder.

Gist URL:
https://gist.github.com/niddhogg/b77033ce13288b18de1559869888bdf8

Loading

@dis-create
Copy link

dis-create commented May 25, 2020

Hey There! I want to create backups but when I'm going to tap script editor it's showing nothing..
Google Sheets
Pls Help!!
How can I create a back up of GOOGLE SHEETS???

Loading

@Horsewhisperer
Copy link

Horsewhisperer commented May 25, 2020

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

The ?ogsrc=32 is not part of the ID. The easiest way to get the ID is to go into Google drive in your browser and navigate to the folder. The ID appears in the address bar as follows:
https://drive.google.com/drive/u/0/folders/Folder_ID_Here

There should be no parameters following the ID (anything after and including a question mark in the address).

Loading

@d4tm
Copy link

d4tm commented May 25, 2020

Great script. Unfortunately, my backups open and reload current data for the cells with importhtml. Is there a way to "freeze" the data written to the backup file? I modified the script below and added it to the end of the backup script but just get a second copy of the backup. The original code overwrites all formulas with the cells' calculated values.
ORIGINAL
function freezeOutput(){
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Stats");
var range = sheet.getRange("A1:AA");
range.copyTo(range, {contentsOnly:true});
}
MODIFIED VERSION
function freezeOutput(){
var timeZone = new Date().getTimezoneOffset()/60; //added to get hour and minutes into file name
var formattedDate = Utilities.formatDate(new Date(), timeZone , "yyyy-MM-dd HH:mm");
var name1 = formattedDate + "_club_stats";
var spreadsheet = SpreadsheetApp.open(name1);
// var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(name1);
var range = sheet.getRange("A1:AA");
range.copyTo(range, {contentsOnly:true});
}

https://docs.google.com/spreadsheets/d/17uyVBfkI7i5sS3rI0rQT00Q6xl4b7nI_lXo3nFT_jCQ/edit?usp=sharing

Loading

@tis-andias
Copy link

tis-andias commented Jul 6, 2020

@AlasdairSnow and @jsromeromnz did you ever find out/work out the code for backing up google Docs? Your help will be much appreciated!

Loading

@d4tm
Copy link

d4tm commented Jul 6, 2020

I have been using Archive Data add-on from Mixed Analytics. It provides the ability to add a hour or day based snapshots of a sheet's current values into another selected sheet in the same doc. The values are added after the last column or row. I am using columns so I am able to archive well over a years's worth of daily snapshots my 24 column sheet. I added now() after the last column to keep track of the snapshot's date.

Loading

@qpackard
Copy link

qpackard commented Oct 28, 2020

Hi, thanks for your work, this is a great idea. I have a problem with the time, I want it corrected for my timezone, and with DST sometimes we're GMT-6 and the other half of the year we are GMT-7. I did see the work above of jklouse-rei and d4tm, but I'm not sure how to alter your orignial file. Obviously, I'm not particularly gifted when it comes to programming. Can you provide an alteration to the line to account for whichever timezone that person is in, which might work for everyone:

// 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");

I have altered, successfully, the above to GMT-6 and GMT-7, but then I would have to change the line twice a year, I would rather never have to alter it.
TIA

Loading

@richinatlanta
Copy link

richinatlanta commented Nov 5, 2020

Works great, but is there a way to just overwrite the file instead of having the date on this?

Loading

@spferras
Copy link

spferras commented Feb 16, 2021

Great, thanks for this

Loading

@MHarriss44
Copy link

MHarriss44 commented Mar 3, 2021

I have copied this and substituted my folderid correctly, but it fails But when I execute it I get
ReferenceError: file is not defined
makeCopy @ Code.gs:20

I have modified this code thus:
var destination = DriveApp.getFolderById("1ksDmM2Ks_vPQZJANXaHgw5E3JHKX6IKl");
var file = DriveApp.getFileById(SpreadsheetApp.getActiveSpreadsheet().getId())

But when I run under the debugger, both these variables are shown as {...} which presumably means undefined. Why can't either of these IDs be found?
MH

Loading

@MHarriss44
Copy link

MHarriss44 commented Mar 3, 2021

Whatever debug is telling me, the app is correctly executing and copying the sheet when I run it manually. I will see if it runs automatically.

Loading

@tsindiastore
Copy link

tsindiastore commented Apr 18, 2021

Back Script Works Fantastic!! Thanks a Ton!! God Bless!! You are the Best!! 😊🙏

Loading

@pbanigo
Copy link

pbanigo commented Apr 25, 2021

If you're using a Document file and not a spreadsheet,
Use DocumentApp.getActiveDocument() or you would get a TypeError

Loading

@jerrychong25
Copy link

jerrychong25 commented May 9, 2021

Thanks for the great sharing! It is working fine!

Loading

@youssef-s-ec
Copy link

youssef-s-ec commented May 18, 2021

It's an amazing solution and really helpful

Are there similar script for Zoho sheets ? Or any hint where to start from?

Thanks in advance

Loading

@Markyb09
Copy link

Markyb09 commented Jun 24, 2021

thank you so much, love you

Loading

@jacq-vaucan
Copy link

jacq-vaucan commented Jul 15, 2021

Thank you for this great script, was really helpful

Loading

@zsurat
Copy link

zsurat commented Sep 6, 2021

HI ABHIJEET SIR,

I'M HAVING ISSUE TO MY BACK UP SHEET. I USED IMPORT RANGE IN MY MASTER SHEET, SO I HAVE TO CHANGE IMPORT RANGE IN ALL MY BACKUP FILES.

master1

CAN YOU PLEASE HELP ME TO ADVICE OR ANYONE CAN HELP ME?

THANKS~!

Loading

@Yonatankh
Copy link

Yonatankh commented Nov 1, 2021

Awesome post and very helpful! Made some adjustments if someone wants to backup a google doc or google slides.

For Doc:


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 = DocumentApp.getActiveDocument().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(DocumentApp.getActiveDocument().getId())

// makes copy of "file" with "name" at the "destination"
file.makeCopy(name, destination);
}

For Slides:


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 = SlidesApp.getActivePresentation().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(SlidesApp.getActivePresentation().getId())

// makes copy of "file" with "name" at the "destination"
file.makeCopy(name, destination);
}

Loading

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment