CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
transit54
transit54

Reputation: 61

copyTo using {contentsOnly:true} not working

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!

Upvotes: 2

Views: 15335

Answers (2)

Serge insas
Serge insas

Reputation: 46812

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.

You could use the Range.copyTo() to copy the whole sheet range to a temporary sheet (in the same spreadsheet), then copy that temporary sheet to the other spreadsheet, and finally delete the temporary sheet from the source spreadsheet.

Upvotes: 7

David Tew
David Tew

Reputation: 1471

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

Upvotes: 0

Related Questions