How can my frozen backup script be improved to avoid timeout?

1
votes

I have a frozen backup script that works (below), but it times out due to the high number of rows and formulas. The script is to create a frozen backup with just the values (no formulas) of the entire spreadsheet (or some sheets). I'm trying to improve it by not copying the sheets with formulas to the new spreadsheet, but only copying the "frozen" copies of those sheets. Any help / guidance would be appreciated.

The script below works just fine with a spreadsheet that does not have too many rows and formulas. I've tried to modify it by using SpreadsheetApp.create instead of .copy to create a blank spreadsheet, and then trying to come up with how to use .forEach method with an if statement to identify _temp sheets and only copy them. I've been stuck for a while, hence this question.

The current script is:

function copyEntireSpreadsheet() {
  var id = "ID"; // Please set the source Spreadsheet ID.

  var ss = SpreadsheetApp.openById(id);
  var srcSheets = ss.getSheets();
  var tempSheets = srcSheets.map(function(sheet, i) {
    var sheetName = sheet.getSheetName();
    var dstSheet = sheet.copyTo(ss).setName(sheetName + "_temp");
    var src = dstSheet.getDataRange();
    src.copyTo(src, {contentsOnly: true});
    return dstSheet;
  });
  var destination = ss.copy(ss.getName() + " - " + new Date().toLocaleString());
  tempSheets.forEach(function(sheet) {ss.deleteSheet(sheet)});
  var dstSheets = destination.getSheets();
  dstSheets.forEach(function(sheet) {
    var sheetName = sheet.getSheetName();
    if (sheetName.indexOf("_temp") == -1) {
      destination.deleteSheet(sheet);
    } else {
      sheet.setName(sheetName.slice(0, -5));
    }
  });
}

The script above times out because it takes too long in the new (copied) spreadsheet to process all cells with formulas, but there should be no need to copy them in the first place.

The script above works by:

  1. Creating copies of all sheets and renaming with the addition of "_temp".
  2. Rewrites each "_temp" sheet over on itself with the parameter contentsOnly: true so that formulas become values.
  3. Creates a copy of the entire spreadsheet.
  4. Deletes the "_temp" sheets from the source spreadsheet.
  5. Deletes the original sheets from the destination spreadsheet.

What I'd like to accomplish is:

  1. Create copies of all sheets ("_temp") and rewrite on themselves to get rid of formulas. (1 & 2 above)
  2. Create a blank spreadsheet (new)
  3. Copy only "_temp" sheets into the new spreadsheet (new)
  4. Delete "_temp" sheets in source spreadsheet (4 above)

If I can modify it so that I can identify which sheets should be copied, that'd be even better, but a simple copy of all _temp sheets will also do the job.

Thanks in advance for any help!

Final Script

@Tanaike's answer was quick and simple - worked perfectly! Added .slice to remove "_temp" text from the new copied sheets. Here's the final script for anyone looking for a similar script:

function copyEntireSpreadsheetTest() {
  var id = "ID"; // Please set the source Spreadsheet ID.

  var ss = SpreadsheetApp.openById(id);
  var srcSheets = ss.getSheets();
  var tempSheets = srcSheets.map(function(sheet, i) {
    var sheetName = sheet.getSheetName();
    var dstSheet = sheet.copyTo(ss).setName(sheetName + "_temp");
    var src = dstSheet.getDataRange();
    src.copyTo(src, {contentsOnly: true});
    return dstSheet;
  });

  var destination = SpreadsheetApp.create(ss.getName() + " - " + new Date().toLocaleString());
  tempSheets.forEach(function(sheet) {
    sheet.copyTo(destination).setName(sheet.getSheetName().slice(0,-5));
    ss.deleteSheet(sheet);
  });
  destination.deleteSheet(destination.getSheets()[0]);
}
1
google-apps-scriptgoogle-sheets

1 Answers

1
votes

  • You want to achieve the following flow by modifying your script.

    1. Create copies of all sheets ("_temp") and rewrite on themselves to get rid of formulas. (1 & 2 above)
    2. Create a blank spreadsheet (new)
    3. Copy only "_temp" sheets into the new spreadsheet (new)
    4. Delete "_temp" sheets in source spreadsheet (4 above)

If my understanding is correct, how about this modification?

From:

var destination = ss.copy(ss.getName() + " - " + new Date().toLocaleString());
tempSheets.forEach(function(sheet) {ss.deleteSheet(sheet)});
var dstSheets = destination.getSheets();
dstSheets.forEach(function(sheet) {
  var sheetName = sheet.getSheetName();
  if (sheetName.indexOf("_temp") == -1) {
    destination.deleteSheet(sheet);
  } else {
    sheet.setName(sheetName.slice(0, -5));
  }
});

To:

var destination = SpreadsheetApp.create(ss.getName() + " - " + new Date().toLocaleString());
tempSheets.forEach(function(sheet) {
  sheet.copyTo(destination).setName(sheet.getSheetName());
  ss.deleteSheet(sheet);
});
destination.deleteSheet(destination.getSheets()[0]);

Reference:

If I misunderstood your question and this was not the result you want, I apologize.