将 excel 文件转换为 Google 电子表格并自动替换现有的电子表格文件

问题描述

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

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

示例脚本:

在运行脚本之前，请设置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 服务
Drive API

问题描述

推荐答案

示例脚本:

注意: