copyTo using {contentOnly: true} doesn't work
As a caveat, I am very new to writing Google Apps. I appreciate any help that can be provided.
I am trying to copy the contents of a sheet into a new document. This code works without issue:
// 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 formulas from the current sheet - I am trying to copy the values only as reference data of formulas to other sheets in the original document.
My attempt at doing it looks like this:
// 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'm not sure what I am doing wrong. Any help would be appreciated. Thanks in advance!
source to share
Actually 2 copyTo
, one is applied to the sheet and the other is applied to the range
According to the documentation (see links above) the second has an optional argument to copy values only when the first does not.
What I can do is use Range.copyTo()
to copy the entire range of sheets to a temporary sheet (in the same spreadsheet) and then copy that temporary sheet to another spreadsheet and finally delete the temporary sheet from the table source.
Hoping this is clear enough ;-)
source to share
There is a script in the gallery script called spreadsheetFrozenBackup through which a copy of the spreadsheet can be made.
The .copyTo range referenced by Serge is used.
This 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");
}
source to share