Google App Script : Pasting Values only in new spreadsheet-CodePudding

I have a dashboard on a gsheet spreadsheet, with one sheet being an overview. It has tables generated by various QUERY Functions. I want to create a script that would make a copy of this sheet only, and save it into a new Spreadsheet, stored on a Google Drive folder.

I managed to do this, but then the result does not display my queries, it just copies the formula. Here is my code :

function CopyToSpreadSheet() {

  //Source sheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sourceSheet = ss.getSheetByName("MTS");
  //Name new sheet in target SpreadSheet
  var formattedDate = Utilities.formatDate(new Date(), "GMT", "yyyy-MM-dd' 'HH:mm:ss");
  var name = SpreadsheetApp.getActiveSpreadsheet().getName()   " Archive "   formattedDate;
  //Create new spreadsheet
  var newss = SpreadsheetApp.create(name);
  var newss_id = newss.getId();
  var newfile = DriveApp.getFileById(newss_id);
  newfile.moveTo(DriveApp.getFolderById('1EUFJcySQ_Mv8qD8OzYUX1QniXQy2gjw0'));

  //open target SpreadSheet
  var target = SpreadsheetApp.openById(newss_id);

  //CopyTo...
  var targetSheet = sourceSheet.copyTo(target);
  targetSheet.setName(name);
    
  return;
}

So, i've been trying the CopyTo parameters {contentsOnly:true} and SpreadsheetApp.CopyPasteType.PASTE_VALUES, which gives me The parameters (SpreadsheetApp.Spreadsheet,(class)) don't match the method signature for SpreadsheetApp.Sheet.copyTo for the ContentsOnly parameter, and The parameters (SpreadsheetApp.Spreadsheet,SpreadsheetApp.CopyPasteType) don't match the method signature for SpreadsheetApp.Sheet.copyTo for the CopypasteType.

At this point, I have no idea how to fix this, but it is clearly coming from me not knowing how to handle this. I've also tried to use .makeCopy, but there was no parameter to force the copy to use the Values and not the formula in itself.

Any ideas? I'm open to anything at this point!

Thanks in advance :)

CodePudding user response：

I believe your goal is as follows.

From your title of Google App Script : Pasting Values only in new spreadsheet and I want to create a script that would make a copy of this sheet only, and save it into a new Spreadsheet, stored on a Google Drive folder., you want to copy only the values from the source sheet to the target sheet in a new Spreadsheet by using Google Apps Script.

Modification points:

First, about the reason for your issue of So, i've been trying the CopyTo parameters {contentsOnly:true} and SpreadsheetApp.CopyPasteType.PASTE_VALUES, which gives me The parameters (SpreadsheetApp.Spreadsheet,(class)) don't match the method signature for SpreadsheetApp.Sheet.copyTo for the ContentsOnly parameter, and The parameters (SpreadsheetApp.Spreadsheet,SpreadsheetApp.CopyPasteType) don't match the method signature for SpreadsheetApp.Sheet.copyTo for the CopypasteType., I thought that you might have used the method of copyTo(destination, options) of Class Range to Class Spreadsheet. If my understanding is correct, such an error occurs and the reason for this is due to it.

In order to copy only the values from a source sheet to a destination sheet, I thought that there might be the following 2 patterns.

Pattern 1:

In this pattern, getValues and setValues are used.

function CopyToSpreadSheet() {
  // Source sheet side.
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sourceSheet = ss.getSheetByName("MTS");
  var range = sourceSheet.getDataRange();

  // This is from your script.
  var formattedDate = Utilities.formatDate(new Date(), "GMT", "yyyy-MM-dd' 'HH:mm:ss");
  var name = ss.getName()   " Archive "   formattedDate;
  var newss = SpreadsheetApp.create(name);
  var newss_id = newss.getId();
  var newfile = DriveApp.getFileById(newss_id);
  newfile.moveTo(DriveApp.getFolderById('1EUFJcySQ_Mv8qD8OzYUX1QniXQy2gjw0'));

  // Destination sheet side.
  var target = SpreadsheetApp.openById(newss_id);
  var targetSheet = target.getSheets()[0];
  targetSheet.setName(sourceSheet.getSheetName()); // If you don't want to rename sheet name, please remove this line.
  targetSheet.getRange(range.getA1Notation()).setNumberFormats(range.getNumberFormats()).setValues(range.getValues());
}

When this script is run, only the values are copied from the source sheet to the destination sheet. In this sample, the number format is also copied.

Pattern 2:

In this pattern, copyTo methods of Class Sheet and Class Spreadsheet are used.

function CopyToSpreadSheet() {
  // Source sheet side.
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sourceSheet = ss.getSheetByName("MTS");
  var tempSheet1 = sourceSheet.copyTo(ss);
  var tempRange1 = tempSheet1.getDataRange();
  tempRange1.copyTo(tempRange1, { contentsOnly: true });

  // This is from your script.
  var formattedDate = Utilities.formatDate(new Date(), "GMT", "yyyy-MM-dd' 'HH:mm:ss");
  var name = ss.getName()   " Archive "   formattedDate;
  var newss = SpreadsheetApp.create(name);
  var newss_id = newss.getId();
  var newfile = DriveApp.getFileById(newss_id);
  newfile.moveTo(DriveApp.getFolderById('1EUFJcySQ_Mv8qD8OzYUX1QniXQy2gjw0'));

  // Destination sheet side.
  var target = SpreadsheetApp.openById(newss_id);
  var tempSheet2 = tempSheet1.copyTo(target);
  var targetSheet = target.getSheets()[0];
  targetSheet.setName(sourceSheet.getSheetName()); // If you don't want to rename sheet name, please remove this line.
  const tempRange2 = tempSheet2.getDataRange();
  tempRange2.copyTo(targetSheet.getRange("A1"));

  // Remove temp sheets.
  ss.deleteSheet(tempSheet1);
  target.deleteSheet(tempSheet2);
}

When this script is run, not only the values but also background colors, font styles, and so on are copied from the source sheet to the destination sheet.

References:

Result: This is the result I got using your code, only the drive folder ID was changed. I am able to get the actual data/values not only the formula.

Explanation:

The reason is using the normal Query function it can only get data from the same Spreadsheet file. Same with normally pasting your Query formula in a different spreadsheet, it will not be able to get the data. For it to be able to Query data from other Spreadsheet you need to use it with importrange.

Also don't forget to change access to the file so those who use it for Query will actually be able to retrieve the data.

Reference: Query From Another Spreadsheet