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

user_18769968 0
Viewed 68

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 ?

google-apps-script google-sheets
2 Answers

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");
  var dstSheet = srcSheet.copyTo(dstSpreadsheet);
  dstSpreadsheet.deleteSheet(dstSpreadsheet.getSheets()[0]);
  dstSheet.setName(sheetName);
  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.

  • I noticed that when I saw the 2nd sample script and, when the value of sheetName is the same as the default sheet name, an error occurs. So, I modified it.

References:

user_71086530

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)
user_29433450
Related