将值和格式从表格复制到新的Google Spreadsheet文档? [英] 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.

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