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

Question

I have:

• .xlsx files being automatically imported on a daily basis to a google drive folder

I want:

• each file converted to a CSV and saved to a different folder
• each file converted to a Google Sheet and saved to a different folder
• the original xlsx file deleted once done processing

Currently my process is this:

• Convert xlsx to 2 new files: CSV & Google Sheet
• Save CSVs to CSV Folder
• Save Google Sheets to Sheets folder

I was originally using this https://ctrlq.org/code/20248-convert-excel-to-csv tutorial to convert to CSV until I realized it saved an "Untitled" copy of each .xlsx sheet as Drive spreadsheet to my root folder. I could not figure out how to assign a title or folder location to those untitled. Being able to do that would also fix my immediate problem.

Then I attempted using a modified version of this (below) https://ctrlq.org/code/20500-convert-microsoft-excel-xlsx-to-google-spreadsheet with the MimeType .CSV which correctly placed my CSV's in the right folder with the right name, but the data wasn't parsed correctly and looked corrupted.

function exceltoSheets() {
  var SOURCE_XLS_FOLDER = '123465';
  var SHEET_FOLDER = '789456';
  var CSV_TEST = '456123';

  var sourceFolderID = DriveApp.getFolderById(SOURCE_XLS_FOLDER),
      mimes = [MimeType.MICROSOFT_EXCEL, MimeType.MICROSOFT_EXCEL_LEGACY];

  for (var m = 0; m < mimes.length; m++) {
    var sourceFiles = sourceFolderID.getFilesByType(mimes[m]);

    while (sourceFiles.hasNext()) {
       try {
         var sourceFile = sourceFiles.next();
         var sourceName = sourceFile.getName().replace(".xlsx","");
         var sourceNameTC = sourceName + ".csv"
         var sourceNameDS = "ds_data_import_" + sourceName;

         var fileId = sourceFile.getId(); 
         var blob = sourceFile.getBlob();
         var resourceDS = {
           title: sourceNameDS,
           mimeType: MimeType.GOOGLE_SHEETS,
           convert: true,
           parents: [{id: SHEET_FOLDER}]
         };
         var resourceTC = {
           title: sourceNameTC,
           mimeType: MimeType.CSV, 
           convert: true,
           parents: [{id: CSV_TEST}],
         };

         Drive.Files.insert(resourceDS, blob);
         Drive.Files.insert(resourceTC, blob);

       } catch (f) {
         Logger.log(f.toString());
       }
      sourceFile.setTrashed(true);
    }
  }
}

If I parse the CSVs correctly I end up with Untitled sheets in my root folder, if I parse the Sheets correctly I end up with corrupted CSVs. I want the result:

• xlsx converted to CSV in designated folder
• xlsx converted to Google Sheet in designated folder
• xlsx deleted off drive once processing complete

Answer 1

• You want to convert XLSX files in the specific folder to Google Spreadsheet.
• You want to put the converted Spreadsheet to the specific folder.
• You want to achieve this by modifying your script.

If my understanding is correct, how about this modification? Please think of this as just one of several answers.

Modification points:

• In order to retrieve the files of MimeType.MICROSOFT_EXCEL and MimeType.MICROSOFT_EXCEL_LEGACY, I used searchFiles().
• In order to convert from XLSX file to Google Spreadsheet and put it to the specific folder, I used Drive.Files.copy().

Modified script:

When you use this, please confirm whether Drive API is enabled at Advanced Google services.

function exceltoSheets() {
  var SOURCE_XLS_FOLDER = '###'; // Please set the source folder ID here.
  var dstFolderId = '####'; // Please set the destination folder ID here.

  var sourceFolderID = DriveApp.getFolderById(SOURCE_XLS_FOLDER);
  var searchQuery = "mimeType='" + MimeType.MICROSOFT_EXCEL + "' or mimeType='" + MimeType.MICROSOFT_EXCEL_LEGACY + "'";
  var sourceFiles = sourceFolderID.searchFiles(searchQuery);
  while (sourceFiles.hasNext()) {
    var sourceFile = sourceFiles.next();
    var fileId = sourceFile.getId();
    Drive.Files.copy({mimeType: MimeType.GOOGLE_SHEETS, parents: [{id: dstFolderId}]}, fileId);
    sourceFile.setTrashed(true);
  }
}

Note:

• If you want to directly delete the XLSX file, you can use Drive.Files.remove(fileId) instead of sourceFile.setTrashed(true).

References:

• searchFiles()
• Files: copy
• Advanced Google services

Edit:

• You want to convert from XLSX files to CSV and Google Spreadsheet files.
• You want to put the converted XLSX files and CSV files to each folder.

For this situation, the modified script is as follows.

function exceltoSheets() {
  var SOURCE_XLS_FOLDER = '###'; // Please set the source folder ID here.
  var dstFolderIdForSpreadsheet = '###'; // Please set the destination folder ID for Spreadsheet here.
  var dstFolderIdForCSV = '###'; // Please set the destination folder ID for CSV here.

  var sourceFolder = DriveApp.getFolderById(SOURCE_XLS_FOLDER);
  var destinationFolderForCSV = DriveApp.getFolderById(dstFolderIdForCSV);
  var searchQuery = "mimeType='" + MimeType.MICROSOFT_EXCEL + "' or mimeType='" + MimeType.MICROSOFT_EXCEL_LEGACY + "'";
  var sourceFiles = sourceFolder.searchFiles(searchQuery);
  while (sourceFiles.hasNext()) {
    var sourceFile = sourceFiles.next();
    var fileId = sourceFile.getId();
    var spreadsheet = Drive.Files.copy({mimeType: MimeType.GOOGLE_SHEETS, parents: [{id: dstFolderIdForSpreadsheet}]}, fileId);
    var sheets = SpreadsheetApp.openById(spreadsheet.id).getSheets();
    sheets[0].getDataRange().getValues()
    var csv = sheets.map(function(sheet) {return sheet.getDataRange().getValues().reduce(function(csv, row) {return csv += row.join(",") + "\n"}, "")});
    destinationFolderForCSV.createFile(spreadsheet.title + ".csv", csv, MimeType.CSV)
    sourceFile.setTrashed(true);
  }
}