将值和格式从工作表复制到新的谷歌电子表格文档? [英] Copy value and format from a sheet to a new google Spreadsheet document?

问题描述

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

I need to copy a sheet on google SpreadSheet to another SpreadSheet document.

我已经完成了我的研究，我发现了两种方法可以做到这一点，但是都存在我不知道如何解决的问题.

I´ve done my research, and I found two methods that do this, however both have problems that I don´t know how to fix.

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

The first method copies the sheet with format, but it keeps the referenced cells, so I get a reference error in the new document (#ref). I need a function that copies the format and the values (not references).

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

   sheet.copyTo(destination);
}

第二种方法复制没有引用的值，但它只复制值，没有格式.

This second method copies the values without references, however it copies only values, without format.

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

}

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

How do I write a function that keeps the format and copies the values?

推荐答案

既然你似乎知道如何使用整个数据范围来获取和设置值，那么只需使用其他方法来获取和设置所有其他参数.

Since you seem to know how to get and set values using the whole data range, just use the other methods to get and set all the other parameters.

>

脚本编辑器自动完成在这种情况下是一个很大的帮助，不要忘记一个！

The script editor autocomplete is a great help in this case to try not to forget one !

我希望列表是完整的，虽然写起来有点痛苦！

I hope the list is complete, it is a bit of a pain to write though !.

下面的代码，如果其中一个对您没有用，只需将其删除(在两个方向(设置和获取)).

code below, if one of them is not useful to you just delete it (in both directions (set and get).

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 的回答和您的评论让我想到了另一种解决方案，它更简单，也可以处理合并的单元格.这是代码:

Edit :

Bryan's answer and your comment made me think of another solution, much more simple and that handles merged cells as well. Here is the code :

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 结构很容易做到.

In both script be sure that the destination sheet name does not already exist. I didn't handle that case although it is quite easy to do with a try/catch structure.

这篇关于将值和格式从工作表复制到新的谷歌电子表格文档?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！