I'm looking for a way to automatically backup a google sheets file without the functions, only the numbers.
I have scavenged 2 scripts from various sources:
// 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("1ysgERhjmrnq5Uzb9Lu7CtqOWccVTHyVj");
// 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);
}
The other is:
function getRangeValues() {
var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange("A2:B4");
var values = range.getValues();
return values;
};
Data from multiple sheets files are pulled into 1 master file, and I wanna have backups every week of every single one of them, but problem is with the 'makeCopy' function the data pulled into the copy of the master file will be coming from the original sheets, because i'm using the importrange function which requires unique sheets ID, and the copy has another ID. How can I combine these 2 together?
Try this:
function makeCopy() {
var folder=DriveApp.getFolderById("1y66aE2WuaRQQM5fyevXEl5uhJamk9VF7");
var ss=SpreadsheetApp.getActive();
var file=DriveApp.getFileById(ss.getId());
var f=file.makeCopy(folder);
var copy=SpreadsheetApp.openById(f.getId());
var shts=copy.getSheets();
SpreadsheetApp.getUi().alert('Go to other sheet to authorize Import. Hit Okay after authorizing the Import');
shts.forEach(function(sh){
var rg=sh.getDataRange();
var dvA=rg.getValues();
sh.clearContents();
rg.setValues(dvA);
});
}
I think the problem was that we needed to authorize the import on the other sheet. Try it. See if it works on your spreadsheet.
Thanks! Tried it and after some tweaking it started to work, but there's something that works funny: The last part clear contents and setValues clears references in the original file, not the copy that's just been made. How can I work around this? I'll post the script I used in an answer down below so you can have a look! We're getting closer!
You're right I modified it and it's now removing the formulas on the copy.
Thanks! It's working now, the formulas in the copy are removed, but the data that was pulled into the original now doesn't exist there. I was using the 'IMPORTRANGE' fuction in the A1 cell and it did the rest, now there's a #REF in the A1 and the whole sheet is empty. I can think of a workaround by having the script look at the values inside the original file and replacing formulas and everything with pure raw values inside the copy. Do you think it can be done?
I didn't experience any loss of data or formulas in my example. Can you share a example copy of your spreadsheet?
This is it: docs.google.com/spreadsheets/d/… and this is the copied version: docs.google.com/spreadsheets/d/…