Google工作表脚本超时错误 [英] 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:

    • 使用云端硬盘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)

        References :

        • setValues(values)

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

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

        通过 @Steve Gon why do you think SetValue is better than 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()"，因为我知道可以将两行的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()的原因.

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