将Excel文件转换为Google Spreadsheet并自动替换现有的电子表格文件 [英] Convert excel files to Google Spreadsheet and replace existing spreadsheet files automatically

问题描述

提供的代码将文件从Excel转换为Google Sheet. 此处的代码，但是它不会覆盖/替换目标文件夹中的当前现有电子表格文件. 是否可以完全转换所有内容，包括子文件夹中的所有内容，并替换具有相同名称的任何现有Google Spreadsheet文件?

The code provided convert files from Excel to Google Sheet. The code from here but it does not overwrite/replace current existing spreadsheet files in the destination folder. Is it possible to convert everything including the ones inside the subfolders altogether and replace any existing Google Spreadsheet files with the same name?

function convertCollection1() 
{
  var user = Session.getActiveUser(); // Used for ownership testing.1aJcbdGhwliTs_CZ-3ZUvQmGRDzBM7fv9
  var origin = DriveApp.getFolderById("1dPsDfoqMQLCokZK4RN0C0VRzaRATr9AN");
  var dest = DriveApp.getFolderById("1M6lDfc_xEkR4w61pUOG4P5AXmSGF1hGy");

  // Index the filenames of owned Google Sheets files as object keys (which are hashed).
  // This avoids needing to search and do multiple string comparisons.
  // It takes around 100-200 ms per iteration to advance the iterator, check if the file
  // should be cached, and insert the key-value pair. Depending on the magnitude of
  // the task, this may need to be done separately, and loaded from a storage device instead.
  // Note that there are quota limits on queries per second - 1000 per 100 sec:
  // If the sequence is too large and the loop too fast, Utilities.sleep() usage will be needed.
  var gsi = dest.getFilesByType(MimeType.GOOGLE_SHEETS), gsNames = {};
  while (gsi.hasNext())
  {
    var file = gsi.next();
    if(file.getOwner().getEmail() == user.getEmail())
      gsNames[file.getName()] = true;

    Logger.log(JSON.stringify(gsNames))
  }

  // Find and convert any unconverted .xls, .xlsx files in the given directories.
  var exceltypes = [MimeType.MICROSOFT_EXCEL, MimeType.MICROSOFT_EXCEL_LEGACY];
  for(var mt = 0; mt < exceltypes.length; ++mt)
  {
    var efi = origin.getFilesByType(exceltypes[mt]);
    while (efi.hasNext())
    {
      var file = efi.next();
      // Perform conversions only for owned files that don't have owned gs equivalents.
      // If an excel file does not have gs file with the same name, gsNames[ ... ] will be undefined, and !undefined -> true
      // If an excel file does have a gs file with the same name, gsNames[ ... ] will be true, and !true -> false
      if(file.getOwner().getEmail() == user.getEmail() && !gsNames[file.getName().replace(/\.[^/.]+$/, "")])
      {
        Drive.Files.insert (
          {title: file.getName(), parents: [{"id": dest.getId()}]},
          file.getBlob(),
          {convert: true}
        );
        // Do not convert any more spreadsheets with this same name.
        gsNames[file.getName()] = true;
      }
    }
  }
  Logger.log(JSON.stringify(gsNames))
}

推荐答案

1. 文件夹中有几个子文件夹，其中有Excel文件(文件名扩展名为.xlsx或.xls).
2. 没有子文件夹的文件夹中有电子表格文件(文件名不带.xlsx或.xls扩展名).
3. 您要用从Excel文件转换后的电子表格来覆盖现有的电子表格文件.
4. 电子表格和Excel文件的数量相同.

从您的问题和评论中，我可以像上面那样理解.

From your question and comments, I could understand like above.

首先，我测试了通过批处理请求更新文件.结果，当使用文件Blob进行更新时，批处理请求似乎无法实现文件的更新.关于这一点，如果我找到了解决此问题的方法，我想更新我的答案.

At first, I tested to update files by the batch request. As the result, it seems that the update of file cannot be achieved by the batch request, when the file blob is used for updating. About this, if I found the workaround for this situation, I would like to update my answer.

因此，在此示例脚本中，我针对上述情况提出了使用Advanced Google Services的Drive API的方法.

So in this sample script, I propose the method for using Drive API of Advanced Google Services for above situation.

使用此脚本时，请在Advanced Google Services和API控制台上启用Drive API.您可以在此处查看.

When you use this script, please enable Drive API at Advanced Google Services and API console. You can see about this at here.

此脚本的流程如下.

1. 检索源文件夹和目标文件夹中的文件.
2. 当目标文件夹中存在源文件夹中的文件名时，这些文件将覆盖现有的电子表格文件.
3. 当目标文件夹中不存在源文件夹中的文件名时，这些文件将作为新文件转换为电子表格.

示例脚本:

在运行脚本之前，请先设置sourceFolderId和destinationFolderId.

function myFunction() {
  var sourceFolderId = "###"; // Folder ID including source files.
  var destinationFolderId = "###"; // Folder ID that the converted files are put.

  var getFileIds = function (folder, fileList, q) {
    var files = folder.searchFiles(q);
    while (files.hasNext()) {
      var f = files.next();
      fileList.push({id: f.getId(), fileName: f.getName().split(".")[0].trim()});
    }
    var folders = folder.getFolders();
    while (folders.hasNext()) getFileIds(folders.next(), fileList, q);
    return fileList;
  };
  var sourceFiles = getFileIds(DriveApp.getFolderById(sourceFolderId), [], "mimeType='" + MimeType.MICROSOFT_EXCEL + "' or mimeType='" + MimeType.MICROSOFT_EXCEL_LEGACY + "'");
  var destinationFiles = getFileIds(DriveApp.getFolderById(destinationFolderId), [], "mimeType='" + MimeType.GOOGLE_SHEETS + "'");
  var createFiles = sourceFiles.filter(function(e) {return destinationFiles.every(function(f) {return f.fileName !== e.fileName});});
  var updateFiles = sourceFiles.reduce(function(ar, e) {
    var dst = destinationFiles.filter(function(f) {return f.fileName === e.fileName});
    if (dst.length > 0) {
      e.to = dst[0].id;
      ar.push(e);
    }
    return ar;
  }, []);
  if (createFiles.length > 0) createFiles.forEach(function(e) {Drive.Files.insert({mimeType: MimeType.GOOGLE_SHEETS, parents: [{id: destinationFolderId}], title: e.fileName}, DriveApp.getFileById(e.id))});
  if (updateFiles.length > 0) updateFiles.forEach(function(e) {Drive.Files.update({}, e.to, DriveApp.getFileById(e.id))});
}

注意:

• 当要转换的文件太多时，脚本的执行时间结束了，请分割文件并运行脚本.
• 高级Google服务
• 驱动器API
• Advanced Google Services
• Drive API

这篇关于将Excel文件转换为Google Spreadsheet并自动替换现有的电子表格文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！