google-apps-script - Copy Spreadsheet to a new file, specify tabs

Question

I am copying a new instance of my spreadsheet without formulas into a folder. I found a script that works great here: Copy an entire Spreadsheet, Just Preserve the Values.

I was trying to define a specific tab to copy but no luck.

var sheet = ss.getSheetByName('tab to dowload');

and

var ss = SpreadsheetApp.getActiveSpreadsheet();
ss.setActiveSheet(ss.getSheets()[2]); 

function myFunction() {
  var spreadsheetId = "###"; // Please set the source Spreadsheet ID.
  var destFolderId = "###";  // Please set the destination folder ID.

  // Copy each sheet in the source Spreadsheet by removing the formulas as the temporal sheets.
  var ss = SpreadsheetApp.openById(spreadsheetId);
  var tempSheets = ss.getSheets().map(function(sheet) {
    var dstSheet = sheet.copyTo(ss).setName(sheet.getSheetName() + "_temp");
    var src = dstSheet.getDataRange();
    src.copyTo(src, {contentsOnly: true});
    return dstSheet;
  });

  // Copy the source Spreadsheet.
  var destination = ss.copy(ss.getName() + " - " + new Date().toLocaleString());

  // Delete the temporal sheets in the source Spreadsheet.
  tempSheets.forEach(function(sheet) {ss.deleteSheet(sheet)});

  // Delete the original sheets from the copied Spreadsheet and rename the copied sheets.
  destination.getSheets().forEach(function(sheet) {
    var sheetName = sheet.getSheetName();
    if (sheetName.indexOf("_temp") == -1) {
      destination.deleteSheet(sheet);
    } else {
      sheet.setName(sheetName.slice(0, -5));
    }
  });

  // Move file to the destination folder.
  var file = DriveApp.getFileById(destination.getId());
  DriveApp.getFolderById(destFolderId).addFile(file);
  file.getParents().next().removeFile(file);
}

标签： google-apps-scriptgoogle-sheets

Answer 1

Explanation:

Your goal is to pass selected sheets, based on their name, to the newly created file.

You can do that by defining a list with the selected sheets:

var selected_sheets = ['Sheet1','Sheet5'];

and then instead of using ss.getSheets() which gets every sheet, use this to get the sheet object of the selected sheet names:

var tempSheets = selected_sheets.map(sn=>ss.getSheetByName(sn))
    .map(function(sheet) {
    var dstSheet = sheet.copyTo(ss).setName(sheet.getSheetName() + "_temp");
    var src = dstSheet.getDataRange();
    src.copyTo(src, {contentsOnly: true});
    return dstSheet;
  });

Solution:

Please modify this list: var selected_sheets = ['Sheet1','Sheet5']; and replace Sheet1, Sheet5 to the name of your sheets. You can also have one sheet: e.g. var selected_sheets = ['Sheet1'];.

function myFunction() {
  var spreadsheetId = "###"; // Please set the source Spreadsheet ID.
  var destFolderId = "###";  // Please set the destination folder ID.

  // Copy each sheet in the source Spreadsheet by removing the formulas as the temporal sheets.
  var ss = SpreadsheetApp.openById(spreadsheetId);
  var selected_sheets = ['Sheet1','Sheet5']; // Add the sheets you want to copy here!
  var tempSheets = selected_sheets.map(sn=>ss.getSheetByName(sn))
    .map(function(sheet) {
    var dstSheet = sheet.copyTo(ss).setName(sheet.getSheetName() + "_temp");
    var src = dstSheet.getDataRange();
    src.copyTo(src, {contentsOnly: true});
    return dstSheet;
  });     

  // Copy the source Spreadsheet.
  var destination = ss.copy(ss.getName() + " - " + new Date().toLocaleString());

  // Delete the temporal sheets in the source Spreadsheet.
  tempSheets.forEach(function(sheet) {ss.deleteSheet(sheet)});

  // Delete the original sheets from the copied Spreadsheet and rename the copied sheets.
  destination.getSheets().forEach(function(sheet) {
    var sheetName = sheet.getSheetName();
    if (sheetName.indexOf("_temp") == -1) {
      destination.deleteSheet(sheet);
    } else {
      sheet.setName(sheetName.slice(0, -5));
    }
  });

  // Move file to the destination folder.
  var file = DriveApp.getFileById(destination.getId());
  DriveApp.getFolderById(destFolderId).addFile(file);
  file.getParents().next().removeFile(file);
}