复制格式与Google表格文件中的值范围 [英] Copy format with the range of values from the google sheet file

问题描述

我使用了以下代码，它正在执行以下操作: ->自动创建新的Google工作表，复制所需列的数据，并将其重命名为上个月的名称.

I have used the following code, It is doing following actions : -> Autocreating the new google sheet, Copying the data of the required columns, Renaming it to the previous month name.

function Copy() {
var sss = SpreadsheetApp.openById('1IK6YsZS3_NNAlFA41d8uKriPzfZs_2Ukyh94eeBFj40'); //replace with source ID
var ss = sss.getSheetByName('CURRENT'); //replace with source Sheet tab name
var range = ss.getRange('A1:H45'); //assign the range you want to copy
var data = range.getValues();
var d = new Date();
d.setMonth(d.getMonth() - 1);
var prevMonth = Utilities.formatDate(d, 'GMT+1','MMMM-yyyy'); //rename sheet to previous month

var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var yourNewSheet = activeSpreadsheet.getSheetByName(prevMonth);

if (yourNewSheet != null) {
    activeSpreadsheet.deleteSheet(yourNewSheet);
}
yourNewSheet = activeSpreadsheet.insertSheet();
yourNewSheet.setName(prevMonth);
yourNewSheet.getRange(yourNewSheet.getLastRow()+1,1,45,8).setValues(data);
}

唯一缺少的是，它没有复制要复制的原始格式.格式正在更改，例如行号，持续时间等.这是原始文件和新文件的屏幕截图.

Only thing missing is that , its not copying the original format from which it is copied. Format is getting changed like line number, duration etc. Here are screenshots of original and new file.

这是原始的，

然后将其复制一个.

And this is copied one.

推荐答案

我相信您可能会使原始代码中的某些事情变得过于复杂.如果您想要将工作表(包含其内容的全部和格式)复制到另一个电子表格中，则可以简单地使用sheet.copyTo(spreadsheet)函数.这样，您甚至不需要使用Range.您必须按如下所示重构代码:

I believe that you may be overcomplicating a few things in your original code. If what you want is to copy a sheet (with all of its contents and formatting) into another Spreadsheet, you can simply use the sheet.copyTo(spreadsheet) function. This way, you will not even need to work with the Range's. You have to refactor the code as follows:

function Copy() {
  var sss = SpreadsheetApp.openById('1IK6YsZS3_NNAlFA41d8uKriPzfZs_2Ukyh94eeBFj40');
  var ss = sss.getSheetByName('CURRENT');

  var d = new Date();
  d.setMonth(d.getMonth() - 1);
  var prevMonth = Utilities.formatDate(d, 'GMT+1', 'MMMM yyyy');
  var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();

  // Check whether "sheet backup" already exists for that month
  if (activeSpreadsheet.getSheetByName(prevMonth) !== null) return;

  var yourNewSheet = ss.copyTo(activeSpreadsheet);
  yourNewSheet.setName(prevMonth);
}

编辑

为了具体化"公式(将其值复制到电子表格，而不是公式本身)，您可以执行以下操作:

Edit

In order to "materialise" the formulas (copy its values to the spreadsheet, rather than the formulas themselves) you can do the following:

在复制工作表之后(如前所述，使用copyTo())，您只需使用原始工作表中的getValues()和目标工作表中的setValues()覆盖内容.您的最终代码如下所示:

After copying the sheet (using copyTo() as previously explained), you simply overwrite the contents using getValues() in the original sheet and setValues() in the destination sheet. Your final code would look like the following:

function Copy() {
  var sss = SpreadsheetApp.openById('1IK6YsZS3_NNAlFA41d8uKriPzfZs_2Ukyh94eeBFj40');
  var ss = sss.getSheetByName('CURRENT');

  var d = new Date();
  d.setMonth(d.getMonth() - 1);
  var prevMonth = Utilities.formatDate(d, 'GMT+1', 'MMMM yyyy');
  var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();

  // Check whether "sheet backup" already exists for that month
  if (activeSpreadsheet.getSheetByName(prevMonth) !== null) return;

  var yourNewSheet = ss.copyTo(activeSpreadsheet);
  yourNewSheet.setName(prevMonth);

  var dataValues = ss.getDataRange().getValues(); // NEW
  yourNewSheet.getDataRange().setValues(dataValues); // NEW
}

这篇关于复制格式与Google表格文件中的值范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！