Script to convert .XLSX to Google Sheet and move converted file

Question

I know it is possible to convert excel files to Google Sheets using script and drive API, but I'm looking for script to convert the excel sheet and move the converted file to a different folder.

So the required steps are as follows:

1. Convert excel (.xls/.xlsx) to Google Sheet from FolderA.
2. Move converted file from FoldarA to FolderB.
3. Delete original excel file from FolderA
4. Hopefully step 3 avoids this, but avoid duplicating already converted file.

The excel files are being pasted into a local folder that is being synced to google drive, and the files are no larger than 3mb. The current script is as follows. This is converting the files but placing them in the root folder, and will duplicate the conversion when the script runs again.

 function importXLS(){
  var files = DriveApp.getFolderById('1hjvNIPgKhp2ZKIC7K2kxvJjfIeEYw4BP').searchFiles('title != "nothing"');
  while(files.hasNext()){
    var xFile = files.next();
    var name = xFile.getName();
    if (name.indexOf('.xlsx')>-1){ 
      var ID = xFile.getId();
      var xBlob = xFile.getBlob();
      var newFile = { title : name+'_converted',
                     key : ID
                    }
      file = Drive.Files.insert(newFile, xBlob, {
        convert: true
      });
    }
  }
}

Answer 1

• You want to create the converted Google Spreadsheet files to "FolderB".
• You want to delete the XLSX files in "FolderA" after the files were converted.
• You want to achieve above using Google Apps Script.

If my understanding correct, how about this modification? In this modification, I modified your script.

Modification points:

• You can directly create the file to the specific folder using the property of parents in the request body.
• You can delete the file using Drive.Files.remove(fileId).

Modified script:

Before you use this script, please enable Drive API at Advanced Google services.

function importXLS(){
  var folderBId = "###"; // Added // Please set the folder ID of "FolderB".

  var files = DriveApp.getFolderById('1hjvNIPgKhp2ZKIC7K2kxvJjfIeEYw4BP').searchFiles('title != "nothing"');
  while(files.hasNext()){
    var xFile = files.next();
    var name = xFile.getName();
    if (name.indexOf('.xlsx')>-1){ 
      var ID = xFile.getId();
      var xBlob = xFile.getBlob();
      var newFile = {
        title : name+'_converted',
        parents: [{id: folderBId}] //  Added
      };
      file = Drive.Files.insert(newFile, xBlob, {
        convert: true
      });
      // Drive.Files.remove(ID); // Added // If this line is run, the original XLSX file is removed. So please be careful this.
    }
  }
}

Note:

• If the number of XLSX files is large, the execution time might be over 6 minutes.
• About // Drive.Files.remove(ID);, when you run this script, please be careful. Because the original XLSX files are completely deleted when the script is run. So I commented this. At first, please test the script using sample files.

References:

• Files: insert
• Files: delete