谷歌工作表脚本超时错误 [英] Google sheet script timeout error

问题描述

我正在尝试从图像中获取文件名和 ID，有 3000 多张图像，而且这个数字还会不断增加.

I'm trying to get file names and IDs from images and there are more than 3000 images and the number will just keep adding.

我在网上有一段运行良好的代码.然而问题是脚本在完成获取所有图像之前总是会超时.

I have this piece of code online that works pretty well. However the problem is that the script will always timeout before it can finish getting all the images.

所以我想知道是否有办法从上次会话的位置继续.我知道有很多解决会话超时问题的答案，但我的 javascript 不够熟练，无法弄清楚如何编写它们.

So I'm wondering if there's a way to continue from where from the last session. I know there are many answers addressing session timeout problems but my javascript isn't proficient enough to figure out how to write them.

谢谢

这是我的代码

function listFilesInFolder(folderName) {

   var sheet = SpreadsheetApp.getActiveSheet();
   sheet.appendRow(["Name", "File-Id"]);


//change the folder ID below to reflect your folder's ID (look in the URL when you're in your folder)
    var folder = DriveApp.getFolderById("ID");
    var contents = folder.getFiles();

    var cnt = 0;
    var file;

    while (contents.hasNext()) {
        var file = contents.next();
        cnt++;
            data = [
                file.getName(),
                file.getId(),
            ];
            sheet.appendRow(data);
    };
};

推荐答案

当我看到你的问题时，我想到了 2 个模式.但我认为对于你的情况有几个答案.因此，请将此视为其中的两个.

When I saw your question, I imaged 2 patterns. But I think that there are several answers for your situation. So please think of this as two of them.

• 使用 setValues() 而不是 appendRow().
  • 数据是在while循环中创建的.并使用 setValues() 将数据放入电子表格.
  • Use setValues() instead of appendRow().
    • The data is created in the while loop. And put the data to Spreadsheet using setValues().

    function listFilesInFolder(folderName) {
      var data = [["Name", "File-Id"]];
      var folder = DriveApp.getFolderById("ID"); // Please set this.
      var contents = folder.getFiles();
      while (contents.hasNext()) {
        var file = contents.next();
        data.push([file.getName(), file.getId()]);
      };
      var sheet = SpreadsheetApp.getActiveSheet();
      sheet.getRange(sheet.getLastRow() + 1, 1, data.length, data[0].length).setValues(data); // Modified
    };
    

    模式 2:

    • 使用 Drive API.
      • 使用 Drive API 检索数据.并使用 setValues() 将数据放入电子表格.

      Pattern 2 :

      • Use Drive API.
        • Retrieve the data using Drive API. And put the data to Spreadsheet using setValues().

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

        In order to use this sample script, please enable Drive API at Advanced Google Services and API console. You can see the flow of this at here.

        function listFilesInFolder2(folderName) {
          var folderId = "ID"; // Please set this.
          var data = [["Name", "File-Id"]];
          var params = {
            'pageSize': 1000,
            'q': "'" + folderId + "' in parents and mimeType!='" + MimeType.FOLDER + "'",
            'fields': "nextPageToken,items(id,title)"
          };
          do {
            var r = Drive.Files.list(params);
            params["pageToken"] = r.nextPageToken;
            r.items.forEach(function(e) {data.push([e.title, e.id])});
          } while(r.nextPageToken);
          var sheet = SpreadsheetApp.getActiveSheet();
          sheet.getRange(sheet.getLastRow() + 1, 1, data.length, data[0].length).setValues(data); // Modified
        };
        

        参考:

        • setValues(values)

        如果这些不是您想要的，我很抱歉.

        If these were not what you want, I'm sorry.

        根据 @Steve Gon 的评论 你为什么认为 SetValue 比 AppendRow 好?，我添加了我建议 setValues() 而不是 appendRow() 的原因.

        By the comment of @Steve Gon why do you think SetValue is better than AppendRow?, I added the reason I propose setValues() instead of appendRow().

        在这个问题中，当一个文件夹中的许多文件(超过 3000 个)被检索并放入电子表格时，处理时间超过 6 分钟，这是限制.如果我的理解是正确的，我认为 OP 想解决这个问题.尽管我建议使用setValues()"，因为我知道可以将 2 列的 10000 行的值放入工作表中而不会出现错误，但我没有在我的答案中显示处理时间的差异.我真的很抱歉.所以我加了这个.

        In this issue, when many files (more than 3000) in a folder are retrieved and put to the spreadsheet, the process time is over 6 minutes which is the limitation. If my understanding is correct, I think that OP wants to solve this issue. Although I proposes to use "setValues()" because I had known that the values of 10000 rows with the 2 columns can be put to a sheet without errors using it, I had not shown about the difference of the process time in my answer. I'm really sorry for this. So I added this.

        我为这种情况准备了 2 个示例脚本.主要工作是将 10,000 行 2 列放在一张纸上.这是大于 3,000 的问题.那时测量了setValues()和appendRow()的处理时间.

        I prepared 2 sample scripts for this situation. The main work is to put 10,000 rows with 2 columns to a sheet. This is larger than 3,000 of the issue. At that time, it measures the process time of setValues() and appendRow().

        var label = "sampleLabel"
        console.time(label);
        var rows = 10000;
        var values = [];
        for (var i = 0; i < rows; i++){
          values.push(["Name", "File-Id"]);
        }
        var ss = SpreadsheetApp.getActiveSheet();
        ss.getRange(ss.getLastRow() + 1, 1, values.length, values[0].length).setValues(values);
        console.timeEnd(label);
        

        使用 appendRow() 的示例脚本

        var label = "sampleLabel"
        console.time(label);
        var rows = 10000;
        var ss = SpreadsheetApp.getActiveSheet();
        for (var i = 0; i < rows; i++){
          ss.appendRow(["Name", "File-Id"]);
        }
        console.timeEnd(label);
        

        结果:

        • 当使用 setValues() 时，处理时间为 2 s - 3 s.而且没有错误.
        • 当使用appendRow()时，当值放到1400 - 1500行时，执行时间超过6分钟.虽然试了好几次，还是放不下3000行.

        Result :

        • When setValues() was used, the process time was 2 s - 3 s. And there are no errors.
        • When appendRow() was used, when the value was put to 1400 - 1500 rows, the execution time was over 6 minutes. Although I had tried several times, 3000 rows couldn't be put.

        这就是我提出setValues()的原因.

        这篇关于谷歌工作表脚本超时错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！