copyTo using {contentsOnly:true} not working

Question

As a caveat, I am very new to Google Apps scripting. I appreciate any assistance that can be provided.

I am trying to copy the contents of a sheet into a new document. This code works without any problem:

// Create a new Spreadsheet and copy the current sheet into it.
var newSpreadsheet = SpreadsheetApp.create("Spreadsheet to export");
var projectname = SpreadsheetApp.getActiveSpreadsheet();
sheet = originalSpreadsheet.getActiveSheet();
sheet.copyTo(newSpreadsheet);

However, this copies over the formulas from the current sheet - I am trying to copy the values only, as the formulas reference data on other sheets in the original document.

My attempt to do this is as follows:

// Create a new Spreadsheet and copy the current sheet into it.
var newSpreadsheet = SpreadsheetApp.create("Spreadsheet to export");
var projectname = SpreadsheetApp.getActiveSpreadsheet();
sheet = originalSpreadsheet.getActiveSheet();
sheet.copyTo(newSpreadsheet, {contentsOnly:true})

However, this generates the following error: Cannot find method (class)copyTo($Proxy914,object).

I am unsure what I am doing wrong. Any assistance would be appreciated. Thanks in advance!

Answer 1

There are actually 2 copyTo, one applies to sheet and the other applies to Range

According to the documentation (see links above), the second one has optional argument to copy values only while the first has not.

What I guess you could do is use the Range.copyTo() to copy the whole sheet range to a temporary sheet (in the same spreadsheet) and then copy that temporary sheet to the other spreadsheet and finally delete the temporary sheet from the source spreadsheet.

Hoping it is clear enough ;-)

Answer 2

In the script gallery there is a script called spreadsheetFrozenBackup, through which a copy of a spreadsheet can be made.

The range.copyTo that Serge alludes to is used.

It is not a long script, so I reproduce it here for information:

// Make copy of current spreadsheet with backup name.                  
function spreadsheetFrozenBackup() {

  // Get current spreadsheet.
  var ss = SpreadsheetApp.getActiveSpreadsheet();                     

  // Name the backup spreadsheet with date.
  var bssName = ss.getName() + " frozen at " + Utilities.formatDate(new Date(), "GMT", "yyyyMMdd HHmmss");
  var bs = SpreadsheetApp.openById((DocsList.copy(DocsList.getFileById(ss.getId()), bssName)).getId());

  // Make sure all the formulae have been evaluated...                     
  SpreadsheetApp.flush();

  // Get all the sheets in the spreadsheet
  var bsl = bs.getSheets();    

  var pl = "";
  for (var i = 0; i < bsl.length; i++) {
    bsl[i].getDataRange().copyTo(bsl[i].getDataRange(), {contentsOnly:true});

    pl = pl + " " + bsl[i].getName();
    SpreadsheetApp.getActiveSpreadsheet().toast(pl, "Processed Sheets");
  }  
  SpreadsheetApp.getActiveSpreadsheet().toast(bssName, "Frozen Backup");
}