google-sheetsgoogle-apps-scriptscriptinggoogle-drive-apigoogle-drive-shared-drive

Move Spreadsheet from My Drive to Shared Drive


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!


Solution

  • 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);
    }