So I have a very simple function that copies a tab in a specific spreadsheet to a different spreadsheet. However, the data is not copying properly and the top-left cell says "Ref" error and when I hover my mouse over it, it says "Error: Unresolved sheet name 'Submission Status'."

Now, I know this is an error with formulas in the origin sheet because I tried copying a sheet without formulas and it works fine. The main reason I'm not copying it as a 2D array is because I want to maintain the formatting in the origin sheet (running a loop through all the cells after pasting an array won't work because it's a rather large amount of data)

Anyone have any thoughts on a possible work-around? I don't care about the formulas, only the contents & formatting in the cells.

My function:

function copySheetFunction () {

let copiedSheet = originSheet.copyTo(destinationSheet);
copiedSheet.activate();
tester.moveActiveSheet(1);

}

CodePudding user response：

After you copied the sheet to the new spreadsheet, overwrite the data range of the new sheet with the data range values of the original sheet:

function copySheetFunction () {

let copiedSheet = originSheet.copyTo(destinationSheet);

let values = originSheet.getDataRange().getValues();
copiedSheet.getDataRange().setValues(values);

copiedSheet.activate();
tester.moveActiveSheet(1);

}