Google sheet script timeout error

Question

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.

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.

Thanks

Here's the code I have

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

I updated my answer because I had not shown about the difference of the process time of ``setValues()`` and ``appendRow()`` in my answer. So I added about this. Could you please confirm it? — Tanaike, Jul 01 '18 at 08:07

Tanaike · Accepted Answer · 2018-07-01T08:01:43.670

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.

Pattern 1 :

Use setValues() instead of appendRow().
- The data is created in the while loop. And put the data to Spreadsheet using setValues().

Modified script :

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

Pattern 2 :

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

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.

Modified script :

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

References :

setValues(values)

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

Added :

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().

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.

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().

Sample script using setValues()

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);

Sample script using 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);

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.

This is the reason I proposed setValues().

Tanaike, why do you think SetValue is better than AppendRow? I have had more problems with SetValue on large spreadsheets than with AppendRow. I don't think you addressed the OP: is there "a way to continue from where from the last session"? — Steve Gon, Jul 01 '18 at 00:37
@SteveGon appendRow is only able to add one row at a time, while setValues could add many rows at once. — Rubén, Jul 01 '18 at 01:56
@Steve Gon Thank you for your advice. From your advice, I could update my answer. Could you please confirm it? And my 2 workarounds are two of several answers. So I think that your workaround is also important. — Tanaike, Jul 01 '18 at 08:01
@Steve Gon By the way, in your comments, about ``I have had more problems with SetValue on large spreadsheets than with AppendRow.``, I'm interested in this. Because I think that this information is useful for users who use ``setValues()``. So could you please provide the detail information of ``lots of problems`` and a sample situation including the script to replicate the issue? I think that you are saying about the important issue of ``setValues()``. If your response, it is found that ``setValues()`` cannot be used for OP's situation, I would like to propose the method for using Sheets API. — Tanaike, Jul 01 '18 at 08:01
@Rubén Thank you for your support. I proposed this way because I had the same opinion with you. Because I had not shown about the difference of the process time of ``setValues()`` and ``appendRow()`` in my answer, I added about this. Could you please confirm it? — Tanaike, Jul 01 '18 at 08:01
I think that the characteristic to highlight are the facts that SpreadsheetApp methods are a low slower than JavaScript methods and that appendRow should be used one time for each row while setValues could be use one time for thousands of rows. — Rubén, Jul 01 '18 at 18:12
All good points. What I have experienced with SetValue (although I have to admit, I've never used it to add more than one row!): large volumes of data, like 2000 rows, you will get timeouts. When I switched to AppendRow, the timeouts stopped...but I believe your SetValue with a 2D array is a better solution. Thanks. — Steve Gon, Jul 01 '18 at 23:03
@Steve Gon Thank you for your response. If this was also useful for you, I'm glad. Thank you, too. — Tanaike, Jul 02 '18 at 22:03

score 0 · Answer 2 · answered Jul 01 '18 at 00:32

Try adding this: if (cnt % 500 ===0){SpreadsheetApp.flush();}

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);
            if (cnt % 500 ===0){
              SpreadsheetApp.flush();
            }

    };
};

I have noticed lots of problems when working with large spreadsheets. I am working on a project right now at Google and I hear they are re-writing the way the interpreter/compiler runs.

I got error at calling `SpreadsheetApp.flush()`: `Exception: Service Spreadsheets timed out while accessing document with id` — BingLi224, Mar 06 '23 at 13:49

Google sheet script timeout error

2 Answers2

Pattern 1 :

Modified script :

Pattern 2 :

Modified script :

References :

Added :

Sample script using setValues()

Sample script using appendRow()

Result :

Linked