1

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

0
  • 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.

  • Thank, Tanaike. Script is work. – Lutfi Creativesys May 20 at 7:19
  • @Lutfi Creativesys Thank you for replying. I'm glad your issue was resolved. – Tanaike May 20 at 8:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.