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

Answer 1

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

Answer 2

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.