Google App Script实现仅粘贴值到新表格的问题求助

The core issue with your original code is that Sheet.copyTo() copies the entire sheet including formulas, and the parameters you tried (contentsOnly, CopyPasteType.PASTE_VALUES) are not valid for this method—they belong to the Range.copyTo() method instead. Here's a straightforward fix that preserves your sheet structure while replacing formulas with their calculated results:

Modified Code #

function CopyToSpreadSheet() {
  // Source sheet setup
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sourceSheet = ss.getSheetByName("MTS");
  
  // Generate archive name with timestamp
  var formattedDate = Utilities.formatDate(new Date(), "GMT", "yyyy-MM-dd' 'HH:mm:ss");
  var name = ss.getName() + " Archive " + formattedDate;
  
  // Create new spreadsheet and move to target folder
  var newss = SpreadsheetApp.create(name);
  var newss_id = newss.getId();
  DriveApp.getFileById(newss_id).moveTo(DriveApp.getFolderById('1EUFJcySQ_Mv8qD8OzYUX1QniXQy2gjw0'));
  
  // Open target spreadsheet
  var target = SpreadsheetApp.openById(newss_id);
  
  // Copy source sheet to target (preserves formatting and structure)
  var targetSheet = sourceSheet.copyTo(target);
  targetSheet.setName(name);
  
  // Replace formulas with calculated values
  var dataRange = targetSheet.getDataRange();
  dataRange.setValues(dataRange.getValues());
  
  // Optional: Delete default "Sheet1" from new spreadsheet
  var defaultSheet = target.getSheetByName("Sheet1");
  if (defaultSheet) {
    target.deleteSheet(defaultSheet);
  }
  
  return;
}

Key Changes Explained #

1. Copy the sheet first: sourceSheet.copyTo(target) still copies the entire sheet (including formatting, column widths, row heights) but retains formulas initially.
2. Replace formulas with values:
   • dataRange.getValues() extracts the calculated results of all cells (ignoring formulas).
   • dataRange.setValues(...) writes these static values back to the sheet, overwriting the original formulas.
3. Optional cleanup: Removes the default "Sheet1" that's automatically created with new spreadsheets for a cleaner archive.

Alternative Approach (Copy Range Directly) #

If you prefer to build the sheet from scratch instead of copying first, you can copy values and formatting explicitly:

function CopyToSpreadSheetAlternative() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sourceSheet = ss.getSheetByName("MTS");
  var formattedDate = Utilities.formatDate(new Date(), "GMT", "yyyy-MM-dd' 'HH:mm:ss");
  var name = ss.getName() + " Archive " + formattedDate;
  
  var newss = SpreadsheetApp.create(name);
  var newss_id = newss.getId();
  DriveApp.getFileById(newss_id).moveTo(DriveApp.getFolderById('1EUFJcySQ_Mv8qD8OzYUX1QniXQy2gjw0'));
  
  var target = SpreadsheetApp.openById(newss_id);
  var targetSheet = target.insertSheet(name);
  
  // Copy values and formatting
  var sourceRange = sourceSheet.getDataRange();
  var targetRange = targetSheet.getRange(1, 1, sourceRange.getNumRows(), sourceRange.getNumColumns());
  
  targetRange.setValues(sourceRange.getValues());
  targetRange.setNumberFormats(sourceRange.getNumberFormats());
  targetRange.setBackgrounds(sourceRange.getBackgrounds());
  targetRange.setFontFamilies(sourceRange.getFontFamilies());
  
  // Copy column widths and row heights
  for (var i = 1; i <= sourceSheet.getMaxColumns(); i++) {
    targetSheet.setColumnWidth(i, sourceSheet.getColumnWidth(i));
  }
  for (var i = 1; i <= sourceSheet.getMaxRows(); i++) {
    targetSheet.setRowHeight(i, sourceSheet.getRowHeight(i));
  }
  
  // Delete default sheet
  var defaultSheet = target.getSheetByName("Sheet1");
  if (defaultSheet) target.deleteSheet(defaultSheet);
  
  return;
}

Content of the question来源于stack exchange，提问作者Nelerdeth