在Google Apps脚本中创建电子表格备份时，如何复制格式和值，而不是公式? [英] How to copy format and values, not formulas, when creating a spreadsheet backup in Google Apps Script?

问题描述

目标是创建具有多个工作表的电子表格的冻结备份.备份电子表格需要复制值和格式，而不是公式.

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.

某些工作表具有=IMPORTRANGE，如果先复制电子表格，然后再复制在相同范围内复制的值以摆脱公式，这将成为一个问题，因为新电子表格需要访问导入的范围在函数中间(导致A1中的#REF).

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

这个问题已经被问了很多遍了，但是似乎没有一个答案能解决这个问题.我看过这些问题(实际上还有更多):链接1 ，链接2 ，链接3 ，链接4 ，链接6 .我尝试从这些问题的答案或它们的组合中实施代码段，但无济于事.跟踪我测试过的每一段代码变得很困难.

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.

我尝试使用copyTo()在forEach函数中复制整张纸，并使用选项{contentsOnly:true}和{formatOnly:true}复制，但是没有成功. copyTo()希望范围位于同一电子表格/工作表中，或者getDataRange()与备份范围不匹配...

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!

已解决:@ Tanaike的以下第二个示例脚本是一种很好的解决方法，可复制引用了其他电子表格的工作表，因此需要在之前对其进行访问，否则仅被值覆盖.非常感谢@Tanaike对此提供的广泛帮助-非常感谢.

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.

推荐答案

• 您想要将电子表格中所有表格的值和格式复制到新的电子表格中.
  • 在这种情况下，您只想复制没有公式的值.
  • The source spreadsheet has a lot of formatting with merged cells, conditional formatting, etc.
  • 源电子表格包含IMPORTRANGE放置的值.
  • 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.

    用于上述目标的示例脚本的流程如下.

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

    1. 将源电子表格中的所有工作表复制为临时工作表.
    2. 在复制的图纸上，单元格仅被值覆盖.这样，可以删除公式.
       • 如果复制了源电子表格，则使用IMPORTRANGE检索的值将变为#REF.因为需要在新复制的电子表格上进行授权.为了避免这种情况，将临时表复制到源Spreadsheet中.
    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.

    通过以上流程，未经IMPORTRANGE的授权，只能复制值.

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

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

    参考文献:

    • 副本(名称)
    • copyTo(目标，选项)

    References:

    • copy(name)
    • copyTo(destination, options)

    这篇关于在Google Apps脚本中创建电子表格备份时，如何复制格式和值，而不是公式?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！