Move Spreadsheet from My Drive to Shared Drive

Question

I have a script that creates a new spreadsheet and copies two tabs from the current sheet to the new sheet. I would prefer if the new sheet were saved in a shared drive folder instead of my My Drive. Below is the current iteration I'm working on. The commented out lines are the ones that don't work.

function SaveNewFinancials() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Inputs'), true);  
  var filename = spreadsheet.getRange("B15").getValue();
  var newSpreadsheet = SpreadsheetApp.create(filename);
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Balance Sheet'), true);  
  SpreadsheetApp.getActiveSheet().copyTo(newSpreadsheet).setName("Balance Sheet");
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Profit and Loss'), true);
  SpreadsheetApp.getActiveSheet().copyTo(newSpreadsheet).setName("Profit and Loss");
  newSpreadsheet.deleteSheet(newSpreadsheet.getSheetByName("Sheet1"));
  //var folderID = "xxxxxxx3nTzzyS-I1pWzsejHDCh5";
  //var sharedFolder = DriveApp.getFolderById(folderID);
  //var sharedDriveID = "xxxxxxxxenHXUk9PVA";
  //var resource = {title: filename, mimeType: MimeType.GOOGLE_SHEETS, parents: [{driveid: sharedDriveID, id: sharedFolder}]};
  //Drive.Files.copy(resource, newSpreadsheet,{supportsAllDrives: true});

I've enabled the advanced drive service, but I still get errors when attempting to find the folder in the shared drive.

I've tried using moveTo and copyTo, but I learned that DriveApp doesn't play well with shared drives. I also don't think I need the drive ID, but I saw it in a few other answers here so I added it in my latest attempt. Thanks in advance for any help!

Answer 1

try like this:

function SaveNewFinancials() {
  var spreadsheet = SpreadsheetApp.getActive();
  
  // Set the active sheet to 'Inputs' and get the filename from cell B15
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Inputs'), true);  
  var filename = spreadsheet.getRange("B15").getValue();
  
  // Create a new spreadsheet with the obtained filename
  var newSpreadsheet = SpreadsheetApp.create(filename);
  
  // Copy the 'Balance Sheet' sheet to the new spreadsheet and rename it
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Balance Sheet'), true);  
  SpreadsheetApp.getActiveSheet().copyTo(newSpreadsheet).setName("Balance Sheet");
  
  // Copy the 'Profit and Loss' sheet to the new spreadsheet and rename it
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Profit and Loss'), true);
  SpreadsheetApp.getActiveSheet().copyTo(newSpreadsheet).setName("Profit and Loss");
  
  // Delete the default 'Sheet1' from the new spreadsheet
  newSpreadsheet.deleteSheet(newSpreadsheet.getSheetByName("Sheet1"));

  // The shared folder ID where the new spreadsheet will be moved
  var folderID = "xxxxxxx3nTzzyS-I1pWzsejHDCh5"; // Replace with your shared folder ID
  var fileID = newSpreadsheet.getId();

  // Use the Drive API to move the file to the shared folder
  var file = DriveApp.getFileById(fileID);
  var folder = DriveApp.getFolderById(folderID);
  folder.addFile(file);
  DriveApp.getRootFolder().removeFile(file);
}