Warm tip: This article is reproduced from serverfault.com, please click

Numbers only Google Sheets automated backup

发布于 2019-12-25 01:56:56

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?

Questioner
haephaistoss
Viewed
0
Cooper 2019-12-26 07:37:14

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.