Google App脚本的解决方法“例外:FILENAME.csv超过了最大文件大小"? [英] Workaround solutions to Google App Script "Exception: FILENAME.csv exceeds the maxmium file size"?

问题描述

我正在构建一个Google App Maker应用程序，该应用程序将用户上传的Excel CSV电子表格文件作为输入.我曾想过多种可能的解决方案来从此文件读取数据，但是每次遇到此错误:"Exception:FILENAME.csv超出了最大文件大小".我尝试过通过parseCSV()将数据提取到Google Cloud SQL，通过.getBlob().getDataAsString()作为一个字符串读入并用"\ n"分割，然后将所有数据写入Google Docs并尝试读取从那里.但是，所有这些方法都导致了相同的错误.

I am building a Google App Maker application that takes a user-uploaded Excel CSV spreadsheet file as input. I have thought of multiple, possible solutions to reading the data from this file, but I have encountered this error: "Exception: FILENAME.csv exceeds the maximum file size" each time. I have tried extracting the data via parseCSV() to Google Cloud SQL, reading in as one string via .getBlob().getDataAsString() and splitting it by "\n," and writing all the data to Google Docs and trying to read it from there. However, all of these methods have resulted in the same error.

是否有解决此最大文件大小问题的解决方法?

Is there any workaround solution to solving this maximum file size problem?

我曾考虑过将文件拆分为较小的CSV文件，但是不确定如何执行此操作.

I have thought of splitting the file into smaller CSV files, but I am unsure of how to do this.

推荐答案

您要将大型CSV文件转换为拆分的电子表格.如果我的理解是正确的，该解决方法如何?

You want to convert the large CSV file to the split Spreadsheet. If my understanding is correct, how about this workaround?

1. 将如此大的CSV文件转换为电子表格时，由于单元格总数和文件大小的原因，它无法直接转换为电子表格.而且，当尝试分割大文件时，它也无法执行此操作，因为可以在GAS上使用的Blob小于50 MB(52,428,800字节).

1. When such large CSV file is converted to Spreadsheet, it cannot directly convert to Spreadsheet, because of both the total number of cells and the size of file. And also when the large file is tried to split, it cannot do it because the blob which can be used at GAS is less than 50 MB (52,428,800 bytes).

• 为了分割如此大的文件，它使用部分下载"个文件.在Drive API中获取.

• In order to split such large file, it uses "Partial download" of files.get in Drive API.

在我的环境中，当此示例脚本使用大小为100 MB的CSV文件时，将文件拆分为10 MB时，将块转换为电子表格大约需要65秒.在这种情况下，当CSV文件完全转换后，可以认为执行GAS将超过限制时间(6分钟).

In my environment, when a CSV file with the size of 100 MB is used for this sample script, when the file is split by 10 MB, about 65 seconds was required to convert a chunk to Spreadsheet. In this case, when the CSV file is completely converted, it is considered that it will be over the limitation time (6 min) for executing GAS.

• 为避免这种情况，需要实现从大型CSV文件到多个电子表格的可恢复转换.

准备:

要使用此示例脚本，请在高级Google服务和API控制台中启用Drive API.

Prepare :

In order to use this sample script, please enable Drive API at Advanced Google Services and API console.

• 在脚本编辑器上
  • 资源->高级Google服务
  • 打开Drive API v2
  • On script editor
    • Resources -> Advanced Google Services
    • Turn on Drive API v2
    • 在脚本编辑器上
      • 资源-> Cloud Platform项目
      • View API控制台
      • 在入门"中，单击启用API"并获取凭据(例如密钥).
      • 在左侧，单击库.
      • 在搜索API和服务，输入驱动器".然后点击Drive API.
      • 单击启用"按钮.
      • 如果已启用API，请不要关闭.
      • On script editor
        • Resources -> Cloud Platform project
        • View API console
        • At Getting started, click Enable APIs and get credentials like keys.
        • At left side, click Library.
        • At Search for APIs & services, input "Drive". And click Drive API.
        • Click Enable button.
        • If API has already been enabled, please don't turn off.

        function createSplitSpreadsheet(obj) {
          var accessToken = ScriptApp.getOAuthToken();
          var baseUrl = "https://www.googleapis.com/drive/v3/files/";
        
          // Retrieve file size.
          var url1 = baseUrl + obj.fileId + "?fields=size";
          var params1 = {
            method: "get",
            headers: {Authorization: "Bearer " + accessToken},
          };
          var fileSize = Number(JSON.parse(UrlFetchApp.fetch(url1, {headers: {Authorization: "Bearer " + accessToken}}).getContentText()).size);
        
          // Calculate number of output files.
          if (obj.files == null) {
            obj.number = 1;
            obj.start = 0;
          }
          var start = obj.start;
          var end = start + obj.chunk;
          var useFileSize = fileSize - start;
          f = Math.floor(useFileSize / obj.chunk);
          f = useFileSize % obj.chunk > 0 ? f + 1 : f;
          if (f < obj.files || obj.files == null) {
            obj.files = f;
          }
        
          // Split large file by chunk size (bytes).
          var url2 = baseUrl + obj.fileId + "?alt=media";
          var i;
          for (i = 0; i < obj.files; i++) {
            var params = {
              method: "get",
              headers: {
                Authorization: "Bearer " + accessToken,
                Range: "bytes=" + start + "-" + end,
              },
            };
            var res = UrlFetchApp.fetch(url2, params).getContentText();
            var e = res.lastIndexOf("\n");
            start += e + 1;
            end = start + obj.chunk;
            Drive.Files.insert(
              {mimeType: MimeType.GOOGLE_SHEETS, title: obj.fileName + (i + obj.number)},
              Utilities.newBlob(res.substr(0, e), MimeType.CSV)
            );
          }
        
          // Return next start value if there is a next chunk for the resume.
          if (start < fileSize) {
            return {nextStart: start, nextNumber: i + obj.number};
          } else {
            return null;
          }
        }
        
        // Please run this function.
        function main() {
            var obj = {
                fileId: "#####", // File ID of the large CSV file.
                chunk: 10485760, // 10MB Please modify this for your situation.
                files: 3, // Please input the number of files you want to convert.
                start: 0,
                fileName: "sample",
                number: 1, // Counter of output files. Please input this as a next number.
            };
            var nextStart = createSplitSpreadsheet(obj);
            Logger.log(nextStart);
        }
        

        用法:

        使用此功能时，请根据情况修改main()中的obj，然后运行main().一个示例案例如下.

        Usage :

        When you use this, please modify obj in main() for your situation, and run main(). A sample case is as follows.

        假设如下.

        • 您要将大小为100 MB的CSV文件转换为10个电子表格.
        • 一个块的大小为10 MB.
        • CSV文件每3个处理一次.

        在此示例情况下，每个obj如下.请在每次运行时输入每个obj.

        In this sample case, each obj is as follows. Please input each obj at each run.

        1. var obj = {fileId: "#####", chunk: 10485760, files: 3, start: 0, fileName: "sample", number: 1}
           从createSplitSpreadsheet()返回
           • {"nextStart": ### nextStart2 ###, "nextNumber": 4}.
        1. var obj = {fileId: "#####", chunk: 10485760, files: 3, start: 0, fileName: "sample", number: 1}
           • {"nextStart": ### nextStart2 ###, "nextNumber": 4} is returned from createSplitSpreadsheet().
        • {"nextStart": ### nextStart3 ###, "nextNumber": 7}是从createSplitSpreadsheet()返回的.
        • {"nextStart": ### nextStart3 ###, "nextNumber": 7} is returned from createSplitSpreadsheet().
        • {"nextStart": ### nextStart4 ###, "nextNumber": 10}是从createSplitSpreadsheet()返回的.
        • {"nextStart": ### nextStart4 ###, "nextNumber": 10} is returned from createSplitSpreadsheet().
        • null是从createSplitSpreadsheet()返回的.
        • null is returned from createSplitSpreadsheet().

        通过此流程，将从CSV文件中创建10个电子表格，大小为100 MB.

        By this flow, 10 spreadsheets are created from the CSV file with the size of 100 MB.

        如果在obj中的files中使用了null，则会自动计算files.但是在这种情况下，执行GAS的限制时间可能会结束.请注意这一点.

        If null is used for files in obj, files is automatically calculated. But in this case, the limitation time for executing GAS may be over. Please be careful this.

        • 部分下载
        • 高级Google服务
        • 驱动器API
        • Partial download
        • Advanced Google Services
        • Drive API

        如果这不是您想要的，对不起.

        If this was not what you want, I'm sorry.

        这篇关于Google App脚本的解决方法“例外:FILENAME.csv超过了最大文件大小"?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！