将驱动器文件夹中的所有文件从图纸转换为CSV并添加到新文件夹 [英] Converting all files in drive folder from sheets to CSV and add to new folder

问题描述

我有:

• .xlsx文件每天自动导入到Google驱动器文件夹

我想要:

• 每个文件转换为CSV并保存到其他文件夹
• 每个文件转换为Google表格并保存到其他文件夹
• 完成处理后删除的原始xlsx文件

当前我的流程是这样:

• 将xlsx转换为2个新文件:CSV& Google表格
• 将CSV保存到CSV文件夹
• 将Google表格保存到表格文件夹

我最初使用的是 https://ctrlq.org/code/20248-convert-excel-to-csv 教程转换为CSV，直到我意识到它已将每个.xlsx工作表的无标题"副本作为Drive电子表格保存到我的根文件夹中.我不知道如何为那些没有标题的人分配标题或文件夹位置.能够做到这一点还将解决我眼前的问题.

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

如果我正确解析了CSV，则最终会在根文件夹中出现无标题"图纸，如果我正确解析了图纸"，则最终会导致CSV损坏.我想要结果:

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在指定文件夹中转换为CSV
• xlsx转换为指定文件夹中的Google表格
• xlsx一旦完成处理就从驱动器中删除

推荐答案

• 您要将特定文件夹中的XLSX文件转换为Google Spreadsheet.
• 您要将转换后的电子表格放入特定的文件夹.
• 您想通过修改脚本来实现.

如果我的理解是正确的，那么该修改如何?请认为这只是几个答案之一.

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

• 为了检索MimeType.MICROSOFT_EXCEL和MimeType.MICROSOFT_EXCEL_LEGACY的文件，我使用了searchFiles().
• 为了将XLSX文件转换为Google Spreadsheet并将其放置到特定的文件夹中，我使用了Drive.Files.copy().
• 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().

使用此功能时，请确认是否在高级Google服务中启用了Drive API.

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

注意:

• 如果要直接删除XLSX文件，可以使用Drive.Files.remove(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).
• searchFiles()
• 文件:复制
• 高级Google服务
• searchFiles()
• Files: copy
• Advanced Google services
• 您要从XLSX文件转换为CSV和Google Spreadsheet文件.
• 您要将转换后的XLSX文件和CSV文件放入每个文件夹中.

在这种情况下，修改后的脚本如下.

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

这篇关于将驱动器文件夹中的所有文件从图纸转换为CSV并添加到新文件夹的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！