google-apps-script - 将值和格式从工作表复制到新的谷歌电子表格文档？

Question

我需要将 google SpreadSheet 上的工作表复制到另一个 SpreadSheet 文档。

我做了研究，发现了两种方法可以做到这一点，但是两者都有我不知道如何解决的问题。

第一种方法复制带有格式的工作表，但它保留引用的单元格，因此我在新文档中收到引用错误 (#ref)。我需要一个复制格式和值(而不是引用)的函数。

function copySheet() {
   var source = SpreadsheetApp.getActiveSpreadsheet();
   var sheet = source.getSheets()[0];
   var destination = SpreadsheetApp.openById("15ucPbZrIYXZAOCYVdpK6OA0oyQT1NcsmuiJmDRfdpHQ");

   sheet.copyTo(destination);
}

第二种方法复制不带引用的值，但它仅复制值，不带格式。

function copySheetValues()
{
  var source = SpreadsheetApp.getActiveSheet();
  var sourcename = source.getSheetName();
  var sourceDataRange = source.getDataRange();
  var sourceSheetValues = sourceDataRange.getValues();
  var sourceRows = sourceDataRange.getNumRows();
  var sourceColumns = sourceDataRange.getNumColumns();

  var destination = SpreadsheetApp.openById('15ucPbZrIYXZAOCYVdpK6OA0oyQT1NcsmuiJmDRfdpHQ');
  destination.insertSheet(sourcename, 0);
  destination.getDataRange().offset(0, 0, sourceRows, sourceColumns).setValues(sourceSheetValues);

}

如何编写一个保留格式并复制值的函数？

Answer 1

由于您似乎知道如何使用整个数据范围获取和设置值，因此只需使用其他方法来获取和设置所有其他参数即可。

在这种情况下，脚本编辑器自动完成功能非常有用，可以避免忘记!

我希望这个列表是完整的，不过写起来有点痛苦!

下面的代码，如果其中之一对您没有用，只需将其删除(双向(设置和获取)。

function copySheetValues(){
  var source = SpreadsheetApp.getActiveSheet();
  var sourcename = source.getSheetName();
  var sValues = source.getDataRange().getValues();
  var sBG = source.getDataRange().getBackgrounds();
  var sFC = source.getDataRange().getFontColors();
  var sFF = source.getDataRange().getFontFamilies();
  var sFL = source.getDataRange().getFontLines();
  var sFFa = source.getDataRange().getFontFamilies();
  var sFSz = source.getDataRange().getFontSizes();
  var sFSt = source.getDataRange().getFontStyles();
  var sFW = source.getDataRange().getFontWeights();
  var sHA = source.getDataRange().getHorizontalAlignments();
  var sVA = source.getDataRange().getVerticalAlignments();
  var sNF = source.getDataRange().getNumberFormats();
  var sWR = source.getDataRange().getWraps();

  var destination = SpreadsheetApp.openById('15ucPbZrIYXZAOCYVdpK6OA0oyQT1NcsmuiJmDRfdpHQ');
  var destinationSheet = destination.insertSheet(sourcename, 0);
  destinationSheet.getRange(1,1,sValues.length,sValues[0].length).setValues(sValues)
  .setBackgrounds(sBG)
  .setFontColors(sFC)
  .setFontFamilies(sFF)
  .setFontLines(sFL)
  .setFontFamilies(sFFa)
  .setFontSizes(sFSz)
  .setFontStyles(sFSt)
  .setFontWeights(sFW)
  .setHorizontalAlignments(sHA)
  .setVerticalAlignments(sVA)
  .setNumberFormats(sNF)
  .setWraps(sWR);
}

编辑:

Bryan 的回答和您的评论让我想到了另一个解决方案，该解决方案更加简单，并且还可以处理合并的单元格。这是代码:

function copySheetValuesV2(){
  var source = SpreadsheetApp.getActiveSheet();
  var sourceName = source.getSheetName();
  var sValues = source.getDataRange().getValues();
  var destination = SpreadsheetApp.openById('15ucPbZrIYXZAOCYVdpK6OA0oyQT1NcsmuiJmDRfdpHQ');
  source.copyTo(destination)
  var destinationSheet = destination.getSheetByName('Copy of '+sourceName)
  destinationSheet.getRange(1,1,sValues.length,sValues[0].length).setValues(sValues);// overwrite all formulas that the copyTo preserved
}

在这两个脚本中，请确保目标工作表名称不已存在。我没有处理这种情况，尽管使用 try/catch 结构很容易做到这一点。