Copy a sheet from a SpreadSheet file to a Google Drive folder-CodePudding

I learn how to use Google Script. I'm trying to make a copy of a sheet form a spreadsheet into a Google Drive folder. That was possible manualy but i need to do that with Google Script.

I try :

var desFolder = DriveApp.getFolderById(folderID);
var spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
DriveApp.getFileById(spreadSheet.getId()).makeCopy("desired file name", destFolder);

But it copy all the spreadSheet, i need to copy one sheet not the entire SpreadSheet. How can i do that ?

CodePudding user response：

In your script, how about the following modification?

Modified script 1:

function sample1() {
  var sheetName = "Sheet1"; // Please set the sheet name you want to copy.
  var folderID = "###"; // Please set the folder ID.

  var destFolder = DriveApp.getFolderById(folderID);
  var srcSpreadSheet = SpreadsheetApp.getActiveSpreadsheet();
  var file = DriveApp.getFileById(srcSpreadSheet.getId()).makeCopy("desired file name", destFolder);
  var dstSpreadsheet = SpreadsheetApp.open(file);
  dstSpreadsheet.getSheets().forEach(s => {
    if (s.getSheetName() != sheetName) {
      dstSpreadsheet.deleteSheet(s);
    }
  });
}

In this modification, the Spreadsheet is copied to the destination folder. And, the sheets except for a sheet you want to use are deleted.

Modified script 2:

function sample1() {
  var sheetName = "Sheet1"; // Please set the sheet name you want to copy.
  var folderID = "###"; // Please set the folder ID.

  var destFolder = DriveApp.getFolderById(folderID);
  var srcSpreadSheet = SpreadsheetApp.getActiveSpreadsheet();
  var srcSheet = srcSpreadSheet.getSheetByName(sheetName);
  var dstSpreadsheet = SpreadsheetApp.create("desired file name");
  srcSheet.copyTo(dstSpreadsheet).setName(sheetName);
  dstSpreadsheet.deleteSheet(dstSpreadsheet.getSheets()[0]);
  DriveApp.getFileById(dstSpreadsheet.getId()).moveTo(destFolder);
}

In this modification, a new Spreadsheet is created and it is moved to the destination folder. And, copy the sheet you want to use to the created Spreadsheet. And, the default sheet is deleted.

References:

CodePudding user response：

you'll need to create an empty spreadsheet and copy specific sheet using your copyTo method.

Something like:

var newSpreadsheet = SpreadsheetApp.create("desired file name")
var spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
var sheet = spreadSheet.getSheetByName("specific_sheet_name")
sheet.copyTo(newSpreadsheet)