Google Apps 脚本创建工作表版本的 excel 文件 [英] Google Apps Script creates sheets version of excel file

问题描述

我正在努力使流程自动化.目前，我正在将 Excel (.xlsx) 文件上传到云端硬盘中的特定文件夹.然后我手动将它们转换为 Google Sheet 文件，并将其移动到单独的文件夹中.我想自动化这个过程.我知道我必须编写脚本来遍历 2 个文件夹以比较并查看哪些文件尚未转换，然后转换在两个位置都找不到的文件.但是，我正在努力寻找实现这一目标的最佳方法.

I am working to automate a process. Currently, I am uploading Excel (.xlsx) files to a specific folder in Drive. I then manually convert them into a Google Sheet file, and move it into a separate folder. I would like to automate this process. I know I would have to write the script to iterate through the 2 folders to compare and see which files have yet to be converted, and then convert those it does not find in both locations. However, I am struggling with the best approach to make this happen.

下面的代码可能在正确的轨道上，也可能不在正确的轨道上，我对此很陌生，真的只是尝试将它们拼凑在一起.任何人的见解将不胜感激.

The code below that may or may not be on the right track, I am rather new at this and really just tried piecing it together. Anyone's insight would be greatly appreciated.

function Excel2Sheets() 
{

  //Logs excel folder and string of files within
  var excelfolder = DriveApp.getFolderById('1JbamZxNhAyZT3OifrIstZKyFF_d257mq');
  var excelfiles = excelfolder.getFiles();

  // Logs sheets folder and string of files within
  var sheetfolder = DriveApp.getFolderById('1y10IwMobCdpQlYwWdveHLzxEz3Xml0Qt');
  var ID = sheetfolder.getId();
  var sheetfiles = sheetfolder.getFiles();
  var MType = MimeType.GOOGLE_SHEETS;

  while (excelfiles.hasNext()) {
    var excelfile = excelfiles.next();
    var excelname = excelfile.getName();

    while (sheetfiles.hasNext()) {
      var sheetfile = sheetfiles.next();
      var sheetname = sheetfile.getName();

      if(sheetname == excelname) {
        break;
      }
      if(sheetfiles.hasNext(0)) {
        var blob = excelfile.getBlob();
        sheetfolder.createFile(excelname, blob, MType);
        break;
      }
    }
  }
}

我也玩过这段代码.谢谢

I have also played around with this code. Thanks

function fileChecker()
{
  try{
    //Establishes Excel Source Folder
    var excelfolder = DriveApp.getFolderById('1JbamZxNhAyZT3OifrIstZKyFF_d257mq');
    //Establishes Sheet Target Folder
    var sheetfolder = DriveApp.getFolderById('1y10IwMobCdpQlYwWdveHLzxEz3Xml0Qt');
    //Establishes Return File Type
    var MType = MimeType.GOOGLE_SHEETS;
    //Gets all files in excel folder
    var excelfiles = excelfolder.getFiles();

    //loop through excel files
    while(excelfiles.hasNext()){
      //Establishes specific excel file  
      var excelfile = excelfiles.next();
      //Checks for file with same name in sheets folder
      var sheetfiles = sheetfolder.getFilesByName(excelfile.getName());
      //Logical Test for file match
      if(sheetfiles.hasNext()){
        //Gets File Name
        var excelname = excelfile.getName();
        //Creates File Blob
        var blob = excelfile.getBlob();  
        // Creates sheet file with given name and data of excel file
        sheetfolder.createFile(excelname, blob, MType);
      }
    } 
  }
  catch(err){
    Logger.log(err.lineNumber + ' - ' + err);
  }
}

推荐答案

你的任何一个代码，如果它们到达 createFile 行，应该抛出这个错误:

Either of your codes, if they were to reach the createFile line, should throw this error:

无效参数:file.contentType

Invalid argument: file.contentType

因为您传递 Blob 而 createFile(name, content, mimetype) 需要一个 String.

because you are passing a Blob while createFile(name, content, mimetype) expects a String.

查看 DriveApp 的参考页面，无疑会注意到 File#getAs(mimetype) 方法，它返回 Blob，以及 Folder#createFile(blob) 方法，并尝试类似:

Reviewing the reference page for DriveApp, one will undoubtedly notice the File#getAs(mimetype) method, which returns Blob, and the Folder#createFile(blob) methods, and try something like:

var gsBlob = excelfile.getAs(MimeType.GOOGLE_SHEETS);
gsfolder.createFile(gsBlob).setName(excelfile.getName());

不过，这也会返回错误:

This too, however, will return an error:

从 application/vnd.openxmlformats-officedocument.spreadsheetml.sheet 转换不支持到 application/vnd.google-apps.spreadsheet.

Converting from application/vnd.openxmlformats-officedocument.spreadsheetml.sheet to application/vnd.google-apps.spreadsheet is not supported.

查看 getAs 方法的文档表明，这通常是不受支持的操作，除非目标 mimetype 是 MimeType.PDF.我的猜测是这是因为 PDF 转换非常简单——它的实现可能使用类似打印"的功能——而电子表格格式转换需要仔细处理公式、图像、图表等.

Looking at the documentation for the getAs method indicates that this is, in general, an unsupported operation unless the destination mimetype is MimeType.PDF. My guess is this is because the PDF conversion is simple enough - its implementation likely uses a "Print"-like functionality - while spreadsheet format conversion requires careful handling of formulas, images, charts, etc.

从过去使用 Google Drive 的经验来看，一般用户都知道使用 Drive 执行 Excel -> Google Sheets 的自动转换的能力是存在的.但是，此功能仅在上传期间可用.从密切相关的问题中，我们观察到我们必须使用 Drive API 高级服务"，而不是更简单的原生 DriveApp.启用高级服务，然后下面的代码片段就可以工作了.请注意，Advanced Drive Service 将文件夹视为具有特定 mimetype 的文件(这就是没有特定于文件夹的方法的原因)，因此对于 Apps Script 环境中的用户来说，同时使用 DriveApp 和 Advanced Service 是最容易的.

From past experiences with Google Drive, the general user knows that the ability to use Drive to perform automatic conversion of Excel -> Google Sheets exists. However, this functionality is only available during upload. Drawing from a closely related question, we observe that we must use the Drive API "Advanced Service", rather than the simpler, native DriveApp. Enable the Advanced Service, and then the following snippet can work. Note that the Advanced Drive Service treats folders as files having a specific mimetype (which is why there are no folder-specific methods), so using both DriveApp and the Advanced Service is easiest for those in the Apps Script environment.

// Convert the user's stored excel files to google spreadsheets based on the specified directories.
// There are quota limits on the maximum conversions per day: consumer @gmail = 250.
function convertExcelToGoogleSheets() 
{
  var user = Session.getActiveUser(); // Used for ownership testing.
  var origin = DriveApp.getFolderById("origin folder id");
  var dest = DriveApp.getFolderById("destination folder id");

  // 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;
  }

  // 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()])
      {
        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;
      }
    }
  }
}

我上面的代码强制执行了一个有点合理的要求，即您关心的文件是您拥有的文件.如果不是这种情况，则建议删除电子邮件检查.请注意不要在一天内转换过多的电子表格.

My code above enforces a somewhat-reasonable requirement that the files you care about are those that you own. If that's not the case, then removal of the email check is advised. Just beware of converting too many spreadsheets in a given day.

如果使用 Apps 脚本中的 高级服务，它由于没有等效的特定 Apps 脚本文档，因此通常有助于查看相关 API 的 Google 的 API 客户端库文档.我个人发现 Python 等价物 最容易使用.

If working with the Advanced Service in Apps Script, it can often be helpful to review Google's API Client Library documentation for the associated API since there is no specific Apps Script documentation equivalent. I personally find the Python equivalent easiest to work with.

这篇关于Google Apps 脚本创建工作表版本的 excel 文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！