问如何在Google脚本中创建电子表格备份时复制格式和值，而不是公式？
EN

Stack Overflow用户

提问于 2019-10-06 18:50:25

回答 1查看 2.2K关注 0票数 1

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

有些工作表有=IMPORTRANGE，如果先复制电子表格，然后复制同一范围内的值以消除公式，则会出现问题，因为新的电子表格需要访问函数中间导入的范围(这将导致A1中的#REF )。

这个问题已经问了很多次了，但似乎没有一个答案能解决这个问题。我看过这些问题(实际上还有更多问题)：链接1、链接2、链接3、链接4、链接5和链接6。我试着从这些问题的答案或这些问题的组合中实现片段，但没有结果。我测试过的每一个片段都很难追踪。

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

下面是我目前使用的脚本，它成功地创建了一个仅包含值的整个电子表格的副本。我想不起来我是从哪一个问题中得到的。

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

源电子表格有许多格式，包括合并单元格、条件格式等，这将是理想的复制。我还可以将某些表硬编码到脚本中，如果这与另一种方法有任何不同的话。

简而言之:我正在寻找一个冻结备份的电子表格与多个工作表。值和格式需要复制，而不是公式。

我可能在所有这些问题的答案中遗漏了什么，所以我会继续尝试。同时，如能提供任何帮助/指导，将不胜感激。提前感谢！

解决了：@Tanaike下面的第二个示例脚本是一个很好的解决方法，可以复制具有其他电子表格引用的工作表，因此需要在被值覆盖之前访问它们。非常感谢“Tanaike”对此的广泛帮助--非常感谢。

google-apps-script

google-sheets

语音识别特惠，低至14.9元！

提供业界非常具有性价比的语音识别服务，超高识别准确率，适用多场景

回答 1

Stack Overflow用户

回答已采纳

发布于 2019-10-06 21:34:15

您希望将电子表格中所有工作表的值和格式复制到新的电子表格中。
- 在这种情况下，您希望只复制没有公式的值。
- The source spreadsheet has a lot of formatting with merged cells, conditional formatting, etc.
- 源电子表格包括与IMPORTRANGE一起放置的值。

您希望使用Google脚本来实现这一点。

上述目标的示例脚本流程如下所示。

流动：

将源电子表格中的所有工作表复制为时态表。
在复制的工作表中，单元格仅被值覆盖。这样，公式就可以被删除。
- 如果复制了源电子表格，则用IMPORTRANGE检索的值将变为#REF。因为它需要在新复制的电子表格上授权。为了避免这种情况，在源电子表格中复制时态表。

复制源电子表格。
删除源电子表格中的时态表。
删除目标电子表格中的原始工作表。

通过上述流程，只有值可以在没有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));
    }
  });
}