将 .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) 从 FolderA 转换为 Google Sheet.
2. 将转换后的文件从 FolderA 移动到 FolderB.
3. 从FolderA中删除原始excel文件
4. 希望第 3 步可以避免这种情况，但要避免复制已转换的文件.

excel 文件正在粘贴到同步到谷歌驱动器的本地文件夹中，文件不超过 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 电子表格文件创建到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.
• 文件:插入
• 文件:删除

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