Google Apps Script - How to change the code to create Google Sheets file in active Google Drive Shar-CodePudding

I have some code below that takes all the tabs (excluding 2 of them) in a Google Sheets file and creates independent Google Sheets files for each of them. Right now, they are saving by default in root folder (My Drive), but I would like them to be created in the same active folder as the file from which I call my Google Apps Script. Does anyone know how I can do that with the below code? I also want to avoid scenarios where it's created first in My Drive and then moved to the Shared Drive. The reason for this is because other people may run this code and I want to avoid an instance where the folder in My Drive needs to be updated in the code based on who runs this script.

The simplified version of my above paragraph for brevity: How can I adapt the below code so that the Google Sheets files that are created show up in the active Google Shared Drive folder? I will enabling the Drive API.

My Current Code:

function copySheetsToSS(){
  var ss = SpreadsheetApp.getActive();
  for(var n in ss.getSheets()){
    var sheet = ss.getSheets()[n];
    var name = sheet.getName();
    if(name != 'ControlTab' && name != 'RawData'){
      var alreadyExist = DriveApp.getFilesByName(name);
      while(alreadyExist.hasNext()){
        alreadyExist.next().setTrashed(true);
      }
      var copy = SpreadsheetApp.create(name);
      sheet.copyTo(copy).setName(name);
      copy.deleteSheet(copy.getSheets()[0]);
    }
  }
}

CodePudding user response：

In your situation, when your script is modified, how about the following modification?

Modified script:

In this modification, Drive API is used. So, please enable Drive API at Advanced Google services.

function copySheetsToSS() {
  var ss = SpreadsheetApp.getActive();
  var folderId = DriveApp.getFileById(ss.getId()).getParents().next().getId();
  for (var n in ss.getSheets()) {
    var sheet = ss.getSheets()[n];
    var name = sheet.getName();
    if (name != 'ControlTab' && name != 'RawData') {
      var alreadyExist = DriveApp.getFilesByName(name);
      while (alreadyExist.hasNext()) {
        alreadyExist.next().setTrashed(true);
      }
      var newSS = Drive.Files.insert({ title: name, mimeType: MimeType.GOOGLE_SHEETS, parents: [{ id: folderId }] }, null, { supportsAllDrives: true });
      var copy = SpreadsheetApp.openById(newSS.id);
      sheet.copyTo(copy).setName(name);
      copy.deleteSheet(copy.getSheets()[0]);
    }
  }
}

In this modification, first, the parent folder of Spreadsheet is retrieved. And, in the loop, the new Spreadsheet is created to the specific folder using Drive API.

Note:

In this case, it is required to have the write permission of the folder. Please be careful about this.

Modified script:

Note:

References: