問題描述

提供的代碼將文件從 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))
}

推薦答案

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.

所以在這個示例腳本中，我針對上述情況提出了使用高級 Google 服務的 Drive API 的方法.

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

使用此腳本時，請在高級 Google 服務和 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 服務
• Drive API

這篇關于將 excel 文件轉換為 Google 電子表格并自動替換現有的電子表格文件的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！

【網站聲明】本站部分內容來源于互聯網,旨在幫助大家更快的解決問題，如果有圖片或者內容侵犯了您的權益，請聯系我們刪除處理，感謝您的支持！