将.XLSX转换为Google Sheet并移动转换后的文件的脚本 [英] Script to convert .XLSX to Google Sheet and move converted file

问题描述

我知道可以使用脚本和驱动器API将excel文件转换为Google表格，但是我正在寻找脚本来转换excel表格并将转换后的文件移动到其他文件夹。

I know it is possible to convert excel files to Google Sheets using script and drive API, but I'm looking for script to convert the excel sheet and move the converted file to a different folder.

因此所需步骤如下：

1. 将excel（.xls / .xlsx）转换为Google表格


2. 将已转换的文件从FoldarA移至FolderB。


3. 从FolderA删除原始excel文件


4. 希望第3步可以避免这种情况，但要避免复制已经转换的文件。

1. Convert excel (.xls/.xlsx) to Google Sheet from FolderA.
2. Move converted file from FoldarA to FolderB.
3. Delete original excel file from FolderA
4. Hopefully step 3 avoids this, but avoid duplicating already converted file.

Excel文件被粘贴到正在同步的本地文件夹中到Google驱动器，文件不超过3mb。当前脚本如下。这将转换文件，但将其放置在根文件夹中，并且在再次运行脚本时将复制该转换。

The excel files are being pasted into a local folder that is being synced to google drive, and the files are no larger than 3mb. The current script is as follows. This is converting the files but placing them in the root folder, and will duplicate the conversion when the script runs again.

 function importXLS(){
  var files = DriveApp.getFolderById('1hjvNIPgKhp2ZKIC7K2kxvJjfIeEYw4BP').searchFiles('title != "nothing"');
  while(files.hasNext()){
    var xFile = files.next();
    var name = xFile.getName();
    if (name.indexOf('.xlsx')>-1){ 
      var ID = xFile.getId();
      var xBlob = xFile.getBlob();
      var newFile = { title : name+'_converted',
                     key : ID
                    }
      file = Drive.Files.insert(newFile, xBlob, {
        convert: true
      });
    }
  }
}

推荐答案

• 您想要将转换后的Google Spreadsheet文件创建为 FolderB。


• 您要在文件之后删除 FolderA中的XLSX文件


• 您想使用Google Apps脚本实现以上目标。



如果我理解正确，该修改如何？在此修改中，我修改了您的脚本。

If my understanding correct, how about this modification? In this modification, I modified your script.



• 您可以使用请求正文中的 parents 属性直接将文件创建到特定文件夹。


• 您可以使用 Drive.Files.remove（fileId）。


• You can directly create the file to the specific folder using the property of parents in the request body.
• You can delete the file using Drive.Files.remove(fileId).

function importXLS(){
  var folderBId = "###"; // Added // Please set the folder ID of "FolderB".

  var files = DriveApp.getFolderById('1hjvNIPgKhp2ZKIC7K2kxvJjfIeEYw4BP').searchFiles('title != "nothing"');
  while(files.hasNext()){
    var xFile = files.next();
    var name = xFile.getName();
    if (name.indexOf('.xlsx')>-1){ 
      var ID = xFile.getId();
      var xBlob = xFile.getBlob();
      var newFile = {
        title : name+'_converted',
        parents: [{id: folderBId}] //  Added
      };
      file = Drive.Files.insert(newFile, xBlob, {
        convert: true
      });
      // Drive.Files.remove(ID); // Added // If this line is run, the original XLSX file is removed. So please be careful this.
    }
  }
}





注意：






• 如果XLSX文件数量很大，则执行时间可能超过6分钟。


• 关于 // Drive.Files.remove（ID）; ，运行此脚本时，请小心。因为原始XLSX文件在运行脚本时被完全删除。所以我对此发表了评论。首先，请使用示例文件测试脚本。



Note:

• If the number of XLSX files is large, the execution time might be over 6 minutes.
• About // Drive.Files.remove(ID);, when you run this script, please be careful. Because the original XLSX files are completely deleted when the script is run. So I commented this. At first, please test the script using sample files.


• 文件：插入


• 文件：删除


• Files: insert
• Files: delete

这篇关于将.XLSX转换为Google Sheet并移动转换后的文件的脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！