Function to convert the Newest EXCEL file in a folder to a Google Sheet stopped working when folders were moved to a shared drive

Question

Function to convert the Newest EXCEL file in a folder to a Google Sheet and moves it to a destination folder.

The function has stopped working after folders were moved to a shared drive.

Converting all files in drive folder from sheets to CSV and add to new folder

How to get the function to work on a shared drive?

function newestExceltoSheetsDF(SOURCE_XLS_FOLDER, dstFolderId, postBackSheetName) {
  /*var SOURCE_XLS_FOLDER = '1jkxxx', // Please set the source folder ID here.
      dstFolderId = '1XmQxxx', // Please set the destination folder ID here.*/

      sourceFolderID = DriveApp.getFolderById(SOURCE_XLS_FOLDER),
      searchQuery = "mimeType='" + MimeType.MICROSOFT_EXCEL + "' or mimeType='" + MimeType.MICROSOFT_EXCEL_LEGACY + "'",
      sourceFiles = sourceFolderID.searchFiles(searchQuery);

  let arr = [];
  while (sourceFiles.hasNext()) {
    let sourceFile = sourceFiles.next();
    let fileId = sourceFile.getId();   

    arr.push({name:sourceFile.getName(),id:sourceFile.getId(),date:sourceFile.getDateCreated(),fileid: fileId})  
  }
  
  //Sort
  arr.filter(Boolean).sort((a,b)=> {
    return new Date(b.date).valueOf() - new Date(a.date).valueOf();
  })
  
  //Copy the newest Excel file to a Google Sheet to the folder "dstFolderId" and get values of Sheet1
  let spreadsheet = Drive.Files.copy({mimeType: MimeType.GOOGLE_SHEETS, parents: [{id: dstFolderId}]}, arr[0].fileid),
        sheets          = SpreadsheetApp.openById(spreadsheet.id).getSheets(),
         valuesGS    = sheets[0].getDataRange().getValues();
  
  //Remove first row (for Cambridge only)
  valuesGS.shift();     

  const ss = SpreadsheetApp.getActive(),
        sh = ss.getSheetByName(postBackSheetName);
   sh.clearContents();
   sh.getRange(1,1,valuesGS.length, valuesGS[0].length).setValues(valuesGS); 
  
  DriveApp.getFileById(spreadsheet.id).setTrashed(true);
  //Delete the source file
  //sourceFiles.setTrashed(true);
}

Answer 1

In your script, how about the following modification?

From:

let spreadsheet = Drive.Files.copy({mimeType: MimeType.GOOGLE_SHEETS, parents: [{id: dstFolderId}]}, arr[0].fileid),

To:

let spreadsheet = Drive.Files.copy({ mimeType: MimeType.GOOGLE_SHEETS, parents: [{ id: dstFolderId }] }, arr[0].fileid, { supportsAllDrives: true }),

Note:

• In this case, it supposes that you have permission for writing to the shared Drive. please be careful about this.

Reference:

• Files: copy of Drive API v2