Google Script timeout after 6 min

user_15948349 0
Viewed 157

I want to list all the folderName and their folderID present in a team Drive(more than 3000 folders). I am using speedsheet and running following code in script-

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('folder ID');
    var contents = folder.getFolders();

    var cnt = 0;
    var folder;

    while (contents.hasNext()) {
        var folder = contents.next();
        cnt++;

           data = [
                folder.getName(),
                folder.getId(),
            ];

            sheet.appendRow(data);
    };
}

But this is getting Error Exceeded maximum execution time which is 6 min by default.

I tried adding triggers from script app, but after triggering it get start from beginning and script still ends after 6min.

How to add a triggers which starts from where it left?

google-drive-shared-drive google-drive-api google-apps-script google-sheets google-api
1 Answers

Answer:

The slow part of this script is the repeated call to sheet.appendRow(). You can speed this up by pushing the results to an array and setting the values at the end of the loop, rather than appending a row on each iteration of the while loop.

More Information:

Using the built-in services such as SpreadsheetApp often can be slow when making many changes to a sheet in a short space of time. You can combat this by minimising the number of calls to the built-in Apps Script services as possible, relying on pure JavaScript to do your processing.

Code Change:

function listFilesInFolder(folderName) {
   const sheet = SpreadsheetApp.getActiveSheet()

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

  let cnt = 0
  let data = [['Name','File-Id']]

  while (contents.hasNext()) {
    folder = contents.next()
    cnt++

    data.push([
      folder.getName(),
      folder.getId(),
    ])
  }
  sheet.insertRows(sheet.getMaxRows(), data.length)
  sheet.getRange(2, 1, data.length, 2).setValues(data)
}

Code changes:

  • data is declared as an array initialised with the header row, as opposed to appending it directly to the sheet
  • On each iteration of the loop, the current folder's name and ID is appended to the data array as a new row of data.
  • After all folders have been looped through, the number of rows in the sheet is extended by the number of rows in data so to not hit an out of bounds error
  • All rows inside data are added to the sheet using setValues().

In my test environment, I had the following set up:

  • Drive folder containing 3424 folders

Using the appendRow() method inside the while loop, execution took 1105.256 seconds (or 18 minutes)

Using push() with the .setValues() method outside the loop, execution took 4.478 seconds.

References:

user_115514680
Related