避免在递归地将 Google 表格转换为 Excel 时超过执行时间

Question

我正在编写一个脚本来浏览Google云端硬盘中的一系列文件夹，复制文件夹结构，并将文件夹中的文件转换为Excel或Word。

有三种可能的选项：谷歌表格文件应转换为Excel;谷歌文档文件到Word;和其他文件应按原样复制。

我正在使用递归函数来探索文件结构。

脚本似乎有效，但我超过了允许的执行时间。分析执行脚本，这似乎是因为将每个Google文件转换为MS-Office文件需要~2秒。

谁能建议：

• 转换文件的更快方法？
• 一种跟踪递归过程在哪里的方法，以便我可以暂停和恢复该过程？（我也希望得到一些关于如何暂停和恢复该过程的建议;-）

抱歉，如果这已经被涵盖，我确实花了一些时间寻找答案，我可以看到类似的问题，但没有我能理解的解决方案。

下面的代码。

function folderRecurse(folderToSearch, folderToSave){
  while (folderToSearch){ // the function has been passed a folder to search inside
    var searchFolderId = folderToSearch.getId(); // get the Id of the folder passed
    var currentFolder = DriveApp.getFolderById(searchFolderId); //open the search folder passed
    var newName = currentFolder.getName(); // get the name of the search folder passed
    var saveFolder = folderToSave.createFolder(newName);// make a copy of the search folder in the backup folder
    var subfolders = currentFolder.getFolders(); //get any subfolders of the folder passed
      while (subfolders.hasNext()){ // if there are subfolders, start again by passing the function the folder to search and the folder to save to
      var subfolder = subfolders.next();
      folderRecurse(subfolder, saveFolder);
    }
    // when there are no more folders left, deal with the files inside the folder
    var folderFiles = folderToSearch.getFiles();
    while (folderFiles.hasNext()){
      // get the file
      var thisFile = folderFiles.next();
      // get the file's name
      var thisFileName = thisFile.getName();
      //test the file type
      var fileType = thisFile.getMimeType();
      Logger.log(fileType);
      if (fileType == "application/vnd.google-apps.document"){ // this is a google doc-- save it as a word document
        //google docs to word method
      }
      else if (fileType == "application/vnd.google-apps.spreadsheet"){ // this is a spreadsheet -- save it as an excel file
        convertToXlsxAndSave(thisFile,saveFolder)
        Logger.log(thisFile.getName());
      }
      else { // save it as whatever kind of file it is
       thisFile.makeCopy(thisFile.getName(),saveFolder);
      }
    }
    return 0;
  }
  return 0;
}
function convertToXlsxAndSave(thisFile, saveFolder){
  //open the file as an excel sheet
  var url = "https://docs.google.com/feeds/download/spreadsheets/Export?key=" + thisFile.getId() + "&exportFormat=xlsx";
  // set parameters
  var params = {
      method      : "get",
      headers     : {"Authorization": "Bearer " + ScriptApp.getOAuthToken()},
      muteHttpExceptions: true
    };
  // get the binary
  var blob = UrlFetchApp.fetch(url, params).getBlob();
  // name the binary with the fileName
  blob.setName(thisFile.getName() + ".xlsx");
  // save the binary into the proper folder
  saveFolder.createFile(blob);
}
function convertToDocxAndSave(thisFile, saveFolder){
  //open the file as a word doc sheet
  var url = "https://docs.google.com/feeds/download/documents/export/Export?id=" + thisFile.getId() + "&exportFormat=docx";
  // set parameters
  var params = {
      method      : "get",
      headers     : {"Authorization": "Bearer " + ScriptApp.getOAuthToken()},
      muteHttpExceptions: true
    };
  // get the binary
  var blob = UrlFetchApp.fetch(url, params).getBlob();
  // name the binary with the fileName
  blob.setName(thisFile.getName() + ".docx");
  // save the binary into the proper folder
  saveFolder.createFile(blob);
}

function createMsOfficeVersions() {
  // this function works through the files in the folders enumerated below, and creates MS Office versions. Eg Google Sheets convert to Excel files, Google Docs to Word files. The files are stored in a folder for downloading to AC.
  // create a date-stamped folder within the backups folder
  var formattedDate = Utilities.formatDate(new Date(), "GMT+12", "dd MMMM yyyy");
  var backupLabel = "HAMP Survey MS Office backup versions " + formattedDate;
  // get the folder Ms Office versions
  var backupDir = DriveApp.getFolderById('XXXXXXX');
  // make a new date-stamped folder to save the files into
  var thisBackup = backupDir.createFolder(backupLabel);
  // get the Survey 2015-2016 folder and subfolders
  var hampSurveyFolder = DriveApp.getFolderById('XXXXXXXXX');
  // loop through all the folders. For each, create a new folder inside the backup folder, grab all the files, test if they are docs or spreadsheets, copy to the new folder. if they are neither, copy as is.
  folderRecurse(hampSurveyFolder,thisBackup);
}

Answer 1

如果 Google 云端硬盘文件夹包含大量文件，则脚本可能会超时。应改用基于时间的触发器，该触发器每 10 分钟运行一次并转换文件，同时文件迭代器应存储为属性，以便从之前中断的位置继续。

function myFunction() {
  var props = PropertiesService.getScriptProperties();
  var token = props.getProperty('TOKEN');
  var files = continuationToken ? DriveApp.continueFileIterator(token) : DriveApp.getFiles();
  var start = Date.now();
  while (files.hasNext() && (Date.now() - start < 5*60*1000)) {
    var file = files.next();    
    // convert files    
  }
  if (files.hasNext()) {
    props.setProperty("TOKEN", files.getContinuationToken());
  } else {
    props.deleteProperty("TOKEN");
  }
}