1
votes

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
1
Can I ask you about your situation? 1. The folder has the subfolders and you want to check the files in all subfolders? 2. What is the maximum size of XLSX files? 3. I think that I need might be different from All I want is. How about this?Tanaike
None of the folders have subfolders, just all the files contained within the folder/placed within a new folder from the root. The XLSX files are somewhat standard, each with 21 columns, under 1,000 rows & under 60 KB. I've also updated the language thank you.eherr
Thank you for replying and updating it. You want to use the XLSX files (less than 60 kbytes) of just under a specific folder. I could understand like this. I have one more question. I cannot understand about xlsx data aggregated to last line in sheet file and all data in each file copied after row 12 and saved to the last line in a designated drive spreadsheet. From your script and question, I cannot understand about the process after the values are retrieved from the XLSX file. Can I ask you about this? I apologize for my poor English skill.Tanaike
Thank you for being patient! Actually I didn't include it in my initial code and figured out the aggregation in the last hour, I'll update the question again. Converting my files and placing them in the correct folder is what I need help with.eherr
Thank you for replying. I thought that I could understand about your question. So I proposed a modified script as an answer. Could you please confirm it? If I misunderstood your question and this was not the direction you want, I apologize.Tanaike

1 Answers

1
votes
  • 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:

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