How to copy format and values, not formulas, when creating a spreadsheet backup in Google Apps Script?

1
votes

The goal is to create frozen backups of a spreadsheet with multiple sheets. The backup spreadsheet needs to copy over values and format, but not the formulas.

Some of the sheets have =IMPORTRANGE, which becomes a problem if the spreadsheet is copied first and then the values copied over the same range, again, to get rid of formulas, because the new spreadsheet requires access to the imported range in the middle of the function (which leads to #REF in A1).

This question was clearly asked many times, but none of the answers seem to solve this issue. I have looked at these questions (and more, actually): Link 1, Link 2, Link 3, Link 4, Link 5, and Link 6. I tried implementing snippets from the answers to those questions or combinations of them to no avail. It became to difficult to track every bit of snippet I tested.

I tried using copyTo() either to copy entire sheets in a forEach function, copy with the options {contentsOnly:true} and {formatOnly:true}, but have been unsuccessful. Either copyTo() wants the ranges to be in the same spreadsheet/sheet or the getDataRange() doesn't match the backup range...

Here's the script I'm currently using, which successfully creates a copy of the entire spreadsheet with values only. I cannot recall from which question I got it from.

function copyEntireSpreadsheet(){
  var ss,ssName,sheet,sheetName,data,destination
  ss = SpreadsheetApp.openById("id").getSheets();
  ssName = SpreadsheetApp.openById("id").getName();
  destination = SpreadsheetApp.create(ssName + " - " + new Date().toLocaleString());

  for (var i = 0; i < ss.length; i++){
    sheet = ss[i];
    sheetName = sheet.getSheetName();
    data = sheet.getSheetValues(1, 1, sheet.getLastRow(), sheet.getLastColumn());
    destination.insertSheet(sheetName);
    destination.getSheets()[i+1].getRange(1, 1, sheet.getLastRow(), sheet.getLastColumn()).setValues(data);
  }

 destination.deleteSheet(destination.getSheetByName('Sheet1'));
}

The source spreadsheet has a lot of formatting with merged cells, conditional formatting, etc., which would be ideal to copy. I can also hard code certain sheets into the script, if that would make any difference with an alternative method.

In short: I'm looking for a frozen backup of a spreadsheet with multiple sheets. Values and formatting need to be copied, not the formulas.

I might be missing something in one of the answers to all those questions, so I'll keep trying. In the meantime, any help/direction would be appreciated. Thanks in advance!

SOLVED: @Tanaike's second sample script below is an excellent workaround to copying sheets that have references to other spreadsheets and hence require access to them before being overwritten by values only. Big thanks to @Tanaike for the extensive help with this -- much appreciated.

1
google-apps-scriptgoogle-sheets

1 Answers

3
votes
  • You want to copy the values and format of all sheets in a Spreadsheet to new Spreadsheet.
    • In this case, you want to copy only values without the formulas.
    • The source spreadsheet has a lot of formatting with merged cells, conditional formatting, etc.
    • The source Spreadsheet includes the values put with IMPORTRANGE.
  • You want to achieve this using Google Apps Script.

The flow of sample script for above goal is as follows.

Flow:

  1. Copy all sheets in the source Spreadsheet as the temporal sheets.
  2. At the copied sheets, the cells are overwritten by only the values. By this, the formulas can be removed.
    • If the source Spreadsheet is copied, the values retrieved with IMPORTRANGE become #REF. Because it is required to authorize at the new copied Spreadsheet. In order to avoid this, the temporal sheets are copied in the source Spreadsheet.
  3. Copy the source Spreadsheet.
  4. Delete the temporal sheets in the source Spreadsheet.
  5. Delete the original sheets in the destination Spreadsheet.

By above flow, only the values can be copied without the authorization of IMPORTRANGE.

Sample script:

function copyEntireSpreadsheet() {
  var 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));
    }
  });
}

References: