Google-sheets – Copy Sheet To New Spreadsheet

google sheetsgoogle-apps-scriptgoogle-drive

I want to copy one sheet in the Spreadsheet file into a new Spreadsheet file.
The sheet that I want to copy contains formulas, I want to just copy the value to a new Spreadsheet file.

enter image description here
I have made the script like this:

function makecopyto() {
  const folderId = '1Y7RmeF9sluTndSUr2KQT8wlOL9oUkLVU';
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('Sheet1');
  const newSsName = sheet.getRange('Sheet2!F1').getValue().toString();
  const resource = {
    title: newSsName,
    mimeType: MimeType.GOOGLE_SHEETS,
    parents: [{ id: folderId }]
  };
  const fileJson = Drive.Files.insert(resource);
  const targetSs = SpreadsheetApp.openById(fileJson.id);
  const targetSheet = sheet.copyTo(targetSs);
}

But the results are not appropriate.

enter image description here

The results I want should be like this:
enter image description here

Best Answer

  • You want to copy a sheet from the active Spreadsheet as new Spreadsheet.
  • You want to copy only the values of the sheet.
  • You want to keep the format of cells.

If my understanding is correct, how about this modification? Please think of this answer as just one of several answers. In this modification, I used the following flow.

  1. Copy the active Spreadsheet.
  2. Rename the copied Spreadsheet.
  3. Copy only values of "Sheet1" to "Sheet1".
  4. Delete sheets except for "Sheet1".

Modified script:

function makecopyto() {
  const folderId = '1Y7RmeF9sluTndSUr2KQT8wlOL9oUkLVU';
  const ss = SpreadsheetApp.getActiveSpreadsheet();

  // I modified the following script.
  var newSpreadsheet = DriveApp.getFileById(ss.getId()).makeCopy(DriveApp.getFolderById(folderId));
  newSpreadsheet.setName(ss.getRange('Sheet2!F1').getValue());
  var newSs = SpreadsheetApp.openById(newSpreadsheet.getId());
  var sheets = newSs.getSheets();
  var range = newSs.getSheetByName("Sheet1").getDataRange();
  range.copyTo(range, {contentsOnly: true});
  for (var i = 0; i < sheets.length; i++) {
    if (sheets[i].getSheetName() != "Sheet1") {
      newSs.deleteSheet(sheets[i]);
    }
  }
}

References:

If I misunderstood your question and this was not the result you want, I apologize.

Related Topic