copyTo's advanced feature not working

0
votes

I am trying to create an automated template process. An individual fills in a unique ID in a specified cell and the sheet auto-populates with pre-created data related to that customer.

After that, the user activates the below code and it creates a new file for that customer, with the new file name as the name of the customer from inside the file. Note, that it is important that the function copies values and formatting, BUT NOT the formulas for this to work. Reason is, the formulas pull data from other sheets within the template. I only want one sheet created within the new customer workbook.

Here is the code:

function copy2() {

var folder=DriveApp.getFoldersByName("Customers").next();
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheets()[0,1];
 var cellWithFileName = ss.getRange("B3");
 var name = cellWithFileName.getValue();
var file=SpreadsheetApp.create(name);
 var fileID = file.getId()
var copyFile=DriveApp.getFileById(fileID);
 var destination = DriveApp.getFileById(fileID)
folder.addFile(copyFile);

DriveApp.getRootFolder().removeFile(copyFile);

ss.getSheetByName("Customer Details").copyTo(file,{formatOnly:true});

}

The code copies like a charm, but copies including the formulas (which I don't want!). Any attempt to use copyTo(destination, AdvancedOption) fails. I used the following: ss.getSheetByName("Customer Details").copyTo(file,{formatOnly:true}) format, and it gives an error: "cannot find method copyTo"...what am I doing wrong?

1
javascriptgoogle-apps-scriptgoogle-sheets

1 Answers

0
votes

How about this answer?

Pattern 1 :

You want "Clears the sheet of contents, while preserving formatting information". If my understanding is correct, you can achieve it using clearContents(). So how about the following modification?

From :

ss.getSheetByName("Customer Details").copyTo(file,{formatOnly:true});

To :

ss.getSheetByName("Customer Details").copyTo(file).clearContents();

Pattern 2 :

If you want values and formats, you can modify as follows. After the active sheet was copied, the copied sheet is overwritten by the values. By this, the formulas are removed.

From :

ss.getSheetByName("Customer Details").copyTo(file,{formatOnly:true});

To :

var newsheet = ss.getSheetByName("Customer Details").copyTo(file);
var srcrng = ss.getDataRange();
var values = srcrng.getValues(); //  or srcrng.getDisplayValues()
newsheet.getRange(srcrng.getA1Notation()).setValues(values);

References :

If I misunderstand your question, please tell me. I would like to modify.