Make copy of Spreadsheet with content only

Question

How to make a copy of a google sheet with content only. My problem is that there is a function that already allows you to do this on the ranges of a sheet with ({contentonly: true}). But it doesn't work if there is an importrange. Is there a way to make a copy of a sheet of values only (even if there is an importange)? May be something like this :

var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var destFolder = DriveApp.getFoldersByName("Experiments").next();
  var file = DriveApp.getFileById(spreadsheet.getId()).makeCopy("Saved Copy", destFolder);
  var newSpreadsheet = SpreadsheetApp.open(file);

  for(var i = 0; i < spreadsheet.getNumSheets(); i++){
    newSpreadsheet.getSheets()[i].getDataRange().setValues(spreadsheet.getSheets()[i].getDataRange().getValues());    
  }

But it is not very efficient, if there are many cells.

I have to apologize for my poor English skill. Unfortunately, I cannot understand your question. Can I ask you about the detail of your question? — Tanaike, Jul 09 '22 at 01:37
I want to copy the values of all sheets in a Spreadsheet to new Spreadsheet.I found a similar problem [here](https://stackoverflow.com/questions/58262948/how-to-copy-format-and-values-not-formulas-when-creating-a-spreadsheet-backup)This solution seems to work only for files that do not have an importrange.For importrange you need an authorization.[I could give this authorization via app script](https://stackoverflow.com/questions/28038768/how-to-allow-access-for-importrange-function-via-apps-script) — meteor314, Jul 10 '22 at 08:23
but my problem is that I have to check on each cell if there is an importrange or not This is not possible for large files. thank you sir in advance for your help. — meteor314, Jul 10 '22 at 08:24
Thank you for replying. I would like to support you. But, I have to apologize for my poor English skill, again. Unfortunately, I cannot still understand your question. But I would like to try to understand it. When I could correctly understand it, I would like to think of a solution. I would be grateful if you can forgive my poor English skill. — Tanaike, Jul 10 '22 at 22:58
I just posted as an answer something to try. If it doesn't solve your issue, please provide more details (i.e. how many sheets, how many cells by sheet, the IMPORTRANGE formulas, etc., the script using `{contentonly: true}`). — Rubén, Jul 20 '22 at 17:06

score 0 · Answer 1 · answered Jul 20 '22 at 17:03

Maybe the performance problem is caused by calling too many Spreadsheet Service methods (i.e. Spreadsheet.getSheets() is called two times inside a loop). Try the following:

var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var destFolder = DriveApp.getFoldersByName("Experiments").next();
var file = DriveApp.getFileById(spreadsheet.getId()).makeCopy("Saved Copy", destFolder);
var newSpreadsheet = SpreadsheetApp.open(file);
var sheets = newSpreadsheet.getSheets(); // Call SpreadsheetApp.getSheets() only once per script execution
for(var i = 0; i < sheets.length; i++){
  sheets[i].getDataRange().setValues(sheets[i].getDataRange().getValues());    
}

Make copy of Spreadsheet with content only

1 Answers1