如何打开Excel文件，这些文件位于带有谷歌脚本的谷歌驱动器文件夹中? [英] How to open excel files, which are located in a google drive folder with google script?

问题描述

我需要在给定的Google驱动器文件夹中打开Excel文件并读取其数据.为此，我使用了以下代码.

I need to open excel files in given google drive folder and read its data. For that, I used the following code.

  var folders = DriveApp.getFoldersByName("Test Folder");
  var foldersnext = folders.next();
  var files = foldersnext.getFiles(); // get all files from folder

  while(files.hasNext()) {

    var sheets = SpreadsheetApp.openByUrl(files.next().getUrl());   // Line A     

  }

但是它在"A行"中给出了这样的错误；

But it gives an error in "Line A" like this;

缺少文档1LDVkBTnkcY32ni9WoGDn6xHonPOX87ZV(也许是删除，或者您没有读取权限?)

Document 1LDVkBTnkcY32ni9WoGDn6xHonPOX87ZV is missing (perhaps it was deleted, or you don't have read access?)

但是当使用记录文件ID时，

But when Log the IDs of the files using,

var selectedFile = files.next();
Logger.log(selectedFile.getId()); 

它给了我预期的结果.因此，我认为错误在于将文件转换为excel文件.请给我一个解决方案，以在给定文件夹中打开多个excel文件并读取其数据...

It gives me the expecting result. So, I think the error is in converting the file to excel file. Please give me a solution to open multiple excel files in a given folder and read its data...

该项目具有以下范围

• https://www.googleapis.com/auth/drive.readonly
• https://www.googleapis.com/auth/spreadsheets

推荐答案

• 您要从Google云端硬盘上的Excel文件中检索值.
• Excel文件放在测试文件夹"的文件夹中.

如果我的理解是正确的，那么该修改如何?

If my understanding is correct, how about this modification?

• 为了从Excel文件中检索值，首先，需要将Excel文件转换为Google Spreadsheet.转换后，可以通过Spreadsheet Service检索值.
  • 认为错误的原因是由于电子表格服务试图打开Excel文件.

  运行脚本时，请按以下说明在高级Google服务中启用Drive API.此修改后的脚本使用了高级Google服务的Drive API.

  When you run the script, please enable Drive API at Advanced Google Services as follows. This modified script used Drive API of Advanced Google Services.

  • 在脚本编辑器上
    • 资源->高级Google服务
    • 打开Drive API v2

    请按如下所示修改脚本.

    Please modify your script as follows.

    while(files.hasNext()) {
    
      var sheets = SpreadsheetApp.openByUrl(files.next().getUrl());   // Line A     
    
    }
    

    至:

    while(files.hasNext()) {
      var file = files.next();
      if (file.getMimeType() == MimeType.MICROSOFT_EXCEL || file.getMimeType() == MimeType.MICROSOFT_EXCEL_LEGACY) {
        var resource = {
          title: file.getName(),
          parents: [{id: foldersnext.getId()}],
          mimeType: MimeType.GOOGLE_SHEETS
        };
        var spreadsheet = Drive.Files.copy(resource, file.getId());
        var sheets = SpreadsheetApp.openById(spreadsheet.id);
      }
    }
    

    注意:

    • 如果Excel文件很大，则可能会发生错误.
    • 如果Excel文件数量很多，则可能会发生错误.在这种情况下，请告诉我.
    • 在此示例脚本中，将转换后的电子表格创建到Excel文件的同一文件夹中.如果要在检索值后删除文件，请使用 Drive.Files.remove(fileId).
    • 文件:复制
    • 文件:删除
    • 电子表格服务

    如果我误解了您的问题，而这不是您想要的结果，我深表歉意.

    If I misunderstood your question and this was not the result you want, I apologize.

    从您的评论中发现，我的理解是正确的.因此，作为测试用例，您可以测试以下流程吗?

    From your comment, it was found that my understanding was correct. So as a test case, can you test the following flow?

    1. 创建新文件夹.
    2. 在创建的文件夹中放置一个Excel文件.
       • 请复制文件夹ID.该文件夹ID在脚本中使用.

    运行以下示例脚本.在运行它之前，请设置文件夹ID.

    Run the following sample script. Before run it, please set the folder ID.

    function sample() {
      var folderId = "###"; // Please set the folder ID.
    
      var folder = DriveApp.getFolderById(folderId);
      var files = folder.getFiles();
      while(files.hasNext()) {
        var file = files.next();
        if (file.getMimeType() == MimeType.MICROSOFT_EXCEL || file.getMimeType() == MimeType.MICROSOFT_EXCEL_LEGACY) {
          var resource = {
            title: file.getName(),
            parents: [{id: folderId}],
            mimeType: MimeType.GOOGLE_SHEETS
          };
          var spreadsheet = Drive.Files.copy(resource, file.getId());
          var sheet = SpreadsheetApp.openById(spreadsheet.id).getSheets()[0];
          var value = sheet.getDataRange().getValues();
          Logger.log(value)
        }
      }
    }
    

    • 在脚本编辑器中运行 sample()的功能.
    • 脚本完成后，请检查日志.
      • 如果您可以在日志中看到Excel文件的值，则表明该脚本有效.

      这篇关于如何打开Excel文件，这些文件位于带有谷歌脚本的谷歌驱动器文件夹中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！