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

Hi Abhijeet,

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

Thanks
Praveen

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

Thanks
Praveen

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?

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?

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?

Thanks for sharing this. Great work!

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

Hey There! I want to create backups but when I'm going to tap script editor it's showing nothing..

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

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

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

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

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.

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

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

Great, thanks for this

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

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.

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

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

Thanks for the great sharing! It is working fine!

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

thank you so much, love you

Thank you for this great script, was really helpful

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.

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

THANKS~!

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);
}

I implemented the code exactly as described and i am getting this error:
Exception: We're sorry, a server error occurred. Please wait a bit and try again. (line 14, file "Code")

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

I have only the following items in the "Tools" menu:

• Creates new form
• Spelling >
• Auto-complete >
• Notification rules
• Accessibility

@Korb: The menu option for Script Editor is changed in the latest version of Google Sheets. Script editor is now called Apps Script and is under "Extensions" --> "Apps Script"

HEyhey, thanks for your Script. I am pretty sure that i did it all as mentioned above.. but when i run the function it just gives me "Execution started" but never ever finishes.. i never had one successful backup. Any one else having this issue?