Is there a better way to make a copy of a spreadsheet with content only?

Question

I've been trying to copy only the content (no equations such as =IMPORTRANGE) of a spreadsheet into a new spreadsheet. I have found a solution however I was wondering if there was an easier way to do this?

I ended up iterating through each sheet in the original spreadsheet, getting the data range of each sheet, getting those values, copying each sheet into the new spreadsheet with .copyTo, deleting the "Sheet1" created with the new spreadsheet, then iterating through the new spreadsheet and pasting the values to each new sheet in the spreadsheet.

function copySpreadsheetContent() {
  var ss1 = SpreadsheetApp.getActiveSpreadsheet();
  var sheetNums = ss1.getNumSheets();
  var ssNew = SpreadsheetApp.create('Test Spreadsheet').getId();
  var ss2 = SpreadsheetApp.openById(ssNew);
  var ss2Nums = ss2.getNumSheets();
  var sheetVals = [];
  for(var i = 0; i < sheetNums; i++){
    var sheet = ss1.getSheets()[i];
    sheetVals[i] = sheet.getDataRange().getValues();
    var newSheet = sheet.copyTo(ss2);
    newSheet.setName(sheet.getName());
  }
  var sheet1 = ss2.getSheetByName("Sheet1");
  ss2.deleteSheet(sheet1);
  for(var j = 0; j <= ss2Nums; j++){
    var ss2Sheet = ss2.getSheets()[j];
    ss2Sheet.getDataRange().setValues(sheetVals[j]);
  }
}

This provides the desired results as far as I can tell, however it just seems like a really roundabout way of doing things and I was hoping someone might be able to point me to a more efficient or straightforward method.

Thanks for the help

score 1 · Accepted Answer · answered Aug 01 '19 at 13:04

1

My coworker ended up finding a better way to do this with a single for loop and the ability to send it to a folder in your drive

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());    
  }

answered Aug 01 '19 at 13:04

Passer-By

487
2
10

Good solution, but FYI, you can also use the `CopyTo()` method for ranges, instead of sheets, with the difference that for ranges you can set the options to `{contentsOnly:true}`. For large data ranges this method might work faster than `getValues()` with `setValues()` . https://developers.google.com/apps-script/reference/spreadsheet/range#copytodestination-options – ziganotschka Aug 01 '19 at 14:23
When I tried this with `spreadsheet.getSheets()[i].getDataRange().copyTo(newSpreadsheet.getSheets()[i], {contentsOnly:true});` in place of what's currently in the if statement, I received this error: `Cannot find method copyTo(Sheet,object). at copySpreadsheetContent(Savely:158)` – Passer-By Aug 01 '19 at 15:11
You need to specify the destination range. Also, unfortunately you can use this method only to copy data from one sheet to another one in the same spreadsheet. If you need to create a new spreadsheet rather then a new sheet within the same spreadsheet, you have to stick to getValues() and setValues() for copying contents only. – ziganotschka Aug 01 '19 at 16:01

Is there a better way to make a copy of a spreadsheet with content only?

1 Answers1

Linked